| In Line Views [message #191283] |
Tue, 05 September 2006 08:13 |
ksmatharu
Messages: 8
Registered: November 2005
|
Junior Member |
|
|
I have a query about in line views and the impact they have when tables are joined together i.e. why the optimizer behaves in the way it does?
I have two querys, the order in which the joins are performed in are different.
Query 1 (Full Scans all tables)
SELECT hdr.*, a.*, b.*
FROM cfr hdr,
s07 a,
s75 b,
(SELECT start_date, end_date
FROM extract_dates) iv
WHERE hdr.supplier_id IN('TCG', 'GUK', 'YKE')
AND hdr.creation_date >= ADD_MONTHS(SYSDATE, -6)
AND hdr.date_loaded BETWEEN iv.start_date AND iv.end_date
AND hdr.creation_date = a.creation_date
AND hdr.generation_number = a.generation_number
AND hdr.supplier_id = a.supplier_id
AND hdr.file_type = a.file_type
AND a.creation_date = b.creation_date
AND a.generation_number = b.generation_number
AND a.confirmation_ref = b.confirmation_ref
AND a.supplier_id = b.supplier_id
AND a.file_type = b.file_type;
Query 2 (Uses Indexes)
SELECT iv.*
FROM extract_dates e,
(SELECT hdr.*
FROM cfr hdr,
s07 a,
s75 b
WHERE hdr.supplier_id IN('TCG', 'GUK', 'YKE')
AND hdr.creation_date >= ADD_MONTHS(SYSDATE, -6)
AND hdr.creation_date = a.creation_date
AND hdr.generation_number = a.generation_number
AND hdr.supplier_id = a.supplier_id
AND hdr.file_type = a.file_type
AND a.creation_date = b.creation_date
AND a.generation_number = b.generation_number
AND a.confirmation_ref = b.confirmation_ref
AND a.supplier_id = b.supplier_id
AND a.file_type = b.file_type) iv
WHERE iv.date_loaded BETWEEN e.start_date AND e.end_date;
The former peforms the SQL quicker because it uses the index...but I can't understand why the former, which contains an inline view decides to FULL SCAN the tables. I know it has something to do with the way I am joining to the extract_dates table, which is populated with a start and end date that are used as part of the criteria to select records.
Can I have a logical explanation.
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
