Vinodpillai
Technical User
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
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