Stanley Clark
Stanley Clark

Reputation: 71

Oracle CASE expression in query plan

The background to this question is that I am currently investigating the query plans generated by having Oracle VPD column masking policies active. I would assume that the underlying rewrite is expressed as a CASE expression, e.g. SELECT CASE WHEN ss_quantity > 13 THEN ss_quantity ELSE NULL END ss_quantity FROM store_sales to represent the cell-level policy ss_quantity > 13 on the ss_quantity column of the store_sales table.

The goal is to be able to see and verify where in the query execution plan the CASE expression is executed. For instance in a query such as:

select ss_quantity
from store_sales, date_dim
where ss_sold_date_sk = d_date_sk
and d_year = 1998;

However, the execution plan generated from DBMS_XPLAN does not tell me where the CASE expression is executed. The plan is below. From this plan I cannot tell if the CASE statement is executed as part of the projection of the HASH JOIN (1) or as part of the projection of the TABLE ACCESS FULL (3).

Does anyone know a way to get this information?

PLAN_TABLE_OUTPUT
Plan hash value: 2770377741
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   117K|  5966K| 11576   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |             |   117K|  5966K| 11576   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DATE_DIM    |    15 |   390 |   377   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| STORE_SALES |  3035K|    75M| 11191   (1)| 00:00:01 |
----------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / "DATE_DIM"@"SEL$1"
   3 - SEL$F5BB74E1 / "STORE_SALES"@"SEL$2"
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("SS_SOLD_DATE_SK"="D_DATE_SK")
   2 - filter("D_YEAR"=1998)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1; rowset=256) "SS_QUANTITY"[NUMBER,22]
   2 - (rowset=256) "D_DATE_SK"[NUMBER,22]
   3 - (rowset=256) "SS_SOLD_DATE_SK"[NUMBER,22], "SS_QUANTITY"[NUMBER,22]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Upvotes: 0

Views: 88

Answers (0)

Related Questions