SQL & PL/SQL

Hello guys

There is a view that's created in the database call "v1",let's say. It is a view of table fact1. The logic behind is that when fact1 with all the transaction type=1 outer join fact1 with all transaction type =2.

So a part of the view goes like this:

select * from (select * from fact1 where transactiontypeid = 1) l
full Outer join (select * from fact1 where transactiontypeid = 2) d
on l.company = d.company;

This query is taking forever to execute.. The explain plan is showing that the query is scanning fact1 4 times, which is normal. When I break this sql down into smaller pieces, like running "select * from fact1 where transactiontypeid = 1" and "select * from fact1 where transactiontypeid = 2" separately, each query only takes 5 seconds ave to return.. But when outer join these 2, it would take more than 20 mins..

I have created local index on transactiontypeid and company columns, but it doesn't help.

Based on our environment, this view will have to be there with such logic, also using hints is not suggested.. Is there a way we can still tune the performance of the above logic?


Ohh BTW, we are using Oracle 10g.

Much thanks

Edited by: user7276913 on Oct 27, 2009 8:18 AM

Edited by: user7276913 on Oct 27, 2009 8:18 AM

Edited by: user7276913 on Oct 27, 2009 8:21 AM