CBO and Explain Plan Question - where can I get answer 1

[cmmrfrds]

Oracle 9.2

Here is a simple query with an inline view. I don't understand why the explain plan does full table scans 3 times.

With PatView AS
(
SELECT
PATIENT.PAT_ID,
PATIENT.PAT_MRN_ID,
PATIENT.PAT_NAME,
PATIENT.HOME_PHONE,
PATIENT.WORK_PHONE,
PATIENT.CUR_PCP_PROV_ID,
PATIENT.DEATH_DATE,
PATIENT.PAT_STATUS
FROM CLARITY.PATIENT PATIENT
)
Select
PAT_ID,
PAT_MRN_ID
From PatView
Union All
Select
PAT_ID,
PAT_MRN_ID
From PatView
Union All
Select
PAT_ID,
PAT_MRN_ID
From PatView

Eplain Plan.
Operation Object Name Rows Bytes Cost

SELECT STATEMENT Optimizer Mode=CHOOSE 2 M 5226
UNION-ALL
TABLE ACCESS FULL CLARITY.PATIENT 924 K 14 M 1742
TABLE ACCESS FULL CLARITY.PATIENT 924 K 14 M 1742
TABLE ACCESS FULL CLARITY.PATIENT 924 K 14 M 1742

Thought this should do 1 full table scan in the inline View. Does the CBO ignore the inline view? Is it cheaper to do 3 full table scans?

 

[haste]

There are no WHERE clauses in the statements joined by the UNION ALL or the view PatView, so the statement does a full tablescan

-- statement 1 of the where clause
Select
    PAT_ID,     
     PAT_MRN_ID
From PatView
-- [ No WHERE clause ] --
Union All   
Select
    PAT_ID,     
     PAT_MRN_ID
From PatView
-- [ No WHERE clause ] --
Union All
Select
    PAT_ID,     
     PAT_MRN_ID
From PatView
-- [ No WHERE clause ] --

I think that answers why the CBO does a full tablescan. The expense of the 3 tablescans will depend on the size of the table. The CBO will do a full tablescan on small tables even if they have an index.
Havent found anything on why the CBO does 3 tablescans, guess it treats each UNION'ed statement as separate.

 

[haste]

Got this from

http://www.techonthenet.com/sql/union_all.php

The UNION ALL query allows you to combine the result sets of 2 or more "select" queries.

Oracle doesnt treat the statements as separate. It doesnt make an attempt to analyse the entire UNION ALL statement before excution.

 

[cmmrfrds]

Thank you for the idea on the Where clause. I added a reference to the pat_id in the inline view and the results changed significantly. Actually, this is what I had hoped for.

Where pat_id >= ' '

New Explain Plan.
Operation Object Name Rows Bytes Cost

SELECT STATEMENT Optimizer Mode=CHOOSE 2 M 234
RECURSIVE EXECUTION .SYS_LE_2_0
TEMP TABLE TRANSFORMATION
UNION-ALL
VIEW 924 K 227 M 78
TABLE ACCESS FULL SYS.SYS_TEMP_0FD9D6604_92FB7ADE 924 K 14 M 78
VIEW 924 K 227 M 78
TABLE ACCESS FULL SYS.SYS_TEMP_0FD9D6604_92FB7ADE 924 K 14 M 78
VIEW 924 K 227 M 78
TABLE ACCESS FULL SYS.SYS_TEMP_0FD9D6604_92FB7ADE 924 K 14 M 78

The cost number now is 234 as opposed to 5226 on the original without the where clause. It is now doing a full table scan on the inline view data instead of the table, which was the purpose of making the inline view.