浅谈Oracle索引-阿里云开发者社区

开发者社区> 数据库> 正文

浅谈Oracle索引

简介:

在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. 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

本文转自斩月博客51CTO博客,原文链接http://blog.51cto.com/ylw6006/810229如需转载请自行联系原作者


ylw6006

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章