| Again Execution Plan Of Query For Second Name Oracle is not using INdex [message #65692] |
Tue, 07 December 2004 03:53  |
Gurinder Mann
Messages: 7
Registered: October 2004
|
Junior Member |
|
|
HI Frank
I am again sending the execution plan and no. of records my queries are fetching in Table 159241 Records
both of the queries are getting same no. of rows. So can u please help me why it is not using Index.
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
6781 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 'AH% HUS_%'
SQL> /
207 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
323 consistent gets
85 physical reads
0 redo size
18773 bytes sent via SQL*Net to client
2206 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
207 rows processed
SQL> SPOOL OFF
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
