Oracle Star Schema Analysis

Oracle Star Schema analysis Star conversion is a very powerful optimization technology, which is implemented by implicit rewriting of the original SQL statement, it can greatly reduce I/O. end users do not need to know any details about star conversion. The database optimizer performs star conversion when appropriate. To obtain the maximum performance of star conversion, follow the following three basic conditions: www.2cto.com 1. The dimension column of the fact table must have a foreign key 2, each foreign key of a fact table has a BITMAP index. 3, star_transformation_enabled = true. The default value is false. It has three values: (TRUE, FALSE, TEMP_DISABLE). TEMP_DISABLE indicates that temporary tables are not allowed to store the result set for the first scan. In a star query, a dimension table is scanned twice. If a dimension table is large and has poor performance, a temporary table is required to store the dimension table set for the first scan. If the three conditions can be met, the query will use star transformation, which is the main technology to improve the query efficiency based on fact tables. Two basic stages are used when the www.2cto.com database performs a star query: the first stage obtains all necessary record rows from the fact table (or result set. This is because the bitmap index is used to retrieve data, so it is more efficient. The second stage associates the result set with the dimension table. This is called semi-join (that is, the writing of exists and in ). Note: Only oracle Enterprise Edition has bitmap indexes. The Standard Edition does not support bitmap indexing and star conversion. The following SQL statement execution plan can be used to explain the conditions for performing a "star query": SQL> show parameter star_transformation_enabledNAME type value ----------- star_transformation_enabled string false SQL> conn sh/shConnected. SQL> SELECT ch. channel_class, c. cust_city, t. calendar_quarter_desc, SUM (s. amount_sold) sales_amountFROM sales s, times t, MERS MERs c, channels chWHERE s. time_id = t. time_id AND s. cu St_id = c. cust_id AND s. channel_id = ch. channel_id AND c. cust_state_province = 'CA' AND ch. channel_desc IN ('internet', 'catalog ') AND t. calendar_quarter_desc IN ('1970-Q1 ', '1970-Q2') 11 group by ch. channel_class, c. cust_city, t. calendar_quarter_desc; Execution Plan ---------------------------------------------------------- Plan hash value: 1612666291 --------------------------------------------------- ------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | Bytes | 0 | select statement | 572 | 48048 | 793 (8) | 00:00:10 | 1 | hash group by | 572 | 48048 | 793 (8) | 00:00:10 | * 2 | hash join | 3116 | 255K | 791 (8) | 00:00:10 | * 3 | Table access full | CHANNELS | 2 | 42 | 3 (0) | 00:00:01 | * 4 | hash join | 6231 | 383K | 788 (8) | 00:00:10 | * 5 | table access full | TIMES | 183 | 2928 | 15 (0) | 00:00:01 | * 6 | hash join | 49822 | 2286K | 771 (8) | 00:00:10 | * 7 | table access full | CUSTOMERS | 383 | 9958 | 332 (2) | 00:00:04 | 8 | partition range subquery | 918K | 18M | 426 (9) | 00:00:06 | KEY (SQ) | 9 | Table access full | SALES | 918K | 18M | 426 (9) | 00:00:06 | KEY (SQ) | protected www.2cto.com Predicate Information (identified by operation id ): ------------------------------------------------- 2-access ("S ". "CHANNEL_ID" = "CH ". "CHANNEL_ID") 3-filter ("CH ". "CHANNEL_DESC" = 'catalog 'OR "CH ". "CHANNEL_DESC" = 'interne T') 4-access ("S ". "TIME_ID" = "T ". "TIME_ID") 5-filter ("T ". "CALENDAR_QUARTER_DESC" = '2017-Q1 'OR "T ". "CALENDAR_QUARTER_DESC" = '1970-Q2 ') 6-access ("S ". "CUST_ID" = "C ". "CUST_ID") 7-filter ("C ". "CUST_STATE_PROVINCE" = 'CA') We can see that the preceding SQL statement does not use star transfermation because star_transformation_enabled = false. The execution plan is just a normal JOIN operation. Let me set star_transformation_enabled to TRUE, so that he can use star transfermation technology to execute SQL statements. SQL> alter system set star_transformation_enabled = true; System altered. SQL> col PLAN_PLUS_EXP format a120SQL> set autotrace traceonly exp; SQL> col PLAN_PLUS_EXP format a120SQL> set autotrace traceonly exp; SELECT ch. channel_class, c. cust_city, t. calendar_quarter_desc, SUM (s. amount_sold) sales_amountFROM sales s, times t, MERS MERs c, channels chWHERE s. time_id = t. time_id AND s. cust_id = c. cust_id AND S. channel_id = ch. channel_id AND c. cust_state_province = 'CA' AND ch. channel_desc IN ('internet', 'catalog ') AND t. calendar_quarter_desc IN ('1970-Q1 ', '1970-Q2') 11 group by ch. channel_class, c. cust_city, t. calendar_quarter_desc; Execution Plan ---------------------------------------------------------- Plan hash value: 580301883 bytes -------------------------------------------------------------------------- ---------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | Bytes | 0 | select statement | 1 | 73 | 786 (2) | 00:00:10 | 1 | temp table transformation | 2 | load as select | SYS_TEMP_0FD9D660B_7E653 | * 3 | TABLE ACC Ess full | CUSTOMERS | 383 | 9958 | 332 (2) | 00:00:04 | 4 | hash group by | 1 | 73 | 455 (2) | 00:00:06 | * 5 | hash join | 1 | 73 | 454 (2) | 00:00:06 | * 6 | hash join | 2 | 104 | 450 (2) | 00:00:06 | * 7 | hash join | 32 | 1184 | 448 (2) | 00:00:06 | * 8 | table access full | TIMES | 183 | 2928 | 15 (0) | 00:00:01 | 9 | partition range subquery | 254 | 5334 | 432 (2) | 00:00:06 | KEY (SQ) | 10 | table access by local index rowid | SALES | 254 | 5334 | 432 (2) | 00:00:06 | KEY (SQ) | 11 | bitmap conversion to rowids | 12 | bitmap and | 13 | bitmap merge | | 14 | bitmap key iteration | 15 | buffer sort | * 16 | table access full | CHANNELS | 2 | 42 | 3 (0) | 00:00:01 | * 17 | Bitmap index range scan | SALES_CHANNEL_BIX | KEY (SQ) | 18 | bitmap merge | 19 | bitmap key iteration | 20 | buffer sort | | * 21 | table access full | TIMES | 183 | 2928 | 15 (0) | 00:00:01 | * 22 | bitmap index range scan | SALES_TIME_BIX | KEY (SQ) | 23 | bitmap merge | 24 | bitmap key iteration | | 25 | buffer sort | 26 | table access full | SYS_TEMP_0FD9D660B_7E653 | 1 | 13 | 2 (0) | 00:00:01 | * 27 | bitmap index range scan | SALES_CUST_BIX | KEY (SQ) | 28 | table access full | SYS_TEMP_0FD9D660B_7E653 | 383 | 5745 | 2 (0) | 00:00:01 | * 29 | table access full | CHANNELS | 2 | 42 | 3 (0) | 00:00:01 | ----------------------------------------------- ----- Www.2cto.com ---------------------------------------------------- Predicate Information (identified by operation id): extract 3-filter ("C ". "CUST_STATE_PROVINCE" = 'CA') 5-access ("S ". "CHANNEL_ID" = "CH ". "CHANNEL_ID") 6-access ("S ". "CUST_ID" = "C0") 7-access ("S ". "TIME_ID" = "T ". "TIME_ID") 8-filter ("T ". "CALENDAR_QUARTER_DESC" = '2017-Q1 'OR "T ". "CALENDA R_QUARTER_DESC "= '1970-Q2 ') 16-filter (" CH ". "CHANNEL_DESC" = 'catalog 'OR "CH ". "CHANNEL_DESC" = 'internet') 17-access ("S ". "CHANNEL_ID" = "CH ". "CHANNEL_ID") 21-filter ("T ". "CALENDAR_QUARTER_DESC" = '2017-Q1 'OR "T ". "CALENDAR_QUARTER_DESC" = '1970-Q2 ') 22-access ("S ". "TIME_ID" = "T ". "TIME_ID") 27-access ("S ". "CUST_ID" = "C0") 29-filter ("CH ". "CHANNEL_DESC" = 'catalog 'OR "CH ". "CHANNEL_DESC" = 'internet') Execution Plan from above It can be seen that this SQL statement performs star transformation. BITMAP indexes are adopted, such as BITMAP MERGE AND BITMAP.