Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Driving table

RE: Driving table

From: Miller, Jay <JayMiller_at_tdwaterhouse.com>
Date: Mon, 09 Apr 2001 11:37:38 -0700
Message-ID: <F001.002E5889.20010409110554@fatcity.com> 






Hi Anup,



You can encourage Oracle to read the tables in a particular order by using
the ORDERED hint.  For example, if you wanted the following query to have
tableb as the driving table:



SELECT /*+ ORDERED */ col1,col2


FROM tableb, tablea, tablec


WHERE ...


Will cause Oracle to read tableb first, followed by tablea, followed by
tablec.  Note that this is exactly the opposite of the order you'd want to
list the tables when using the Rule Based Optimizer.



But you'd only want to do this if you were certain Oracle was choosing an
inferior access path.  Especially in recent releases it usually (but still
not always) chooses the best path.



Jay Miller




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


Sent: Friday, April 06, 2001 2:06 PM


To: Multiple recipients of list ORACLE-L




Hi there ,


Thanks for the reply .



I am in Cost Based Optimizer ( we all will be now a days ) .



Does that mean , if I want to know ( or decide ) while writting the query ,
my


driving table , Its not possible ???


If I have a good idea ( size of data in tables , indexes etc.) about the
tables involved in the join and I want to know before writting the query my
driving table , I do not think I am expecting too much .



Please do reply .



Cheers .






First question is whether you're in Cost Based or Rule Based optimization.
In RBO you are correct that it will read tables right to left unless
instructed otherwise by a hint.



In CBO, Oracle will choose what it thinks is 'best', which most of the time,
but not all of the time :), will be true.



Jay Miller



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


Sent: Friday, April 06, 2001 6:45 AM


To: Multiple recipients of list ORACLE-L




Hi friends ,



I have got some query regarding , how the query is exactly executed ??



We always take joins and if we see the plan of the query , there is always
one


main driving table . Some times I get feeling that , in the join the right
most table is the driving table . But that is not always true , this is my
observation from several plans .



In Oracle documentation alos ita mentioned that ( Server Concepts, 815 or
816


: Optimisation of Joins ), "one driving table is selected" , but at least I
cannot makeout which of the several tables will be selected as driving table



What can be the potential criterions of selecting a driving table ?



Please , if you all can throw some light on the same or can redirect to some
document , that would be great .



Cheers ,


Anup Saxena








The contents of this e-mail are confidential to the ordinary user
of the e-mail address to which it was addressed and may also be
privileged. If you are not the addressee of this e-mail you should
not copy, forward, disclose or otherwise use it or any part of it
in any form whatsoever. If you have received this e-mail in error
please notify us by telephone or e-mail the sender by replying to
this message, and then delete this e-mail and other copies of it
from your computer system. Thank you.



We reserve the right to monitor all e-mail communications through
our network.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: A.SAXENA_at_ponl.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: Miller, Jay
  INET: JayMiller_at_TDWaterhouse.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).




-------------------------------------------------------------------

The contents of this e-mail are confidential to the ordinary user
of the e-mail address to which it was addressed and may also be
privileged. If you are not the addressee of this e-mail you should
not copy, forward, disclose or otherwise use it or any part of it
in any form whatsoever. If you have received this e-mail in error
please notify us by telephone or e-mail the sender by replying to
this message, and then delete this e-mail and other copies of it
from your computer system. Thank you.

We reserve the right to monitor all e-mail communications through
our network.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: A.SAXENA_at_ponl.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: Miller, Jay
  INET: JayMiller_at_TDWaterhouse.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).


Received on Mon Apr 09 2001 - 13:37:38 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US