SQL & PL/SQL

Hi ,

it's always stated (e.g. by Tom Kyte) that

"select * from view;
select * from ( <text of view> );

are the same -

However if you look at the following example you might find out that this is not always the case.
First of all the statements result in different execution plans and (what is more dramatic) they also result in a huge
performance difference in "real life" (where the 3 tables are populated with thousands of records) - Why ?

I have 3 tables in my example (PARENTTAB,CHILDTAB,FOREIGNTAB) and one of them is also realized as a view (FOREIGNTAB -> FOREIGNVIEW):
See the following DDL/DML Statements to reproduce the szenario. (I did that one in ORACLE 11g

CREATE TABLE CHILDTAB
(
ID NUMBER(2) NOT NULL ,
FKID NUMBER(2) NULL ,
TEXT VARCHAR2(128) NULL
);



CREATE UNIQUE INDEX XPKCHILDTAB ON CHILDTAB
(ID ASC);



ALTER TABLE CHILDTAB
ADD CONSTRAINT XPKCHILDTAB PRIMARY KEY (ID);



CREATE INDEX XIF2CHILDTAB ON CHILDTAB
(FKID ASC);



CREATE TABLE FOREIGNTAB
(
ID NUMBER(2) NOT NULL ,
TEXT VARCHAR2(128) NULL
);



CREATE UNIQUE INDEX XPKFOREIGNTAB ON FOREIGNTAB
(ID ASC);



ALTER TABLE FOREIGNTAB
ADD CONSTRAINT XPKFOREIGNTAB PRIMARY KEY (ID);



CREATE TABLE PARENTTAB
(
ID NUMBER(2) NOT NULL ,
TEXT VARCHAR2(128) NULL ,
CHILDID NUMBER(2) NOT NULL
);



CREATE UNIQUE INDEX XPKPARENTTAB ON PARENTTAB
(ID ASC);



ALTER TABLE PARENTTAB
ADD CONSTRAINT XPKPARENTTAB PRIMARY KEY (ID);



CREATE INDEX XIF1PARENTTAB ON PARENTTAB
(CHILDID ASC);



ALTER TABLE CHILDTAB
ADD (CONSTRAINT FOREIGNTAB_CHILDTAB FOREIGN KEY (FKID) REFERENCES FOREIGNTAB(ID));



ALTER TABLE PARENTTAB
ADD (CONSTRAINT PARENT_CHILD FOREIGN KEY (CHILDID) REFERENCES CHILDTAB(ID));



The fast way:

SELECT count(*)
FROM PARENTTAB,CHILDTAB,FOREIGNTAB
WHERE CHILDTAB.ID = PARENTTAB.CHILDID
AND FOREIGNTAB.id(+) = CHILDTAB.FKID;



Operation Optimizer Cost Cardinality Bytes CPU Cost IO Cost Time
SELECT STATEMENT REMOTE ALL_ROWS 2 1 13 307121 2 1
b SORT AGGREGATE 1 13
INDEX FAST FULL SCAN XIF1PARENTTAB 2 1 13 307121 2 1


create or replace view foreignView
as
select id,text
from foreigntab;

The slower way:


SELECT count(*)
FROM PARENTTAB,CHILDTAB,foreignView
WHERE CHILDTAB.ID = PARENTTAB.CHILDID
AND foreignView.id(+) = CHILDTAB.FKID;


Operation Optimizer Cost Cardinality Bytes CPU Cost IO Cost Time
SELECT STATEMENT REMOTE ALL_ROWS 2 1 39 309021 2 1
SORT AGGREGATE 1 39
NESTED LOOPS 2 1 39 309021 2 1
INDEX FAST FULL SCAN XIF1PARENTTAB 2 1 13 307121 2 1
INDEX UNIQUE SCAN XPKCHILDTAB 0 1 26 1900 0 1

**Please Note that with "real data" there is a huge impact on the performance to do it over the view (I know that the costs do not indicate this – but that’s another thing)**
The example was produced with Version 11.1.0.7.0