Solved

plsql - table acces full

Posted on 2014-04-23
2
382 Views
Last Modified: 2014-04-25
hdhgds,

Query 1
SELECT TC.OBJID,
       TC.PHONE,
       TC.X_CONTACT_ADD2ADDRESS,
       TC.S_LAST_NAME,
       TC.S_FIRST_NAME,
       TC.X_CONTACT_STATUS,
       TC.X_DOC_TYPE,
       X_DOC_NUM,
       trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8))
FROM SA.TABLE_CONTACT TC, SA.TABLE_CONTACT_ROLE CR, TABLE_SITE TS
WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
   AND CR.CONTACT_ROLE2SITE = TS.OBJID
   AND TC.S_FIRST_NAME LIKE 'JOHAN%'
   AND TC.S_LAST_NAME LIKE 'TOLENTINO%'
   AND trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8)) = 'Postpago'

Open in new window

see imagen 1

Query 2
SELECT TC.OBJID,
       TC.PHONE,
       TC.X_CONTACT_ADD2ADDRESS,
       TC.S_LAST_NAME,
       TC.S_FIRST_NAME,
       TC.X_CONTACT_STATUS,
       TC.X_DOC_TYPE,
       X_DOC_NUM,
       trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8))
  FROM SA.TABLE_CONTACT TC, SA.TABLE_CONTACT_ROLE CR, TABLE_SITE TS
WHERE CR.CONTACT_ROLE2CONTACT = TC.OBJID
   AND CR.CONTACT_ROLE2SITE = TS.OBJID
  AND TRIM(RTRIM(TC.S_FIRST_NAME)) = 'JOHAN STIVE'
   AND TRIM(RTRIM(TC.S_LAST_NAME)) = 'TOLENTINO RAMIREZ'
   AND trim(SUBSTR(TS.X_CATEGORY_CUSTOMER, 1, 8)) = 'Postpago'

Open in new window

1. Searches should be accurate so as not LIKE was used,
2. Also indicates that there are blanks so we are using in the development environment TRIM, RTRIM, LTRIM in the database for S_FIRST_NAME and S_LAST_NAME column, however analyzing execution plans shows a TABLE ACCESS FULL, there any other way?
image01.jpg
image02.jpg
0
Comment
Question by:enrique_aeo
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40018757
Best option is to fix your data and apps to not allow leading or trailing spaces.

If you cannot fix the apps a simple database trigger to trim the columns on insert or update would work.

Functions on an indexed column negate the ability for Oracle to use the index.

If you MUST use functions around the column, create a function-based index on the column.

http://docs.oracle.com/cd/E11882_01/appdev.112/e10471/adfns_indexes.htm#ADFNS00505

LTRIM(RTRIM('   hello   '))  is the same as TRIM('   hello   ')

A like where the wildcard is on the right and you have a decent number of characters on the left, an index can be used when one is available.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
Oracle Syntax 8 56
Oracle - Query Insert and Update multiple tables 5 58
Schema creation in Oracle12c 6 39
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now