Home » RDBMS Server » Server Administration » In Line Views
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


Previous Topic: Space allocation in a segment
Next Topic: Russian and Hindi Database Creation
Goto Forum:
  


Current Time: Fri Dec 09 15:43:17 CST 2016

Total time taken to generate the page: 0.16211 seconds