在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; SQL> create index ind_t1 on t1(name); SQL> set autot trace exp stat Execution Plan -------------------------------------------------------------------------- Predicate Information (identified by operation id): 1 - filter("NAME"<>'STANDARD') Note Statistics |
2: 使用IS NULL或者IS NOT NULL
| SQL> exec dbms_stats.gather_table_stats('HR','T1',cascade=>true); SQL> select * from t1 where name is null; Execution Plan -------------------------------------------------------------------------- Predicate Information (identified by operation id): 1 - filter("NAME" IS NULL) Statistics |
3：不使用基于函数的索引条件下，使用函数
| SQL> select * from t1 where lower(name)='standard'; Execution Plan -------------------------------------------------------------------------- Predicate Information (identified by operation id): 1 - filter(LOWER("NAME")='standard') Statistics |
4：比较不匹配的数据类型
| SQL> create table t2 (id varchar2(10),name varchar2(10)); SQL> begin SQL> create index ind_t2 on t2(id); SQL> exec dbms_stats.gather_table_stats('HR','T2',cascade=>true); SQL> select * from t2 where id=100; Execution Plan -------------------------------------------------------------------------- Predicate Information (identified by operation id): 1 - filter(TO_NUMBER("ID")=100) Statistics |
二：使用B树索引
1:新建2张表，并分别对name字段创建索引，收集表统计信息；t2表的name字段为倾斜型，t1表则相对比较均匀；
- SQL> grant select_catalog_role to sale;
- Grant succeeded.
- SQL> conn sale/123456
- Connected.
- SQL> create table t1 as select * from dba_source;
- Table created.
- SQL> create table t2 as select * from dba_source;
- Table created.
- SQL> update t2 set name='SALE';
- 292436 rows updated.
- SQL> commit;
- Commit complete.
- SQL> update t2 set name='T2' where rownum < 2;
- 1 row updated.
- SQL> commit;
- Commit complete.
- SQL> create index ind_t1 on t1(name);
- Index created.
- SQL> create index ind_t2 on t2(name);
- Index created.
- SQL> exec dbms_stats.gather_table_stats('sale','t1',cascade=>true);
- PL/SQL procedure successfully completed.
- SQL> exec dbms_stats.gather_table_stats('sale','t2',cascade=>true);
- PL/SQL procedure successfully completed.
2：对t1表进行查询，可以看到，查询1使用索引ind_t1,查询2根据hint提示使用全表扫描，对比两次查询的CPU代价和一致性读，可以看出索引的优势！
| SQL> set autot trace exp stat Execution Plan -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 476 | 55692 | 18 (0)| 00:0 | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 476 | 55692 | 18 (0)| 00:0 |* 2 | INDEX RANGE SCAN | IND_T1 | 476 | | 4 (0)| 00:0 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - access("NAME"='STANDARD') Statistics SQL> select /*+full(t1)*/ * from t1 where name='STANDARD'; Execution Plan -------------------------------------------------------------------------- Predicate Information (identified by operation id): 1 - filter("NAME"='STANDARD') Statistics |
3：对t2表进行查询，可以看到，查询1使用索引ind_t2,查询2则使用全表扫描，因为name字段基于所以的值均为SALE，所以这个时候使用全表扫描反而更快，查询3使用hint提示使用ind_t2索引，将产生更大的开销，由此可见，B树索引适合于值分布比较均匀的场合,否则2次I/O将导致查询更慢！
| SQL> set autot trace exp stat Execution Plan -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 107 | 4 (0)| 00:0 | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 107 | 4 (0)| 00:0 |* 2 | INDEX RANGE SCAN | IND_T2 | 1 | | 3 (0)| 00:0 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - access("NAME"='T2') Statistics SQL> select * from t2 where name='SALE'; Execution Plan -------------------------------------------------------------------------- Predicate Information (identified by operation id): 1 - filter("NAME"='SALE') Statistics SQL> select /*+index(t2 ind_t2)*/ * from t2 where name='SALE'; Execution Plan -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 291K| 29M| 6850 (1)| 00:0 | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 291K| 29M| 6850 (1)| 00:0 |* 2 | INDEX RANGE SCAN | IND_T2 | 292K| | 662 (2)| 00:0 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - access("NAME"='SALE') Statistics |
三：表和索引的压缩,在大数据量条件下，除了使用分区技术来裁剪I/O外，压缩也是个不错的选择，但压缩比较适合于更新不频繁的场景
1：查看T1表和IND_T1索引的DDL创建语句和存储占用情况
- SQL> show user;
- USER is "SYS"
- SQL> set long 5000
- SQL> set heading off
- SQL> select dbms_metadata.get_ddl('TABLE', 'T1', 'SALE') from dual;
- CREATE TABLE "SALE"."T1"
- ( "OWNER" VARCHAR2(30),
- "NAME" VARCHAR2(30),
- "TYPE" VARCHAR2(12),
- "LINE" NUMBER,
- "TEXT" VARCHAR2(4000)
- ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMP
- RESS LOGGING
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
- 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_PO
- OL DEFAULT)
- TABLESPACE "USERS"
- SQL> select dbms_metadata.get_ddl('INDEX', 'IND_T1', 'SALE') from dual;
- CREATE INDEX "SALE"."IND_T1" ON "SALE"."T1" ("NAME")
- PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUT
- E STATISTICS
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENT
- S 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P
- OOL DEFAULT)
- TABLESPACE "USERS"
- SQL> desc show_space;
- PROCEDURE show_space
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- P_SEGNAME VARCHAR2 IN
- P_OWNER VARCHAR2 IN DEFAULT
- P_TYPE VARCHAR2 IN DEFAULT
- P_PARTITION VARCHAR2 IN DEFAULT
- SQL> set serveroutput on
- SQL> exec show_space('T1','SALE','table');
- Free Blocks.............................
- Total Blocks............................6400
- Total Bytes.............................52428800
- Total MBytes............................50
- Unused Blocks...........................118
- Unused Bytes............................966656
- Last Used Ext FileId....................4
- Last Used Ext BlockId...................6665
- Last Used Block.........................10
- PL/SQL procedure successfully completed.
- SQL> exec show_space('IND_T1','SALE','index');
- Free Blocks.............................
- Total Blocks............................1152
- Total Bytes.............................9437184
- Total MBytes............................9
- Unused Blocks...........................114
- Unused Bytes............................933888
- Last Used Ext FileId....................4
- Last Used Ext BlockId...................14217
- Last Used Block.........................14
- PL/SQL procedure successfully completed.
2：新建表t3和索引ind_t3，带压缩属性,对比表t1所占的存储，表为50M:37M，索引为9M:4M
- SQL> create table t3 compress as select * from t1;
- Table created.
- SQL> select table_name,compression from user_tables where table_name like 'T%';
- TABLE_NAME COMPRESS
- ------------------------------ --------
- T3 ENABLED
- T2 DISABLED
- T1 DISABLED
- SQL> exec show_space('T3','SALE','table');
- Free Blocks.............................
- Total Blocks............................4736
- Total Bytes.............................38797312
- Total MBytes............................37
- Unused Blocks...........................70
- Unused Bytes............................573440
- Last Used Ext FileId....................4
- Last Used Ext BlockId...................19849
- Last Used Block.........................58
- PL/SQL procedure successfully completed.
- SQL> create index ind_t3 on t3(name) compress;
- Index created.
- SQL> select table_name,compression from user_indexes where table_name like 'T%';
- TABLE_NAME COMPRESS
- ------------------------------ --------
- T3 ENABLED
- T2 DISABLED
- T1 DISABLED
- SQL> exec show_space('IND_T3','SALE','index');
- Free Blocks.............................
- Total Blocks............................512
- Total Bytes.............................4194304
- Total MBytes............................4
- Unused Blocks...........................32
- Unused Bytes............................262144
- Last Used Ext FileId....................4
- Last Used Ext BlockId...................20361
- Last Used Block.........................96
- PL/SQL procedure successfully completed.
3：分区表的本地索引压缩，需要先在对象级别上启用压缩
- SQL> select table_name from user_part_tables;
- TABLE_NAME
- ------------------------------
- SALE_DATA
- EMP
- SQL> select index_name from user_part_indexes where table_name in (select table_name from user_part_tables);
- INDEX_NAME
- ------------------------------
- IND_SALE_DATA_DATE
- SQL> select partition_name,tablespace_name,compression from user_ind_partitions where
- index_name='IND_SALE_DATA_DATE';
- PARTITION_NAME TABLESPACE_NAME COMPRESS
- ------------------------------ ------------------------------ --------
- SALES_01 TBS_SALE01 DISABLED
- SALES_02 TBS_SALE02 DISABLED
- SALES_03 TBS_SALE03 DISABLED
- SALES_04 TBS_SALE04 DISABLED
- SALES_05 TBS_SALE05 DISABLED
- SALES_06 TBS_SALE06 DISABLED
- SALES_07 TBS_SALE07 DISABLED
- SALES_08 TBS_SALE08 DISABLED
- SALES_09 TBS_SALE09 DISABLED
- SALES_10 TBS_SALE10 DISABLED
- SALES_11 TBS_SALE11 DISABLED
- PARTITION_NAME TABLESPACE_NAME COMPRESS
- ------------------------------ ------------------------------ --------
- SALES_12 TBS_SALE12 DISABLED
- SQL> alter index IND_SALE_DATA_DATE modify partition sales_01 compress;
- alter index IND_SALE_DATA_DATE modify partition sales_01 compress
- *
- ERROR at line 1:
- ORA-28659: COMPRESS must be specified at object level first
- SQL> drop index IND_SALE_DATA_DATE;
- Index dropped.
- SQL> create index ind_sale_data_date on sale_data(sale_id) local compress;
- Index created.
- SQL> select partition_name,tablespace_name,compression from user_ind_partitions where
- index_name='IND_SALE_DATA_DATE';
- PARTITION_NAME TABLESPACE_NAME COMPRESS
- ------------------------------ ------------------------------ --------
- SALES_01 TBS_SALE01 ENABLED
- SALES_02 TBS_SALE02 ENABLED
- SALES_03 TBS_SALE03 ENABLED
- SALES_04 TBS_SALE04 ENABLED
- SALES_05 TBS_SALE05 ENABLED
- SALES_06 TBS_SALE06 ENABLED
- SALES_07 TBS_SALE07 ENABLED
- SALES_08 TBS_SALE08 ENABLED
- SALES_09 TBS_SALE09 ENABLED
- SALES_10 TBS_SALE10 ENABLED
- SALES_11 TBS_SALE11 ENABLED
- PARTITION_NAME TABLESPACE_NAME COMPRESS
- ------------------------------ ------------------------------ --------
- SALES_12 TBS_SALE12 ENABLED
- SQL> alter index ind_sale_data_date modify partition sales_01 nocompress;
- Index altered.
- SQL> alter index ind_sale_data_date rebuild partition sales_02 nocompress;
- Index altered.
- SQL> select partition_name,tablespace_name,compression from user_ind_partitions where
- index_name='IND_SALE_DATA_DATE';
- PARTITION_NAME TABLESPACE_NAME COMPRESS
- ------------------------------ ------------------------------ --------
- SALES_01 TBS_SALE01 DISABLED
- SALES_02 TBS_SALE02 DISABLED
- SALES_03 TBS_SALE03 ENABLED
- SALES_04 TBS_SALE04 ENABLED
- SALES_05 TBS_SALE05 ENABLED
- SALES_06 TBS_SALE06 ENABLED
- SALES_07 TBS_SALE07 ENABLED
- SALES_08 TBS_SALE08 ENABLED
- SALES_09 TBS_SALE09 ENABLED
- SALES_10 TBS_SALE10 ENABLED
- SALES_11 TBS_SALE11 ENABLED
- PARTITION_NAME TABLESPACE_NAME COMPRESS
- ------------------------------ ------------------------------ --------
- 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; SQL> update t4 set object_id=1 where rownum=1; SQL> commit; SQL> create index ind_t4 on t4(object_id); SQL> set autot trace exp stat Execution Plan -------------------------------------------------------------------------- Predicate Information (identified by operation id): 1 - filter("OBJECT_ID"=100) Note Statistics SQL> select * from t4 where object_id=1; Execution Plan -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:0 | 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 79 | 2 (0)| 00:0 |* 2 | INDEX RANGE SCAN | IND_T4 | 1 | | 1 (0)| 00:0 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - access("OBJECT_ID"=1) Note Statistics |
2：删除表中所有的数据后，收集统计信息，然后再插入原先一样的数据
- SQL> delete from t4;
- 50415 rows deleted.
- SQL> exec dbms_stats.gather_table_stats('sale','t4',cascade=>true);
- PL/SQL procedure successfully completed.
- SQL> insert into t4 select 100 object_id,object_name from dba_objects;
- 50416 rows created.
- SQL> update t4 set object_id=1 where rownum=1;
- 1 row updated.
- SQL> commit
- Commit complete.
- SQL> select object_id,count(*) from t4 group by object_id;
- OBJECT_ID COUNT(*)
- ---------- ----------
- 1 1
- 100 50415
3：这个时候，统计信息未及时更新，认为下面的查询语句选择走索引会更优，则会产生错误的执行计划，导致查询语句缓慢！在生产环境，大数据量情况下尤为明显！
| SQL> set autot trace exp stat Execution Plan -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:0 | 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 79 | 1 (0)| 00:0 |* 2 | INDEX RANGE SCAN | IND_T4 | 1 | | 1 (0)| 00:0 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - access("OBJECT_ID"=100) Statistics 本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/810229如需转载请自行联系原作者 ylw6006 |
![[Oracle]索引](https://ucc.alicdn.com/pic/developer-ecology/3mfxe2r26qbaq_c50f50e14c2447959ba0c272c4967106.jpeg?x-oss-process=image/resize,h_160,m_lfit)