Message-Id: <10705.124136@fatcity.com> From: Vivek Date: Sat, 9 Dec 2000 07:14:17 -0500 Subject: Index problem ? 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 on 12/08/2000 10:33:49 AM Please respond to ORACLE-L@fatcity.com To: Multiple recipients of list ORACLE-L 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='' and a.owner='' ; However, with the last line modified to read as and rtrim(a.owner)=' 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,