Oracle 星型模式的調優

pineapple_lch發表於2015-03-12

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

星型模式是資料倉儲中極為常見的一種設計模式，它包括一個大的用來儲存詳細業務資料的事實表（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>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27661381/viewspace-1457775/，如需轉載，請註明出處，否則將追究法律責任。

Oracle優化器：星型轉換（Star Query Transformation ）
2015-06-05
Oracle優化ORM
優化星型查詢
2015-05-08
優化
oracle的調優(轉)
2007-08-10
Oracle
ORACLE調優方法
2011-07-17
Oracle
oracle sql調優
2011-05-12
OracleSQL
oracle效能調優
2008-03-04
Oracle
Oracle常用調優手段
2017-03-13
Oracle
Oracle 調優總結
2014-06-26
Oracle
淺談Oracle調優
2012-07-23
Oracle
Oracle調優總結
2016-05-24
Oracle
Oracle 效能調優概述
2013-03-26
Oracle
針對oracle效能的io調優
2013-06-08
Oracle
Unix環境下的Oracle調優
2007-05-27
Oracle
Oracle常用調優手段[轉]
2011-08-11
Oracle
Oracle效能調優原則
2007-03-14
Oracle
【Oracle】undo 自動調優
2011-10-16
Oracle
Oracle專家調優秘密
2007-10-08
Oracle
DBA 應遵循的 Oracle 調優原則
2007-05-10
Oracle
oracle資料庫調優描述
2022-03-23
Oracle資料庫
ORACLE DW效能調優研究方向
2010-05-05
Oracle
Oracle專家調優秘密(zt)
2007-06-30
Oracle
Oracle專家調優秘密(轉)
2007-08-13
Oracle
Oracle調優---調整優化器行為的一般性原則
2007-11-26
Oracle優化
Oracle 調優確定存在問題的SQL
2020-07-02
OracleSQL
Unix環境下的Oracle調優原則
2010-03-09
Oracle
Oracle調優之看懂Oracle執行計劃
2020-06-18
Oracle
死星模式、死星架構
2018-12-12
模式架構
Oracle SQL調優之分割槽表
2019-05-18
OracleSQL
Oracle專家調優祕密(轉)
2019-06-03
Oracle
部落格連結—Oracle效能調優
2016-02-18
Oracle
oracle效能優化-共享池調整
2008-06-06
Oracle優化
Oracle調優(入門及提高篇)
2011-05-13
Oracle
Oracle記憶體引數調優
2009-11-04
Oracle記憶體
Oracle專家調優祕密(zt)
2007-10-19
Oracle
Oracle效能調優實踐中的幾點心得
2007-10-21
Oracle
【Spark篇】---Spark調優之程式碼調優，資料本地化調優，記憶體調優，SparkShuffle調優，Executor的堆外記憶體調優
2018-03-01
Spark記憶體
oracle效能調優:管理oracle日誌之調整線上日誌檔案
2010-03-08
Oracle
oracle筆記整理13——效能調優之SQL優化
2016-01-16
Oracle筆記SQL優化

Oracle 星型模式的調優

相關文章