Re: Max permutations

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 9 Feb 2004 19:49:55 -0000
Message-ID: <01e601c3ef45$f3cfa740$6702a8c0@Primary>

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_at_radisys.com> To: <oracle-l_at_freelists.org> 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Feb 09 2004 - 13:49:55 CST