這裡，我們介紹一下對於星型模式的一些調優思想。我們先來介紹一下關於星型模式的一些知識。

星型模式是資料倉儲中極為常見的一種設計模式，它包括一個大的用來儲存詳細業務資料的事實表（Fact Table），同時包括外來鍵。這些外來鍵關聯到一個相對較小的更靜態的維度表（dimenstion table）。在維度表中記錄了用來描述事實表中業務資料的一些分類，比如客戶、時間、產品等資訊。

在OLAP大型資料集中，星型模式是一個實現高效能有效的儲存途徑。在Oracle及其他一些商業資料庫當中都做了重要的最佳化，

星型模式的一種特殊形式稱為雪花模式。雪花模式是指維度表本身也包括外健，用以關聯其他更高一級的維度表或是其他資料表。它是一種常見的模式，能夠合理地滿足一些資料倉儲的需求。

下面，我們以Oracle示例模式中的SH下的表為例，來看一下星型模式。

我把SH模式下的銷售（sale）相關的業務表畫了一個圖，中間的要SALES事實表中的銷售資料（QUANTITY_SOLD）、銷售金額（AMOUNT_SOLD）是根據各個時間段、產品、客戶、銷售渠道和促銷聚合而來的。透過關聯時間維度表（TIMES）、產品維度表（PRODUCTS）、客戶維度表（CUSTOMERS）、渠道維度表（CHANNELS）和促銷活動維度表（PROMOTIONS），我們可以得到事實表中每個聚集的詳細描述。

下面，我們來看一個查詢。

sh@ORA11> select quantity_sold,amount_sold

2 from times t join sales s on t.time_id = s.time_id

3 join products p1 on s.prod_id = p1.prod_id

4 join customers c1 on s.cust_id = c1.cust_id

5 join channels c2 on s.channel_id = c2.channel_id

6 join promotions p2 on s.promo_id = p2.promo_id

7 where c1.cust_first_name = 'Ramsay'

8 and c1.cust_last_name = 'Alden'

9 and t.day_name = 'Saturday'

10 and c2.channel_class = 'Direct'

11 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'

12 and p2.promo_name = 'NO PROMOTION #'

13 /

QUANTITY_SOLD AMOUNT_SOLD

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

1 1264.4

sh@ORA11>

Oracle處理這種星型連線的預設方法是查詢所有的維度表來檢索與WHERE條件相關的外來鍵值，然後使用全笛卡爾連線合並這些結果集，產生的外來鍵被用於識別事實表的記錄。如果事實表中對外來鍵值上有合理的索引，那麼它可以被用來最佳化最後一步。

為了便於分析，下面，我把表SALES上所建立的索引進行一個刪除。

sh@ORA11> begin

2 for i in (select index_name

3 from user_indexes

4 where table_name = 'SALES')

5 loop

6 execute immediate 'drop index ' || i.index_name;

7 end loop;

8 end;

9 /

PL/SQL procedure successfully completed.

sh@ORA11>

刪除完成。

在事實表沒有索引的情況下，我們來看一下上述查詢的執行計劃。

sh@ORA11> set autotrace traceonly explain

sh@ORA11> select quantity_sold,amount_sold

2 from times t join sales s on t.time_id = s.time_id

3 join products p1 on s.prod_id = p1.prod_id

4 join customers c1 on s.cust_id = c1.cust_id

5 join channels c2 on s.channel_id = c2.channel_id

6 join promotions p2 on s.promo_id = p2.promo_id

7 where c1.cust_first_name = 'Ramsay'

8 and c1.cust_last_name = 'Alden'

9 and t.day_name = 'Saturday'

10 and c2.channel_class = 'Direct'

11 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'

12 and p2.promo_name = 'NO PROMOTION #'

13 /

Execution Plan

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

Plan hash value: 2561703891

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

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

| 0 | SELECT STATEMENT | | 1 | 136 | 776 (2)| 00:00:10 | | |

| 1 | NESTED LOOPS | | | | | | | |

| 2 | NESTED LOOPS | | 1 | 136 | 776 (2)| 00:00:10 | | |

|* 3 | HASH JOIN | | 241 | 27956 | 535 (3)| 00:00:07 | | |

|* 4 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 | | |

|* 5 | HASH JOIN | | 578 | 60690 | 532 (3)| 00:00:07 | | |

|* 6 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29 | 17 (0)| 00:00:01 | | |

|* 7 | HASH JOIN | | 2312 | 171K| 514 (3)| 00:00:07 | | |

| 8 | PART JOIN FILTER CREATE | :BF0000 | 261 | 4437 | 18 (0)| 00:00:01 | | |

|* 9 | TABLE ACCESS FULL | TIMES | 261 | 4437 | 18 (0)| 00:00:01 | | |

|* 10 | HASH JOIN | | 12941 | 745K| 496 (3)| 00:00:06 | | |

|* 11 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |

| 12 | PARTITION RANGE JOIN-FILTER| | 918K| 25M| 489 (2)| 00:00:06 |:BF0000|:BF0000|

| 13 | TABLE ACCESS FULL | SALES | 918K| 25M| 489 (2)| 00:00:06 |:BF0000|:BF0000|

|* 14 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 0 (0)| 00:00:01 | | |

|* 15 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 20 | 1 (0)| 00:00:01 | | |

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

Predicate Information (identified by operation id):

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

3 - access("S"."CHANNEL_ID"="C2"."CHANNEL_ID")

4 - filter("C2"."CHANNEL_CLASS"='Direct')

5 - access("S"."PROMO_ID"="P2"."PROMO_ID")

6 - filter("P2"."PROMO_NAME"='NO PROMOTION #')

7 - access("T"."TIME_ID"="S"."TIME_ID")

9 - filter("T"."DAY_NAME"='Saturday')

10 - access("S"."PROD_ID"="P1"."PROD_ID")

11 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')

14 - access("S"."CUST_ID"="C1"."CUST_ID")

15 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')

sh@ORA11> set autotrace off

sh@ORA11>

對事實表的外來鍵值建立索引。

sh@ORA11> create index sale_idx01 on sales(prod_id,cust_id,time_id,channel_id,promo_id)

2 /

Index created.

sh@ORA11> exec dbms_stats.gather_table_stats(user,'sales',cascade=>true)

PL/SQL procedure successfully completed.

sh@ORA11>

我們再來看一下上述查詢的執行計劃。

Execution Plan

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

Plan hash value: 1244022574

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

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

| 0 | SELECT STATEMENT | | 1 | 136 | 489 (1)| 00:00:06 | | |

| 1 | NESTED LOOPS | | | | | | | |

| 2 | NESTED LOOPS | | 1 | 136 | 489 (1)| 00:00:06 | | |

| 3 | MERGE JOIN CARTESIAN | | 21 | 2247 | 446 (1)| 00:00:06 | | |

| 4 | MERGE JOIN CARTESIAN | | 1 | 90 | 428 (1)| 00:00:06 | | |

| 5 | MERGE JOIN CARTESIAN | | 1 | 79 | 425 (1)| 00:00:06 | | |

| 6 | MERGE JOIN CARTESIAN | | 1 | 59 | 20 (0)| 00:00:01 | | |

|* 7 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |

| 8 | BUFFER SORT | | 1 | 29 | 17 (0)| 00:00:01 | | |

|* 9 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29 | 17 (0)| 00:00:01 | | |

| 10 | BUFFER SORT | | 1 | 20 | 408 (1)| 00:00:05 | | |

|* 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 20 | 405 (1)| 00:00:05 | | |

| 12 | BUFFER SORT | | 2 | 22 | 23 (0)| 00:00:01 | | |

|* 13 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 | | |

| 14 | BUFFER SORT | | 261 | 4437 | 443 (1)| 00:00:06 | | |

|* 15 | TABLE ACCESS FULL | TIMES | 261 | 4437 | 18 (0)| 00:00:01 | | |

|* 16 | INDEX RANGE SCAN | SALE_IDX01 | 1 | | 2 (0)| 00:00:01 | | |

| 17 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 1 | 29 | 3 (0)| 00:00:01 | ROWID | ROWID |

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

Predicate Information (identified by operation id):

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

7 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')

9 - filter("P2"."PROMO_NAME"='NO PROMOTION #')

11 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')

13 - filter("C2"."CHANNEL_CLASS"='Direct')

15 - filter("T"."DAY_NAME"='Saturday')

16 - access("S"."PROD_ID"="P1"."PROD_ID" AND "S"."CUST_ID"="C1"."CUST_ID" AND

"T"."TIME_ID"="S"."TIME_ID" AND "S"."CHANNEL_ID"="C2"."CHANNEL_ID"

AND "S"."PROMO_ID"="P2"."PROMO_ID")

對維度表PRODUCTS、PROMOTIONS、CUSTOMERS 、CHANNELS 、TIMES的笛卡爾合併連線（ID為3、4、5、5）定位出外來鍵值的組合。它們被提供給組合索引進行索引區間查詢（ID為16），以檢索表SALES中的記錄。

對於上述SQL，我們使用10053事件來看一下最佳化器進行最佳化重寫後的SQL是個什麼樣子的。

SELECT /*+ OPT_ESTIMATE (TABLE "C1" MIN=16.000000 )

OPT_ESTIMATE (INDEX_SCAN "C1" "CUSTOMERS_PK" MIN=518.000000 )

OPT_ESTIMATE (INDEX_FILTER "C1" "CUSTOMERS_PK" MIN=518.000000 )

OPT_ESTIMATE (INDEX_SCAN "S" "SALE_IDX01" MIN=1.000000 )

OPT_ESTIMATE (INDEX_FILTER "S" "SALE_IDX01" MIN=1.000000 )

OPT_ESTIMATE (TABLE "S" ROWS=1.000000 ) */

"S"."QUANTITY_SOLD" "QUANTITY_SOLD", "S"."AMOUNT_SOLD" "AMOUNT_SOLD"

FROM "SH"."TIMES" "T",

"SH"."SALES" "S",

"SH"."PRODUCTS" "P1",

"SH"."CUSTOMERS" "C1",

"SH"."CHANNELS" "C2",

"SH"."PROMOTIONS" "P2"

WHERE "C1"."CUST_FIRST_NAME" = 'Ramsay'

AND "C1"."CUST_LAST_NAME" = 'Alden'

AND "T"."DAY_NAME" = 'Saturday'

AND "C2"."CHANNEL_CLASS" = 'Direct'

AND "P1"."PROD_NAME" = '17" LCD w/built-in HDTV Tuner'

AND "P2"."PROMO_NAME" = 'NO PROMOTION #'

AND "S"."PROMO_ID" = "P2"."PROMO_ID"

AND "S"."CHANNEL_ID" = "C2"."CHANNEL_ID"

AND "S"."CUST_ID" = "C1"."CUST_ID"

AND "S"."PROD_ID" = "P1"."PROD_ID"

AND "T"."TIME_ID" = "S"."TIME_ID"

按這個方法執行星型連線是高效的，因為它減少了對大事實表的訪問。但是也存在一個較嚴重的缺陷，因為星型模式通常用在資料倉儲下，而對於資料倉儲又存在大量不同的where子句，這也就意味著需要大量不同的組合索引。

用於星型查詢的笛卡爾連線方法在面臨更復雜的模式中是查詢時有可能失敗，比如如下兩個問題。

如果在維度表中匹配的記錄數很多，笛卡爾集可能產生很大的結果集；

需要支援所有可能的維度健的組合的組合索引，建立所有這些索引可能不實際。

為了解決這些問題，Oracle提供了星型轉化這種最佳化方法。星型轉化利用事實表上的點陣圖索引來產生一個最佳化的執行計劃。來處理維度表過多或是無法為所有可能的查詢建立組合索引的查詢。關於點陣圖索引，請參考http://blog.163.com/donfang_jianping/blog/static/136473951201321610288334/中的介紹。

下面，我們對上述的查詢建立點陣圖索引，並檢視相應的執行計劃。

sh@ORA11> drop index sale_idx01;

Index dropped.

sh@ORA11> create bitmap index sales_prod_bi on sales(prod_id) local;

Index created.

sh@ORA11> create bitmap index sales_cust_bi on sales(cust_id) local;

Index created.

sh@ORA11> create bitmap index sales_time_bi on sales(time_id) local;

Index created.

sh@ORA11> create bitmap index sales_channel_bi on sales(channel_id) local;

Index created.

sh@ORA11> create bitmap index sales_promo_bi on sales(promo_id) local;

Index created.

sh@ORA11> exec dbms_stats.gather_table_stats(user,'sales',cascade=>true);

PL/SQL procedure successfully completed.

sh@ORA11>

執行上述查詢SQL。

sh@ORA11> set autotrace traceonly explain

sh@ORA11> select quantity_sold,amount_sold

2 from times t join sales s on t.time_id = s.time_id

3 join products p1 on s.prod_id = p1.prod_id

4 join customers c1 on s.cust_id = c1.cust_id

5 join channels c2 on s.channel_id = c2.channel_id

6 join promotions p2 on s.promo_id = p2.promo_id

7 where c1.cust_first_name = 'Ramsay'

8 and c1.cust_last_name = 'Alden'

9 and t.day_name = 'Saturday'

10 and c2.channel_class = 'Direct'

11 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'

12 and p2.promo_name = 'NO PROMOTION #'

13 /

Execution Plan

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

Plan hash value: 815725874

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

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

| 0 | SELECT STATEMENT | | 1 | 136| 703 (1)| 00:00:09 | | |

| 1 | NESTED LOOPS | | || | | | |

| 2 | NESTED LOOPS | | 1 | 136| 703 (1)| 00:00:09 | | |

|* 3 | HASH JOIN | | 241 | 27956| 462 (1)| 00:00:06 | | |

|* 4 | TABLE ACCESS FULL | CHANNELS | 2 | 22| 3 (0)| 00:00:01 | | |

|* 5 | HASH JOIN | | 578 | 60690| 458 (1)| 00:00:06 | | |

|* 6 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29| 17 (0)| 00:00:01 | | |

|* 7 | HASH JOIN | | 2312 | 171K| 441 (1)| 00:00:06 | | |

|* 8 | TABLE ACCESS FULL | TIMES | 261 | 4437| 18 (0)| 00:00:01 | | |

| 9 | NESTED LOOPS | | || | | | |

| 10 | NESTED LOOPS | | 12941 | 745K| 422 (0)| 00:00:06 | | |

|* 11 | TABLE ACCESS FULL | PRODUCTS | 1 | 3| 3 (0)| 00:00:01 | | |

| 12 | PARTITION RANGE ALL | | || | | 1 | 28 |

| 13 | BITMAP CONVERSION TO ROWIDS | | || | | | |

|* 14 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BI | || | | 1 | 28 |

| 15 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 361K| 422 (0)| 00:00:06 | 1 | 1 |

|* 16 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 || 0 (0)| 00:00:01 | | |

|* 17 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 20| 1 (0)| 00:00:01 | | |

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

Predicate Information (identified by operation id):

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

3 - access("S"."CHANNEL_ID"="C2"."CHANNEL_ID")

4 - filter("C2"."CHANNEL_CLASS"='Direct')

5 - access("S"."PROMO_ID"="P2"."PROMO_ID")

6 - filter("P2"."PROMO_NAME"='NO PROMOTION #')

7 - access("T"."TIME_ID"="S"."TIME_ID")

8 - filter("T"."DAY_NAME"='Saturday')

11 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')

14 - access("S"."PROD_ID"="P1"."PROD_ID")

16 - access("S"."CUST_ID"="C1"."CUST_ID")

17 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')

sh@ORA11> set autotrace off

sh@ORA11>

從執行計劃來看，它沒有實現我們想要實現的執行計劃。

有一個問題，要注意一下。對於星型轉化，需要設定引數star_transformation_enabled為true，然後在SQL中加入”star_transformation“hint，或是在SQL中加入“opt_param('star_transformation_enabled',true) star_transformation ”這樣的hint。如下：

sh@ORA11> set autotrace traceonly explain

sh@ORA11> select /*+opt_param('star_transformation_enabled','true')

2 star_transformation*/

3 quantity_sold,amount_sold

4 from times t join sales s on t.time_id = s.time_id

5 join products p1 on s.prod_id = p1.prod_id

6 join customers c1 on s.cust_id = c1.cust_id

7 join channels c2 on s.channel_id = c2.channel_id

8 join promotions p2 on s.promo_id = p2.promo_id

9 where c1.cust_first_name = 'Ramsay'

10 and c1.cust_last_name = 'Alden'

11 and t.day_name = 'Saturday'

12 and c2.channel_class = 'Direct'

13 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'

14 and p2.promo_name = 'NO PROMOTION #'

15 /

Execution Plan

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

Plan hash value: 1748952924

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

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

| 0 | SELECT STATEMENT | | 1 | 46 | 877 (1)| 00:00:11 | | |

|* 1 | HASH JOIN | | 1 | 46 | 450 (1)| 00:00:06 | | |

| 2 | PARTITION RANGE SUBQUERY | | | 5 | 431 (1)| 00:00:06 |KEY(SQ)|KEY(SQ)|

| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | | 5 | 431 (1)| 00:00:06 |KEY(SQ)|KEY(SQ)|

| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |

| 5 | BITMAP AND | | | | | | | |

| 6 | BITMAP MERGE | | | | | | | |

| 7 | BITMAP KEY ITERATION | | | | | | | |

| 8 | BUFFER SORT | | | | | | | |

|* 9 | TABLE ACCESS FULL | CUSTOMERS | 1 | 20 | 405 (1)| 00:00:05 | | |

| 11 | BITMAP MERGE | | | | | | | |

| 12 | BITMAP KEY ITERATION | | | | | | | |

| 13 | BUFFER SORT | | | | | | | |

|* 14 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |

| 16 | BITMAP MERGE | | | | | | | |

| 17 | BITMAP KEY ITERATION | | | | | | | |

| 18 | BUFFER SORT | | | | | | | |

|* 19 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 | | |

| 21 | BITMAP MERGE | | | | | | | |

| 22 | BITMAP KEY ITERATION | | | | | | | |

| 23 | BUFFER SORT | | | | | | | |

|* 24 | TABLE ACCESS FULL | PROMOTIONS | 1 | 29 | 17 (0)| 00:00:01 | | |

|* 26 | TABLE ACCESS FULL | TIMES | 261 | 4437 | 18 (0)| 00:00:01 | | |

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

Predicate Information (identified by operation id):

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

1 - access("T"."TIME_ID"="S"."TIME_ID")

9 - filter("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')

10 - access("S"."CUST_ID"="C1"."CUST_ID")

14 - filter("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')

15 - access("S"."PROD_ID"="P1"."PROD_ID")

19 - filter("C2"."CHANNEL_CLASS"='Direct')

20 - access("S"."CHANNEL_ID"="C2"."CHANNEL_ID")

24 - filter("P2"."PROMO_NAME"='NO PROMOTION #')

25 - access("S"."PROMO_ID"="P2"."PROMO_ID")

26 - filter("T"."DAY_NAME"='Saturday')

Note

-----

- star transformation used for this statement

sh@ORA11> set autotrace off

sh@ORA11>

這裡有一個問題要注意一下，星型轉化並不比對事實表新增組合索引的方式得到的效能更好。然而，在實際當中，我們無法為所有可能的查詢建立足夠的組合索引。但是，透過星型轉化，我們只需要在每個外來鍵上建立一個點陣圖索引以支援WHERE子句中可能存在的組合。

當然，我們對於上述的SQL可以進行一步最佳化，比較為客戶名、產品名稱建立索引等。下面，我們來看一個相對較簡單的SQL。

sh@ORA11> set autotrace traceonly explain

sh@ORA11> select /*+opt_param('star_transformation_enabled','true')

2 star_transformation*/

3 quantity_sold,amount_sold

4 from sales s join products p1 on s.prod_id = p1.prod_id

5 join customers c1 on s.cust_id = c1.cust_id

6 where c1.cust_first_name = 'Ramsay'

7 and c1.cust_last_name = 'Alden'

8 and p1.prod_name = '17" LCD w/built-in HDTV Tuner'

9 /

Execution Plan

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

Plan hash value: 2220034904

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

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

| 0 | SELECT STATEMENT | | 2 | 34 |12 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE ALL | | 2 | 39 | 7 (0)| 00:00:01 | 1 | 28 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2 | 39 | 7 (0)| 00:00:01 | 1 | 28 |

| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |

| 4 | BITMAP AND | | | | | | | |

| 5 | BITMAP MERGE | | | | | | | |

| 6 | BITMAP KEY ITERATION | | | | | | | |

| 7 | BUFFER SORT | | | | | | | |

| 8 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 10 | 200 | 3 (0)| 00:00:01 | | |

|* 9 | INDEX RANGE SCAN | CUST_NAME_IDX | 10 | | 1 (0)| 00:00:01 | | |

|* 10 | BITMAP INDEX RANGE SCAN | SALES_CUST_BI | | | | | 1 | 28 |

| 11 | BITMAP MERGE | | | | | | | |

| 12 | BITMAP KEY ITERATION | | | | | | | |

| 13 | BUFFER SORT | | | | | | | |

| 14 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | 2 (0)| 00:00:01 | | |

|* 15 | INDEX RANGE SCAN | PROD_NAME_IDX | 1 | | 1 (0)| 00:00:01 | | |

|* 16 | BITMAP INDEX RANGE SCAN | SALES_PROD_BI | | | | | 1 | 28 |

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

Predicate Information (identified by operation id):

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

9 - access("C1"."CUST_FIRST_NAME"='Ramsay' AND "C1"."CUST_LAST_NAME"='Alden')

10 - access("S"."CUST_ID"="C1"."CUST_ID")

15 - access("P1"."PROD_NAME"='17" LCD w/built-in HDTV Tuner')

16 - access("S"."PROD_ID"="P1"."PROD_ID")

Note

-----

- star transformation used for this statement

sh@ORA11> set autotrace off

sh@ORA11>

上述的索引，我們建立的傳統的B*樹索引。對於星型轉化，我們可以點陣圖連線索引來進一步的最佳化，如下。

sh@ORA11> create bitmap index sales_prod_bjix on sales(p.prod_name)

2 from sales s,products p

3 where s.prod_id = p.prod_id

4 local

5 /

Index created.

sh@ORA11> create bitmap index sales_cust_bjix on sales(c.cust_first_name,c.cust_last_name)

2 from sales s,customers c

3 where s.cust_id = c.cust_id

4 local

5 /

Index created.

sh@ORA11>

在上述，我們建立了兩個點陣圖連線索引，下面，我們來看查詢。

sh@ORA11> set autotrace traceonly explain

sh@ORA11> select quantity_sold,amount_sold

2 from sales s join products p on s.prod_id = p.prod_id

3 join customers c on s.cust_id = c.cust_id

4 where c.cust_first_name = 'Ramsay'

5 and c.cust_last_name = 'Alden'

6 and p.prod_name = '17" LCD w/built-in HDTV Tuner'

7 /

Execution Plan

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

Plan hash value: 3815036790

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

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

| 0 | SELECT STATEMENT | | 386 | 6562 | 59 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE ALL | | 386 | 6562 | 59 (0)| 00:00:01 | 1 | 28 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 386 | 6562 | 59 (0)| 00:00:01 | 1 | 28 |

| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |

| 4 | BITMAP AND | | | | | | | |

|* 5 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BJIX | | | | | 1 | 28 |

|* 6 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BJIX | | | | | 1 | 28 |

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

Predicate Information (identified by operation id):

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

5 - access("S"."SYS_NC00009$"='Ramsay' AND "S"."SYS_NC00010$"='Alden')

6 - access("S"."SYS_NC00008$"='17" LCD w/built-in HDTV Tuner')

sh@ORA11> set autotrace off

sh@ORA11>

Oracle 星型模式的調優

相關文章