Home » SQL & PL/SQL » SQL & PL/SQL » Using source index in Merge Statement (10g)
Using source index in Merge Statement [message #417947] Wed, 12 August 2009 04:33 Go to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
Hi

Can anyone please guide me on the below ... thanks a lot

part1)

MERGE /*+ INDEX (B IDX1) */ INTO WAREHOUSE.TAB1 A
USING STAGED.TAB1 B
ON (A.ID = B.ID)
WHEN MATCHED THEN update...
WHEN NOT MATCHED THEN insert...

i have indexes on the coulmns A.ID as well as B.ID

PROBLEM: when i check the explain plan only the index on A.ID is used and B is FTS. I tried forcing a hint for B but it gets ignored.

can I not use an index of the SOURCE table in a MERGE stmt?


part2)

For the above merge statementm table 'WAREHOUSE.TAB1' (A) is List partitioned on a tab1_type column.

I assume that since 'A' is partitioned so, any upserts that are fired will first evaluate as to which partition the new records will be allocated to.

So why not split the merge based on individual 'tab1_type' values on which the table is partitioned and hopw to speed up the operation?

Heres what i tried


MERGE /*+ INDEX (B IDX2) */ INTO WAREHOUSE.TAB1 A
USING STAGED.TAB1 B
ON (A.ID = B.ID
AND B.TAB1_TYPE = 'INV')
WHEN MATCHED THEN update...
WHEN NOT MATCHED THEN insert...


MERGE /*+ INDEX (B IDX2) */ INTO WAREHOUSE.TAB1 A
USING STAGED.TAB1 B
ON (A.ID = B.ID
AND B.TAB1_TYPE = 'CNO')
WHEN MATCHED THEN update...
WHEN NOT MATCHED THEN insert...

MERGE /*+ INDEX (B IDX2) */ INTO WAREHOUSE.TAB1 A
USING STAGED.TAB1 B
ON (A.ID = B.ID
AND B.TAB1_TYPE = 'INV')
WHEN MATCHED THEN update...
WHEN NOT MATCHED THEN insert...

WHERE IDX2 IS A COMPOSITE INDEX ON (B.ID , B.TAB1_TYPE);

AGAIN THE INDEX IDX2 isnt included in the expain plans ...


I m i doing this wrong ?

1) can I not use an index of the SOURCE table in a MERGE stmt?

2) Is it a good idea to split the merge to correspond to partitions ?



Re: Using source index in Merge Statement [message #417952 is a reply to message #417947] Wed, 12 August 2009 04:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd expect the query in 1) to use a FTS and a hash join. You've got no conditions on Staged.Tab1, so it's going to look at all the rows.

2) If you rewrote the merge as
MERGE INTO WAREHOUSE.TAB1 A
USING (SELECT *
       FROM   STAGED.TAB1
       WHERE  TAB1_TYPE = 'INV' )B
ON (A.ID = B.ID)
WHEN MATCHED THEN update...
WHEN NOT MATCHED THEN insert...
and changed the index to have Tab_Type as a leading column then it might use the index.
Re: Using source index in Merge Statement [message #417956 is a reply to message #417952] Wed, 12 August 2009 05:07 Go to previous messageGo to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
yes, i did try that, but the query cost actually increased which leaves me with 2 questions again..

1) am i right to assume theat spliting the merge would help?

2) and more importantly , why isnt the index on the source tables ever being used ?

Re: Using source index in Merge Statement [message #417967 is a reply to message #417956] Wed, 12 August 2009 05:30 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're going to be accessing all (or most) of the rows in the source table that it will almost certainly be slower to split the update into several smaller updates that each access rows via an index.

The index on the source table isn't being used because
1) the only time you specify any restrictions on which rows from the source table you're looking for is when you are looking to see if this row exists in table A, at which point it's already fetched the row, so it's too late for the restriciton to have an effect

2) Your index has Tab_Type as the second column, not the first - there's no easy way to use this index to select rows of a specific Tab_Type from table B.
Previous Topic: DROP TABLE DEPT - dependencies
Next Topic: vaidating the table structure of 2 tables (merged)
Goto Forum:
  


Current Time: Sat Dec 10 04:49:27 CST 2016

Total time taken to generate the page: 0.10051 seconds