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

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. Received on Thu Jul 15 1999 - 03:51:24 CDT