Oracle优化——星型联结转换（star_transformation_enabled）

最新推荐文章于 2025-04-09 09:25:57 发布

原创

最新推荐文章于 2025-04-09 09:25:57 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #优化 #性能优化

本文深入探讨了Oracle数据库中的星型联结转换技术，旨在提升SQL查询性能。结合位图联结索引的最佳实践，帮助读者掌握数据库性能调优的关键策略。

参见 Oracle性能优化求生指南。

另外请了解位图联结索引，此类问题的最佳实践。

这是一个星型联结的例子，sales为事实表，其它为维度表。Where条件全部都作用在维度表上面。
SH@ prod> select quantity_sold , amount_sold from sales s join products p using ( prod_id )
  2  join times using ( time_id ) join customers c using ( cust_id )
  3  where week_ending_day = '29-Nov-2008' 
  4  and prod_name = '1.44MB External 3.5'' Diskette' 
  5  and cust_year_of_birth = 1965 ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3891315047

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |     1 |    80 |    30   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                         |                  |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                        |                  |     1 |    80 |    30   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                       |                  |     4 |   284 |    26   (0)| 00:00:01 |       |       |
|   4 |     MERGE JOIN CARTESIAN              |                  |     1 |    46 |    21   (0)| 00:00:01 |       |       |
|*  5 |      TABLE ACCESS FULL                | TIMES            |     1 |    16 |    18   (0)| 00:00:01 |       |       |
|   6 |      BUFFER SORT                      |                  |     1 |    30 |     3   (0)| 00:00:01 |       |       |
|*  7 |       TABLE ACCESS FULL               | PRODUCTS         |     1 |    30 |     3   (0)| 00:00:01 |       |       |
|   8 |     TABLE ACCESS BY GLOBAL INDEX ROWID| SALES            |     9 |   225 |     5   (0)| 00:00:01 | ROWID | ROWID |
|*  9 |      INDEX RANGE SCAN                 | SALES_CONCAT_IDX |     9 |       |     2   (0)| 00:00:01 |       |       |
|* 10 |    INDEX UNIQUE SCAN                  | CUSTOMERS_PK     |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 11 |   TABLE ACCESS BY INDEX ROWID         | CUSTOMERS        |     1 |     9 |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("TIMES"."WEEK_ENDING_DAY"=TO_DATE(' 2008-11-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - filter("P"."PROD_NAME"='1.44MB External 3.5'' Diskette')
   9 - access("S"."PROD_ID"="P"."PROD_ID" AND "S"."TIME_ID"="TIMES"."TIME_ID")
  10 - access("S"."CUST_ID"="C"."CUST_ID")
  11 - filter("C"."CUST_YEAR_OF_BIRTH"=1965)


Statistics
-----------------------------------------------------