This will be the final series of this topic, incase if you have not yet
read the previous
column, 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 and how the STAR transformation collectively helps the optimizer to
produce better plans.
So all sort of these optimizations exists before 12c. In short, STAR
transformation is good when the predicate is selective enough to filter few
rows. What if the cardinality estimate has gone wrong and most of the FACT
table rows have the matching value – this is where the optimization (Adaptive
bitmap pruning) in 12c kicks in – the optimizer can choose to stop iteration
the bitmap branch, we just ignore the predicate at that step, and the join back
to the dimension Cartesian join with filter
If you could check the execution plan with predicates, you can see the
predicate on dimension in the two table (DIM1, DIM2) access.
Here I still run the same query but we have changed the data, in
previous examples, only one row was coming from DIM3 dimension, but now we have
all the rows in the DIM3 to match the predicates.
demo@PDB1>
update dim3 set dim3_cod = 'One';
10 rows
updated.
demo@PDB1>
commit;
Commit
complete.
demo@PDB1>
exec
dbms_stats.gather_table_stats(user,'dim3',no_invalidate=>false,estimate_percent=>100);
PL/SQL
procedure successfully completed.
demo@PDB1>
show parameter star_transformation_enabled
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
star_transformation_enabled string TRUE
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: 3143700915
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 100K|
| 1 |
TEMP TABLE TRANSFORMATION
| | 1
| | 100K|
| 2 |
LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_...| 1 |
| 0 |
|* 3 |
TABLE ACCESS FULL
| DIM1 | 1 |
1 | 2 |
|* 4 |
HASH JOIN | | 1 |
95000 | 100K|
| 5 |
MERGE JOIN CARTESIAN
| | 1 |
10 | 20 |
| 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 |
10 | 20 |
|* 11 | TABLE ACCESS FULL | DIM3 |
1 | 10 | 10 |
| 12 |
VIEW
| VW_ST_......| 1 | 100K|
100K|
| 13 |
NESTED LOOPS
| | 1 |
100K| 100K|
| 14 |
BITMAP CONVERSION TO ROWIDS
| | 1 |
100K| 100K|
| 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 |
BITMAP MERGE
| | 1 |
| 26 |
| 21 |
BITMAP KEY ITERATION | | 1 |
| 119 |
|* 22 | TABLE ACCESS FULL | DIM2 |
1 | 1 | 1 |
|* 23 | BITMAP INDEX RANGE SCAN | FACT_DIM2 |
1 | | 119 |
| 24 |
TABLE ACCESS BY USER ROWID
| FACT | 100K|
1 | 100K|
-------------------------------------------------------------------------------------------
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")
22 -
filter("DIM2"."DIM2_COD"='One')
23 -
access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
Note
-----
- star transformation used for this
statement
Look at the end, when the statistics collector has seen the threshold
has been passed over, it has decided to skip that bitmap branch for DIM3
dimension table. This is the third case of Adaptive
plans: Adaptive Bitmap Pruning. The bitmap branch is good only if it helps
to filter out lots of rows. If it is not the case, then it is just an overhead,
and hence the optimizer skips it.
The final transformation sql from 10053 optimizer trace shows 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"."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"
Now recreated the tables – as how it exists in the first
demo – and in-memory in place, with no Star transformations.
demo@PDB1>
alter table dim1 inmemory priority high;
Table altered.
demo@PDB1>
alter table dim2 inmemory priority high;
Table altered.
demo@PDB1>
alter table dim3 inmemory priority high;
Table altered.
demo@PDB1>
alter table fact inmemory priority high;
Table altered.
demo@PDB1>
select owner,segment_name,populate_status
2 from
v$im_segments ;
OWNER SEGMENT_NA POPULATE_STAT
----------
---------- -------------
DEMO FACT COMPLETED
demo@PDB1>
demo@PDB1>
set serveroutput off
demo@PDB1>
alter session set statistics_level=all;
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: 792673006
------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | | 10000 |
|* 1 |
HASH JOIN
| | 1 |
10000 | 10000 |
| 2 |
JOIN FILTER CREATE |
:BF0000 | 1 | 1 |
1 |
| 3 |
MERGE JOIN CARTESIAN
| | 1 |
1 | 1 |
| 4 |
MERGE JOIN CARTESIAN | |
1 | 1 | 1 |
|* 5 |
TABLE ACCESS INMEMORY FULL | DIM1
| 1 | 1 |
1 |
| 6 |
BUFFER SORT | |
1 | 1 | 1 |
|* 7 |
TABLE ACCESS INMEMORY FULL| DIM2
| 1 | 1 |
1 |
| 8 |
BUFFER SORT | |
1 | 1 | 1 |
|* 9 |
TABLE ACCESS INMEMORY FULL | DIM3
| 1 | 1 |
1 |
| 10 |
JOIN FILTER USE |
:BF0000 | 1 | 10M|
1000K|
|* 11 | TABLE ACCESS INMEMORY FULL | FACT
| 1 | 10M|
1000K|
------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 -
access("FACT"."DIM3_ID"="DIM3"."DIM3_ID"
AND
"FACT"."DIM2_ID"="DIM2"."DIM2_ID"
AND
"FACT"."DIM1_ID"="DIM1"."DIM1_ID")
5 -
inmemory("DIM1"."DIM1_COD"='One')
filter("DIM1"."DIM1_COD"='One')
7 -
inmemory("DIM2"."DIM2_COD"='One')
filter("DIM2"."DIM2_COD"='One')
9 -
inmemory("DIM3"."DIM3_COD"='One')
filter("DIM3"."DIM3_COD"='One')
11 -
inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"FACT"."DIM3_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"FACT"."DIM3_ID"))
40 rows
selected.
demo@PDB1>
As usual, we get the Cartesian merge join to get the dimension, but
without star transformation the access to FACT table was either NESTED LOOP or
HASH JOIN – and that was adaptive.
Here the FACT table is stored in-memory, and there is now index, so we
do full scan. Star Transformation was nice because it pushes down the predicate
to filter the FACT table earlier. Here we have something else. The criteria are
pushed with bloom filters. Because we have read all the dimension first, then
we can build the bloom filter (JOIN FILTER CREATE) and use it (JOIN FILTER USE)
to filter a large part of the rows –Saving the cost of lots of Hash lookups.
Hash lookups have to be done only for the few bloom filters false positives
& vector processing, which is a way to scan columnar data, is very
efficient with bloom filters.
I have concluded with In-memory because Star transformation (with
Bitmap index) is good when the predicate is selective enough to filter few
rows, but the problem is they don’t like OLTP updates. In-memory is good
solution for adhoc queries on OLTP database – as long as we have enough memory
to keep the data in the in-memory column store (and in-memory column store will
have greater compression ratio’s on fact tables because of repeated dimension
keys).
