CODEWITHSUNDEEP
sqloracle-databasequery-performance
daniel_mutu
daniel_mutu

Reputation: 163

Tuning for Oracle query

I need to increase performance of this query:

 select t.*
        ,(select max(1)
          from schema1.table_a t1
          where  1=1
                    to_date(t.misdate, 'YYYYMMDD') between t1.startdateref and t1.enddateref
                     and sysdate between t1.startdatevalue and t1.enddatevalue
                     and t1.idpma = t.idpm)
     from schema2.table_b t

Any Ideas? Thanks

Upvotes: 0

Views: 48

Answers (1)

The Impaler
The Impaler

Reputation: 48875

Well you don't have any filtering condition on table_b. This means the best plan includes a full table scan on table_b. This would be optimal.

Having said that, now you need to focus on table_a. That one should be accessed using index range scans on either:

  • idpma, then by startdateref.
  • or idpma, then by startdateref.

Yes, it's one or the other. For Oracle's cost-based optimizer (CBO) to pick the best plan, you'll need to add the following indexes:

create index ix1 on schema1.table_a (idpma, startdateref);

create index ix2 on schema1.table_a (idpma, startdatevalue);

Try with this ones and see how it works.

Upvotes: 1

Related Questions