CODEWITHSUNDEEP
plsqloracle11gexists
tefached616
tefached616

Reputation: 53

Oracle - Invalid relational operator when I'm using EXISTS

I have the following query that was given to me, where the returned record is removed from the table et_fact_reclam_ter_his_misc when the condition is met:

DELETE FROM et_fact_reclam_ter_his_misc
 WHERE num_siniest || num_exped IN
 (SELECT DISTINCT num_siniest || num_exped
          FROM et_fact_reclam_ter_his_misc t1
         WHERE NOT EXISTS (SELECT *
                  FROM et_fact_reclam_ter_misc t2
                 WHERE t1.num_siniest = t2.num_siniest));

I've been searching, and found that using EXISTS instead of IN will improve query performance. But I have run into 2 errors. The first is an error message that appears when I replace the IN operator with EXISTS:

DELETE FROM et_fact_reclam_ter_his_misc
 WHERE num_siniest || num_exped EXISTS
 (SELECT DISTINCT num_siniest || num_exped
          FROM et_fact_reclam_ter_his_misc t1
         WHERE NOT EXISTS (SELECT *
                  FROM et_fact_reclam_ter_misc t2
                 WHERE t1.num_siniest = t2.num_siniest));


ORA-00920: invalid relational operator

The second is when I try to nest a SELECT inside the DELETE, but the number of rows returned are different:

DELETE FROM et_fact_reclam_ter_his_misc
 WHERE EXISTS
 (SELECT FROM et_fact_reclam_ter_his_misc
         WHERE num_siniest || num_exped IN
               (SELECT DISTINCT num_siniest || num_exped
                  FROM et_fact_reclam_ter_his_misc t1
                 WHERE NOT EXISTS
                 (SELECT *
                          FROM et_fact_reclam_ter_misc t2
                         WHERE t1.num_siniest = t2.num_siniest)));

I appreciate any help to improve query performance

Upvotes: 0

Views: 266

Answers (2)

William Robertson
William Robertson

Reputation: 15991

Looks like this might do it:

delete et_fact_reclam_ter_his_misc t1
where  not exists
       ( select * from et_fact_reclam_ter_misc t2
         where  t2.num_siniest = t1.num_siniest
       );

In the original version, the first level subquery seems to be querying the same table that is being deleted, just in order to construct a not exists subquery. I suspect that all you want to do is delete his rows where num_siniest does not exist in the main table. Some sample data and expected results would make this clearer.

Upvotes: 0

VN'sCorner
VN'sCorner

Reputation: 1552

Could you try below, not the best approach to concatenate fields for joins,

DELETE FROM et_fact_reclam_ter_his_misc A
 WHERE EXISTS
 (  SELECT 1 FROM 
         (SELECT DISTINCT num_siniest || num_exped
          FROM et_fact_reclam_ter_his_misc t1) B
          WHERE A.num_siniest|| A.num_exped = B.num_siniest || B.num_exped 
         AND NOT EXISTS (SELECT 1
                  FROM et_fact_reclam_ter_misc t2
                 WHERE B.num_siniest = t2.num_siniest));

Seems like the concatenated columns are numbers and it would force oracle to cast it to char which could hinder performance.Below one should be faster if you are dealing with lots of records.

DELETE FROM et_fact_reclam_ter_his_misc A
 WHERE EXISTS
 (  SELECT 1 FROM 
         (SELECT DISTINCT num_siniest, num_exped
          FROM et_fact_reclam_ter_his_misc t1) B
          WHERE A.num_siniest = B.num_siniest
          AND  A.num_exped = B.num_exped 
         AND NOT EXISTS (SELECT 1
                  FROM et_fact_reclam_ter_misc t2
                 WHERE B.num_siniest = t2.num_siniest));

DISTINCT will overkill, substituting with group by will definitely be far better, so the best one will be :

DELETE FROM et_fact_reclam_ter_his_misc A
 WHERE EXISTS
 (  SELECT 1 FROM 
         (SELECT num_siniest, num_exped,count(1)
          FROM et_fact_reclam_ter_his_misc t1
          group by num_siniest, num_exped) B
          WHERE A.num_siniest = B.num_siniest
          AND  A.num_exped = B.num_exped 
         AND NOT EXISTS (SELECT 1
                  FROM et_fact_reclam_ter_misc t2
                 WHERE B.num_siniest = t2.num_siniest));

Upvotes: 1

Related Questions