一条sql添加分区过滤条件后cost反而上升
在10.2.0.1.0版本上查询分区表上加了一个过滤条件后，cost反而增加了，原因是这是oracle的bug，下面来做个试验：
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> drop table test purge; 表已删除。 SQL> create table test(cl varchar2(8), r integer) 2 partition by list(cl) ( 3 partition big values('big'), 4 partition small values ('small'), 5 partition empty values (default) 6 ); 表已创建。 SQL> insert /*+ append */ into test(cl,r) select case when level between 1 and 4 then 'small' else 'big' end, dbms_random.value(1,9000) from dual connect by level<=10000; 已创建10000行。 SQL> commit; 提交完成。 SQL> create index i_tr on test(r); 索引已创建。 SQL> exec dbms_stats.gather_table_stats(user,'test', cascade=>true); PL/SQL 过程已成功完成。 SQL> select count(*) from test where cl='big'; COUNT(*) ---------- 9996 SQL> select count(*) from test where cl='small'; COUNT(*) ---------- 4 SQL> set autotrace traceonly SQL> select /*+index(test)*/ * from test where cl='small' and r=549; 执行计划 ---------------------------------------------------------- Plan hash value: 397514736 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 2359 (1)| 00:00:29 | | | |* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TEST | 1 | 10 | 2359 (1)| 00:00:29 | 2 | 2 | |* 2 | INDEX RANGE SCAN | I_TR | 2500 | | 7 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("CL"='small') 2 - access("R"=549) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 460 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>select /*+index(test)*/ * from test where r=549; 执行计划 ---------------------------------------------------------- Plan hash value: 885212093 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 14 | 3 (0)| 00:00:01 | | | | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TEST | 2 | 14 | 3 (0)| 00:00:01 | ROWID | ROWID | |* 2 | INDEX RANGE SCAN | I_TR | 2 | | 1 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------