Question: 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.

Answer: 
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.
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.