Storage Indexes - Part I

Storage Indexes are the most useful Exadata feature. These are not the indexes that are stored in the database like Bitmap or B*Tree indexes, they are not capable of identifying set of records that has a certain value in a given column. Rather they are the feature of storage server software that is designed to eliminate the disk IO. That is because they identify the location where the requested records are not. They work by storing the minimum and maximum value of disk storage units (which are 1MB by default).

 

Because SQL Predicates are passed to storage servers when smart scans are performed, the storage software can check the prerequisite against the storage index metadata (maximum and minimum values) before doing the requested IO. So any storage region that cannot possibly have a matching row is skipped. In many cases this can result in significant reduction in the amount of IO that must be performed.

 

The ability to monitor the storage index is very limited. The optimizer doesn’t know whether a storage index is used for a particular sql statement. Not does AWR or ASH capture any information about usage of storage index for a particular sql statement. There is a single statistics that tracks the storage index usage at database level. The statistics cell physical IO bytes saved by storage index keep tracks of accumulated IO that has been avoided by the use of storage index.

 

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3       v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

------------------------------------------------------------ ----------------

cell physical IO bytes saved by storage index                               0

cell physical IO interconnect bytes returned by smart scan                  0

 

c##rajesh@PDB1> $ type script.sql

 

 

set termout off serveroutput off

alter session set statistics_level=all;

select * from big_table where owner ='JYU';

set termout on

 

select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

 

 

c##rajesh@PDB1> @script.sql

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

SQL_ID  dhxc0c7g6bufm, child number 0

-------------------------------------

select * from big_table where owner ='JYU'

 

Plan hash value: 3993303771

 

----------------------------------------------------------------------------------------

| Id  | Operation                 | Name      | A-Rows |   A-Time   | Buffers | Reads  |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |           |   7384 |00:00:00.35 |    2024K|   2024K|

|*  1 |  TABLE ACCESS STORAGE FULL| BIG_TABLE |   7384 |00:00:00.35 |    2024K|   2024K|

----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - storage("OWNER"='JYU')

       filter("OWNER"='JYU')

 

 

19 rows selected.

 

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3      v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

------------------------------------------------------------ ----------------

cell physical IO bytes saved by storage index                     11338186752

cell physical IO interconnect bytes returned by smart scan            7679576

 

 

In this example, the query used storage index that eliminated about 11GB of disk IO. This is the amount of additional IO that would have been necessary without storage indexes.

Adaptive Bitmap Pruning - Part III

 

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).

 

Adaptive Bitmap Pruning - Part II

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.