Re: Question for a guru -?- regarding outer join+hints...

Hi.

My guess is that your driving table is different because of outer join.
Try using "ordered" hint.

  Good luck. Michael.

In article <7mk7ea$akg$1_at_nnrp1.deja.com>,   monsri_at_my-deja.com wrote:
> Hi here,
> I have 2 tables: table_A (which is pretty large, millions of records),
> and table_B (about 40 records).
> I've noticed the following (remember I use no index for this query):
>
> This query:
> > select --+ FULL(table_A) PARALLEL(table_A, 3)
> > count(*)
> > FROM table_A srd,
> > table_B cfg
> > WHERE srd.sie_msisdn = cfg.msisdn(+)
> > AND cfg.msisdn IS NOT NULL;
>
> is much quicker and more efficient than:
> > select --+ FULL(table_A) PARALLEL(table_A, 3)
> > count(*)
> > FROM table_A srd,
> > table_B cfg
> > WHERE srd.sie_msisdn = cfg.msisdn;
>
> that yet looks simpler. Of course both queries give me the same
result;
> I checked Oracle's behaviour with the EXPLAIN PLAN statement:
> > select lpad(' ', 2 * level)||operation||' '||options||'
||object_name
> q_plan
> > from plan_table
> > where statement_id = 'KTV2'
> > connect by prior id = parent_id
> > and statement_id = 'KTV2'
> > start with id = 1;
>
> and I found:
>
> > SORT AGGREGATE
> > HASH JOIN
> > TABLE ACCESS FULL VIP_ARCHIVE_CONFIG
> > TABLE ACCESS FULL SIE_RAW_DATA_19990713
> with the first query, and:
>
> > HASH JOIN
> > TABLE ACCESS FULL SIE_RAW_DATA_19990713
> > TABLE ACCESS FULL VIP_ARCHIVE_CONFIG
> > SORT AGGREGATE
> > HASH JOIN
> > TABLE ACCESS FULL SIE_RAW_DATA_19990713
> > TABLE ACCESS FULL VIP_ARCHIVE_CONFIG
> with the 2nd one (the latter is then obviously heavier, although the
> code is lighter).
>
> Could anyone please explain me WHY the first query is the best one ??
>
> Thanks !
>
> Spendius
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 16 1999 - 10:04:23 CDT