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.