在Oracle世界里，索引主要有B树索引，位图索引和全文索引等，其中最常见于OLTP系统的是B树索引，位图索引和全文索引则多用于OLAP系统中；索引就类似于一本书的目录，若只想了解某个章节，则在不需要通读全书，可以通过索引迅速的找到需要的内容；若需要通读全书，恐怕没有人会看一页文章内容，再回头看下目录这样阅读吧，这2次I/O，必然浪费时间和资源；B树索引比较适合值分布比较均匀的场合，因而普遍用于OLTP系统中；位图索引则适用于取值范围相对较小，且更改频率低的场合，相比B树索引，位图索引将占用更少的存储空间；全文索引则类似分词系统，一般用在CLOB字段或者TEXT字段上，全文索引会占用很大的储存空间，有可能索引的大小要远大于基表本身;索引会随着基表的更改而被oracle自动维护！下面就来简要的谈谈B树索引！

一：下列几种情况将不使用索引

1：使用不等于操作符

SQL> create table t1 as select * from dba_source; Table created. SQL> create index ind_t1 on t1(name); Index created. SQL> set autot trace exp stat SQL> select * from t1 where name <> 'STANDARD'; 634915 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   565K|  1108M|  2948   (1)| 00:00:36 | |*  1 |  TABLE ACCESS FULL| T1   |   565K|  1108M|  2948   (1)| 00:00:36 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"<>'STANDARD') Note -----    - dynamic sampling used for this statement (level=2) Statistics ----------------------------------------------------------          10  recursive calls           0  db block gets       52377  consistent gets       10977  physical reads           0  redo size    52132322  bytes sent via SQL*Net to client      466016  bytes received via SQL*Net from client       42329  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)      634915  rows processed

2: 使用IS NULL或者IS NOT NULL

SQL> exec dbms_stats.gather_table_stats('HR','T1',cascade=>true); PL/SQL procedure successfully completed. SQL> select * from t1 where name is null; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |   100 |  2947   (1)| 00:00:36 | |*  1 |  TABLE ACCESS FULL| T1   |     1 |   100 |  2947   (1)| 00:00:36 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME" IS NULL) Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets       10709  consistent gets       10706  physical reads           0  redo size         517  bytes sent via SQL*Net to client         408  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed

3：不使用基于函数的索引条件下，使用函数

SQL> select * from t1 where lower(name)='standard'; 3966 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |  6389 |   623K|  2950   (1)| 00:00:36 | |*  1 |  TABLE ACCESS FULL| T1   |  6389 |   623K|  2950   (1)| 00:00:36 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(LOWER("NAME")='standard') Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets       10968  consistent gets       10706  physical reads           0  redo size      237461  bytes sent via SQL*Net to client        3323  bytes received via SQL*Net from client         266  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)        3966  rows processed

4：比较不匹配的数据类型

SQL> create table t2 (id varchar2(10),name varchar2(10)); Table created. SQL> begin   2  for i in 1..100000   3  loop   4  insert into t2 values (i,'t2');   5  end loop;   6  commit;   7  end;   8  / PL/SQL procedure successfully completed. SQL> create index ind_t2 on t2(id); Index created. SQL> exec dbms_stats.gather_table_stats('HR','T2',cascade=>true); PL/SQL procedure successfully completed. SQL> select * from t2 where id=100; Execution Plan ---------------------------------------------------------- Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |     9 |    69   (2)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T2   |     1 |     9 |    69   (2)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_NUMBER("ID")=100) Statistics ----------------------------------------------------------           1  recursive calls           1  db block gets         255  consistent gets           0  physical reads           0  redo size         478  bytes sent via SQL*Net to client         419  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed

二：使用B树索引

1:新建2张表，并分别对name字段创建索引，收集表统计信息；t2表的name字段为倾斜型，t1表则相对比较均匀；

1. SQL> grant select_catalog_role to sale;  
2. Grant succeeded.  
3.  
4. SQL> conn sale/123456  
5. Connected.  
6.  
7. SQL> create table t1 as select * from dba_source;  
8. Table created.  
9.  
10. SQL> create table t2 as select * from dba_source;  
11. Table created.  
12.  
13. SQL> update t2 set name='SALE';  
14. 292436 rows updated.  
15.  
16. SQL> commit;  
17. Commit complete.  
18.  
19. SQL> update t2 set name='T2' where rownum < 2;  
20. 1 row updated.  
21.  
22. SQL> commit;  
23. Commit complete.  
24.  
25. SQL> create index ind_t1 on t1(name);  
26. Index created.  
27.  
28. SQL> create index ind_t2 on t2(name);  
29. Index created.  
30.  
31. SQL> exec dbms_stats.gather_table_stats('sale','t1',cascade=>true);  
32. PL/SQL procedure successfully completed.  
33.  
34. SQL> exec dbms_stats.gather_table_stats('sale','t2',cascade=>true);  
35. PL/SQL procedure successfully completed. 

2：对t1表进行查询，可以看到，查询1使用索引ind_t1,查询2根据hint提示使用全表扫描，对比两次查询的CPU代价和一致性读，可以看出索引的优势！

SQL> set autot trace exp stat SQL> select * from t1 where name='STANDARD'; 3933 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 634656657 -------------------------------------------------------------------------------- ------ | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time      | -------------------------------------------------------------------------------- ------ |   0 | SELECT STATEMENT            |        |   476 | 55692 |    18   (0)| 00:0 0:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   476 | 55692 |    18   (0)| 00:0 0:01 | |*  2 |   INDEX RANGE SCAN          | IND_T1 |   476 |       |     4   (0)| 00:0 0:01 | -------------------------------------------------------------------------------- ------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("NAME"='STANDARD') Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets         575  consistent gets           0  physical reads           0  redo size      336844  bytes sent via SQL*Net to client        3351  bytes received via SQL*Net from client         264  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)        3933  rows processed SQL> select /*+full(t1)*/ * from t1 where name='STANDARD'; 3933 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   476 | 55692 |  1389   (1)| 00:00:17 | |*  1 |  TABLE ACCESS FULL| T1   |   476 | 55692 |  1389   (1)| 00:00:17 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"='STANDARD') Statistics ----------------------------------------------------------         802  recursive calls           0  db block gets        6580  consistent gets        6134  physical reads           0  redo size      250150  bytes sent via SQL*Net to client        3351  bytes received via SQL*Net from client         264  SQL*Net roundtrips to/from client          20  sorts (memory)           0  sorts (disk)        3933  rows processed

3：对t2表进行查询，可以看到，查询1使用索引ind_t2,查询2则使用全表扫描，因为name字段基于所以的值均为SALE，所以这个时候使用全表扫描反而更快，查询3使用hint提示使用ind_t2索引，将产生更大的开销，由此可见，B树索引适合于值分布比较均匀的场合,否则2次I/O将导致查询更慢！

SQL> set autot trace exp stat SQL> select * from t2 where name='T2'; Execution Plan ---------------------------------------------------------- Plan hash value: 591771271 -------------------------------------------------------------------------------- ------ | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time      | -------------------------------------------------------------------------------- ------ |   0 | SELECT STATEMENT            |        |     1 |   107 |     4   (0)| 00:0 0:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |   107 |     4   (0)| 00:0 0:01 | |*  2 |   INDEX RANGE SCAN          | IND_T2 |     1 |       |     3   (0)| 00:0 0:01 | -------------------------------------------------------------------------------- ------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("NAME"='T2') Statistics ----------------------------------------------------------         183  recursive calls           0  db block gets          28  consistent gets           4  physical reads           0  redo size         875  bytes sent via SQL*Net to client         469  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           5  sorts (memory)           0  sorts (disk)           1  rows processed SQL> select * from t2 where name='SALE'; 292435 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   291K|    29M|  1391   (2)| 00:00:17 | |*  1 |  TABLE ACCESS FULL| T2   |   291K|    29M|  1391   (2)| 00:00:17 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"='SALE') Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets       25293  consistent gets         605  physical reads           0  redo size    30733266  bytes sent via SQL*Net to client      214914  bytes received via SQL*Net from client       19497  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)      292435  rows processed SQL> select /*+index(t2 ind_t2)*/ * from t2 where name='SALE'; 292435 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 591771271 -------------------------------------------------------------------------------- ------ | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time      | -------------------------------------------------------------------------------- ------ |   0 | SELECT STATEMENT            |        |   291K|    29M|  6850   (1)| 00:0 1:23 | |   1 |  TABLE ACCESS BY INDEX ROWID| T2     |   291K|    29M|  6850   (1)| 00:0 1:23 | |*  2 |   INDEX RANGE SCAN          | IND_T2 |   292K|       |   662   (2)| 00:0 0:08 | -------------------------------------------------------------------------------- ------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("NAME"='SALE') Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets       45391  consistent gets        2454  physical reads           0  redo size    36955099  bytes sent via SQL*Net to client      214914  bytes received via SQL*Net from client       19497  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)      292435  rows processed

三：表和索引的压缩,在大数据量条件下，除了使用分区技术来裁剪I/O外，压缩也是个不错的选择，但压缩比较适合于更新不频繁的场景

1：查看T1表和IND_T1索引的DDL创建语句和存储占用情况

1. SQL> show user;  
2. USER is "SYS" 
3. SQL> set long 5000  
4. SQL> set heading off 
5. SQL> select dbms_metadata.get_ddl('TABLE', 'T1', 'SALE') from dual;  
6. CREATE TABLE "SALE"."T1" 
7.    (    "OWNER" VARCHAR2(30),  
8.         "NAME" VARCHAR2(30),  
9.         "TYPE" VARCHAR2(12),  
10.         "LINE" NUMBER,  
11.         "TEXT" VARCHAR2(4000)  
12.    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMP  
13. RESS LOGGING  
14.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS  
15.  1 MAXEXTENTS 2147483645  
16.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_PO  
17. OL DEFAULT)  
18. TABLESPACE "USERS" 
19.  
20. SQL> select dbms_metadata.get_ddl('INDEX', 'IND_T1', 'SALE') from dual;  
21. CREATE INDEX "SALE"."IND_T1" ON "SALE"."T1" ("NAME")  
22.   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUT  
23. E STATISTICS 
24.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENT  
25. S 1 MAXEXTENTS 2147483645  
26.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P  
27. OOL DEFAULT)  
28.   TABLESPACE "USERS" 
29.  
30. SQL> desc show_space;  
31. PROCEDURE show_space  
32.  Argument Name                  Type                    In/Out Default?  
33.  ------------------------------ ----------------------- ------ --------  
34.  P_SEGNAME                      VARCHAR2                IN 
35.  P_OWNER                        VARCHAR2                IN     DEFAULT 
36.  P_TYPE                         VARCHAR2                IN     DEFAULT 
37.  P_PARTITION                    VARCHAR2                IN     DEFAULT 
38.  
39. SQL> set serveroutput on 
40. SQL> exec show_space('T1','SALE','table');  
41. Free Blocks.............................  
42. Total Blocks............................6400  
43. Total Bytes.............................52428800  
44. Total MBytes............................50  
45. Unused Blocks...........................118  
46. Unused Bytes............................966656  
47. Last Used Ext FileId....................4  
48. Last Used Ext BlockId...................6665  
49. Last Used Block.........................10  
50. PL/SQL procedure successfully completed.  
51.  
52. SQL> exec show_space('IND_T1','SALE','index');  
53. Free Blocks.............................  
54. Total Blocks............................1152  
55. Total Bytes.............................9437184  
56. Total MBytes............................9  
57. Unused Blocks...........................114  
58. Unused Bytes............................933888  
59. Last Used Ext FileId....................4  
60. Last Used Ext BlockId...................14217  
61. Last Used Block.........................14  
62. PL/SQL procedure successfully completed. 

2：新建表t3和索引ind_t3，带压缩属性,对比表t1所占的存储，表为50M:37M，索引为9M:4M

1. SQL> create table t3 compress as select * from t1;  
2. Table created.  
3.  
4. SQL> select table_name,compression from user_tables where table_name like 'T%';  
5.  
6. TABLE_NAME                     COMPRESS  
7. ------------------------------ --------  
8. T3                             ENABLED  
9. T2                             DISABLED  
10. T1                             DISABLED  
11.  
12. SQL> exec show_space('T3','SALE','table');  
13. Free Blocks.............................  
14. Total Blocks............................4736  
15. Total Bytes.............................38797312  
16. Total MBytes............................37  
17. Unused Blocks...........................70  
18. Unused Bytes............................573440  
19. Last Used Ext FileId....................4  
20. Last Used Ext BlockId...................19849  
21. Last Used Block.........................58  
22. PL/SQL procedure successfully completed.  
23.  
24. SQL> create index ind_t3 on t3(name) compress;  
25. Index created.  
26.  
27. SQL> select table_name,compression from user_indexes where table_name like 'T%';  
28.  
29. TABLE_NAME                     COMPRESS  
30. ------------------------------ --------  
31. T3                             ENABLED  
32. T2                             DISABLED  
33. T1                             DISABLED  
34.  
35. SQL> exec show_space('IND_T3','SALE','index');  
36. Free Blocks.............................  
37. Total Blocks............................512  
38. Total Bytes.............................4194304  
39. Total MBytes............................4  
40. Unused Blocks...........................32  
41. Unused Bytes............................262144  
42. Last Used Ext FileId....................4  
43. Last Used Ext BlockId...................20361  
44. Last Used Block.........................96  
45. PL/SQL procedure successfully completed. 

3：分区表的本地索引压缩，需要先在对象级别上启用压缩

1. SQL> select table_name from user_part_tables;  
2.  
3. TABLE_NAME  
4. ------------------------------  
5. SALE_DATA  
6. EMP  
7.  
8. SQL> select index_name from user_part_indexes where table_name in (select table_name from user_part_tables);  
9.  
10. INDEX_NAME  
11. ------------------------------  
12. IND_SALE_DATA_DATE  
13.  
14. SQL> select partition_name,tablespace_name,compression from user_ind_partitions where   
15.  
16. index_name='IND_SALE_DATA_DATE';  
17.  
18. PARTITION_NAME                 TABLESPACE_NAME                COMPRESS  
19. ------------------------------ ------------------------------ --------  
20. SALES_01                       TBS_SALE01                     DISABLED  
21. SALES_02                       TBS_SALE02                     DISABLED  
22. SALES_03                       TBS_SALE03                     DISABLED  
23. SALES_04                       TBS_SALE04                     DISABLED  
24. SALES_05                       TBS_SALE05                     DISABLED  
25. SALES_06                       TBS_SALE06                     DISABLED  
26. SALES_07                       TBS_SALE07                     DISABLED  
27. SALES_08                       TBS_SALE08                     DISABLED  
28. SALES_09                       TBS_SALE09                     DISABLED  
29. SALES_10                       TBS_SALE10                     DISABLED  
30. SALES_11                       TBS_SALE11                     DISABLED  
31.  
32. PARTITION_NAME                 TABLESPACE_NAME                COMPRESS  
33. ------------------------------ ------------------------------ --------  
34. SALES_12                       TBS_SALE12                     DISABLED  
35.  
36. SQL> alter index IND_SALE_DATA_DATE modify partition sales_01 compress;  
37. alter index IND_SALE_DATA_DATE modify partition sales_01 compress  
38. *  
39. ERROR at line 1:  
40. ORA-28659: COMPRESS must be specified at object level first 
41.  
42. SQL> drop index IND_SALE_DATA_DATE;  
43. Index dropped.  
44.  
45. SQL> create index ind_sale_data_date on sale_data(sale_id) local compress;  
46. Index created.  
47.  
48. SQL>  select partition_name,tablespace_name,compression from user_ind_partitions where   
49.  
50. index_name='IND_SALE_DATA_DATE';  
51.  
52. PARTITION_NAME                 TABLESPACE_NAME                COMPRESS  
53. ------------------------------ ------------------------------ --------  
54. SALES_01                       TBS_SALE01                     ENABLED  
55. SALES_02                       TBS_SALE02                     ENABLED  
56. SALES_03                       TBS_SALE03                     ENABLED  
57. SALES_04                       TBS_SALE04                     ENABLED  
58. SALES_05                       TBS_SALE05                     ENABLED  
59. SALES_06                       TBS_SALE06                     ENABLED  
60. SALES_07                       TBS_SALE07                     ENABLED  
61. SALES_08                       TBS_SALE08                     ENABLED  
62. SALES_09                       TBS_SALE09                     ENABLED  
63. SALES_10                       TBS_SALE10                     ENABLED  
64. SALES_11                       TBS_SALE11                     ENABLED  
65.  
66. PARTITION_NAME                 TABLESPACE_NAME                COMPRESS  
67. ------------------------------ ------------------------------ --------  
68. SALES_12                       TBS_SALE12                     ENABLED  
69.  
70. SQL> alter index ind_sale_data_date modify partition sales_01 nocompress;  
71. Index altered.  
72.  
73. SQL> alter index ind_sale_data_date rebuild partition sales_02 nocompress;  
74. Index altered.  
75.  
76. SQL>  select partition_name,tablespace_name,compression from user_ind_partitions where   
77.  
78. index_name='IND_SALE_DATA_DATE';  
79.  
80. PARTITION_NAME                 TABLESPACE_NAME                COMPRESS  
81. ------------------------------ ------------------------------ --------  
82. SALES_01                       TBS_SALE01                     DISABLED  
83. SALES_02                       TBS_SALE02                     DISABLED  
84. SALES_03                       TBS_SALE03                     ENABLED  
85. SALES_04                       TBS_SALE04                     ENABLED  
86. SALES_05                       TBS_SALE05                     ENABLED  
87. SALES_06                       TBS_SALE06                     ENABLED  
88. SALES_07                       TBS_SALE07                     ENABLED  
89. SALES_08                       TBS_SALE08                     ENABLED  
90. SALES_09                       TBS_SALE09                     ENABLED  
91. SALES_10                       TBS_SALE10                     ENABLED  
92. SALES_11                       TBS_SALE11                     ENABLED  
93.  
94. PARTITION_NAME                 TABLESPACE_NAME                COMPRESS  
95. ------------------------------ ------------------------------ --------  
96. SALES_12                       TBS_SALE12                     ENABLED 

 四：对象分析与执行计划,oracle 10g以后关于sql语句的执行，默认都是使用cbo，即基于代价的优化器，而不是基于规则的，这个代价则是有对象分析而来，也就是我们平常说的统计信息，下面来看下当统计信息未及时更新的情况下，sql执行计划的选择！

1：创建基表和索引，可以看到，当没有统计信息的时候，oracle将采取动态采样方式，也可以获得正确的执行计划 

SQL> create table t4 as select 100 object_id,object_name from dba_objects; Table created. SQL> update t4 set object_id=1 where rownum=1; 1 row updated. SQL> commit; Commit complete SQL> create index ind_t4 on t4(object_id); Index created. SQL> set autot trace exp stat SQL> select * from t4 where object_id=100; 50414 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2560505625 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      | 54273 |  4187K|    56   (4)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T4   | 54273 |  4187K|    56   (4)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("OBJECT_ID"=100) Note -----    - dynamic sampling used for this statement Statistics ----------------------------------------------------------          52  recursive calls           0  db block gets        3658  consistent gets           5  physical reads           0  redo size     1691795  bytes sent via SQL*Net to client       37429  bytes received via SQL*Net from client        3362  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)       50414  rows processed SQL> select * from t4 where object_id=1; Execution Plan ---------------------------------------------------------- Plan hash value: 4096627024 -------------------------------------------------------------------------------- ------ | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time      | -------------------------------------------------------------------------------- ------ |   0 | SELECT STATEMENT            |        |     1 |    79 |     2   (0)| 00:0 0:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T4     |     1 |    79 |     2   (0)| 00:0 0:01 | |*  2 |   INDEX RANGE SCAN          | IND_T4 |     1 |       |     1   (0)| 00:0 0:01 | -------------------------------------------------------------------------------- ------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_ID"=1) Note -----    - dynamic sampling used for this statement Statistics ----------------------------------------------------------           7  recursive calls           0  db block gets          76  consistent gets           0  physical reads           0  redo size         595  bytes sent via SQL*Net to client         469  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed

2：删除表中所有的数据后，收集统计信息，然后再插入原先一样的数据

1. SQL> delete from t4;  
2. 50415 rows deleted.  
3.  
4. SQL> exec dbms_stats.gather_table_stats('sale','t4',cascade=>true);  
5. PL/SQL procedure successfully completed.  
6.  
7. SQL> insert into t4 select 100 object_id,object_name from dba_objects;  
8. 50416 rows created.  
9.  
10. SQL> update t4 set object_id=1 where rownum=1;  
11. 1 row updated.  
12.  
13. SQL> commit 
14. Commit complete.  
15.  
16. SQL> select object_id,count(*) from t4 group by object_id;  
17.  
18.  OBJECT_ID   COUNT(*)  
19. ---------- ----------  
20.          1          1  
21.        100      50415 

3：这个时候，统计信息未及时更新，认为下面的查询语句选择走索引会更优，则会产生错误的执行计划，导致查询语句缓慢！在生产环境，大数据量情况下尤为明显！

SQL> set autot trace exp stat SQL> select * from t4 where object_id=100; 50415 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4096627024 -------------------------------------------------------------------------------- ------ | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time      | -------------------------------------------------------------------------------- ------ |   0 | SELECT STATEMENT            |        |     1 |    79 |     1   (0)| 00:0 0:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T4     |     1 |    79 |     1   (0)| 00:0 0:01 | |*  2 |   INDEX RANGE SCAN          | IND_T4 |     1 |       |     1   (0)| 00:0 0:01 | -------------------------------------------------------------------------------- ------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_ID"=100) Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets        3581  consistent gets           0  physical reads           0  redo size     1692701  bytes sent via SQL*Net to client       37429  bytes received via SQL*Net from client        3362  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)       50415  rows processed