Home > Querying Dimensional Objects > Viewing Execution Plans
![]() Previous |
![]() Next |
You can generate and view execution plans for queries against cubes and dimensions the same as for those against relational tables.
The SQL EXPLAIN PLAN
command creates a table with the content of the explain plan. The default table name is PLAN_TABLE
.
The following command creates an execution plan for a basic query on a cube:
EXPLAIN PLAN FOR SELECT t.long_description time, p.long_description product, cu.long_description customer, ch.long_description channel, f.sales sales FROM time_calendar_view t, product_primary_view p, customer_shipments_view cu, channel_primary_view ch, units_cube_view f WHERE t.level_name = 'CALENDAR_YEAR' AND p.level_name = 'TOTAL' AND cu.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY t.end_date;
The DISPLAY
table function of the DBMS_XPLAN
PL/SQL package formats and displays information from an execution plan, as shown in Example: Execution Plan for a Cube Query.
Execution Plan for a Cube Query
SQL> SELECT plan_table_output FROM TABLE(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ Plan hash value: 1667678335 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 100 | 104 (3)| 00:00:02 | | 1 | SORT ORDER BY | | 1 | 100 | 104 (3)| 00:00:02 | | 2 | JOINED CUBE SCAN PARTIAL OUTER| | | | | | | 3 | CUBE ACCESS | UNITS_CUBE | | | | | | 4 | CUBE ACCESS | CHANNEL | | | | | | 5 | CUBE ACCESS | CUSTOMER | | | | | | 6 | CUBE ACCESS | PRODUCT | | | | | |* 7 | CUBE ACCESS | TIME | 1 | 100 | 103 (2)| 00:00:02 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - filter(SYS_OP_ATG(VALUE(KOKBF$),12,13,2)='CALENDAR_YEAR' AND SYS_OP_ATG(VALUE(KOKBF$),43,44,2)='TOTAL' AND SYS_OP_ATG(VALUE(KOKBF$),33,34,2)='TOTAL' AND SYS_OP_ATG(VALUE(KOKBF$),23,24,2)='TOTAL') 22 rows selected.
Example: Execution Plan for a Cube Join shows an execution plan for a query that joins a cube and a table. See "Joining Cubes to Tables and Views" for the query.
Execution Plan for a Cube Join
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- Plan hash value: 3634608218 ------------------------------------------------------------------------------------------------- | Id | Operation |Name |Rows | Bytes |TempSpc|Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | |1464 | 128K| | 1524 (94)| 00:00:19| | 1 | SORT ORDER BY | |1464 | 128K| 152K| 1524 (94)| 00:00:19| |* 2 | CUBE JOIN | |1464 | 128K| | 1422 (100)| 00:00:18| | 3 | TABLE ACCESS FULL |CUSTOMER_DIM| 61 | 2379 | | 4 (0)| 00:00:01| | 4 | JOINED CUBE SCAN PARTIAL OUTER| | | | | | | | 5 | CUBE ACCESS |UNITS_CUBE | | | | | | | 6 | CUBE ACCESS |CHANNEL | | | | | | | 7 | CUBE ACCESS |PRODUCT | | | | | | |* 8 | CUBE ACCESS |TIME |2520 | 125K| | 1417 (100)| 00:00:18| -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CU"."SHIP_TO_ID"=SYS_OP_ATG(VALUE(KOKBF$),76,77,2)) 8 - filter(SYS_OP_ATG(VALUE(KOKBF$),32,33,2)='CY2006' AND SYS_OP_ATG(VALUE(KOKBF$),85,86,2)='TOTAL' AND SYS_OP_ATG(VALUE(KOKBF$),65,66,2)='TOTAL') 22 rows selected.
Table: Descriptions of Execution Plans for Cubes and Dimensions describes the types of execution plans for cubes.
Descriptions of Execution Plans for Cubes and Dimensions
Operation | Option | Description |
---|---|---|
|
-- |
Joins a table or view on the left and a cube on the right. |
|
|
Uses an antijoin for a table or view on the left and a cube on the right. |
|
|
Uses an antijoin (Single-sided Null Aware) for a table or view on the left and a cube on the right. The join column on the right (cube side) is SELECT cols FROM table WHERE table.c1 NOT IN (SELECT col FROM cube WHERE cube.col IS NOT NULL) |
|
|
Uses an outer join for a table or view on the left and a cube on the right. |
|
|
Uses a right semijoin for a table or view on the left and a cube on the right. |
|
-- |
Uses inner joins for all cube access. |
|
|
Uses an outer join for least one dimension, and inner joins for the other dimensions. |
|
|
Uses outer joins for all cube access. |