Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query problem

Re: Query problem

From: <>
Date: Fri, 8 Dec 2000 09:03:08 -0500
Message-Id: <>

Try running the first query and specify that the plan not use an index or speficy a full scan ( you could do this in MS SQL server), not sure about oracle.

Would be interesting to know.

Sukumar Kurup <> on 12/07/2000 10:34:54 PM

Please respond to

To: Multiple recipients of list ORACLE-L <> cc:
Subject: Query problem

Hi List,

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, 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 is there some other issue ?

Thanks in advance, for any enlightment,

Sukumar Kurup

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.

Please see the official ORACLE-L FAQ:

Author: Sukumar Kurup
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: (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 also send the HELP command for other information (like subscribing). Received on Fri Dec 08 2000 - 08:03:08 CST

Original text of this message