--环境：windows下oracle9i 9.2.0.1.0
--建一张表有一个state状态字段，同时给这个字段上建一个索引，状态为1的记录有100000条，为2的只有1条。
--select * from t where state=1，最好的执行计划是走索引;
--select * from t where state=2, 最好的执行计划是走全表扫描
--当没有统计信息时，全部走索引
--当用analyze table t compute statistics;分析表后，全部走全表扫描
--当只有正确生成了直方图信息后：
BEGIN
dbms_stats.gather_table_stats(
ownname => 'scott',
tabname => 't',
estimate_percent => 100,
method_opt => 'FOR COLUMNS size 10 state'
);
END;
/
--执行计划才正确。
--查看直方图
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
FROM user_HISTOGRAMS
WHERE TABLE_NAME ='T' AND COLUMN_NAME='STATE'
ORDER BY ENDPOINT_NUMBER;
create table t(id number primary key,name varchar2(256),state number);
insert into t values(100001,'aaaaa',1);
commit;
begin
for i in 1..100000 loop
insert into t values
(i,'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345
678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234
56789012345678901234567890',2);
end loop;
end;
/
commit;
/
create index idx_t_state on t(state);
--没有统计信息和直方图信息时，都会走到索引
SQL> select count(*) from user_HISTOGRAMS where table_name='T';
COUNT(*)
----------
0
SQL> select * from t where state=2;
已选择100000行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (RANGE SCAN) OF 'IDX_T_STATE' (NON-UNIQUE)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
16541 consistent gets
1647 physical reads
0 redo size
1796196 bytes sent via SQL*Net to client
73713 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL> select * from t where state=1;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (RANGE SCAN) OF 'IDX_T_STATE' (NON-UNIQUE)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
486 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--分析表：analyze table t compute statistics;
--可以看出直方图信息并不正确,执行SQL时，总是走全表扫描，所以是错误的。
col endpoint_actual_value format a20
select endpoint_number,endpoint_value,endpoint_actual_value from user_HISTOGRAMS where
table_name='T' and column_name='STATE';
SQL> set autotrace off
SQL> col endpoint_actual_value format a20
SQL> select endpoint_number,endpoint_value,endpoint_actual_value from user_HISTOGRAMS where ta
me='T' and column_name='STATE';
ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
--------------- -------------- --------------------
0 1
1 2
SQL> set autotrace trace
SQL> select * from t where state=2;
已选择100000行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=312 Card=50001 Bytes
=11300226)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=312 Card=50001 Bytes=1130
0226)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9688 consistent gets
1293 physical reads
0 redo size
1796196 bytes sent via SQL*Net to client
73713 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL> select * from t where state=1;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=312 Card=50001 Bytes
=11300226)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=312 Card=50001 Bytes=1130
0226)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3236 consistent gets
1171 physical reads
0 redo size
486 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--生成正确的直方图信息后，执行计划就能正确执行了，
BEGIN
dbms_stats.gather_table_stats(
ownname => 'scott',
tabname => 't',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR COLUMNS size 10 state '
);
END;
/
SQL> BEGIN
2 dbms_stats.gather_table_stats(
3 wnname => 'scott',
4 tabname => 't',
5 estimate_percent => dbms_stats.auto_sample_size,
6 method_opt => 'FOR COLUMNS size 10 state '
7 );
8 END;
9 /
PL/SQL 过程已成功完成。
SQL> select * from t where state=1;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=227)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
227)
2 1 INDEX (RANGE SCAN) OF 'IDX_T_STATE' (NON-UNIQUE) (Cost=1
Card=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
486 bytes sent via SQL*Net to client
387 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 * from t where state=2;
已选择100100行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=312 Card=99972 Bytes
=22693644)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=312 Card=99972 Bytes=2269
3644)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9695 consistent gets
1055 physical reads
0 redo size
1798049 bytes sent via SQL*Net to client
73790 bytes received via SQL*Net from client
6675 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100100 rows processed
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15145533/viewspace-464430/ ，如需转载，请注明出处，否则将追究法律责任。
