subquery unnesting example: select t1.* from table where t1.col1 in ( select t2.co1 from t2 where t2.col2 like 'adfasdfas' ); rewritten internally as select t1.* (select distinct t2.co11 from t2 where t2.col2 like 'adfasdfas' ) v1, t1 where t1.col1 = v1.col1 There are various conditions that apply, but basically there are cases where subqueries can be rewritten in some way and turned into an inline view in the main query. Possibly these should be renamed Gaja'd queries, in honour of the person who first published this as a possibly strategy for improving subquery performance. (It doesn't always help, by the way, which is why the uncosted unnesting that appears in 9i has caused problems for a few people). Cartestion Joins: I have an example lurking somewhere of Oracle join the results of two indexes access using a cartesian join without shoing the word (CARTESIAN). I'll see if I can find it. Cartesian joins appear if the optimizer thinks they are the fastest option - as with all features of the optimizer, it's not always obvious why the code should have been written in a way which makes a cartesian join sensible - your example is a good one, or course, I can't think of any other classes which would 'obviously' contain good candidates. (Apart from the extension to 4 or more tables). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st ----- Original Message ----- From: <ryan.gaffuri@xxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, June 08, 2004 12:19 PM Subject: Re: Re: CBO irregularity : questions in line... : > : > From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx> : > Date: 2004/06/08 Tue AM 03:23:00 EDT : > To: <oracle-l@xxxxxxxxxxxxx> : > Subject: Re: CBO irregularity : > : > : > I think the word 'minority' is the critical word. : > : > The existence of nested loop with inner full tablescan : > is a necessity because it is usually the best way : > of performing a Cartesian join. (And a Cartesian : > join isn't inevitably that sin that people think it is - : > and they don't necessarily realise they are doing : > them because they can be performed without : > being reported in the execution plan). : : I have seen Oracle use a cartesian join when I have a 3 table join and 2 tables are small and one is large. Oracle cartesian joins the two small tables and then hash joins them to the large table. : : When else is it beneficial to have a cartesian join? When does Oracle do cartesian joins without 'telling' you? : : > : > ORDERED is a usually a very good hint for a : > simple join if you know the business intent of : > the query. You tend to know the appropriate : > table order, and tell Oracle what it is. It is often : > an immediate winner. : > : > BUT it is extremely restrictive - it also has : > the unfortunate defect that it is applied only after : > subquery unnesting. Since 8i and 9i have : > different strategies for unnesting subqueries, the : : I never quite understood what sub-query unnesting was. Could you explain it? : : > same text with just the ordered hint may have : > dramatically different execution paths in the three : > versions. (I didn't mention 10g, because I haven't : > done any checks on its unnesting strategy - it may : > be different again: I do know that there are a couple : > of new spfile entries relating to unnesting subqueries). : > : ---------------------------------------------------------------- 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 -----------------------------------------------------------------