Suggest me which query to use

General
Enthusiast

Suggest me which query to use

Hi All,

Please find the below two queries. I need to know, is both the queries will give the same result? if yes then which one will consume more spool.

FYI: TABLEA contains 4billion records, TABLEB contains 10 million records and TABLEC contains 1million records.

SELECT * FROM TABLEA

INNER JOIN TABLEB

ON A.COLUMN = B.COLUMN

LEFT OUTER JOIN TABLEC

ON A.COLUMN = C.COLUMN AND C.COL_DT = CURRENT_DATE

WHERE A.COL_DT BETWEEN B.COL_DT AND B.COL_DT1;

SELECT * FROM TABLEA

INNER JOIN TABLEB

ON A.COLUMN = B.COLUMN

AND A.COL_DT BETWEEN B.COL_DT AND B.COL_DT

LEFT OUTER JOIN TABLEC

ON A.COLUMN = C.COLUMN AND C.COL_DT = CURRENT_DATE;

Tags (1)
1 REPLY
Enthusiast

Re: Suggest me which query to use

Run an explain on each construct and see which one is less expensive.  At first glance it looks like the output should be identical, but that's up to the optimizer.   The data volume you've described isn't a problem for TD if you're joining on the Primay Indices.  Your predicate columns appear to be dates, you should consider partitioning the tables by the date columns in the query, and partition all the tables alike if this query is going to be run frequently.  You should also look at building a sparse JI incorporating the join and where clauses.

Cheers