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@oriol cc:
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
>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:
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).
