Tuning a query which uses like

[Vinodpillai]

The below query when fired with a like on a 800K records table takes 90 sec and fetches 2000 rec. Even without hint takes the same time
SELECT /*+ index (A DDUP_INDEX_NEW) */ A.*
FROM NAPS_DDUP_PER_INDX_NEW A
WHERE A.C_REF_NO <> '0713403128312'
AND A.C_STDCODE_RES = '0671'
AND (A.C_ADDRESS_RES like '%ARUNODAYA%' OR A.C_ADDRESS_RES like '%NAGAR%')

SELECT STATEMENT Optimizer=CHOOSE (Cost=46 Card=7020 Bytes=2
597400)

TABLE ACCESS (BY INDEX ROWID) OF 'NAPS_DDUP_PER_INDX_NEW'
(Cost=46 Card=7020 Bytes=2597400)

INDEX (FULL SCAN) OF 'DDUP_INDEX_NEW' (NON-UNIQUE) (Cost
=26 Card=74460)



Whereas without the like it fetches 9000 records and takes 57 sec.
SELECT /*+ index (A DDUP_INDEX_NEW) */ A.*
FROM NAPS_DDUP_PER_INDX_NEW A
WHERE A.C_REF_NO <> '0713403128312'
AND A.C_STDCODE_RES = '0671'

How can i tune this to exeute in atleast 7-8 sec

 

[sem]

DDUP_INDEX_NEW is not quite descriptive name. What fields (with order) does it include?

Regards, Dima

http://seminihin.narod.ru

 

[Vinodpillai]

structure of table is
C_REF_NO NOT NULL VARCHAR2(13)
C_INDEX_ADD VARCHAR2(100
C_INDEX_ADD_WORD VARCHAR2(100
C_INDEX_NAME VARCHAR2(100
C_INDEX_ADD_OFFI VARCHAR2(100
C_INDEX_ADD_WORD_OFFI VARCHAR2(100
C_ADD_WD_RES1 VARCHAR2(45)
C_ADD_WD_RES2 VARCHAR2(45)
C_ADD_WD_RES3 VARCHAR2(45)
C_ADD_WD_OFF1 VARCHAR2(45)
C_ADD_WD_OFF2 VARCHAR2(45)
C_ADD_WD_OFF3 VARCHAR2(45)
C_ADD_NAME1 VARCHAR2(45)
C_ADD_NAME2 VARCHAR2(45)
C_ADD_NAME3 VARCHAR2(45)
C_STDCODE_RES VARCHAR2(10)
C_STDCODE_OFF VARCHAR2(10)
C_RES_PHONE VARCHAR2(20)
C_OFF_PHONE VARCHAR2(20)
D_DOB DATE
C_PAN VARCHAR2(16)
C_SOUND_NAME VARCHAR2(5)
C_SOUND_NAME1 VARCHAR2(5)
C_SOUND_NAME2 VARCHAR2(5)
C_SOUND_NAME3 VARCHAR2(5)
C_ALPHA1_RES VARCHAR2(4)
C_ALPHA4_RES VARCHAR2(4)
C_ALPHA2_RES VARCHAR2(4)
C_ALPHA3_RES VARCHAR2(4)

 

[carp]

A list of columns in the table STILL does not tell us anything about the index!

The problem you are running into is that when you use LIKE with a "%" on the leading edge, the index on that column will not be used. This is because index keys are listed in a specific order. A target value with a leading "%" cannot take advantage of this ordering, so the index is ignored.

If you remove the left-most "%", you will see better performance (for illustration purposes only; I know this would return different results than you want!).

 

[Vinodpillai]

removing the leftmost index will result in a different resultset.

We added an index on the C_ADDRESS_RES field and the query when run uses the index but the time taken is around 120 sec

 

[vivekm]

Add an index to C_STDCODE_RES column and run the query:

SELECT /*+ index (A DDUP_INDEX_NEW) */ A.*
FROM NAPS_DDUP_PER_INDX_NEW A
WHERE A.C_REF_NO <> '0713403128312'
AND A.C_STDCODE_RES = '0671'
;

Then run :
SELECT /*+ index (A DDUP_INDEX_NEW) */ A.*
FROM NAPS_DDUP_PER_INDX_NEW A
WHERE A.C_STDCODE_RES = '0671'

-- Run analyze on the table:
begin
dbms_stats.gather_table_stats(tabname=> 'NAPS_DDUP_PER_INDX_NEW', partname=> NULL , estimate_percent=> 50 );
end;

-- Then run the queries as above again.
Let us know your results from each query.

Vivek

 

[Vinodpillai]

for the first two staements the plan is similar

SELECT STATEMENT, GOAL = CHOOSE 0 02/06/2004 9:13:29 PM SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID 1 0 NUCLEUS NAPS_DDUP_PER_INDX_NEW 02/06/2004 9:13:29 PM TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN 2 1 NUCLEUS PART_DDUP 02/06/2004 9:13:29 PM INDEX RANGE SCAN

after running the analyze the plan is still the same

 

[sem]

Index on C_ADDRESS_RES is COMPLETELY unusefull in such situation; compound one on (C_REF_NO, C_STDCODE_RES) may help.

BTW, I asked about DDUP_INDEX_NEW structure, not about the whole table. And according to the information provided your query must be rejected by compiler because there's no such field as C_ADDRESS_RES in table

Regards, Dima

http://seminihin.narod.ru

 

[Vinodpillai]

Hi Dima
AS of now i have dropped the index on c_address_res. It was put for testing purposes.
I did not list the entire tables , but just to give u an idea listed some fields in the table.
The table has primary key as c_ref_no, indexes on c_stdcode_res, c_index_add.
Tried adding the combi of c_sref_no and c_stdcode_res, but it was not getting used.

Finally now i dropped the statistics for the table and the indexes with it and then re-run the query. This gives me a gain of 5-15 sec for certain application numbers, while for others it remains the same.

Is there any way that this query can be re-written to execute faster. I wanted to have the output returned in atleast 5-10 sec, rather then the present 30-120 sec depending on number of rows returned(3000-15000).

 

[sem]

How did you calculate that times? Is it possible that that 120 sec were spent in displaying 15000 records, not selecting them? In any case 15000 records is quite big amount to fetch.
I'd also suggest you to gather statistics again, because current improvement has been reached due to dropping harmful index, not deleting statistics.

About your original query: you added some index on unknown field(s), added explicit hint to use it and then complained that your query is slow... You definitely don't want your task to be done!

Regards, Dima

http://seminihin.narod.ru

 

[Vinodpillai]

Hi dima
I fired the query without the hint after dropping the unwanted index and then got the response time. Post that dropped the statistics and got the statistics. Now i am monitoring this in production and the response is better then when the table is analyzed.

The query in production is without a hint. Had added the hint while testing and forgot to remove when puting in tek-tips.

Basically this query is a cursor in my stored procedure. After i open the cursor, i process the records one by one. This is the pocess that takes time overall.