CODEWITHSUNDEEP
oracle-databaseperformanceindexing
Ram
Ram

Reputation: 39

Oracle Query Performance Improvement

I have two tables with the below structure

INPUTFILELOG with columns as

FILE_ID
RECORD_SEQ
ACCOUNT
CHILD_ACCOUNT

PURGE with column as

ACCOUNT
DATE
DETAILS

QUERY

delete from INPUTFILELOG il
where  exists
       ( select 1 from PURGE po
         where  po.account = ':NUMBER'
         and    ( po.account = il.account OR po.account = il.child_account ) )

The performance of the above query is bad even though there are indexes on PURGE.ACCOUNT and INPUTFILELOG.ACCOUNT and INPUTFILELOG.CHILD_ACCOUNT. Can someone please help ?

UPDATE

I tried the below query. Does it look good? Is there a better way to handle it?

delete from INPUTLOG sif
where  exists
       ( select t.fileno, t.rec
         from   ( SELECT sif2.FILE_ID as fileno, sif2.RECORD_SEQ as rec
                  FROM   purge po 
                         INNER JOIN INPUTLOG sif2 
                              ON (   po.ACCOUNT = sif2.ACCOUNT
                                  OR po.ACCOUNT = sif2.CHILD_ACCOUNT )
                  where  po.account = ':NUMBER' ) t
         WHERE  sif.FILE_ID = t.FILEno
         AND    sif.record_seq = t.rec)

Upvotes: 2

Views: 89

Answers (3)

Aslam Jiffry
Aslam Jiffry

Reputation: 1316

Even though you have indexes , if you use "OR" operator in the Where clause, index will not be used. If you want to benefit Indexes avoid "OR" operator in where clause.

Thanks & Regards

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You want to delete one account only. If there were only, say, ten accounts in the table, a full table scan would be the best way to delete that big percentage of table data. But you say that deletion is much faster when you only either delete accounts or child accounts. I gather that the added up time of the two deletes is very much shorter then the one with your combined query. So obviously an index is used then, which means it must be only a very small percentage of the table that gets deleted.

Two ideas:

  1. Well, obviously you can just run two delete statements instead of one and have achieved what you want to.
  2. Show the DBMS very clearly that it's only one account you want to delete from the table INPUTFILELOG (by inputfilelog.account = ...).

Hence:

delete from inputfilelog where account =
  (select account from purge where account = ':NUMBER');

delete from inputfilelog where child_account = 
  (select account from purge where account = ':NUMBER');

Maybe the = suffices even to make the combined statemet run fast (but using two indexes combined with OR usually isn't).

delete from inputfilelog
where account = (select account from purge where account = ':NUMBER')
or child_account = (select account from purge where account = ':NUMBER');

Another approach with inputfilelog.account = ...:

delete from inputfilelog
where (account = :NUMBER or child_account = :NUMBER)
and exists (select * from purge where account = :NUMBER);

Upvotes: 1

dbusern
dbusern

Reputation: 315

Based on your latest inputs, I would like to suggest to break the orignial query into the following two queries and validate if they are functinally fine (which I think they should be) and validate their performance. I think the following two may perform better and the issue here could be due to the "OR OPERATOR" that leads to the full table scan.

DELETE FROM INPUTLOG il
      WHERE EXISTS
               (SELECT 1
                  FROM PURGE po
                 WHERE po.account = ':NUMBER' AND (po.account = il.account))


DELETE FROM INPUTLOG il
      WHERE EXISTS
               (SELECT 1
                  FROM PURGE po
                 WHERE     po.account = ':NUMBER'
                       AND (po.account = il.child_account))

Upvotes: 2

Related Questions