How can i force optimizer to use hash anit join
Guys...
I tried all sorts of hints but still oracle goes for filter and FTS on a temp table.. I dont want to create an index for this reason..
Is there any way to force Oracle to use Hash Aj in this case..
Below is the code and plan..
Code:
PRO> ed
Wrote file afiedt.buf
1 explain plan set statement_id = 'ABHAY' for
2 MERGE /*+ Ordered */ INTO Shipment_Line X
3 USING
4 (
5 select /*+ Ordered */ * from shipment_line sd
6 where
7 (
8 Sd.SALES_ORGANIZATION_CD ,
9 Sd.REFERENCE_DOCUMENT_NO ,
10 Sd.REFRENCE_DOCUMENT_ITEM_NO ,
11 SUBSTR(sd.SOURCE_NM,-3,3)
12 )
13 not in (select /*+ Hash_Aj */
14 S.SALES_ORGANIZATION_CD ,
15 S.SALES_ORDER_NO ,
16 S.sales_order_item_no ,
17 SUBSTR(s.SOURCE_NM,-3,3)
18 from temp_slordt s) and
19 sales_document_type <> '??' and rownum > 0 and
20 exists (select null from temp_slsord s where
21 S.SALES_ORGANIZATION_CD = Sd.SALES_ORGANIZATION_CD And
22 S.SALES_ORDER_NO = Sd.REFERENCE_DOCUMENT_NO And
23 SUBSTR(s.SOURCE_NM,-3,3) = SUBSTR(sd.SOURCE_NM,-3,3))
24 ) Y
25 ON
26 (
27 X.SALES_ORGANIZATION_CD = Y.SALES_ORGANIZATION_CD AND
28 X.SHIPMENT_NO = Y.SHIPMENT_NO AND
29 X.SHIPMENT_ITEM_NO = Y.SHIPMENT_ITEM_NO AND
30 X.SOURCE_NM = Y.SOURCE_NM
31 )
32 WHEN MATCHED THEN
33 UPDATE SET
34 x.sales_document_type = '??', x.reportable_flag = '?',
35 x.sab101_flag = '?', x.country_cd = '?', x.Ship_To_Customer = '?',
36 x.So_Entry_Date = '31-Dec-2999', x.SALES_ORDER_ITEM_SHORT_TX = '??'
37 WHEN NOT MATCHED THEN
38 INSERT
39 (
40 X.SALES_ORGANIZATION_CD ,
41 X.SHIPMENT_NO ,
42 X.SHIPMENT_ITEM_NO ,
43 X.SOURCE_NM
44 )
45 VALUES
46 (
47 Y.SALES_ORGANIZATION_CD ,
48 Y.SHIPMENT_NO ,
49 Y.SHIPMENT_ITEM_NO ,
50 Y.SOURCE_NM
51* )
PRO> /
Explained.
Elapsed: 00:00:01.39
PRO> @display_plan ABHAY
Id Par Pos Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
0 #### MERGE STATEMENT (choose) Cost (4938,1,164)
1 0 1 MERGE WWCIW_DBA SHIPMENT_LINE
2 1 1 12 VIEW WWCIW_DBA
3 2 1 NESTED LOOPS (outer) Cost (4938,1,303)
4 3 1 7 VIEW WWCIW_DBA Cost (4937,1,177)
5 4 1 COUNT
6 5 1 FILTER
7 6 1 HASH JOIN PARALLEL_TO_SERIAL Cost (4046,1,150)
8 7 1 SORT (unique) PARALLEL_FROM_SERIAL
9 8 1 10 TABLE ACCESS (analyzed) WWCIW_SAP TEMP_SLSORD (full) Cost (165,79119,1898856)
10 7 2 PARTITION RANGE (all) PARALLEL_COMBINED_WITH_PARENT Pt id: 10 Pt Range: 1 - 4
11 10 1 8 TABLE ACCESS (analyzed) WWCIW_DBA SHIPMENT_LINE (full) PARALLEL_TO_PARALLEL Pt id: 10 Pt Range: 1 - 4 Cost (3501,32155,4051530)
12 6 2 9 TABLE ACCESS (analyzed) WWCIW_SAP TEMP_SLORDT (full) Cost (891,2,58)
13 3 2 2 TABLE ACCESS (analyzed) WWCIW_DBA SHIPMENT_LINE (by global index rowid) Pt id: 13 Pt Range: ROW LOCATION - ROW LOCATION Cost (3,1,126)
14 13 1 INDEX (analyzed) UNIQUE WWCIW_DBA UK_SHIPMENT_LINE (unique scan) (Columns 4 Cost (2,1,)
Thx
Abhay.