CODEWITHSUNDEEP
sqloracle-databasenested
Pramendra Raghuwanshi
Pramendra Raghuwanshi

Reputation: 415

Simplify the nested query

I want to fetch the records which are added and removed on given date (on the basis of id_external ), so i have used below query which is giving me expected result but it's taking a lot of time and LOGICALREAD are too high,... can somebody please simplify it

SELECT ar.*
      FROM t_row_data ar
      WHERE ar.id_instance     IS NULL
      AND ar.id_category     IS NULL
      AND ar.source_name      ='SomeSource'
      AND ar.eco_date        IN (date '2017-12-22', date '2017-12-21')
      AND ar.active           = 'Y'
      AND ar.id_external IN 
        (SELECT ar.id_external
              FROM t_row_data ar
              WHERE ar.id_instance     IS NULL
              AND ar.id_category     IS NULL
              AND ar.source_name      ='SomeSource'
              AND ar.eco_date        IN (date '2017-12-22', date '2017-12-21')
              AND ar.active           = 'Y'
              GROUP BY ar.id_external
              HAVING COUNT(1) = 1)

Upvotes: 2

Views: 54

Answers (1)

user5683823
user5683823

Reputation:

Assuming the table t_row_data has the columns id_external, id_instance, id_category, source_name, eco_date, active, col7, col8, ..., you could rewrite the query like this:

select   id_external, null as id_instance, null as id_category,
         'SomeSource' as source_name, max(eco_date) as eco_date, 'Y' as active,
         max(col7) as col7, max(col8) as col8, ...
from     t_row_data
where    id_instance is null
  and    id_category is null
  and    source_name = 'SomeSource'
  and    eco_date    between date '2017-12-21' and date '2017-12-22'
  and    active      = 'Y'
group by id_external
having   count(*) = 1
;

Upvotes: 1

Related Questions