RE: Optimizer theory: Question on access paths for outer joins
Hi Jay, Under rule based optimization deficient (outer joined) relations are always last in the join order. In general cost based optimization will do the same, but it is not bound to do so. For example, if T2 below is a complex view, then it cannot be merged into the parent query because it is being outer joined. That would mean that the view must be instantiated and accessed via a sort-merge join or hash join or used to drive query. If so, the optimizer may well choose to instantiate the view and drive the query as a nested loops join from there. However, in general it will no do so without a good reason. In this case however I suspect that the problem is that at least one of the in-line views T0 and T1 are mergeable. Because the merging of in-line views is done before query optimization, the optimizer never gets to consider the cardinality of the potential instantiations of the in-line views. The optimizer is presented with a merged query involving all the base tables for the merged views and PHONE and ACCOUNT. Worse than that, because transitivity analysis is not done for join predicates, you end up with an almost linear join topology. Also, because PHONE and T2 are outer joined, OR expansion and IN-list iterators cannot be used (lest duplicates might be introduced) to obtain index-based access paths to the other base tables involved and thus driving the query from PHONE (or from an index on ACCOUNT with your extraneous predicate) is natural. If this analysis is right, and you are right about the cardinality of the in-line views, the correct approach to optimizing this query is to place a NO_MERGE hint in the query block for the relevant in-line views, move T2 to the end of the from clause, and place ORDERED and USE_HASH(T2) hints in the outer query block. Hope this help, @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Hi all, I was just trying to figure out why in an outer join Oracle prefers to access the table with the (+) first. I would have thought that the table from which all the data was coming would come first and then appropriate rows would come from the second table with nulls being generated for non-existent rows. I started looking at it because of the following query: Simplified SQL: select stuff from inlineview1 T0, inlineview2 T1, inlineview3 T2, account ac, phone ph where T0.generic_id = T1.account_id and T1.account_id = T2.account_id (+) and T1.valid_flag = T2.valid_flag (+) and T0.generic_id = ac.id and ac.id = ph.current_phone_id(+) In this query the inline views are rather complicated but apply substantial restrictions on ACCOUNT (a huge table, as is PHONE). Logically, it is faster to run the inline views first, join them to ACCOUNT and then go to PHONE. The Optimizer kept doing a full table scan on PHONE first, and then joining to Account. I tried ORDERED, FIRST_ROWS and INDEX hints to no avail. The hints work if I take away the outer join symbol (but of course this gives incomplete results). I finally tricked Oracle into going in the correct order by adding a WHERE clause to the ACCOUNT of AND ac.id 0 (presumably causing the Optimizer to think there's more of a restriction on ACCOUNT and therefore taking it first). Since id is always greater than 0 this doesn't change the results but makes the query run much faster. So I have it working the way I want, but I'm still wonderinG why the Optimizer prefers to read the (+) table first? From the "Everything you always Wanted to Know About the Oracle Optimizer" book I know that the Optimizer tries to sort the join orders in ascending order of their computed cardinality. I'd guess that the Optimizer assumes an outer joined table will be returning some default percentage of the other table and therefore should always be accessed first? Can anyone confirm or refute this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Optimizer theory: Question on access paths for outer joins
Hi Jay, Under rule based optimization deficient (outer joined) relations are always last in the join order. In general cost based optimization will do the same, but it is not bound to do so. For example, if T2 below is a complex view, then it cannot be merged into the parent query because it is being outer joined. That would mean that the view must be instantiated and accessed via a sort-merge join or hash join or used to drive query. If so, the optimizer may well choose to instantiate the view and drive the query as a nested loops join from there. However, in general it will no do so without a good reason. In this case however I suspect that the problem is that at least one of the in-line views T0 and T1 are mergeable. Because the merging of in-line views is done before query optimization, the optimizer never gets to consider the cardinality of the potential instantiations of the in-line views. The optimizer is presented with a merged query involving all the base tables for the merged views and PHONE and ACCOUNT. Worse than that, because transitivity analysis is not done for join predicates, you end up with an almost linear join topology. Also, because PHONE and T2 are outer joined, OR expansion and IN-list iterators cannot be used (lest duplicates might be introduced) to obtain index-based access paths to the other base tables involved and thus driving the query from PHONE (or from an index on ACCOUNT with your extraneous predicate) is natural. If this analysis is right, and you are right about the cardinality of the in-line views, the correct approach to optimizing this query is to place a NO_MERGE hint in the query block for the relevant in-line views, move T2 to the end of the from clause, and place ORDERED and USE_HASH(T2) hints in the outer query block. Hope this help, @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Hi all, I was just trying to figure out why in an outer join Oracle prefers to access the table with the (+) first. I would have thought that the table from which all the data was coming would come first and then appropriate rows would come from the second table with nulls being generated for non-existent rows. I started looking at it because of the following query: Simplified SQL: select stuff from inlineview1 T0, inlineview2 T1, inlineview3 T2, account ac, phone ph where T0.generic_id = T1.account_id and T1.account_id = T2.account_id (+) and T1.valid_flag = T2.valid_flag (+) and T0.generic_id = ac.id and ac.id = ph.current_phone_id(+) In this query the inline views are rather complicated but apply substantial restrictions on ACCOUNT (a huge table, as is PHONE). Logically, it is faster to run the inline views first, join them to ACCOUNT and then go to PHONE. The Optimizer kept doing a full table scan on PHONE first, and then joining to Account. I tried ORDERED, FIRST_ROWS and INDEX hints to no avail. The hints work if I take away the outer join symbol (but of course this gives incomplete results). I finally tricked Oracle into going in the correct order by adding a WHERE clause to the ACCOUNT of AND ac.id 0 (presumably causing the Optimizer to think there's more of a restriction on ACCOUNT and therefore taking it first). Since id is always greater than 0 this doesn't change the results but makes the query run much faster. So I have it working the way I want, but I'm still wonderinG why the Optimizer prefers to read the (+) table first? From the "Everything you always Wanted to Know About the Oracle Optimizer" book I know that the Optimizer tries to sort the join orders in ascending order of their computed cardinality. I'd guess that the Optimizer assumes an outer joined table will be returning some default percentage of the other table and therefore should always be accessed first? Can anyone confirm or refute this? - You can view this message online at http://www.ixora.com.au/q+a/0104/11164729.htm To unsubscribe from Ixora Answers send a blank email to [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Optimizer theory: Question on access paths for outer joins
I am a little confused. Is not PHONE, in this query, the one WITHOUT the +? At 11:25 AM 4/10/01 -0800, you wrote: Hi all, I was just trying to figure out why in an outer join Oracle prefers to access the table with the (+) first. I would have thought that the table from which all the data was coming would come first and then appropriate rows would come from the second table with nulls being generated for non-existent rows. I started looking at it because of the following query: Simplified SQL: select stuff from inlineview1 T0, inlineview2 T1, inlineview3 T2, account ac, phone ph where T0.generic_id = T1.account_id and T1.account_id = T2.account_id (+) and T1.valid_flag = T2.valid_flag (+) and T0.generic_id = ac.id and ph.id = ac.current_phone_id(+) In this query the inline views are rather complicated but apply substantial restrictions on ACCOUNT (a huge table, as is PHONE). Logically, it is faster to run the inline views first, join them to ACCOUNT and then go to phone. The Optimizer kept doing a full table scan on PHONE first, and then joining to Account. I tried ORDERED, FIRST_ROWS and INDEX hints to no avail. The hints work if I take away the outer join symbol (but of course this gives incomplete results). I finally tricked Oracle into going in the correct order by adding a WHERE clause to the ACCOUNT of AND ac.id 0 (presumably causing the Optimizer to think there's more of a restriction on ACCOUNT and therefore taking it first). Since id is always greater than 0 this doesn't change the results but makes the query run much faster. So I have it working the way I want, but I'm still wonderinG why the Optimizer prefers to read the (+) table first? From the "Everything you always Wanted to Know About the Oracle Optimizer" book I know that the Optimizer tries to sort the join orders in ascending order of their computed cardinality. I'd guess that the Optimizer assumes an outer joined table will be returning some default percentage of the other table and therefore should always be accessed first? Can anyone confirm or refute this? Thanks! Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Regina Harter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Optimizer theory: Question on access paths for outer joins
if ac.id column is indexed for outer join to use this index this column should be not ull or condition should be added ac.id is not null or ac.id0 etc. It is because an index will not have rowid's for rows when this column is null. Alex Hillman -Original Message- Sent: Tuesday, April 10, 2001 3:26 PM To: Multiple recipients of list ORACLE-L Hi all, I was just trying to figure out why in an outer join Oracle prefers to access the table with the (+) first. I would have thought that the table from which all the data was coming would come first and then appropriate rows would come from the second table with nulls being generated for non-existent rows. I started looking at it because of the following query: Simplified SQL: select stuff from inlineview1 T0, inlineview2 T1, inlineview3 T2, account ac, phone ph where T0.generic_id = T1.account_id and T1.account_id = T2.account_id (+) and T1.valid_flag = T2.valid_flag (+) and T0.generic_id = ac.id and ph.id = ac.current_phone_id(+) In this query the inline views are rather complicated but apply substantial restrictions on ACCOUNT (a huge table, as is PHONE). Logically, it is faster to run the inline views first, join them to ACCOUNT and then go to phone. The Optimizer kept doing a full table scan on PHONE first, and then joining to Account. I tried ORDERED, FIRST_ROWS and INDEX hints to no avail. The hints work if I take away the outer join symbol (but of course this gives incomplete results). I finally tricked Oracle into going in the correct order by adding a WHERE clause to the ACCOUNT of AND ac.id 0 (presumably causing the Optimizer to think there's more of a restriction on ACCOUNT and therefore taking it first). Since id is always greater than 0 this doesn't change the results but makes the query run much faster. So I have it working the way I want, but I'm still wonderinG why the Optimizer prefers to read the (+) table first? From the "Everything you always Wanted to Know About the Oracle Optimizer" book I know that the Optimizer tries to sort the join orders in ascending order of their computed cardinality. I'd guess that the Optimizer assumes an outer joined table will be returning some default percentage of the other table and therefore should always be accessed first? Can anyone confirm or refute this? Thanks! Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Optimizer theory: Question on access paths for outer joins
No, I was the one who was confused when I retyped the query to send it. It should read: select stuff from inlineview1 T0, inlineview2 T1, inlineview3 T2, account ac, phone ph where T0.generic_id = T1.account_id and T1.account_id = T2.account_id (+) and T1.valid_flag = T2.valid_flag (+) and T0.generic_id = ac.id and ac.id = ph.current_phone_id(+) Thanks for the catch :). -Original Message- Sent: Tuesday, April 10, 2001 4:21 PM To: Multiple recipients of list ORACLE-L I am a little confused. Is not PHONE, in this query, the one WITHOUT the +? At 11:25 AM 4/10/01 -0800, you wrote: Hi all, I was just trying to figure out why in an outer join Oracle prefers to access the table with the (+) first. I would have thought that the table from which all the data was coming would come first and then appropriate rows would come from the second table with nulls being generated for non-existent rows. I started looking at it because of the following query: Simplified SQL: select stuff from inlineview1 T0, inlineview2 T1, inlineview3 T2, account ac, phone ph where T0.generic_id = T1.account_id and T1.account_id = T2.account_id (+) and T1.valid_flag = T2.valid_flag (+) and T0.generic_id = ac.id and ph.id = ac.current_phone_id(+) In this query the inline views are rather complicated but apply substantial restrictions on ACCOUNT (a huge table, as is PHONE). Logically, it is faster to run the inline views first, join them to ACCOUNT and then go to phone. The Optimizer kept doing a full table scan on PHONE first, and then joining to Account. I tried ORDERED, FIRST_ROWS and INDEX hints to no avail. The hints work if I take away the outer join symbol (but of course this gives incomplete results). I finally tricked Oracle into going in the correct order by adding a WHERE clause to the ACCOUNT of AND ac.id 0 (presumably causing the Optimizer to think there's more of a restriction on ACCOUNT and therefore taking it first). Since id is always greater than 0 this doesn't change the results but makes the query run much faster. So I have it working the way I want, but I'm still wonderinG why the Optimizer prefers to read the (+) table first? From the "Everything you always Wanted to Know About the Oracle Optimizer" book I know that the Optimizer tries to sort the join orders in ascending order of their computed cardinality. I'd guess that the Optimizer assumes an outer joined table will be returning some default percentage of the other table and therefore should always be accessed first? Can anyone confirm or refute this? Thanks! Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Regina Harter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
