Thanks for the question, AMIT.
Asked: June 30, 2016 - 3:26 pm UTC
Last updated: July 01, 2016 - 3:53 am UTC
Version: 11g
Viewed 1000+ times
You Asked
I have three tables T1, T2 & T3.
T1 has about 29 million rows
T2 has 25 million rows
T3 has about 9 million rows
The below query takes more than an hour to run. I have tried indexes on the join columns, temp tables, hints but nothing helps.
is there any way we can tune this query to run faster.
select
T1.*,
T2.*,
T3.*
from
t1,
t2,
t3
where
T1.sku = T2.Sku (+)
AND
T1.Product = T3.Product;
the explain plan does a full table scan for all the three tables
and Connor said...
To me, a full scan looks like the perfectly sensible option.
On the assumption that T3.product is natural parent of T1.product, then the join T1=>T3, doesnt reduce the number of rows. And since T1=>T2 is an outer join, then the same applies - every row in T1 is a likely member of the result set.
We dont know the nullability of sku and product, but on the asssumption they are present, I certainly dont want to do 29million index lookups from T1 to T3, and similarly 29million index lookups from T1 to T2.
Full scan probably makes the most sense here.
If you need it faster, perhaps look at parallel query.
Is this answer out of date? If it is, please let us know via a Comment