CODEWITHSUNDEEP
sqloracle-databaseloopssubquerynotin
Cmen535
Cmen535

Reputation: 25

Query Performance Opttimization using NOT IN(Oracle Sql Developer)

I have been trying to optimize performance to the following query. I request all experts in this field to give me a hand and suggestions.

I have app. 70k records and my requirement says to remove duplicates. I need to improve the performance of the below query.

select *
  from x.vw_records
 where id not in
       (select distinct id
          from x.vw_datarecords
         where effective_date >= trunc(sysdate - 30)
           and book in (select book_shortname from x.vw_datarecords))
union
select distinct id
  from x.vw_historyrecords
 where effective_date >= trunc(sysdate - 30)
   and book in (select book_shortname from x.vw_datarecords)
union
select distinct id
  from x.vw_transactiondata
 where effective_date >= trunc(sysdate - 30)
   and book in (select book_shortname from x.vw_datarecords);
union
  select distinct id
    from x.vw_cashdata
   where effective_date >= trunc(sysdate - 30)
     and book in (select book_shortname from x.vw_datarecords)

Currently It takes ten minutes to count no. of rows using count(*). Suggest me any ideas to tune performance of this query.

Thanks in Advance.

Upvotes: 0

Views: 7362

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35603

I suspect you need indexes. What indexes do you have on the tables involved in your query?

& Time to learn how to use an "explain plan" which is an essential tool for query optimization. It isn't that hard to get one. They may be a bit harder to understand however. Please include the explain plan output with your question.

    EXPLAIN PLAN FOR
      <<Your SQL_Statement here>>
    ;

    SET LINESIZE 130
    SET PAGESIZE 0
    SELECT * FROM table(DBMS_XPLAN.DISPLAY);

There is absolutely zero benefit from using "select distinct" when you are using "union", do not do both, just do one.

Upvotes: 1

Kritner
Kritner

Reputation: 13765

I've always found better performance swapping out a NOT IN (query) with a left join + where iS NULL

example instead of:

select *
from x.vw_records
where id not in (
    select distinct id
    from x.vw_datarecords
    where effective_date >= trunc(sysdate - 30)
        and book in (
            select book_shortname from x.vw_datarecords
        )

use:

select *
from x.vw_records vr
left join vw_datarecords vdr on vr.id = vdr.id
    and effective_date >= trunc(sysdate - 30)
        and book in (
            select book_shortname from x.vw_datarecords
        )
where vdr.id IS NULL

additionally, you can sometimes get noticeably better performance by doing a group by rather than distinct.

Upvotes: 3

Shepherdess
Shepherdess

Reputation: 651

If you could try to use exists/not exists clause in place of in/not in (http://www.techonthenet.com/sql/exists.php). That generally runs much faster.

Upvotes: 0

Related Questions