RE: Query problem

From: Kevin Kostyszyn <kevin_at_dulcian.com>
Date: Fri, 8 Dec 2000 09:51:03 -0500
Message-Id: <10704.124018@fatcity.com>

Yes, it is possible that a query using an index can be slower than a full table scan. If the query has to keep turning around and accessing the table using the index search. this something to do with the CBO. It is possible in Oracle to over ride the CBO and query a specific way if you know more about the system than Oracle can figure out with just the data.

Kevin

-----Original Message-----

From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Hannah.M.Doran_at_sb.com
Sent: Friday, December 08, 2000 9:07 AM
To: Multiple recipients of list ORACLE-L Subject: Re: Query problem

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 <sukuora_at_yahoo.com> on 12/07/2000 10:34:54 PM

Please respond to ORACLE-L_at_fatcity.com

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

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. 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 also send the HELP command for other information (like subscribing).

--

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

Author:
INET: Hannah.M.Doran_at_sb.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