Oracle – SQL – Nest Queries Vs Where Clase

I have 3 tables

1. BIG   (~6 Million Records, indexed on ID1 and some other columns     , not partitioned        , on DB instance 1)
2. VBIG  (~6 Billion Records, indexed on ID2 and some other columns     , partition on DATE field, on DB instance 2)
3. VVBIG (> VBIG by 10-15%  , indexed on ID1, ID2 and some other columns, partition on DATE field, on DB instance 2)

For a given DATE and few other filter conditions, I am using data from these 3 table to run some processing. I have to decide between the 2 queries.

select /*+ ORDERED */ 
    column1, column2
from
    BIG, VBIG, VVBIG
where 
    BIG.ID1   = VVBIG.ID1    and
    VBIG.ID2  = VVBIG.ID2    and
    VBIG.DATE = VVBIG.DATE   and
    VBIG.DATE = '1-Jan-2015' and
    BIG.CL1   = 'XYZ'        and
    VVBIG.CL1 = 'ABC'

OR

select /*+ ORDERED */ 
    column1, column2
from
    (select /*+ parallel */ from BIG   
        where BIG.CL1    = 'XYZ'), 
    (select /*+ parallel */ from VBIG  
        where VBIG.DATE  = '1-Jan-2015'), 
    (select /*+ parallel */ from VVBIG 
        where VVBIG.DATE = '1-Jan-2015' and VVBIG.CL1 = 'ABC')
where 
    BIG.ID1   = VVBIG.ID1    and
    VBIG.ID2  = VVBIG.ID2    and
    VBIG.DATE = VVBIG.DATE   and

Not sure if oracle is playing tricks, or if it is the distributed DB architecture, but my explain plan changes randomly.

My tests with synthetic data shows better performance with option#2.
Is there a way I can rest assured that this would be the correct choice?

Also my Performance DBA suggested of using

/*+ use_hash( BIG, VBIB, VVBIG ) full(BIG) full(VBIG) full(VVBIG)  */

instead of the ORDERED hint. Would it be advisable as I am getting a CARTESIAN JOIN MERGE with his suggested change.


Source: oracle

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.