Best way to join the large partitioned_table and a very small table in query
Hi ,
I have a very large partitioned table L ( 140 million records ) and a very small table S ( 10 reords ) and I have following query :
select a.*
from L , S
where L.col1=S.col1
and S.col2 = 'MOBILE' ;
Note :
1. Both L.col1 and S.col1(PK) has indexes.
2. we do not have PK-FK relationship as L is fact table and does not have PK.
3. Absense of PK-FK relationship is is restricting us from creating bitmap-join-index and cluster index.
4. creating a index on S involving col1 and col2 does not help us as well.
Above query takes a lot of time and has huge cost , I get the following explain plan :
I have a very large partitioned table L ( 140 million records ) and a very small table S ( 10 reords ) and I have following query :
select a.*
from L , S
where L.col1=S.col1
and S.col2 = 'MOBILE' ;
Note :
1. Both L.col1 and S.col1(PK) has indexes.
2. we do not have PK-FK relationship as L is fact table and does not have PK.
3. Absense of PK-FK relationship is is restricting us from creating bitmap-join-index and cluster index.
4. creating a index on S involving col1 and col2 does not help us as well.
Above query takes a lot of time and has huge cost , I get the following explain plan :
0