RE: Query problem

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Fri, 8 Dec 2000 14:30:19 -0500
Message-Id: <10704.124101@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.50E276AE
Content-Type: text/plain;

charset="iso-8859-1"

Sukumar, is the syntax of your hint correct? Are you aliasing the table in your query? If you are, the hint must refer to the table alias, not the table name, like below.

SELECT /*+ FULL(x) */
FROM table_name x
;

HTH Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174
F: 954.484.2933 
C: 954.658.5849

http://www.qode.com

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

-----Original Message-----
From: Sukumar Kurup [mailto:sukuora_at_yahoo.com] Sent: Friday, December 08, 2000 12:52 PM To: Multiple recipients of list ORACLE-L Subject: Re: Query problem

Hannah,

I tried that, but it is still not doing a full table scan,

Thanks

Sukumar Kurup

Hannah.M.Doran_at_sb.com wrote:
>
>
>
> Ok. In Oracle , you can use the FULL optimizer hint
> in order to force a table
> scan. Not sure how to specify for joins.
>
>
> select /*Full(table_name)*/* from table_name;
>
>
> Hannah
>
>
>
>
>
> 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
> 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).

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.50E276AE
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.2650.12">
<TITLE>RE: Query problem</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Sukumar, is the syntax of your hint correct?&nbsp; =
Are you aliasing the table in your query?&nbsp; If you are, the hint =
must refer to the table alias, not the table name, like =
below.</FONT></P>

<P><FONT SIZE=3D2>SELECT /*+ FULL(x) */</FONT>
<BR><FONT SIZE=3D2>FROM table_name x</FONT>
<BR><FONT SIZE=3D2>;</FONT>
</P>

<P><FONT SIZE=3D2>HTH</FONT>
</P>

<P><FONT SIZE=3D2>Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=3D2>Oracle Database Administrator</FONT>
<BR><FONT SIZE=3D2>Qode.com</FONT>
<BR><FONT SIZE=3D2>4850 North State Road 7</FONT>
<BR><FONT SIZE=3D2>Suite G104</FONT>
<BR><FONT SIZE=3D2>Fort Lauderdale, FL&nbsp; 33319</FONT>
</P>

<P><FONT SIZE=3D2>V: 954.484.3191, x174</FONT>
<BR><FONT SIZE=3D2>F: 954.484.2933 </FONT>
<BR><FONT SIZE=3D2>C: 954.658.5849</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A></FONT>
</P>

<P><FONT SIZE=3D2>&quot;The information contained herein does not =
express the opinion or position of Qode.com and cannot be attributed to =
or made binding upon Qode.com.&quot;</FONT></P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Sukumar Kurup [<A =
HREF=3D"mailto:sukuora_at_yahoo.com">mailto:sukuora_at_yahoo.com</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Friday, December 08, 2000 12:52 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: Query problem</FONT>
</P>
<BR>

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

<P><FONT SIZE=3D2>I tried that, but it is still not doing a full =
table</FONT>
<BR><FONT SIZE=3D2>scan,</FONT>
</P>

<P><FONT SIZE=3D2>Thanks</FONT>
</P>

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

<P><FONT SIZE=3D2>--- Hannah.M.Doran_at_sb.com wrote:</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Ok.&nbsp; In Oracle , you can use the FULL =
optimizer hint</FONT>
<BR><FONT SIZE=3D2>&gt; in order to force a table</FONT>
<BR><FONT SIZE=3D2>&gt; scan.&nbsp; Not sure how to specify for =
joins.</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp; select /*Full(table_name)*/* from =
table_name;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Hannah</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Sukumar Kurup &lt;sukuora_at_yahoo.com&gt; on =
12/07/2000</FONT>
<BR><FONT SIZE=3D2>&gt; 10:34:54 PM</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Please respond to ORACLE-L_at_fatcity.com</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; To:&nbsp;&nbsp; Multiple recipients of list =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;ORACLE-L_at_fatcity.com&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; cc:</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Hi List,</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; The following query gives out results after =
allmost</FONT>
<BR><FONT SIZE=3D2>&gt; 5</FONT>
<BR><FONT SIZE=3D2>&gt; minutes :</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; select a.table_name,a.index_name,b.bytes</FONT>
<BR><FONT SIZE=3D2>&gt; from dba_indexes a, dba_segments b</FONT>
<BR><FONT SIZE=3D2>&gt; where a.table_name=3Db.segment_name</FONT>
<BR><FONT SIZE=3D2>&gt; and a.table_name=3D'&lt;table_name&gt;'</FONT>
<BR><FONT SIZE=3D2>&gt; and a.owner=3D'&lt;owner_name&gt;' ;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; However, with the last line modified to read =
as</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; and rtrim(a.owner)=3D'&lt;owner_name&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; the result comes immediately.</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; I tried this on different servers, and the =
results</FONT>
<BR><FONT SIZE=3D2>&gt; are</FONT>
<BR><FONT SIZE=3D2>&gt; similar.</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; I did an explain plan on both the queries, and =
the</FONT>
<BR><FONT SIZE=3D2>&gt; notable difference was that while the first =
query</FONT>
<BR><FONT SIZE=3D2>&gt; did</FONT>
<BR><FONT SIZE=3D2>&gt; an index scan on OBJ$ table (one of the table =
used</FONT>
<BR><FONT SIZE=3D2>&gt; in dba_indexes view), whereas in the second =
query,</FONT>
<BR><FONT SIZE=3D2>&gt; it</FONT>
<BR><FONT SIZE=3D2>&gt; did a full table scan (due to the rtrim =
function).</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Could the presence of index on obj$ table be =
the</FONT>
<BR><FONT SIZE=3D2>&gt; reason for such a difference in the query =
timings ?</FONT>
<BR><FONT SIZE=3D2>&gt; can Index actually slow down a query to such =
an</FONT>
<BR><FONT SIZE=3D2>&gt; extent</FONT>
<BR><FONT SIZE=3D2>&gt; ?</FONT>
<BR><FONT SIZE=3D2>&gt; Or is there some other issue ?</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Thanks in advance, for any enlightment,</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Sukumar Kurup</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; =
__________________________________________________</FONT>
<BR><FONT SIZE=3D2>&gt; Do You Yahoo!?</FONT>
<BR><FONT SIZE=3D2>&gt; Yahoo! Shopping - Thousands of Stores. Millions =
of</FONT>
<BR><FONT SIZE=3D2>&gt; Products.</FONT>
<BR><FONT SIZE=3D2>&gt; <A HREF=3D"http://shopping.yahoo.com/" =
TARGET=3D"_blank">http://shopping.yahoo.com/</A></FONT>
<BR><FONT SIZE=3D2>&gt; --</FONT>
<BR><FONT SIZE=3D2>&gt; Please see the official ORACLE-L FAQ:</FONT>
<BR><FONT SIZE=3D2>&gt; <A HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>&gt; --</FONT>
<BR><FONT SIZE=3D2>&gt; Author: Sukumar Kurup</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp; INET: sukuora_at_yahoo.com</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Fat City Network Services&nbsp;&nbsp;&nbsp; -- =
(858) 538-5051&nbsp; FAX:</FONT>
<BR><FONT SIZE=3D2>&gt; (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&gt; San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public =
Internet</FONT>
<BR><FONT SIZE=3D2>&gt; access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>&gt; To REMOVE yourself from this mailing list, send =
an</FONT>
<BR><FONT SIZE=3D2>&gt; E-Mail message</FONT>
<BR><FONT SIZE=3D2>&gt; to: ListGuru_at_fatcity.com (note EXACT spelling =
of</FONT>
<BR><FONT SIZE=3D2>&gt; 'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&gt; the message BODY, include a line containing: =
UNSUB</FONT>
<BR><FONT SIZE=3D2>&gt; ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt; (or the name of mailing list you want to be =
removed</FONT>
<BR><FONT SIZE=3D2>&gt; from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&gt; also send the HELP command for other =
information</FONT>
<BR><FONT SIZE=3D2>&gt; (like subscribing).</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; -- </FONT>
<BR><FONT SIZE=3D2>&gt; Please see the official ORACLE-L FAQ:</FONT>
<BR><FONT SIZE=3D2>&gt; <A HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>&gt; -- </FONT>
<BR><FONT SIZE=3D2>&gt; Author: </FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp; INET: Hannah.M.Doran_at_sb.com</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Fat City Network Services&nbsp;&nbsp;&nbsp; -- =
(858) 538-5051&nbsp; FAX:</FONT>
<BR><FONT SIZE=3D2>&gt; (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&gt; San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public =
Internet</FONT>
<BR><FONT SIZE=3D2>&gt; access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>&gt; To REMOVE yourself from this mailing list, send =
an</FONT>
<BR><FONT SIZE=3D2>&gt; E-Mail message</FONT>
<BR><FONT SIZE=3D2>&gt; to: ListGuru_at_fatcity.com (note EXACT spelling =
of</FONT>
<BR><FONT SIZE=3D2>&gt; 'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&gt; the message BODY, include a line containing: =
UNSUB</FONT>
<BR><FONT SIZE=3D2>&gt; ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt; (or the name of mailing list you want to be =
removed</FONT>
<BR><FONT SIZE=3D2>&gt; from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&gt; also send the HELP command for other =
information</FONT>
<BR><FONT SIZE=3D2>&gt; (like subscribing).</FONT>
</P>
<BR>

<P><FONT =
SIZE=3D2>__________________________________________________</FONT>
<BR><FONT SIZE=3D2>Do You Yahoo!?</FONT>
<BR><FONT SIZE=3D2>Yahoo! Shopping - Thousands of Stores. Millions of =
Products.</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://shopping.yahoo.com/" =
TARGET=3D"_blank">http://shopping.yahoo.com/</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<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>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =

Received on Fri Dec 08 2000 - 13:30:19 CST