Re: SMJ, NL or HJ
By the way, when I added the ORDERED and USE_HASH hints to this query, I did not encounter the errors about the TEMP tablespace utilization, as with the MERGE SORT JOIN. Thanks a ton, Jonathan, for your inputs. Raj Jonathan Lewis [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] mon.co.uk cc: Sent by: Subject: Re: SMJ, NL or HJ [EMAIL PROTECTED] May 04, 2002 08:58 AM Please respond to ORACLE-L Notes inline. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- | hash | table C | hash | table B | table A | Table C will be scanned and hashed Table B will be scanned and hashed Table A will be scanned rows will be tested against hash B successful rows will be tested against has C Successful rows will be forwarded to the next step. I have not tested exhaustively the effects of this path when the tables are too large to hash in memory, but I would take as a first hypothesis that if only one tenth of table C fits in memory, and one eight of table B, then Oracle would choose the larger table to define the in memory pattiition size, so your usage of temp would be limited to 9/10 of C requirements +9/10 of B requirements +9/10 of A requirments. where 'requirements' for B and C is a measure of the number of rows that would be used for the two hash tables, bearing in mind that each row has to carry the hashkey column and the relevant data rows, and you lose about 10% of the hash_area_size to overheads. The requirements for A is the total size of the largest number of rows which may end up passing through the hash join |Is my understanding right? Also, will my usage of hash joins reduce the |TEMP tablespace utilization. I know for sure that none of these tables will |fit in the hash area size. So, part of it will definitely be written to |TEMP. But will this utilization be less than that of a merge sort join? | Utilisation is likely to be less than a sort/mergeas a large multiplass sort requires some input and output data to exist concurrently in the TEMP tablespace. The difference may not be large though. |You say the memory usage will be twice the hash area size? From the little |search that I have done on this parameter, I find no reference in the |Oracle Docs. saying it will be so. Anyways, if you say it, it must be so ; |-) Will look up at ixora from home. | If everything were in the Oracle Docs then this list wouldn't be about Oracle, it would be about the merits of different alcoholic beverages ;) There is a note I spotted somewhere in the manuals once that said two concurrent hashes could be running concurrently. It's wrong, however: in an N-table join you could have N-1 concurrent hashes. | |Something I found out during my research: HASH_AREA_SIZE is done away with |in Oracle 9i, or retained for backward compatibility. | You have the option to forget about it (and sort_area_size and half a dozen others) if you let Oracle monitor PGA memory usage through the PGA_AGGREGATE_MAX feature. However it will still apply to shared servers (formerly MTS). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED
Re: SMJ, NL or HJ
Notes inline. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- | hash | table C | hash | table B | table A | Table C will be scanned and hashed Table B will be scanned and hashed Table A will be scanned rows will be tested against hash B successful rows will be tested against has C Successful rows will be forwarded to the next step. I have not tested exhaustively the effects of this path when the tables are too large to hash in memory, but I would take as a first hypothesis that if only one tenth of table C fits in memory, and one eight of table B, then Oracle would choose the larger table to define the in memory pattiition size, so your usage of temp would be limited to 9/10 of C requirements +9/10 of B requirements +9/10 of A requirments. where 'requirements' for B and C is a measure of the number of rows that would be used for the two hash tables, bearing in mind that each row has to carry the hashkey column and the relevant data rows, and you lose about 10% of the hash_area_size to overheads. The requirements for A is the total size of the largest number of rows which may end up passing through the hash join |Is my understanding right? Also, will my usage of hash joins reduce the |TEMP tablespace utilization. I know for sure that none of these tables will |fit in the hash area size. So, part of it will definitely be written to |TEMP. But will this utilization be less than that of a merge sort join? | Utilisation is likely to be less than a sort/mergeas a large multiplass sort requires some input and output data to exist concurrently in the TEMP tablespace. The difference may not be large though. |You say the memory usage will be twice the hash area size? From the little |search that I have done on this parameter, I find no reference in the |Oracle Docs. saying it will be so. Anyways, if you say it, it must be so ; |-) Will look up at ixora from home. | If everything were in the Oracle Docs then this list wouldn't be about Oracle, it would be about the merits of different alcoholic beverages ;) There is a note I spotted somewhere in the manuals once that said two concurrent hashes could be running concurrently. It's wrong, however: in an N-table join you could have N-1 concurrent hashes. | |Something I found out during my research: HASH_AREA_SIZE is done away with |in Oracle 9i, or retained for backward compatibility. | You have the option to forget about it (and sort_area_size and half a dozen others) if you let Oracle monitor PGA memory usage through the PGA_AGGREGATE_MAX feature. However it will still apply to shared servers (formerly MTS). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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: SMJ, NL or HJ
Depends. The number of rows matching a given FK may vary widely. Collect stats, and let the CBO decide, it should not have it too wrong in such a case. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 03 May 2002 08:13:27 Hello Gurus, A SQL tuning question. Given three large tables with the same millions of rows, and all three are referenced in a query, without any filter, as under: Select .. from largetableA a, largeTableB b, largeTableC c where a.empnum = b.empnum and a.empnum = c.empnum; What would be the prefered way of joining these tables, Merge Join, Nested Loops or Hash Joins? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). --- - Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: SMJ, NL or HJ
Consider analyzing the EMPNUM column for each table as well, to provide the CBO with possibly crucial data distribution information... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 11:18 AM Depends. The number of rows matching a given FK may vary widely. Collect stats, and let the CBO decide, it should not have it too wrong in such a case. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 03 May 2002 08:13:27 Hello Gurus, A SQL tuning question. Given three large tables with the same millions of rows, and all three are referenced in a query, without any filter, as under: Select .. from largetableA a, largeTableB b, largeTableC c where a.empnum = b.empnum and a.empnum = c.empnum; What would be the prefered way of joining these tables, Merge Join, Nested Loops or Hash Joins? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). --- - Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Tim Gorman 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: SMJ, NL or HJ
The CBO, presently does quite a good job. It chooses a sort merge join on the tables. Given my understanding of the data distribution in the tables, I agree its the best execution plan. But this kills my temporary tablespace, ORA-1652. To accomodate this query, I altered the sort area for the session to a high value, and then, I took a hit on my temporary tablespace utilization, not withstanding the rather small values for the extent sizes. And changing it would require me go thru a lot of bureaucracy, change management controls, approvals, the works. So, I was actually looking for a way to get around using sort merge joins, and not compromise on performance. I would tend to use hash joins, when a join happens between a smaller row source, and a large one. But that, I know, is not the case here. I would like to drive this query via a full table access, since I expect the query to return me about 90% of the rows from each table. So, a nested loop is also not feasible. Left without an option, I guess. Headed now to put in a change management request :( Raj Stephane Faroult To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sfaroult@oriolcc: ecorp.com Subject: RE: SMJ, NL or HJ Sent by: [EMAIL PROTECTED] m May 03, 2002 01:18 PM Please respond to ORACLE-L Depends. The number of rows matching a given FK may vary widely. Collect stats, and let the CBO decide, it should not have it too wrong in such a case. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Fri, 03 May 2002 08:13:27 Hello Gurus, A SQL tuning question. Given three large tables with the same millions of rows, and all three are referenced in a query, without any filter, as under: Select .. from largetableA a, largeTableB b, largeTableC c where a.empnum = b.empnum anda.empnum = c.empnum; What would be the prefered way of joining these tables, Merge Join, Nested Loops or Hash Joins? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). --- - Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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
Re: SMJ, NL or HJ
Since table B and C are using the same column to join to table A, then it should be possible to ensure that Oracle hashes tables B and C at the same time, then scans table A passing rows through each hash in turn. (The order can be permuted as necessary). If you can set the hash area size to something large enough you can start getting your results through without any I/O above a single table scan of A B and C. Remember that the total memory usage in this case will be 2 x hash_area_size though - one for table B, one for table C. The path would be: hash table C hash table B table A Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 03 May 2002 18:43 | |The CBO, presently does quite a good job. It chooses a sort merge join on |the tables. Given my understanding of the data distribution in the tables, |I agree its the best execution plan. But this kills my temporary |tablespace, ORA-1652. To accomodate this query, I altered the sort area for |the session to a high value, and then, I took a hit on my temporary |tablespace utilization, not withstanding the rather small values for the |extent sizes. And changing it would require me go thru a lot of |bureaucracy, change management controls, approvals, the works. | |So, I was actually looking for a way to get around using sort merge joins, |and not compromise on performance. I would tend to use hash joins, when a |join happens between a smaller row source, and a large one. But that, I |know, is not the case here. I would like to drive this query via a full |table access, since I expect the query to return me about 90% of the rows |from each table. So, a nested loop is also not feasible. | |Left without an option, I guess. Headed now to put in a change management |request :( | |Raj | | | | |Select .. |from largetableA a, largeTableB b, largeTableC c |where a.empnum = b.empnum |anda.empnum = c.empnum; | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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: SMJ, NL or HJ
Thanks Jonathan, for your inputs. I am trying to understand hash joins. New to it. As I understand this, and looking at the plan that you have mentioned : hash table C hash table B table A 1. Tables B and C will be hashed in parallel, and Hash table for A will be created. 2. Rows from B will be probed against the hash table in A. 3. Rows from step 2 will be probed against the hash table in C. Is my understanding right? Also, will my usage of hash joins reduce the TEMP tablespace utilization. I know for sure that none of these tables will fit in the hash area size. So, part of it will definitely be written to TEMP. But will this utilization be less than that of a merge sort join? You say the memory usage will be twice the hash area size? From the little search that I have done on this parameter, I find no reference in the Oracle Docs. saying it will be so. Anyways, if you say it, it must be so ; -) Will look up at ixora from home. Have a nice Weekend, e'one. Raj Something I found out during my research: HASH_AREA_SIZE is done away with in Oracle 9i, or retained for backward compatibility. Jonathan Lewis [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] mon.co.uk cc: Sent by: Subject: Re: SMJ, NL or HJ [EMAIL PROTECTED] May 03, 2002 04:13 PM Please respond to ORACLE-L Since table B and C are using the same column to join to table A, then it should be possible to ensure that Oracle hashes tables B and C at the same time, then scans table A passing rows through each hash in turn. (The order can be permuted as necessary). If you can set the hash area size to something large enough you can start getting your results through without any I/O above a single table scan of A B and C. Remember that the total memory usage in this case will be 2 x hash_area_size though - one for table B, one for table C. The path would be: hash table C hash table B table A Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 03 May 2002 18:43 | |The CBO, presently does quite a good job. It chooses a sort merge join on |the tables. Given my understanding of the data distribution in the tables, |I agree its the best execution plan. But this kills my temporary |tablespace, ORA-1652. To accomodate this query, I altered the sort area for |the session to a high value, and then, I took a hit on my temporary |tablespace utilization, not withstanding the rather small values for the |extent sizes. And changing it would require me go thru a lot of |bureaucracy, change management controls, approvals, the works. | |So, I was actually looking for a way to get around using sort merge joins, |and not compromise on performance. I would tend to use hash joins, when a |join happens between a smaller row source, and a large one. But that, I |know, is not the case here. I would like to drive this query via a full |table access, since I expect the query to return me about 90% of the rows |from each table. So, a nested loop is also not feasible. | |Left without an option, I guess. Headed now to put in a change management
