CODEWITHSUNDEEP
sqloracle-databaseperformancequery-performance
user1751356
user1751356

Reputation: 615

Oracle Sql tuning with index

I have a table T with some 500000 records. That table is a hierarchical table. My goal is to update the table by self joining the same table based on some condition for parent - child relationship The update query is taking really long because the number of rows is really high. I have created an unique index on the column which helps identifying the rows to update (meanign x and Y). After creating the index the cost has reduced but still the query is performing a lot slower.

This my query format 

update T
set a1, b1
= (select T.parent.a1, T.parent.b1
 from T T.paremt, T T.child
where T.parent.id = T.child.Parent_id
and T.X = T.child.X
and T.Y = T.child.Y

after creating the index the execution plan shows that it is doing an index scan for CRS.PARENT but going for a full table scan for for CRS.CHILD and also during update as a result the query is taking for ever to complete.

Please suggest any tips or recommendations to solve this problem

Upvotes: 0

Views: 150

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17944

You are updating all 500,000 rows, so an index is a bad idea. 500,000 index lookups will take much longer than it needs to.

You would be better served using a MERGE statement.

It is hard to tell exactly what your table structure is, but it would look something like this, assuming X and Y are the primary key columns in T (...could be wrong about that):

MERGE INTO T
USING ( SELECT  TC.X,
                TC.Y,
                TP.A1,
                TP.A2 
        FROM    T TC 
        INNER JOIN T TP ON TP.ID = TC.PARENT_ID ) U
ON ( T.X = U.X AND T.Y = U.Y )
WHEN MATCHED THEN UPDATE SET T.A1 = U.A1, 
                             T.A2 = U.A2;

Upvotes: 3

Related Questions