Re: Index problem ?

From: <Hannah.M.Doran_at_sb.com>
Date: Fri, 8 Dec 2000 10:51:34 -0500
Message-Id: <10704.124034@fatcity.com>

Try this:

select /*FULL(a)*/a.table_name,/*FULL(a)*/a.index_name,/*FULL(b)*/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>'

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:
Subject: Index problem ?

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,

Sukumar Kurup

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Sukumar Kurup
INET: sukuora_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Fri Dec 08 2000 - 09:51:34 CST