Query problem

From: Sukumar Kurup <sukuora_at_yahoo.com>
Date: Thu, 7 Dec 2000 15:00:53 -0800 (PST)
Message-Id: <10703.123966@fatcity.com>

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. Received on Thu Dec 07 2000 - 17:00:53 CST