Home » SQL & PL/SQL » SQL & PL/SQL » CBO and multiple paths
CBO and multiple paths [message #211592] Fri, 29 December 2006 13:59 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Many times there are multiple ways in which tables can be joined together. In general, is it better to pick one link, or should I be linking every possible connection? Will the optimizer keep track of all these paths for me and figure out the best way?

As example say I have TABLEs A, B, and C all share one id values is it better to link say

A<-->B
B<-->C

or

A<-->B
B<-->C
C<-->A

Thanks,
Andrew
Re: CBO and multiple paths [message #211597 is a reply to message #211592] Fri, 29 December 2006 15:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>As example say I have TABLEs A, B, and C all share one id values is it better to link say
HUH?
Can you restate your question so it has some relevance to Oracle and/or SQL?
What/how do you define "link"?
>Will the optimizer keep track of all these paths for me and figure out the best way?
BEST based upon which metric?
The CBO does a decent job, except when it doesn't.
Re: CBO and multiple paths [message #211641 is a reply to message #211592] Sat, 30 December 2006 05:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
For the optimizer there is no need to explicitly state that c=a.
Re: CBO and multiple paths [message #211686 is a reply to message #211641] Sun, 31 December 2006 12:33 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Frank wrote on Sat, 30 December 2006 05:43
For the optimizer there is no need to explicitly state that c=a.

This is known as Transitive Closure.

There is a good note about ways to lay out join queries for human readability on Jonathan Lewis' blog:
jonathanlewis.wordpress.com/2006/11/02/clarity-clarity-clarity

[Updated on: Sun, 31 December 2006 12:34]

Report message to a moderator

Previous Topic: how to call sql loader from pl/sql
Next Topic: delete several columns from a table
Goto Forum:
  


Current Time: Sun Dec 11 05:55:52 CST 2016

Total time taken to generate the page: 0.09709 seconds