Star Transformation in 11gR2
Star Transformation in 11gR2
In the article we will review the prerequisites for the CBO to perform a star query transformation. We will use the SH samples schema. We will look at two cases with and without a bit map join index.
- Star transformation without a bitmap join index.
- Star transformation with a bitmap join index.
The prerequisites are as follows.
- Make sure that the primary and foreign keys are enabled.
alter table products enable validate primary key;
alter table times enable validate primary key;
alter table promotions enable validate primary key;
alter table channels enable validate primary key;
alter table customers enable validate primary key;
alter table sales enable validate constraint products_fk;
alter table sales enable validate constraint times_fk;
alter table sales enable validate constraint promotions_fk;
alter table sales enable validate constraint channels_fk;
alter table sales enable validate constraint customers_fk;
- Enable star transformation.
alter system set star_transformation_enabled=true;
- Make sure that bitmap indexes exist on the fact table if not create the bitmap indexes.
create bitmap index products_bix on sales(prod_id) local nologging compute statistics;
create bitmap index customers_bix on sales(cust_id) local nologging compute statistics;
create bitmap index channels_bix on sales(channel_id) local nologging compute statistics;
create bitmap index times_bix on sales(time_id) local nologging compute statistics;
create bitmap index promotions_bix on sales(promo_id) local nologging compute statistics;
- Gather statistics for the SH schema.
exec dbms_stats.gather_schemas_stats(‘SH’);
- Run the query. For the case we will use the following query.
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 (‘1999-01′,’2002-02’)
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
- Examine the execution plan. Run the following immediately after running the query in the preceding step.
select * from table(dbms_xplan.display_cursor());
- Example of the execution plan.
SQL> SQL>
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID 8g98r364zu4c8, child number 0
————————————-
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 (‘1999-01′,’2002-02′) GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc
Plan hash value: 478886510
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
———————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
———————————————————————————————————————————
| 0 | SELECT STATEMENT | | | | 556 (100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | | | | | | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
| 4 | HASH GROUP BY | | 253 | 20493 | 150 (2)| 00:00:02 | | |
|* 5 | HASH JOIN | | 253 | 20493 | 149 (2)| 00:00:02 | | |
|* 6 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 253 | 15180 | 146 (2)| 00:00:02 | | |
|* 8 | HASH JOIN | | 253 | 11385 | 143 (1)| 00:00:02 | | |
|* 9 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |
| 10 | VIEW | VW_ST_62EEF96F | 254 | 7366 | 125 (1)| 00:00:02 | | |
| 11 | NESTED LOOPS | | 254 | 14478 | 101 (0)| 00:00:02 | | |
| 12 | PARTITION RANGE SUBQUERY | | 254 | 7117 | 55 (2)| 00:00:01 |KEY(SQ)|KEY(SQ)|
| 13 | BITMAP CONVERSION TO ROWIDS| | 254 | 7117 | 55 (2)| 00:00:01 | | |
| 14 | BITMAP AND | | | | | | | |
| 15 | BITMAP MERGE | | | | | | | |
| 16 | BITMAP KEY ITERATION | | | | | | | |
| 17 | BUFFER SORT | | | | | | | |
|* 18 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | |
|* 19 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 20 | BITMAP MERGE | | | | | | | |
| 21 | BITMAP KEY ITERATION | | | | | | | |
| 22 | BUFFER SORT | | | | | | | |
|* 23 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |
|* 24 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 25 | BITMAP MERGE | | | | | | | |
| 26 | BITMAP KEY ITERATION | | | | | | | |
| 27 | BUFFER SORT | | | | | | | |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_12046E | 383 | 1915 | 2 (0)| 00:00:01 | | |
|* 29 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 30 | TABLE ACCESS BY USER ROWID | SALES | 1 | 29 | 70 (0)| 00:00:01 | ROWID | ROWID |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_12046E | 383 | 5745 | 2 (0)| 00:00:01 | | |
———————————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
3 – filter(“C”.”CUST_STATE_PROVINCE”=’CA’)
5 – access(“ITEM_1″=”CH”.”CHANNEL_ID”)
6 – filter((“CH”.”CHANNEL_DESC”=’Catalog’ OR “CH”.”CHANNEL_DESC”=’Internet’))
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
7 – access(“ITEM_2″=”C0”)
8 – access(“ITEM_3″=”T”.”TIME_ID”)
9 – filter((“T”.”CALENDAR_QUARTER_DESC”=’1999-01′ OR “T”.”CALENDAR_QUARTER_DESC”=’2002-02′))
18 – filter((“CH”.”CHANNEL_DESC”=’Catalog’ OR “CH”.”CHANNEL_DESC”=’Internet’))
19 – access(“S”.”CHANNEL_ID”=”CH”.”CHANNEL_ID”)
23 – filter((“T”.”CALENDAR_QUARTER_DESC”=’1999-01′ OR “T”.”CALENDAR_QUARTER_DESC”=’2002-02′))
24 – access(“S”.”TIME_ID”=”T”.”TIME_ID”)
29 – access(“S”.”CUST_ID”=”C0″)
Note
—–
– star transformation used for this statement
68 rows selected.
SQL>
- With a bit map join index. We will create a bitmap join index as below.
CREATE BITMAP INDEX bjix
ON sales(c.cust_state_province,ch.channel_desc, t.calendar_quarter_desc)
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
LOCAL NOLOGGING COMPUTE STATISTICS;
- Example of the execution plan.
————————————-
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 (‘1999-01′,’2002-02′) GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc
Plan hash value: 3670985093
—————————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————————————-
| 0 | SELECT STATEMENT | | | | 553 (100)| | | |
| 1 | HASH GROUP BY | | 572 | 48048 | 553 (1)| 00:00:07 | | |
|* 2 | HASH JOIN | | 1993 | 163K| 531 (1)| 00:00:07 | | |
|* 3 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 1993 | 122K| 527 (1)| 00:00:07 | | |
|* 5 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 1997 | 93859 | 508 (1)| 00:00:07 | | |
|* 7 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
| 8 | PARTITION RANGE SUBQUERY | | 36834 | 755K| 102 (0)| 00:00:02 |KEY(SQ)|KEY(SQ)|
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 36834 | 755K| 102 (0)| 00:00:02 |KEY(SQ)|KEY(SQ)|
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 11 | BITMAP AND | | | | | | | |
| 12 | BITMAP MERGE | | | | | | | |
| 13 | BITMAP KEY ITERATION | | | | | | | |
| 14 | BUFFER SORT | | | | | | | |
|* 15 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | |
|* 16 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 17 | BITMAP MERGE | | | | | | | |
| 18 | BITMAP KEY ITERATION | | | | | | | |
| 19 | BUFFER SORT | | | | | | | |
|* 20 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |
|* 21 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 22 | BITMAP MERGE | | | | | | | |
|* 23 | BITMAP INDEX RANGE SCAN | BJIX | | | | |KEY(SQ)|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”=’1999-01′ OR “T”.”CALENDAR_QUARTER_DESC”=’2002-02′))
6 – access(“S”.”CUST_ID”=”C”.”CUST_ID”)
7 – filter(“C”.”CUST_STATE_PROVINCE”=’CA’)
15 – filter((“CH”.”CHANNEL_DESC”=’Catalog’ OR “CH”.”CHANNEL_DESC”=’Internet’))
16 – access(“S”.”CHANNEL_ID”=”CH”.”CHANNEL_ID”)
20 – filter((“T”.”CALENDAR_QUARTER_DESC”=’1999-01′ OR “T”.”CALENDAR_QUARTER_DESC”=’2002-02′))
21 – access(“S”.”TIME_ID”=”T”.”TIME_ID”)
23 – access(“S”.”SYS_NC00012$”=’CA’)
filter(“S”.”SYS_NC00012$”=’CA’)
Note
—–
– star transformation used for this statement
61 rows selected.
SQL> SQL> SQL>
No comments yet.
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Leave a Reply