I have the following example of view merging:
CREATE VIEW VIEW3_1 AS
(SELECT PS_PARTKEY, P_NAME, P_RETAILPRICE, PS_SUPPKEY, PS_AVAILQTY
FROM PARTSUPP JOIN PART
ON PARTSUPP.PS_PARTKEY = PART.P_PARTKEY
WHERE PS_AVAILQTY > &FROMPRICE);
CREATE VIEW VIEW3_2 AS
(SELECT * FROM VIEW3_1
WHERE PS_AVAILQTY < (SELECT MIN (PS_AVAILQTY) + &MARGIN
FROM VIEW3_1) );
EXPLAIN PLAN FOR
SELECT *
FROM VIEW3_2
WHERE P_RETAILPRICE >1940;
Where PART and PARTSUPP are designed in a star pattern: A PART is supplied by a SUPPLIER and PARTSUPP records the part's supplier with (PS_PARTKEY, PS_SUPPKEY) as its primary key.
The resulting query processing plan is as follows:
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------
Plan hash value: 2032191277
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 959 | 90146 | 2443 (1) | 00:00:01 |
| 1 | NESTED LOOPS | | 959 | 90146 | 587 (1) | 00:00:01 |
| 2 | NESTED LOOPS | | 19652 | 90146 | 587 (1) | 00:00:01 |
|* 3 | TABLE ACCESS FULL | PART | 68 | 3740 | 401 (1) | 00:00:01 |
|* 4 | INDEX RANGE SCAN | PARTSUPP_PKEY | 289 | | 2 (0) | 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| PARTSUPP | 14 | 546 | 52 (0) | 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 13 | | |
|* 7 | TABLE ACCESS FULL | PARTSUPP | 228K | 2898K | 1856 (1) | 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PART"."P_RETAILPRICE">1940)
4 - access("PARTSUPP"."PS_PARTKEY"="PART"."P_PARTKEY")
5 - filter("PARTSUPP"."PS_AVAILQTY">200 AND "PARTSUPP"."PS_AVAILQTY"< (SELECT
MIN("PARTSUPP"."PS_AVAILQTY")+40 FROM "TPCHR"."PARTSUPP" "PARTSUPP" WHERE
"PARTSUPP"."PS_AVAILQTY">200))
7 - filter("PARTSUPP"."PS_AVAILQTY">200)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
My question is: Is the way I am merging the views correct? How do I know if the views are merged correctly? Is there a better solution without changing the SELECT statement?
I am new to database tuning and view mergings so any help would be deeply appreciated.