Home » RDBMS Server » Performance Tuning » Again Execution Plan Of Query For Second Name Oracle is not using INdex
Again Execution Plan Of Query For Second Name Oracle is not using INdex [message #65692] Tue, 07 December 2004 03:53 Go to next message
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
Re: Again Execution Plan Of Query For Second Name Oracle is not using INdex [message #65693 is a reply to message #65692] Tue, 07 December 2004 04:03 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
analyze the concerned tables and indexes.
Statistics may be OUT OF DATE.
and Let the CBO decide whehter to use the index.
an FULLTABLE SCAN is not always harmful as it looks like.
Previous Topic: io waits on IBM FastT with Oracle 81
Next Topic: Getting Data in one pass
Goto Forum:
  


Current Time: Tue Sep 29 12:40:27 CDT 2020