| Index not properly used when LIKE operator used [message #65960] |
Sun, 06 February 2005 22:42  |
Sreedhar Reddy
Messages: 55
Registered: January 2002
|
Member |
|
|
SQL> select * from retailer where rt_name='Annapurana Bhandar';
RT_ID RT_NAME
---------- -----------------------------------------------------------------------------------------
K000003529 Annapurana Bhandar
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=79)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'RETAILER' (Cost=2 Card=1
Bytes=79)
2 1 INDEX (RANGE SCAN) OF 'IDX_RTNAME' (NON-UNIQUE) (Cost=1
Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
984 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from retailer where rt_name like 'Ann%';
RT_ID RT_NAME
---------- -----------------------------------------------------------------------------------------
K000000287 Annpurna Store ( Ashram )
K000000392 Annapurna General Store
K000000957 Annapurna Store ( Noida)
K000001075 Annpurna Store ( Laxmi Bai Ng.)
K000001473 Annapurna Bhandar ( Baharampur- Kol.)
K000001566 Annapurna Bhandar ( Hoogly)
K000001672 Annapurna Bhandar ( Goriahat)
K000001933 Annapurna Bh.( Salt Lake)
K000002052 Annapurna Dep.Store( Jay 9th Block)
K000002483 Annapurna Stores
K000002499 Annapurna Bhandar
K000002501 Annapurna Bhandar ( Oil Mill)
K000002543 Annapurna Shoping Hall
K000002610 Annapurna Bhandar
K000002737 Annapurna Store
K000002820 Annapurna Bhandar
K000003021 Annapurna Bhandar
K000003201 Annapurna Store
K000003423 Annapurna Bhandar
K000003452 Annapurna Hotel
K000003529 Annapurana Bhandar
21 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=523 Bytes=413
17)
1 0 TABLE ACCESS (FULL) OF 'RETAILER' (Cost=3 Card=523 Bytes=4
1317)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
2722 bytes sent via SQL*Net to client
510 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
in the first query index IDX_RTNAME is used properly.
in the secord query IDX_RTNAME is not used with LIKE, insteard it has done full access
Check the below query index IDX_DSRNO is used in both the querys ..whatz the reason...
i want my query to user the index while using LIKE
SQL> select * from transactions where dsr_no='06/11/04/NR';
TRN_ID DSR_NO TDATE EMP_ID AG_ID T EUSER
---------- -------------------- --------- ---------- ---------- - ----------------------------------
K000000316 06/11/04/NR 06-NOV-04 K000000101 K000000005 D MKT
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=3 Bytes=285)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TRANSACTIONS' (Co
st=1 Card=3 Bytes=285)
2 1 INDEX (RANGE SCAN) OF 'IDX_DSRNO' (NON-UNIQUE) (Cost=1 C
ard=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
813 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from transactions where dsr_no like '16/%';
TRN_ID DSR_NO TDATE EMP_ID AG_ID T EUSER
---------- -------------------- --------- ---------- ---------- - ----------------------------------
K000000141 16/11/04/AA 24-NOV-04 K000000081 K000000044 D MKT
K000000191 16/11/04/AN 22-NOV-04 K000000119 K000000008 D MKT
K000000286 16/11/04/KB 20-NOV-04 K000000086 K000000005 D MKT
K000000480 16/11/04/KK 20-NOV-04 K000000117 K000000016 D MKT
K000000605 16/11/04/MK 21-NOV-04 K000000122 K000000109 S MANOJ
K000000348 16/11/04/NM 18-NOV-04 K000000146 K000000072 S MKT
K000000387 16/11/04/NR 20-NOV-04 K000000101 K000000139 S MKT
K000000289 16/11/04/PB 16-NOV-04 K000000085 K000000024 D MKT
K000000206 16/11/04/RD 20-NOV-04 K000000118 K000000092 D MKT
K000000625 16/11/04/SB 20-NOV-05 K000000161 K000000005 D SIMMI
K000000254 16/11/04/SC 21-NOV-04 K000000084 K000000017 D MKT
K000000253 16/11/04/SR 20-NOV-04 K000000141 K000000035 D MKT
K000000573 16/12/04/GR 16-DEC-04 K000000147 K000000018 D MANOJ
K000000693 16/12/04/NR 20-DEC-04 K000000101 K000000005 D SIMMI
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=16 Bytes=1520
)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TRANSACTIONS' (Co
st=1 Card=16 Bytes=1520)
2 1 INDEX (RANGE SCAN) OF 'IDX_DSRNO' (NON-UNIQUE) (Cost=1 C
ard=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1710 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
|
|
|
|
|
|
|
|
| Re: Index not properly used when LIKE operator used [message #65963 is a reply to message #65962] |
Sun, 06 February 2005 23:30  |
Shikha
Messages: 36
Registered: January 2002
|
Member |
|
|
Were the tables analyzed? Use DBMS_STATS. That holds
a proper distribution of data. And after that when the
optimizer chooses FULL scan, it IS THE best way out,
Oracle has found for itself. Index utilization may not
always be the best choice.
I wanted to know the # of records, to find if the %age
of results are high.
I am not sure about this behaviour, but surely the
number of records are not enough to truely measure the
index utilization.
In fact, if it is the approximate size that the table
is finally going to be, then no need to have an index
at all.
Regards,
shikha
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
