Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Index problem ?

Index problem ?

From: Vivek <Vivek_at_1800FLOWERS.com>
Date: Sat, 9 Dec 2000 07:14:17 -0500
Message-Id: <10705.124136@fatcity.com>


Try the following



select/*+ full(a) */ a.table_name,a.index_name,b.bytes   2 from dba_indexes a, dba_segments b
  3 where a.table_name=b.segment_name
  4 and a.table_name= <'table_name'>
  5* and a.owner=<'owner'>

Sukumar Kurup <sukuora_at_yahoo.com> on 12/08/2000 10:33:49 AM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

Hi ,

The following query gives out results after allmost 5 minutes :

select a.table_name,a.index_name,b.bytes from dba_indexes a, dba_segments b
where a.table_name=b.segment_name
and a.table_name='<table_name>'
and a.owner='<owner_name>' ;

However, with the last line modified to read as

and rtrim(a.owner)='<owner_name>

the result comes immediately.

I tried this on different servers/Databases, and the results are similar.

I did an explain plan on both the queries, and the notable difference was that while the first query did an index scan on OBJ$ table (one of the table used in dba_indexes view), whereas in the second query, it did a full table scan (due to the rtrim function).

Could the presence of index on obj$ table be the reason for such a difference in the query timings ? can Index actually slow down a query to such an extent ?
Or could there be any other reason ?

Sun solaris = 2.6
Oracle 8.1.7 & 8.1.6

Thanks in advance, for any enlightment, Received on Sat Dec 09 2000 - 06:14:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US