Guenadi N Jilevski's Oracle BLOG

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.

1. Star transformation without a bitmap join index.
2. Star transformation with a bitmap join index.

The prerequisites are as follows.

 

1. 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;

 

2. Enable star transformation.

 

alter system set  star_transformation_enabled=true;

 

3. 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;

 

4. Gather statistics for the SH schema.

 

exec dbms_stats.gather_schemas_stats(‘SH’);

 

5. 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;

 

6. Examine the execution plan. Run the following immediately after running the query in the preceding step.

 

select * from table(dbms_xplan.display_cursor());

 

 

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

 

 

 

8. 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;

 

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

 

 

 

May 4, 2016 - Posted by gjilevski | oracle