When tuning a query in the sub 1 second range you should NOT look at response time as an indicator of improvement. You can run a query 10 times and get 10 different sub 1 second response times because its such a small amount of time.
You can also signfiicantly improve the query by reducing logical IOs with little to know response time improvement. Why? Well, its overall stress on the system, but the response of the query in isolation is so fast, that you will not see an improvement in its response time.
Lets look at the query:
AND P.SEARCH_COMPANY_NAME LIKE 'ACME%
like statements can often radically increase LIOs all by themselves. How selective is 'ACME'? If it is not very selective, then you are returning a large number of records. According to your explain plan this is the originating filter(you can tell this by your index range scan). If you look at your explain plan you can see how many rows Oracle is returning from this operation.
There are two things to check here:
1. What happens if you choose a far more selective value than ACME? Do your LIOs drop significantly?
2. Look at column ordering in the indexes. They should be:
(I do not know what table this is from 'HSBC_USER_CATEGORY ')
P.SEARCH_COMPANY_NAME, P.PROFILEDUSERID
E.CUSTOMERID ,.PROFILEDUSERID
You also have the 'HSBC_USER_CATEGORY. If this is in table 'E', the column should come after CUSTOMERID,
If it is in TABLE 'P' it should come first.
The mistake I typically see with index column ordering is that people put the 'join' column first. This is your least selective column. By default your ordering should be
1. Columns with '='
2. Columns with 'IN' and 'OR'
3. Columns with 'like'
4. Join Columns
2 and 3 can flip based on data distribution.
SELECT DISTINCT P.PROFILEDUSERID PROFILEDUSERID,
SEARCH_LAST_NAME,
SEARCH_FIRST_NAME
FROM PROFILEDUSER P ,
EXTENDEDATTRIBUTES E
WHERE P.PROFILEDUSERID = E.PROFILEDUSERID
AND P.SEARCH_COMPANY_NAME LIKE 'ACME%' ESCAPE '/'
AND E.CUSTOMERID = 'ABCDEFGH'
AND HSBC_USER_CATEGORY IN ('VAL1','VAL2')
AND ROWNUM < 150
ORDER BY SEARCH_LAST_NAME,SEARCH_FIRST_NAME
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at
http://www.freelists.org/archives/oracle-l/
FAQ is at
http://www.freelists.org/help/fom-serve/cache/1.html
Received on Fri Aug 06 2004 - 10:41:30 CDT
