Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Improve View Merging

User_3DSZWJun 4 2021 — edited Jun 4 2021

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.

Comments

Post Details

Added on Jun 4 2021
0 comments
84 views