| Execution Plan of Query For Second Name Oracle is not using Index [message #65689] |
Mon, 06 December 2004 01:06  |
Gurinder Mann
Messages: 7
Registered: October 2004
|
Junior Member |
|
|
Dear Sir/Madam
i am sending the execution plan of the query
MY INDEX on column is
CREATE INDEX PMOPDetails_Name ON PMOPDetails(UPPER(Name));
/*
AND PARAMETER QUERY_REWRITE_ENABLED=TRUE
*/
SQL> SET AUTOT TRACEONLY EXP STAT
SQL> SELECT
2 opno,Name,Ward,Address1 Home, Address3 Atol ,
3 Place Island,cancel
4 FROM
5 PMOPDetails
6 WHERE
7 Upper(Name) LIKE 'AH% HUS%';
207 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=979 Card=7895 Bytes=
386855)
1 0 TABLE ACCESS (FULL) OF 'PMOPDETAILS' (Cost=979 Card=7895 B
ytes=386855)
Statistics
----------------------------------------------------------
1019 recursive calls
6 db block gets
6780 consistent gets
6526 physical reads
0 redo size
20423 bytes sent via SQL*Net to client
2206 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
207 rows processed
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 opno,Name,Ward,Address1 Home, Address3 Atol ,
3 Place Island,cancel
4 FROM
5 PMOPDetails
6 WHERE
7* Upper(Name) LIKE 'AS% MOH_%'
SQL> /
152 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=7895 Bytes=3
86855)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PMOPDETAILS' (Cost=10 Ca
rd=7895 Bytes=386855)
2 1 INDEX (RANGE SCAN) OF 'PMOPDETAILS_NAME' (NON-UNIQUE) (C
ost=2 Card=7895)
Statistics
----------------------------------------------------------
63 recursive calls
0 db block gets
192 consistent gets
11 physical reads
0 redo size
14637 bytes sent via SQL*Net to client
1813 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
152 rows processed
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 opno,Name,Ward,Address1 Home, Address3 Atol ,
3 Place Island,cancel
4 FROM
5 PMOPDetails
6 WHERE
7* Upper(Name) LIKE 'SHIF%'
SQL> /
359 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=7895 Bytes=3
86855)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PMOPDETAILS' (Cost=10 Ca
rd=7895 Bytes=386855)
2 1 INDEX (RANGE SCAN) OF 'PMOPDETAILS_NAME' (NON-UNIQUE) (C
ost=2 Card=7895)
Statistics
----------------------------------------------------------
63 recursive calls
0 db block gets
405 consistent gets
5 physical reads
0 redo size
34209 bytes sent via SQL*Net to client
3516 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
359 rows processed
SQL> SPOOL OFF
|
|
|
|
| Re: Execution Plan of Query For Second Name Oracle is not using Index [message #65690 is a reply to message #65689] |
Mon, 06 December 2004 03:02  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Cost based optimizer at work here.
You have more rows where name like AH% then AS%
To be able to use the index the opimizer can only use the portion BEFORE the first %.
Whatever you put behind that first % will not affect the execution path.
The optimizer decided there are so many names starting with AH%, that it is cheaper to FTS than to use the index.
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
