Improve View Merging

User_3DSZW

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.