Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA 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;

 

  1. Enable star transformation.

 

alter system set  star_transformation_enabled=true;

 

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

 

  1. Gather statistics for the SH schema.

 

exec dbms_stats.gather_schemas_stats(‘SH’);

 

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

 

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

 

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

 

 

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

 

 

 

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

 

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

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: