为什么select时用index比full scan慢？

显示全部楼层 · 2008-8-26 12:36:25

为什么select时用index比full scan慢？
SELECTCOUNT(*) AS COUNT FROM ACCOUNT TABLE57,
ACCOUNT_CUSTOMER TABLE56,
CUSTOMER TABLE58
WHERE
TABLE57.ACCOUNT_key = TABLE56.ACCOUNT_key AND
TABLE58.customer_key = TABLE56.customer_key AND
TABLE56.TARGET_TYPE = 'A' AND TABLE57.CUSTOMER_TYPE in ('00000003999', '00000004014');
customer表有主键在customer_key上，
account表有主键在account_key上，
运行特慢，执行计划用了INDEX FAST FULL SCAN 在customer.customer_key索引及account.account_key索引.
再我运行了
analyze table customer compute statistics;
analyze table account compute statistics;
之后，执行计划改成对account表进行full scan,
结果运行很快就出来了.
请教为什么select时用index比full scan慢了？

千问 · 2008-8-26 12:36:25

可能你ACCOUNT 和ACCOUNT_CUSTOMER 数据量也不是很大。
而且你开始的执行计划也是用的INDEX FAST FULL SCAN 。

千问 · 2008-8-26 12:36:25

晕,你要搞清楚index fast full scan其实也是很慢的,
当你在account表上执行full scan时,
大概在customer及accunt_customer上执行的是index unique scan吧,
full scan+index unique > index fast full scan+index fast full scan.

千问 · 2008-8-26 12:36:25

为什么select时用index比full scan慢？
没人说用index就一定比FTS快.当返回的数据量占总数据量到达一定比例的时候.
FTS比index快.你分析了表只有优化器选择了正确的执行计划

千问 · 2008-8-26 12:36:25

COUNT(*) 换成count(column_name)试试

千问 · 2008-8-26 12:36:25

把执行计划以及统计信息贴出来比较一下看看?

千问 · 2008-8-26 12:36:25

plan 1:
| 0 | SELECT STATEMENT
|
| 1 |80 |2379 |
| 1 |SORT AGGREGATE
|
| 1 |80 | |
| 2 | NESTED LOOPS
|
|90 |7200 |2379 |
|*3 |HASH JOIN
|
| 122 |8296 |2135 |
|*4 | TABLE ACCESS FULL | ACCOUNT | 122 |4880 |1777 |
|*5 | TABLE ACCESS FULL | ACCOUNT_CUSTOMER| 554K|14M| 355 |
|*6 |TABLE ACCESS BY INDEX ROWID| CUSTOMER
| 1 |12 | 2 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
|*7 | INDEX UNIQUE SCAN | PK_CUSTOMER | 2 | | 1 |
-----------------------------------------------------------------------------------
plan 2:
| 0 | SELECT STATEMENT |
| 1 |80 |2868 |
| 1 |SORT AGGREGATE|
| 1 |80 | |
|*2 | HASH JOIN
|
|90 |7200 |2868 |
|*3 |HASH JOIN |
| 122 |8296 |2135 |
|*4 | TABLE ACCESS FULL| ACCOUNT | 122 |4880 |1777 |
|*5 | TABLE ACCESS FULL| ACCOUNT_CUSTOMER| 554K|14M| 355 |
|*6 |TABLE ACCESS FULL | CUSTOMER
| 345K|4048K| 730 |

结果是plan2 运行比plan1快得多.

千问 · 2008-8-26 12:36:25

有方法disable Oracle 得优化器吗？

千问 · 2008-8-26 12:36:25

你这里第二个plan快是因为第二个plan走了hash join

千问 · 2008-8-26 12:36:25

alter session set optimizer_goal=first_rows;