Star Schema Optimization in Autonomous Data Warehouse Cloud

Oracle Autonomous Data Warehouse Cloud does not allow to create indexes. Is this a problem for star schemas because no Star Transformation can be used? Or are the required bitmap indexes automatically created? A look under the hood of ADWC.

A typical recommendation for star schemas in an Oracle database is to create a bitmap index on each dimension key of a fact table. I used (and still use) this index strategy in many data warehouses and recommend it in reviews, trainings and presentations. Why are bitmap indexes so important on a fact table? Because they are required for the Star Transformation, a special join strategy for star schemas. Without explaining all the details, here a short summary of the Star Transformation approach:

1. For each dimension table with a filter (WHERE condition) in the query, a bit array is built based on the bitmap index of the dimension key in the fact table
2. All these bit arrays are combined with a BITMAP AND operator. The result is a bit array for all rows of the fact table that fit all filter conditions
3. This resulting bit array is used to access the corresponding rows in the fact table

But how can this efficient join and access method in a star schema be used, when the database does not allow to create any bitmap indexes or non-unique b-tree indexes? Are bitmap indexes created automatically in ADWC? Or how are these kind of queries on a star schema handled by the optimizer? To find the answer, let’s look at the execution plan of a typical query on the SSB schema (sample star schema benchmark) that is available in every ADWC database. Some example queries can be found in Appendix D of the ADWC documentation.

Sample Star Schema Benchmark

The SSB schema contains one fact table LINEORDER with around 6 billion rows and four dimension tables. The time dimension DWDATE contains all calendar days for 8 years, all other dimensions contains between 2 and 30 million rows. The data model is a typical star schema (the foreign key constraints I added manually in Data Modeler for better readability. The SSB schema constains no PK/FK constraints at all).

 

Execution Plan of a Star Schema Query

A query that joins the fact table with all four dimension tables, each of them containing a filter, leads to the following execution plan. We can see several interesting details in this plan:

• Parallel Execution: The query runs in parallel (all the PX operators in the execution plan). This is generally the case in ADWC, except for connections with consumer group LOW or for ADWC configurations with only 1 CPU core. The degree of parallelism (DOP) depends on the number of CPU cores. More details about scalability can be found in Christian Antognini’s blog post Observations About the Scalability of Data Loads in ADWC.
• Result Cache: The result cache is activated (see green line 1 in execution plan). The parameter RESULT_CACHE_MODE is set to FORCE in ADWC and cannot be changed. This allows very short response times for queries that are executed multiple times. Only the first execution reads and joins the tables, all subsequent executions read the result from the cache. This works only for queries with a small result set. In a star schema, this is usually the case for high aggregated data (i.e. when the facts are aggregated on a high hierarchy level of the dimensions).
• No Star Transformation: No indexes are used in the execution plan. There are two simple reasons for this: Indexes cannot be created manually, and there is no automatic creation of indexes in the Autonomous Data Warehouse. Because no indexes are available, no Star Transformation can be used here.
• Vector Transformation: Instead of Star Transformation, an even better approach is used in ADWC: Vector Transformation (see blue lines in execution plan). This is very interesting, because Vector Transformation works only in combination with Oracle Database In-Memory. Although this feature is not supported in ADWC at the moment, this very efficient join approach for star schema queries takes place here.

 

————————————————————————————————————————————————————————————————

| Id  | Operation                                        | Name                        | Rows  |

————————————————————————————————————————————————————————————————

|   0 | SELECT STATEMENT                                 |                             |   708K|

|   1 |  RESULT CACHE                                    | 0v7cjd9tjv4vb78py6r10duh4r  |       |

|   2 |   TEMP TABLE TRANSFORMATION                      |                             |       |

|   3 |    LOAD AS SELECT                                | SYS_TEMP_0FDA1A147_1F199710 |       |

|   4 |     PX COORDINATOR                               |                             |       |

|   5 |      PX SEND QC (RANDOM)                         | :TQ10001                    |     8 |

|   6 |       HASH GROUP BY                              |                             |     8 |

|   7 |        PX RECEIVE                                |                             |     8 |

|   8 |         PX SEND HASH                             | :TQ10000                    |     8 |

|   9 |          KEY VECTOR CREATE BUFFERED              | :KV0000                     |     8 |

|  10 |           PX BLOCK ITERATOR                      |                             |   639 |

|* 11 |            TABLE ACCESS STORAGE FULL             | DWDATE                      |   639 |

|  12 |    LOAD AS SELECT                                | SYS_TEMP_0FDA1A148_1F199710 |       |

|  13 |     PX COORDINATOR                               |                             |       |

|  14 |      PX SEND QC (RANDOM)                         | :TQ20001                    |     1 |

|  15 |       HASH GROUP BY                              |                             |     1 |

|  16 |        PX RECEIVE                                |                             |     1 |

|  17 |         PX SEND HASH                             | :TQ20000                    |     1 |

|  18 |          KEY VECTOR CREATE BUFFERED              | :KV0001                     |     1 |

|  19 |           PX BLOCK ITERATOR                      |                             |  6000K|

|* 20 |            TABLE ACCESS STORAGE FULL             | CUSTOMER                    |  6000K|

|  21 |    LOAD AS SELECT                                | SYS_TEMP_0FDA1A149_1F199710 |       |

|  22 |     PX COORDINATOR                               |                             |       |

|  23 |      PX SEND QC (RANDOM)                         | :TQ30001                    |   249 |

|  24 |       HASH GROUP BY                              |                             |   249 |

|  25 |        PX RECEIVE                                |                             |   249 |

|  26 |         PX SEND HASH                             | :TQ30000                    |   249 |

|  27 |          KEY VECTOR CREATE BUFFERED              | :KV0002                     |   249 |

|  28 |           PX BLOCK ITERATOR                      |                             | 80000 |

|* 29 |            TABLE ACCESS STORAGE FULL             | SUPPLIER                    | 80000 |

|  30 |    LOAD AS SELECT                                | SYS_TEMP_0FDA1A14A_1F199710 |       |

|  31 |     PX COORDINATOR                               |                             |       |

|  32 |      PX SEND QC (RANDOM)                         | :TQ40001                    |  1006 |

|  33 |       HASH GROUP BY                              |                             |  1006 |

|  34 |        PX RECEIVE                                |                             |  1006 |

|  35 |         PX SEND HASH                             | :TQ40000                    |  1006 |

|  36 |          KEY VECTOR CREATE BUFFERED              | :KV0003                     |  1006 |

|  37 |           PX BLOCK ITERATOR                      |                             | 80000 |

|* 38 |            TABLE ACCESS STORAGE FULL             | PART                        | 80000 |

|  39 |    PX COORDINATOR                                |                             |       |

|  40 |     PX SEND QC (ORDER)                           | :TQ50004                    |   708K|

|  41 |      SORT GROUP BY                               |                             |   708K|

|  42 |       PX RECEIVE                                 |                             |   708K|

|  43 |        PX SEND RANGE                             | :TQ50003                    |   708K|

|  44 |         HASH GROUP BY                            |                             |   708K|

|* 45 |          HASH JOIN                               |                             |   708K|

|  46 |           PX RECEIVE                             |                             |  1006 |

|  47 |            PX SEND BROADCAST                     | :TQ50000                    |  1006 |

|  48 |             PX BLOCK ITERATOR                    |                             |  1006 |

|  49 |              TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FDA1A14A_1F199710 |  1006 |

|* 50 |           HASH JOIN                              |                             |   708K|

|  51 |            PX RECEIVE                            |                             |   249 |

|  52 |             PX SEND BROADCAST                    | :TQ50001                    |   249 |

|  53 |              PX BLOCK ITERATOR                   |                             |   249 |

|  54 |               TABLE ACCESS STORAGE FULL          | SYS_TEMP_0FDA1A149_1F199710 |   249 |

|* 55 |            HASH JOIN                             |                             |   708K|

|  56 |             TABLE ACCESS STORAGE FULL            | SYS_TEMP_0FDA1A147_1F199710 |     8 |

|* 57 |             HASH JOIN                            |                             |   708K|

|  58 |              TABLE ACCESS STORAGE FULL           | SYS_TEMP_0FDA1A148_1F199710 |     1 |

|  59 |              VIEW                                | VW_VT_846B3E5D              |   708K|

|  60 |               HASH GROUP BY                      |                             |   708K|

|  61 |                PX RECEIVE                        |                             |   708K|

|  62 |                 PX SEND HASH                     | :TQ50002                    |   708K|

|  63 |                  VECTOR GROUP BY                 |                             |   708K|

|  64 |                   HASH GROUP BY                  |                             |   708K|

|  65 |                    KEY VECTOR USE                | :KV0001                     |  3729K|

|  66 |                     KEY VECTOR USE               | :KV0000                     |  3875K|

|  67 |                      KEY VECTOR USE              | :KV0003                     |    14M|

|  68 |                       KEY VECTOR USE             | :KV0002                     |   247M|

|  69 |                        PX BLOCK ITERATOR         |                             |  5999M|

|* 70 |                         TABLE ACCESS STORAGE FULL| LINEORDER                   |  5999M|

————————————————————————————————————————————————————————————————

 

Note

—–

   – automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

   – vector transformation used for this statement

 

 

 

Vector Transformation

Like with Star Transformation, the base idea of Vector Transformation is similar: Before accessing the (usually much bigger) fact table, the result set is reduced by applying all dimension filters. To do this, the Vector Transformation is executed in two phases:

Phase 1: The following steps are performed for each dimension table with filter criteria (i.e. a WHERE condition in the query):

• The dimension table is scanned with a full table scan. All rows that do not fit the WHERE condition are ignored
• A key vector is calculated to determine which rows of the dimension are required for the query (KEY VECTOR CREATE BUFFERED in the plan)
• The data is aggregated with an “In-Memory Accumulator” and stored in a temporary table (LOAD AS SELECT in the plan)

Phase 2: Now the key vectors and temporary tables are used to find the corresponding rows in the fact table:

• A full table scan is performed on the fact table, and the data is filtered based on the pre-calculated key vectors (KEY VECTOR USE in the plan)
• Aggregation of the result set using HASH GROUP BY and VECTOR GROUP BY
• To get the required dimension attributes, a join back on the temporary table is required for each of the dimension
• Finally, additional dimension tables (without filters) are joined to the result set. This is not the case in our example.

This approach is very fast – even faster than Star Transformation – especially for large fact tables and weak selectivities on the dimensions. The benchmark query on the 6 billion fact table was running between 1 and 7 minutes (depending on the number of CPU cores that were configured). For most star schemas (with “only” a few million rows), the queries will run in a few seconds (and if the data is highly aggregated, less than a second for the second execution because of the result cache).

Vector Transformation was introduced in Oracle 12.1.0.2 for In-Memory Aggregations and only takes place when the In-Memory option is enabled. If this is the case, the transformation can even be used for tables that are not populated in the In-Memory Column Store.

When we check the In-Memory parameters on an ADWC database (which cannot be changed, by the way), we can see that an In-Memory Column Store of 1 gigabyte is allocated (parameter INMEMORY_SIZE). This enables the In-Memory option and allows the optimizer to use Vector Transformation.

 

SELECT name, value

FROM v$parameter

WHERE NAME LIKE ‘inmemory%’;

 

NAME                                          VALUE               

——————————————— ——————–

inmemory_adg_enabled                          TRUE                

inmemory_size                                 1073741824          

inmemory_clause_default                                           

inmemory_force                                DEFAULT             

inmemory_query                                ENABLE              

inmemory_expressions_usage                    ENABLE              

inmemory_virtual_columns                      MANUAL              

inmemory_max_populate_servers                 42                  

inmemory_trickle_repopulate_servers_percent   1                   

 

 

Although the In-Memory option is enabled, it is not possible to populate any table into the In-Memory Column Store (IMCS). If a table is created or altered with an INMEMORY clause, nothing happens. The clause is just ignored, and nothing is populated to IMCS.

Conclusion

The absence of indexes (especially bitmap indexes) in Autonomous Data Warehouse is not a problem at all, although it prevents the usage of Star Transformation. Queries on a star schema are very efficient because of the combination of Vector Transformation, Parallel Execution and Result Cache. This is a very good setup for most data warehouses using dimensional data marts.

The performance could even be improved with Oracle Database In-Memory. Currently, this feature seems not to be used in ADWC. Hopefully, this will be changed somewhen in the near future.