Home » RDBMS Server » Performance Tuning » Execution Plan of Query For Second Name Oracle is not using Index
Execution Plan of Query For Second Name Oracle is not using Index [message #65689] Mon, 06 December 2004 01:06 Go to next message
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 Go to previous message
Frank
Messages: 7880
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
Previous Topic: For second Name oracle is not using function Index
Next Topic: io waits on IBM FastT with Oracle 81
Goto Forum:
  


Current Time: Sun Sep 20 15:30:06 CDT 2020