Re: Max permutations
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 9 Feb 2004 19:49:55 -0000
Jared,
Thanks for the input. I think it demonstrates
that changing the o_m_p does change the
strategy of joining, not just (possibly not even)
the actual numeric limit.
This is a truly horrible example,though.
How can a join order introduce a table
that was not in the first join order ?!
The join orders you've sent fall into 4 separate
passes, by the way, which means (I think) that each
set is independently subject to the o_m_p limit.
Would it be possible to send me the whole
of the files so I could get a better idea of what
the o_m_p is trying to achieve.
Thanks.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: <Jared.Still@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, February 09, 2004 7:07 PM
Subject: Re: Max permutations
Jonathan,
Here are join orders from a system where we normally run O_M_P = 1000 due
to excessive parse times on certain views.
These are all join orders (11) from the 1k file and the first 11 from the
80k file.
Notice that the 1k considers a join on FACILITY that is not seen in the
80k file.
This join order is not considered in the 80k trace file until join order
#61
Jared
O_M_P=1000
Join order[1]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J] PRODUCT [ZZ]
Join order[2]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J] WIP_CONTENT_REF [ K]
Join order[3]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] PRODUCT [ZZ]
WIP_SERIAL_NO [ J]
Join order[4]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] MATERIAL_ORDER_CONTENT
[ E] PRODUCT [ZZ]
Join order[5]: SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] WIP_SERIAL_NO [ J] MATERIAL_ORDER_CONTENT
[ E] WIP_CONTENT_REF [ K]
Join order[6]: FACILITY [PP] SERIAL_NO [ A] SERIAL_NO_LEGACY [ X]
MATERIAL_CONTENT_SERIAL_NO [ D] MATERIAL_ORDER_CONTENT [ E] WIP_SERIAL_NO
[ J]
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
