Database DataWarehousing (MOSC)

MOSC Banner

Best way to join the large partitioned_table and a very small table in query

edited Oct 4, 2010 11:46AM in Database DataWarehousing (MOSC) 12 commentsAnswered
 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 :

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center