Oracle star schema

Star conversion is a very powerful optimization technology. It is implemented by implicit rewriting of the original SQL statement, which 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:

1. The dimension column of the fact table must have a foreign key.
2. Each foreign key of the 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.

When a database performs a star query, two basic stages are used:
In the first phase, all necessary record rows are obtained 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 provides a good description of the conditions for performing a "star query:

SQL> show parameter star_transformation_enabled

Name type value
-----------------------------------------------------------------------------
Star_transformation_enabled string false

 

 

SQL> conn sh/SH
Connected.

 

SQL> select Ch. channel_class, C. cust_city,
T. calendar_quarter_desc,
Sum (S. amount_sold) sales_amount
From sales S, times t, customers C, channels ch
Where 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 ('2017-Q1 ', '2017-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 |

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

| 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 | 426 k | 18 M | (9) |
00:00:06 | key (SQ) |

| 9 | table access full | sales | 426 k | 18 M | (9) |
00:00:06 | key (SQ) |

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

Predicate information (identified by Operation ID ):
---------------------------------------------------

2-access ("S". "channel_id" = "ch". "channel_id ")
3-filter ("ch". "channel_desc" = 'catalog 'or "ch". "channel_desc" = 'internet ')
4-access ("S". "time_id" = "T". "time_id ")
5-filter ("T". "calendar_quarter_desc" = '2017-Q1 'or "T". "calendar_quarter_des
C "= '2017-Q2 ')

6-access ("S". "cust_id" = "C". "cust_id ")
7-filter ("C". "cust_state_province" = 'CA ')

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 a120
SQL> set autotrace traceonly exp;

 

SQL> Col plan_plus_exp format a120
SQL> set autotrace traceonly exp;
Select Ch. channel_class, C. cust_city,
T. calendar_quarter_desc,
Sum (S. amount_sold) sales_amount
From sales S, times t, customers C, channels ch
Where 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 ('2017-Q1 ', '2017-Q2 ')
11 Group by Ch. channel_class, C. cust_city, T. calendar_quarter_desc;

Execution Plan
----------------------------------------------------------
Plan hash value: 580301883

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

| ID | operation | Name | row
S | bytes | cost (% CPU) | time | pstart | pstop |

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

| 0 | SELECT statement |
1 | 73 | 786 (2) | 00:00:10 |

| 1 | temp table transformation |
|

| 2 | load as select | sys_temp_0fd9d660b_7e653 |
|

| * 3 | table access full | MERs | 3
83 | 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 | 1
83 | 2928 | 15 (0) | 00:00:01 |

| 9 | partition range subquery | 2
54 | 5334 | 432 (2) | 00:00:06 | key (SQ) |

| 10 | table access by local index rowid | sales | 2
54 | 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 | 1
83 | 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 | 3
83 | 5745 | 2 (0) | 00:00:01 |

| * 29 | table access full | channels |
2 | 42 | 3 (0) | 00:00:01 |

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

Predicate information (identified by Operation ID ):
---------------------------------------------------

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". "calendar_quarter_des
C "= '2017-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_des
C "= '2017-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 ')

From the execution plan above, we can see that this SQL statement performs star transformation, and uses Bitmap indexes, such as bitmap merge and bitmap and.