Message-Id: <10704.124102@fatcity.com> From: Alex Hillman Date: Fri, 8 Dec 2000 14:28:33 -0500 Subject: RE: Index problem ? This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0614D.11321230 Content-Type: text/plain; charset="iso-8859-1" if sys schema was not analyzed (and usually it shouldn't be) oracle will use RULE optimizer which will use indexes even if it is slower then doing FTS. Alex Hillman -----Original Message----- From: Sukumar Kurup [mailto:sukuora@yahoo.com] Sent: Friday, December 08, 2000 10:34 AM To: Multiple recipients of list ORACLE-L 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='' 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, 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@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@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). ------_=_NextPart_001_01C0614D.11321230 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Index problem ?

if sys schema was not analyzed (and usually it = shouldn't be) oracle will use RULE optimizer which will use indexes = even if it is slower then doing FTS.

Alex Hillman

-----Original Message-----
From: Sukumar Kurup [mailto:sukuora@yahoo.com]
Sent: Friday, December 08, 2000 10:34 AM
To: Multiple recipients of list ORACLE-L
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=3Db.segment_name
and a.table_name=3D'<table_name>'
and a.owner=3D'<owner_name>' ;

However, with the last line modified to read = as

and rtrim(a.owner)=3D'<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 =3D 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@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@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 =