This will be the continuation from the previous
post. Incase if you have not read the previous column yet, I encourage you
to do so now before reading this column. In this column, I am going to assume
that you have got introduced to Dimension and Fact tables in a star schema
& understand the basic capabilities and have walked through the previous
column example.
In this post, we are going to see how the plan changes when we enable
Star transformation.
demo@PDB1>
alter session set star_transformation_enabled=temp_disable;
Session
altered.
Yes, Star transformation is enabled but without “temp”.
In Star transformation, we get twice the access to dimension tables.
·
One to apply the predicates and get the
dimension key for the result.
·
Other one to get additional information once, we
got the FACT rows.
The principal behind the Star transformation is to push the dimension
table filters to fact table as subqueries like this where fact.dim1_key in ( select d.dim1_key from dim1 d where
d.some_column = ‘value’ )
demo@PDB1>
@script.sql
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID bwx1y7bh32t8v, child number 0
-------------------------------------
select *
from fact join dim1 on
(fact.dim1_id = dim1.dim1_id ) join
dim2 on
(fact.dim2_id = dim2.dim2_id ) join dim3
on (fact.dim3_id =
dim3.dim3_id
) where dim1.dim1_cod = 'One' and dim2.dim2_cod = 'One'
and
dim3.dim3_cod = 'One'
Plan hash
value: 1703177289
----------------------------------------------------------------------------------
| Id |
Operation |
Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 10000 |
| * 1
| HASH JOIN | | 1 |
10000 | 10000 |
| 2 |
MERGE JOIN CARTESIAN
| | 1 |
1 | 1 |
| 3 |
MERGE JOIN CARTESIAN
| | 1 |
1 | 1 |
| * 4
| TABLE ACCESS FULL | DIM1 |
1 | 1 | 1 |
| 5 |
BUFFER SORT | | 1 |
1 | 1 |
| * 6
| TABLE ACCESS FULL | DIM2 |
1 | 1 | 1 |
| 7 |
BUFFER SORT | | 1 |
1 | 1 |
| * 8
| TABLE ACCESS FULL | DIM3 |
1 | 1 | 1 |
| 9 |
VIEW |
VW_ST_A50 | 1 | 10000 |
10000 |
| 10 |
NESTED LOOPS | | 1 |
10000 | 10000 |
| 11 |
BITMAP CONVERSION TO ROWIDS |
| 1 | 10000 |
10000 |
| 12 |
BITMAP AND | | 1 |
| 4 |
| 13 |
BITMAP MERGE |
| 1 | |
12 |
| 14 |
BITMAP KEY ITERATION | | 1 |
| 52 |
| * 15 |
TABLE ACCESS FULL |
DIM1 | 1 |
1 | 1 |
| * 16 |
BITMAP INDEX RANGE SCAN | FACT_DIM1 | 1 |
| 52 |
|- 17 |
STATISTICS COLLECTOR | | 1 |
| 26 |
| 18 |
BITMAP MERGE | | 1 |
| 26 |
| 19 |
BITMAP KEY ITERATION | | 1 |
| 119 |
| * 20 | TABLE ACCESS FULL | DIM2 |
1 | 1 | 1 |
| * 21 | BITMAP INDEX RANGE SCAN| FACT_DIM2
| 1 | |
119 |
|- 22 |
STATISTICS COLLECTOR | | 1 |
| 87 |
| 23 |
BITMAP MERGE | | 1 |
| 87 |
| 24 |
BITMAP KEY ITERATION | | 1 |
| 394 |
| * 25 | TABLE ACCESS FULL | DIM3 |
1 | 1 |
1 |
| * 26 | BITMAP INDEX RANGE SCAN| FACT_DIM3
| 1 | |
394 |
| 27 |
TABLE ACCESS BY USER ROWID |
FACT | 10000 |
1 | 10000 |
----------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 -
access("ITEM_3"="DIM3"."DIM3_ID" AND
"ITEM_2"="DIM2"."DIM2_ID"
AND
"ITEM_1"="DIM1"."DIM1_ID")
4 -
filter("DIM1"."DIM1_COD"='One')
6 -
filter("DIM2"."DIM2_COD"='One')
8 -
filter("DIM3"."DIM3_COD"='One')
15 -
filter("DIM1"."DIM1_COD"='One')
16 -
access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
20 -
filter("DIM2"."DIM2_COD"='One')
21 -
access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
25 -
filter("DIM3"."DIM3_COD"='One')
26 -
access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")
Note
-----
- star transformation used for this
statement
- this is an adaptive plan (rows marked '-'
are inactive)
61 rows
selected.
The first part – MERGE JOIN CARTESIAN – is similar, but now the BITMAP
INDEX SINGLE VALUE has been replaced. We
read the dimension, apply the predicates and for each dimension key we get to
the bitmap index (BITMAP INDEX RANGE SCAN). These bitmaps are then merged for
each dimension (BITMAP MERGE) and then ANDed with the ones coming from the
other dimensions.
This is very efficient when the predicates has a good selectivity – but
what if the dimension tables are huge. We have to read it twice (once to get
the required dimension attributes and other to filter FACT table rows)
Now let’s load few more data into the dimension DIM1 and enable Star
transformation with Temp.
demo@PDB1>
insert into dim1
2
select ( select count(*) from dim1 )+ rownum ,
3
Initcap( to_char( to_date(level,'j'),'jsp') ) dim1_cod ,
4
rpad( '*',800,'*') dim1_text
5 from
dual
6
connect by level <=10000;
10000 rows
created.
demo@PDB1>
commit;
Commit
complete.
demo@PDB1>
exec dbms_stats.gather_table_stats(user,'dim1',no_invalidate=>false);
PL/SQL
procedure successfully completed.
demo@PDB1>
alter session set star_transformation_enabled=true;
Session
altered.
demo@PDB1>
@script.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID bwx1y7bh32t8v, child number 0
-------------------------------------
select *
from fact join dim1 on
(fact.dim1_id = dim1.dim1_id ) join
dim2 on
(fact.dim2_id = dim2.dim2_id ) join dim3
on (fact.dim3_id =
dim3.dim3_id
) where dim1.dim1_cod = 'One' and dim2.dim2_cod = 'One'
and
dim3.dim3_cod = 'One'
Plan hash
value: 4211837692
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 10000 |
| 1 |
TEMP TABLE TRANSFORMATION
| | 1 |
| 10000 |
| 2 |
LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_... | 1 |
| 0 |
| * 3
| TABLE ACCESS FULL | DIM1 |
1 | 1 | 2 |
| * 4
| HASH JOIN | | 1 |
10000 | 10000 |
| 5 |
MERGE JOIN CARTESIAN
| | 1 |
1 | 2 |
| 6 |
MERGE JOIN CARTESIAN
| | 1 |
1 | 2 |
| * 7
| TABLE ACCESS FULL | DIM2 |
1 | 1 | 1 |
| 8 |
BUFFER SORT
| | 1 |
1 | 2 |
| 9 |
TABLE ACCESS FULL
| SYS_TEMP_... | 1 | 1 |
2 |
| 10 |
BUFFER SORT
| | 2 |
1 | 2 |
| * 11 |
TABLE ACCESS FULL
| DIM3 |
1 | 1 | 1 |
| 12 |
VIEW
| VW_ST_B7873F0| 1 | 10000 |
10000 |
| 13 |
NESTED LOOPS
| | 1 |
10000 | 10000 |
| 14 |
BITMAP CONVERSION TO ROWIDS
| | 1 |
10000 | 10000 |
| 15 |
BITMAP AND
| | 1 |
| 4 |
| 16 |
BITMAP MERGE
| | 1 |
| 12 |
| 17 |
BITMAP KEY ITERATION
| | 1 |
| 52 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_... | 1 |
1 | 2 |
| * 19 | BITMAP INDEX RANGE SCAN | FACT_DIM1 |
2 | | 52 |
|- 20 |
STATISTICS COLLECTOR
| | 1 |
| 26 |
| 21 |
BITMAP MERGE
| | 1 |
| 26 |
| 22 | BITMAP KEY ITERATION | | 1 |
| 119 |
| * 23 | TABLE ACCESS FULL | DIM2 |
1 | 1 | 1 |
| * 24 | BITMAP INDEX RANGE SCAN | FACT_DIM2 |
1 | | 119 |
|- 25 |
STATISTICS COLLECTOR | | 1 |
| 87 |
| 26 |
BITMAP MERGE
| | 1 |
| 87 |
| 27 | BITMAP KEY ITERATION | | 1 |
| 394 |
| * 28 | TABLE ACCESS FULL | DIM3 |
1 | 1 | 1 |
| * 29 | BITMAP INDEX RANGE SCAN | FACT_DIM3 |
1 | | 394 |
| 30 |
TABLE ACCESS BY USER ROWID
| FACT | 10000 |
1 | 10000 |
----------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - filter("DIM1"."DIM1_COD"='One')
4 -
access("ITEM_3"="DIM3"."DIM3_ID"
AND
"ITEM_2"="DIM2"."DIM2_ID"
AND
"ITEM_1"="C0")
7 -
filter("DIM2"."DIM2_COD"='One')
11 -
filter("DIM3"."DIM3_COD"='One')
19 -
access("FACT"."DIM1_ID"="C0")
23 - filter("DIM2"."DIM2_COD"='One')
24 -
access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
28 -
filter("DIM3"."DIM3_COD"='One')
29 -
access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")
Note
-----
- star transformation used for this
statement
- this is an adaptive plan (rows marked '-'
are inactive)
63 rows
selected.
demo@PDB1>
We have loaded some huge datasets into DIM1 and re-gathered the stats –
now DIM1 appears as a large dimension to the optimizer. In order to avoid
repeated scans, the optimizer chose to put it in a temporary table – look at
the beginning of the plan.
The DIM1 table is first loaded into a session private temporary table,
then that temporary table is used in the merge Cartesian join(to build a hash
table to look for additional information) and is also used to apply the
predicate before going to the FACT_DIM1 bitmap index.
Nothing else is different
·
Join each dimension to the FACT bitmap indexes
·
Get the resulting rowid’s and get the FACT rows
·
Then join back to the dimension Cartesian join
in order to get additional information.
The 10053 Optimizer trace file shows that the final query after Star
transformation was like this:
SELECT
"VW_ST_A500F760"."ITEM_4" "FACT_ID",
"VW_ST_A500F760"."ITEM_1" "DIM1_ID",
"VW_ST_A500F760"."ITEM_2" "DIM2_ID",
"VW_ST_A500F760"."ITEM_3" "DIM3_ID",
"VW_ST_A500F760"."ITEM_5" "FACT_MEASURE",
"DIM1"."DIM1_ID"
"DIM1_ID",
"DIM1"."DIM1_COD"
"DIM1_COD",
"DIM1"."DIM1_TEXT"
"DIM1_TEXT",
"DIM2"."DIM2_ID"
"DIM2_ID",
"DIM2"."DIM2_COD"
"DIM2_COD",
"DIM2"."DIM2_TEXT"
"DIM2_TEXT",
"DIM3"."DIM3_ID"
"DIM3_ID",
"DIM3"."DIM3_COD"
"DIM3_COD",
"DIM3"."DIM3_TEXT"
"DIM3_TEXT"
FROM
(
SELECT /*+ ORDERED USE_NL
("SYS_CP_FACT") NOPARALLEL ("FACT") */
"SYS_CP_FACT"."DIM1_ID" "ITEM_1",
"SYS_CP_FACT"."DIM2_ID" "ITEM_2",
"SYS_CP_FACT"."DIM3_ID"
"ITEM_3",
"SYS_CP_FACT"."FACT_ID" "ITEM_4",
"SYS_CP_FACT"."FACT_MEASURE" "ITEM_5"
FROM
"DEMO"."FACT"
"FACT",
"DEMO"."FACT"
"SYS_CP_FACT"
WHERE
"FACT".rowid =
"SYS_CP_FACT".rowid
AND
"FACT"."DIM3_ID" = ANY (
SELECT /*+ SEMIJOIN_DRIVER
*/
"DIM3"."DIM3_ID" "ITEM_1"
FROM
"DEMO"."DIM3" "DIM3"
WHERE
"DIM3"."DIM3_COD" = 'One'
)
AND
"FACT"."DIM2_ID" = ANY (
SELECT /*+ SEMIJOIN_DRIVER
*/
"DIM2"."DIM2_ID" "ITEM_1"
FROM
"DEMO"."DIM2" "DIM2"
WHERE
"DIM2"."DIM2_COD" = 'One'
)
AND
"FACT"."DIM1_ID" = ANY (
SELECT /*+ SEMIJOIN_DRIVER
*/
"DIM1"."DIM1_ID" "ITEM_1"
FROM
"DEMO"."DIM1" "DIM1"
WHERE
"DIM1"."DIM1_COD" = 'One'
)
) "VW_ST_A500F760",
"DEMO"."DIM1"
"DIM1",
"DEMO"."DIM2"
"DIM2",
"DEMO"."DIM3"
"DIM3"
WHERE
"DIM1"."DIM1_COD"
= 'One' AND
"DIM2"."DIM2_COD"
= 'One' AND
"DIM3"."DIM3_COD"
= 'One' AND
"VW_ST_A500F760"."ITEM_3"
= "DIM3"."DIM3_ID" AND
"VW_ST_A500F760"."ITEM_2"
= "DIM2"."DIM2_ID" AND
"VW_ST_A500F760"."ITEM_1"
= "DIM1"."DIM1_ID"
Stay tuned more to come.
No comments:
Post a Comment