CODEWITHSUNDEEP
sqloracle-databaseperformanceselectview
egnew
egnew

Reputation: 1

Two independent Oracle Views run fast but are extremely slow when combined

I have two separate views that return data in Oracle. Queries using the views independently work well but a query using both views together is extremely slow. Here is an example:

create view masterview as 
select masterno,detailno from mastertable;

create view detailview as 
select detailno,field1,field2 from detailtable;

MasterTable has an index on MasterNo and DetailTable has an index on DetailNo.

select * from masterview 
where masterno = 1234; -- 0.429 seconds to run

select * from detailview 
where detailno in (10,20,30); -- 0.251 seconds to run

select * from DetailView 
where DetailNo in (Select DetailNo from MasterView 
                   where MasterNo = 1234); -- 407.057 seconds to run

I have tried using a number of approaches but none have made a difference. How can I get the combined query to run fast?

The problem appears to be related to the view. I can eliminate the second view and I can eliminate the IN and the problem persists:

select d.*
  from detailview d, testtable t
 where t.masterno = 1234
   and d.detailno = t.detailno;
-- DetailTable used by DetailView is indexed on DetailNo.
-- TestTable is indexed on MasterNo
-- TestTable contains two fields MasterNo,DetailNo
-- This takes 100's of seconds to run

select d.*
  from detailtable d, testtable t
 where t.masterno = 1234
   and d.detailno = t.detailno;
-- This runs in less than a second.

Upvotes: 0

Views: 401

Answers (2)

Mittal Patel
Mittal Patel

Reputation: 2762

Try to use INNER JOIN instead of IN. Maybe, Your query performance slow due to IN

select d.* 
from DetailView as d
inner join MasterView  as m 
  on d.DetailNo = m.DetailNo AND  m.MasterNo = 1234;

Upvotes: 1

Iurii
Iurii

Reputation: 1

I am not sure, but you can try this:

select d.* 
  from MasterView m, DetailView d
 where m.MasterNo = 1234
   and m.DetailNo = d.DetailNo
;

if DetailNo in mastertable non unique, use distinct

select distinct d.* ...

Upvotes: 0

Related Questions