参见 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"=