RE: Index problem ?

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Fri, 8 Dec 2000 14:28:33 -0500
Message-Id: <10704.124102@fatcity.com>

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_at_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='<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 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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0">

<TITLE>RE: Index problem ?</TITLE>
</HEAD>
<BODY>

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 [<A = HREF=3D"mailto:sukuora_at_yahoo.com">mailto:sukuora_at_yahoo.com</A>]

<BR><FONT SIZE=3D2>Sent: Friday, December 08, 2000 10:34 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Index problem ?</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Hi ,</FONT>

The following query gives out results after allmost = 5
 minutes :

<P><FONT SIZE=3D2>select a.table_name,a.index_name,b.bytes</FONT>
<BR><FONT SIZE=3D2>from dba_indexes a, dba_segments b</FONT>
<BR><FONT SIZE=3D2>where a.table_name=3Db.segment_name</FONT>
<BR><FONT SIZE=3D2>and a.table_name=3D'&lt;table_name&gt;'</FONT>
<BR><FONT SIZE=3D2>and a.owner=3D'&lt;owner_name&gt;' ;</FONT>
</P>

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

<BR><FONT SIZE=3D2>an index scan on OBJ$ table (one of the table used =
</FONT>
<BR><FONT SIZE=3D2>in dba_indexes view), whereas in the second query, =

it

<BR><FONT SIZE=3D2>did a full table scan (due to the rtrim function). =
</FONT>
</P>

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

<BR><FONT SIZE=3D2>? </FONT>
<BR><FONT SIZE=3D2>Or could there be any other reason ? </FONT>
</P>

<P><FONT SIZE=3D2>Sun solaris =3D 2.6</FONT>

Oracle 8.1.7 & 8.1.6

Thanks in advance, for any enlightment,

<P><FONT SIZE=3D2>Sukumar Kurup</FONT>
</P>
<BR>
<BR>
<BR>

<FONT =

SIZE=3D2>__________________________________________________</FONT>

Do You Yahoo!? Yahoo! Shopping - Thousands of Stores. Millions of = Products.
 <A HREF=3D"http://shopping.yahoo.com/" = TARGET=3D"_blank">http://shopping.yahoo.com/</A> -- 
 Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Sukumar Kurup</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: sukuora_at_yahoo.com</FONT>
</P>

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

SIZE=3D2>---------------------------------------------------------------=

-----
 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 = Received on Fri Dec 08 2000 - 13:28:33 CST