设为首页 收藏本站
查看: 2017|回复: 0

[经验分享] Oracle——星型查询

[复制链接]

尚未签到

发表于 2018-9-6 10:24:42 | 显示全部楼层 |阅读模式
  星型转换的基本思路是尽量避免直接去扫描星型模式中的事实表,因为这些事实表总会因为存有大量数据而十分庞大,对这些表的全表扫描会引起大量物理读并且效率低下。在典型的星型查询中,事实表总是会和多个与之相比小得多的维度表发生连接(join)操作。典型的事实表针对每一个维度表会存在一个外键(foreign key),除去这些键值(key)外还会存在一些度量字段譬如销售额度(sales amount)。与之对应的键值(key)在维度表上扮演主键的角色。而事实表与维度表间的连接操作一般都会发生在事实表上的外键和与之对应的维度表的主键间。同时这类查询总是会在维度表的其他列上存在限制十分严格的过滤谓词。充分结合这些维度表上的过滤谓词可以有效减少需要从事实表上访问的数据集合。这也就是星型转换(star transformation)的根本目的,仅访问事实表上相关的、过滤后精简的数据集合。
  星型转换可以有效改善大的事实表与多个具有良好选择率的维度表间连接的查询。星型转换有效避免了全表扫描的性能窘境。它只fetch那些事实表上的”绝对”相关行。同时星型转换是基于CBO优化器的,Oracle能很好地认清使用该种转换是否有利。一旦维度表上的过滤无法有效减少需要从事实表上处理的数据集和时,那么可能全表扫描相对而言更为恰当。
  以上我们力图通过一些简单的查询和执行计划来诠释星型转换的基本理念,但现实生产环境中实际的查询语句可能要复杂的多;举例而言如果查询涉及星型模型中的多个事实表的话,那么其复杂度就大幅提高了;如何正确构建事实表上的索引,收集相关列上的柱状图信息,在Oracle优化器无法正确判断的情况下循循善诱,都是大型数据仓库环境中DBA所面临的难题。
  TEMP_DISABLE:当一个维度表超过100个块时,”如果简单地设置star_transformation_enabled为TRUE来启用星型变换,那么会话会创建一个内存中的全局临时表(global temporary table)来保存已过滤的维度数据,这在过去会造成很多问题;”这里说的100个块其实是隐式参数_temp_tran_block_threshold(number of blocks for a dimension before we temp transform)的默认值,此外隐式参数_temp_tran_cache(determines if temp table is created with cache option,默认为TRUE)决定了这类临时表是否被缓存住;为了避免创建全局临时表可能带来的问题,就可以用到TEMP_DISABLE这个禁用临时表的选项,让优化器不再考虑使用物化的临时表。
  set autotace on;
  1、执行如下语句,注意sales表跟多个表做连接查询,但是其它的表之间没有连接条件:

  SQL>>
  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 ('1999-01','1999-02','2000-03','2000-04')
  GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 593420798
  -------------------------------------------------------------------------------------------------------------

  |>  -------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                |           |  1144 | 96096 |   964   (3)| 00:00:12 |       |       |
  |   1 |  HASH GROUP BY                  |           |  1144 | 96096 |   964   (3)| 00:00:12 |       |       |
  |*  2 |   HASH JOIN                     |           |  6231 |   511K|   963   (3)| 00:00:12 |       |       |
  |*  3 |    TABLE ACCESS FULL            | CHANNELS  |     2 |    42 |     3   (0)| 00:00:01 |       |       |
  |*  4 |    HASH JOIN                    |           | 12462 |   766K|   960   (3)| 00:00:12 |       |       |
  |   5 |     PART JOIN FILTER CREATE     | :BF0000   |   365 |  5840 |    18   (0)| 00:00:01 |       |       |
  |*  6 |      TABLE ACCESS FULL          | TIMES     |   365 |  5840 |    18   (0)| 00:00:01 |       |       |
  |*  7 |     HASH JOIN                   |           | 49822 |  2286K|   941   (3)| 00:00:12 |       |       |
  |*  8 |      TABLE ACCESS FULL          | CUSTOMERS |   383 |  9958 |   406   (1)| 00:00:05 |       |       |
  |   9 |      PARTITION RANGE JOIN-FILTER|           |   918K|    18M|   530   (3)| 00:00:07 |:BF0000|:BF0000|
  |  10 |       TABLE ACCESS FULL         | SALES     |   918K|    18M|   530   (3)| 00:00:07 |:BF0000|:BF0000|
  -------------------------------------------------------------------------------------------------------------

  Predicate Information (identified by operation>  ---------------------------------------------------
  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")
  6 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
  "T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
  7 - access("S"."CUST_ID"="C"."CUST_ID")
  8 - filter("C"."CUST_STATE_PROVINCE"='CA')
  Note
  -----
  - automatic DOP: skipped because of IO calibrate statistics are missing
  Statistics
  ----------------------------------------------------------
  2793  recursive calls
  2  db block gets
  6185  consistent gets
  2098  physical reads

  0  redo>  2168  bytes sent via SQL*Net to client
  545  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  225  sorts (memory)
  0  sorts (disk)
  41  rows processed
  2、Without modifying the SH schema, how can you improve the execution plan for the query
  mentioned in step 2? Verify your solution and explain why it is probably a better solution.
  You can use second_run.sql.
  a) Enable star transformation in your session. In this step, you do not want to use a
  temporary table for the star transformation. Looking at the previous execution plan, the
  optimizer estimates the data that is to be manipulated in megabytes. Using the star
  transformation as follows, the estimation is now expressed in kilobytes. That is why this

  new execution plan is probably a much better>  CUSTOMERS table is accessed using full scan twice. If the table is larger, the impact is significant.

  SQL>>
  SQL>>  ALTER SESSION SET star_transformation_enabled=TEMP_DISABLE;
  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','1999-02','2000-03','2000-04')
  GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 1869106227
  ------------------------------------------------------------------------------------------------------------------------

  |>  ------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                   |                   |   506 | 46552 |  1405   (1)| 00:00:17 |       |       |
  |   1 |  HASH GROUP BY                     |                   |   506 | 46552 |  1405   (1)| 00:00:17 |       |       |
  |*  2 |   HASH JOIN                        |                   |   506 | 46552 |  1404   (1)| 00:00:17 |       |       |
  |*  3 |    TABLE ACCESS FULL               | CUSTOMERS         |   383 |  9958 |   406   (1)| 00:00:05 |       |       |
  |*  4 |    HASH JOIN                       |                   |   506 | 33396 |   998   (1)| 00:00:12 |       |       |
  |*  5 |     TABLE ACCESS FULL              | CHANNELS          |     2 |    42 |     3   (0)| 00:00:01 |       |       |
  |*  6 |     HASH JOIN                      |                   |   506 | 22770 |   994   (1)| 00:00:12 |       |       |
  |*  7 |      TABLE ACCESS FULL             | TIMES             |   365 |  5840 |    18   (0)| 00:00:01 |       |       |
  |   8 |      VIEW                          | VW_ST_34C376F1    |   507 | 14703 |   976   (1)| 00:00:12 |       |       |
  |   9 |       NESTED LOOPS                 |                   |   507 | 28899 |   549   (1)| 00:00:07 |       |       |
  |  10 |        PARTITION RANGE SUBQUERY    |                   |   506 | 14195 |   462   (1)| 00:00:06 |KEY(SQ)|KEY(SQ)|
  |  11 |         BITMAP CONVERSION TO ROWIDS|                   |   506 | 14195 |   462   (1)| 00:00:06 |       |       |
  |  12 |          BITMAP AND                |                   |       |       |            |          |       |       |
  |  13 |           BITMAP MERGE             |                   |       |       |            |          |       |       |
  |  14 |            BITMAP KEY ITERATION    |                   |       |       |            |          |       |       |
  |  15 |             BUFFER SORT            |                   |       |       |            |          |       |       |
  |* 16 |              TABLE ACCESS FULL     | CHANNELS          |     2 |    26 |     3   (0)| 00:00:01 |       |       |
  |* 17 |             BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX |       |       |            |          |KEY(SQ)|KEY(SQ)|
  |  18 |           BITMAP MERGE             |                   |       |       |            |          |       |       |
  |  19 |            BITMAP KEY ITERATION    |                   |       |       |            |          |       |       |
  |  20 |             BUFFER SORT            |                   |       |       |            |          |       |       |
  |* 21 |              TABLE ACCESS FULL     | TIMES             |   365 |  5840 |    18   (0)| 00:00:01 |       |       |
  |* 22 |             BITMAP INDEX RANGE SCAN| SALES_TIME_BIX    |       |       |            |          |KEY(SQ)|KEY(SQ)|
  |  23 |           BITMAP MERGE             |                   |       |       |            |          |       |       |
  |  24 |            BITMAP KEY ITERATION    |                   |       |       |            |          |       |       |
  |  25 |             BUFFER SORT            |                   |       |       |            |          |       |       |
  |* 26 |              TABLE ACCESS FULL     | CUSTOMERS         |   383 |  6128 |   406   (1)| 00:00:05 |       |       |
  |* 27 |             BITMAP INDEX RANGE SCAN| SALES_CUST_BIX    |       |       |            |          |KEY(SQ)|KEY(SQ)|
  |  28 |        TABLE ACCESS BY USER ROWID  | SALES             |     1 |    29 |   514   (1)| 00:00:07 | ROWID | ROWID |
  ------------------------------------------------------------------------------------------------------------------------

  Predicate Information (identified by operation>  ---------------------------------------------------
  2 - access("ITEM_2"="C"."CUST_ID")
  3 - filter("C"."CUST_STATE_PROVINCE"='CA')
  4 - access("ITEM_1"="CH"."CHANNEL_ID")
  5 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
  6 - access("ITEM_3"="T"."TIME_ID")
  7 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
  "T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
  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"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
  "T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
  22 - access("S"."TIME_ID"="T"."TIME_ID")
  26 - filter("C"."CUST_STATE_PROVINCE"='CA')
  27 - access("S"."CUST_ID"="C"."CUST_ID")
  Note
  -----
  - automatic DOP: skipped because of IO calibrate statistics are missing
  - star transformation used for this statement
  Statistics
  ----------------------------------------------------------
  2697  recursive calls
  0  db block gets
  37357  consistent gets
  3599  physical reads

  0  redo>  2168  bytes sent via SQL*Net to client
  545  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  215  sorts (memory)
  0  sorts (disk)
  41  rows processed
  注意:与第一个查询相比,这里用了星形查询,返回的数据少了,只有K级别,原来的是M级别。但是customers有两次的全表扫描。
  3、How would you enhance the previous optimization without changing the SH schema? You
  can use third_run.sql.
  a) Let the optimizer decide if it is better to use a temporary table. You can try to set the
  STAR_TRANSFORMATION_ENABLED parameter to TRUE.

  SQL>>
  SQL>>  ALTER SESSION SET star_transformation_enabled=TRUE;
  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','1999-02','2000-03','2000-04')
  GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 3070449514
  ---------------------------------------------------------------------------------------------------------------------------------

  |>  ---------------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                    |                           |   506 | 40986 |   600   (1)| 00:00:08 |       |       |
  |   1 |  TEMP TABLE TRANSFORMATION          |                           |       |       |            |          |       |       |
  |   2 |   LOAD AS SELECT                    | SYS_TEMP_0FD9D660B_110935 |       |       |            |          |       |       |
  |*  3 |    TABLE ACCESS FULL                | CUSTOMERS                 |   383 |  9958 |   406   (1)| 00:00:05 |       |       |
  |   4 |   HASH GROUP BY                     |                           |   506 | 40986 |   194   (2)| 00:00:03 |       |       |
  |*  5 |    HASH JOIN                        |                           |   506 | 40986 |   193   (2)| 00:00:03 |       |       |
  |   6 |     TABLE ACCESS FULL               | SYS_TEMP_0FD9D660B_110935 |   383 |  5745 |     2   (0)| 00:00:01 |       |       |
  |*  7 |     HASH JOIN                       |                           |   506 | 33396 |   191   (2)| 00:00:03 |       |       |
  |*  8 |      TABLE ACCESS FULL              | CHANNELS                  |     2 |    42 |     3   (0)| 00:00:01 |       |       |
  |*  9 |      HASH JOIN                      |                           |   506 | 22770 |   187   (1)| 00:00:03 |       |       |
  |* 10 |       TABLE ACCESS FULL             | TIMES                     |   365 |  5840 |    18   (0)| 00:00:01 |       |       |
  |  11 |       VIEW                          | VW_ST_62EEF96F            |   507 | 14703 |   169   (1)| 00:00:03 |       |       |
  |  12 |        NESTED LOOPS                 |                           |   507 | 28899 |   145   (0)| 00:00:02 |       |       |
  |  13 |         PARTITION RANGE SUBQUERY    |                           |   506 | 14195 |    58   (2)| 00:00:01 |KEY(SQ)|KEY(SQ)|
  |  14 |          BITMAP CONVERSION TO ROWIDS|                           |   506 | 14195 |    58   (2)| 00:00:01 |       |       |
  |  15 |           BITMAP AND                |                           |       |       |            |          |       |       |
  |  16 |            BITMAP MERGE             |                           |       |       |            |          |       |       |
  |  17 |             BITMAP KEY ITERATION    |                           |       |       |            |          |       |       |
  |  18 |              BUFFER SORT            |                           |       |       |            |          |       |       |
  |* 19 |               TABLE ACCESS FULL     | CHANNELS                  |     2 |    26 |     3   (0)| 00:00:01 |       |       |
  |* 20 |              BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX         |       |       |            |          |KEY(SQ)|KEY(SQ)|
  |  21 |            BITMAP MERGE             |                           |       |       |            |          |       |       |
  |  22 |             BITMAP KEY ITERATION    |                           |       |       |            |          |       |       |
  |  23 |              BUFFER SORT            |                           |       |       |            |          |       |       |
  |* 24 |               TABLE ACCESS FULL     | TIMES                     |   365 |  5840 |    18   (0)| 00:00:01 |       |       |
  |* 25 |              BITMAP INDEX RANGE SCAN| SALES_TIME_BIX            |       |       |            |          |KEY(SQ)|KEY(SQ)|
  |  26 |            BITMAP MERGE             |                           |       |       |            |          |       |       |
  |  27 |             BITMAP KEY ITERATION    |                           |       |       |            |          |       |       |
  |  28 |              BUFFER SORT            |                           |       |       |            |          |       |       |
  |  29 |               TABLE ACCESS FULL     | SYS_TEMP_0FD9D660B_110935 |   383 |  1915 |     2   (0)| 00:00:01 |       |       |
  |* 30 |              BITMAP INDEX RANGE SCAN| SALES_CUST_BIX            |       |       |            |          |KEY(SQ)|KEY(SQ)|
  |  31 |         TABLE ACCESS BY USER ROWID  | SALES                     |     1 |    29 |   110   (0)| 00:00:02 | ROWID | ROWID |
  ---------------------------------------------------------------------------------------------------------------------------------

  Predicate Information (identified by operation>  ---------------------------------------------------
  3 - filter("C"."CUST_STATE_PROVINCE"='CA')
  5 - access("ITEM_2"="C0")
  7 - access("ITEM_1"="CH"."CHANNEL_ID")
  8 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
  9 - access("ITEM_3"="T"."TIME_ID")
  10 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
  "T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
  19 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
  20 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  24 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
  "T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
  25 - access("S"."TIME_ID"="T"."TIME_ID")
  30 - access("S"."CUST_ID"="C0")
  Note
  -----
  - automatic DOP: skipped because of IO calibrate statistics are missing
  - star transformation used for this statement
  Statistics
  ----------------------------------------------------------
  2980  recursive calls
  20  db block gets
  36271  consistent gets
  2208  physical reads

  604  redo>  2168  bytes sent via SQL*Net to client
  545  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  230  sorts (memory)
  0  sorts (disk)
  41  rows processed
  注意:执行计划的1-3行指出了TEMP TABLE TRANSFORMATION LOAD AS SELECT  TABLE ACCESS FULL CUSTOMERS的全局临时表为SYS_TEMP_0FD9D660B_110935,可以避免多次访问维度表,以后创建bitmap join就在该表上创建。解决了以前的疑惑。
  4,How do you eliminate one access on the CUSTOMERS table from the previous execution
  plan for the same SELECT statement seen in step 3?
  a) Create a bitmap join index between the SALES and CUSTOMERS tables.
  6. Try to apply your finding. What happens and why?
  a) Because the CUSTOMERS_PK primary key constraint is not enforced, it is not possible to
  create a bitmap join index between the SALES and CUSTOMERS tables.
  alter table sh.customers enable constraint customers_pk;
  为什么知道需要enable customers表的customers_pk的主键约束呢,因为星形查询跟维度表连接的列必须是主键或者唯一键约束,所以在创建bitmap join索引的时候,只要看跟哪张维度表连接,就把该维度表的约束给enable就行了。而且选择的维度表的列必须是查询条件语句中出现的列,比如customers.cust_state_province。
  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 sales.time_id = times.time_id AND
  sales.cust_id = customers.cust_id AND
  sales.channel_id = channels.channel_id AND
  c.cust_state_province = 'CA' AND
  ch.channel_desc in ('Internet','Catalog') AND
  t.calendar_quarter_desc IN ('1999-01','1999-02','2000-03','2000-04')
  GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
  CREATE BITMAP INDEX sales_c_state_bjix ON
  sales(customers.cust_state_province)
  FROM sales, customers
  WHERE sales.cust_id=customers.cust_id
  LOCAL NOLOGGING COMPUTE STATISTICS;
  desc user_constraints;
  select CONSTRAINT_NAME from user_constraints where TABLE_NAME='CUSTOMERS';
  alter table customers enable constraint CUSTOMERS_PK;
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 632695221
  -----------------------------------------------------------------------------------------------------------------------------

  |>  -----------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                       |                    |  1144 | 96096 |   574   (1)| 00:00:07 |       |       |
  |   1 |  HASH GROUP BY                         |                    |  1144 | 96096 |   574   (1)| 00:00:07 |       |       |
  |*  2 |   HASH JOIN                            |                    |  3975 |   326K|   552   (1)| 00:00:07 |       |       |
  |*  3 |    TABLE ACCESS FULL                   | CHANNELS           |     2 |    42 |     3   (0)| 00:00:01 |       |       |
  |*  4 |    HASH JOIN                           |                    |  3975 |   244K|   549   (1)| 00:00:07 |       |       |
  |*  5 |     TABLE ACCESS FULL                  | TIMES              |   365 |  5840 |    18   (0)| 00:00:01 |       |       |
  |*  6 |     HASH JOIN                          |                    |  3984 |   182K|   530   (1)| 00:00:07 |       |       |
  |*  7 |      TABLE ACCESS FULL                 | CUSTOMERS          |   383 |  9958 |   406   (1)| 00:00:05 |       |       |
  |   8 |      PARTITION RANGE SUBQUERY          |                    | 73467 |  1506K|   124   (1)| 00:00:02 |KEY(SQ)|KEY(SQ)|
  |   9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SALES              | 73467 |  1506K|   124   (1)| 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              |   365 |  5840 |    18   (0)| 00:00:01 |       |       |
  |* 21 |            BITMAP INDEX RANGE SCAN     | SALES_TIME_BIX     |       |       |            |          |KEY(SQ)|KEY(SQ)|
  |* 22 |          BITMAP INDEX SINGLE VALUE     | SALES_C_STATE_BJIX |       |       |            |          |KEY(SQ)|KEY(SQ)|
  -----------------------------------------------------------------------------------------------------------------------------

  Predicate Information (identified by operation>  ---------------------------------------------------
  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"='1999-02' OR
  "T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
  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"='1999-02' OR
  "T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
  21 - access("S"."TIME_ID"="T"."TIME_ID")
  22 - access("S"."SYS_NC00008$"='CA')
  Note
  -----
  - automatic DOP: skipped because of IO calibrate statistics are missing
  - star transformation used for this statement
  Statistics
  ----------------------------------------------------------
  2808  recursive calls
  0  db block gets
  9161  consistent gets
  2045  physical reads

  132  redo>  2168  bytes sent via SQL*Net to client
  545  bytes received via SQL*Net from client
  4  SQL*Net roundtrips to/from client
  220  sorts (memory)
  0  sorts (disk)
  41  rows processed
  5、
  总结:通过比较,当我们建立了位图连接索引后,系统就没有用到两个临时表和视图了,节省了执行的流程。
  星形查询的要点:
  1、至少是3张表连接查询。
  2、一张是事实表,其它是维度表,所谓的事实表是跟其它表连接的表,比如本案例中的sales表。
  3、一般在事实表的连接条件的列上创建位图连接索引。
  4、纬度表上的连接条件列上是主键约束。
  6、位图连接索引:
  1、位图连接索引(bitmap join index)是基于两个表的连接的位图索引,在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时,标准方法是连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时,就不需要执行连接,因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的ROWID进一步改进性能,并且减少访问数据所需的I/O数量。
  2、创建位图连接索引时,指定涉及的两个表。相应的语法应该遵循如下模式:
  create bitmap index FACT_DIM_COL_IDX
  on FACT(DIM.Descr_Col)
  from FACT, DIM
  where FACT.JoinCol = DIM.JoinCol;
  位图连接的语法比较特别,其中包含FROM子句和WHERE子句,并且引用两个单独的表。索引列通常是维度表中的描述列-- 就是说,如果维度是CUSTOMER,并且它的主键是CUSTOMER_ID,则通常索引Customer_Name这样的列。如果事实表名为SALES,可以使用如下的命令创建索引:
  create bitmap index SALES_CUST_NAME_IDX
  on SALES(CUSTOMER.Customer_Name)
  from SALES, CUSTOMER
  where SALES.Customer_ID=CUSTOMER.Customer_ID;
  如果用户接下来使用指定Customer_Name列值的WHERE子句查询SALES和CUSTOMER表,优化器就可以使用位图连接索引快速返回匹配连接条件和Customer_Name条件的行。
  3、限制使用条件
  位图连接索引的使用一般会受到限制:只可以索引维度表中的列。用于连接的列必须是维度表中的主键或唯一约束;如果是复合主键,则必须使用连接中的每一列。不可以对索引组织表创建位图连接索引,并且适用于常规位图索引的限制也适用于位图连接索引。
  创建位图连接索引11g帮助文档入口:
  Data Warehousing and Business Intelligence ——> Data Warehousing Guide ->indexes ->Using Bitmap Indexes in Data Warehouses -> 7.3


运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.iyunv.com/thread-563930-1-1.html 上篇帖子: oracle 官网下载 下篇帖子: Oracle中的日期类型
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表