|
| 学习 内容
|
| 1 | 为什么要引入索引 引入索引的目的是为了加快查询的速度。 |
| 2 | 索引对查询语句的影响 一个索引一旦被建立就由oracle系统自动维护，而且由oracle系统决定什么时候使用索引，读者不用在查询语句中指定使用哪个索引，其实使用查询语句的对象与没创建索引时几乎完全一样，只是查询的速度快多了。 |
| 3 | 索引对DML语句的影响 虽然oracle是一个独立于表的对象，但是当一个表被删除时所有基于该表的索引都会被自动的删除掉。 |
| 4 | 索引的逻辑分类 索引的逻辑分类是从应用的角度分类的： 逻辑分类如下： 单列索引：基于一列的索引，如：在emp表中的ename列上的索引 多列索引：也叫组合索引，是基于多列的索引，如：在emp表中的job和sal两列上所创建的索引。组合索引的列不一定与表中的顺序相同，这些列也没必要相邻。 唯一索引：保证表中任何数据行的索引列的值都不相同。 非唯一索引：表中不同数据行的索引列的值可以相同。 基于函数的索引：利用表中的一列或者多列使用函数或或者表达式所创建的索引。基于函数的索引可以是B树索引也可以是位图索引。 |
| 5 | 索引的物理分类 分区或非分区索引：当在一个大型或超大型表上创建索引时，可以像分区表一样，将该索引划分为若干个分区，每个索引分区为一个单独的段。 这样一个索引可以分布在不同的表空间上，但是每个索引分区（段）只能存放在一个表空间上。引入索引分区的目的也是为了减少输入输出的竞争。 B树索引： 位图索引： |
| 6 | B-树索引的结构： B树索引的结构：根-------分枝-------叶子 在叶子节点存有指向表中数据行的索引项。叶子节点被双向链表链在一起以方便按索引关键字的升序或降序扫描。 |
| 7 | B-树索引的应用范围 |
| 8 | B-树索引和位图索引的主要区别 位图索引也是一种B树结构，但是位图索引的叶子节点存的不是ROWID而是每一个键值的位图。位图中的每一个位置对应着可能的ROWID，如果该位被置位就意味着ROWID所对应的行中包含键值。 位图索引所需要的存储空间要比B树索引小的多，oracle服务器在使用位图索引的时候将整个位图索引段装入内存中，实际上是将一个磁盘上的搜索过程变成了一个内存查找过程，从而大大的提高了系统的效率。 结论：B树索引更适合于联机事物处理（OLTP）系统，因为在联机事物处理系统中DML的操作频繁。 位图索引更加适合数据仓库系统（Data Warehouse）系统，因为在数据仓库系统中表一般比较大但是静态的并且查询较为复杂） |
| 9 | 创建一个索引应该考虑哪些问题 1、平衡查询和DML的需要。在易挥发（DML操作频繁的）表上尽量减少索引的数量。-------------因为索引虽然加快了查询的速度但是却降低了DML操作的速度。 2、将其放入单独的表空间，不要与表、临时段或者还原（回滚）段放在一个表空间。----------因为索引段会与这些段竞争输入输出（I/O）。 3、使用统一的EXTENT尺寸，数据块尺寸的5倍，或表空间的MINIMUM EXTENT的尺寸。--------因为这样做的目的是为了减少系统的转换时间。 4、对大索引可以使用NOLOGGING。---------------------------------------------------------------------------------因为这样做的目的是通过减少REDO操作来提高系统的效率。 |
| 10 | 如何创建索引(正常索引与位图索引） 1.首先从数据字典dba_indexes中获取scott用户的索引基于的表、所在的表空间、索引的类型和索引的状态等信息。 SQL> col index_name for a10; SQL> col table_name for a10; SQL> col tablespace_name for a15; SQL> col index_type for a10; SQL> set line 100 SQL> select index_name,TABLE_NAME, TABLESPACE_NAME, 2 INDEX_TYPE,UNIQUENESS,STATUS 3 from dba_indexes 4 where owner='SCOTT';
INDEX_NAME TABLE_NAME TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS ---------- ---------- --------------- ---------- --------- -------- PK_DEPT DEPT USERS NORMAL UNIQUE VALID PK_EMP EMP USERS NORMAL UNIQUE VALID
由以上的实验才看出数据中scott用户共有两个索引，他们都是正常索引。 2.然后从数据字典dba_ind_columns字典中获得scott用户的索引基于的表和列的信息。 SQL> col INDEX_OWNER for a15; SQL> col COLUMN_NAME for a15; SQL> col TABLE_OWNER for a15; SQL> select INDEX_OWNER ,COLUMN_NAME,TABLE_OWNER 2 from dba_ind_columns 3 where TABLE_OWNER='SCOTT';
INDEX_OWNER COLUMN_NAME TABLE_OWNER --------------- --------------- --------------- SCOTT DEPTNO SCOTT SCOTT EMPNO SCOTT 3.创建索引； SQL> create index scott.emp_ename_idx----------基于ename列创建一个正常索引 2 on scott.emp(ename) 3 pctfree 20----------在创建索引时每一个块中预留的空间） 4 storage(initial 100k next 100k 5 pctincrease 0 maxextents 100) 6 tablespace pioneer_indx;
Index created.
SQL> create bitmap index scott.emp_job_idx------------基于job列创建一个位图索引。 2 on scott.emp(job) 3 pctfree 20 4 storage(initial 100k next 100k 5 pctincrease 0 maxextents 100) 6 tablespace pioneer_indx; 4.查询所创建的索引是否创建成功；
SQL> COL INDEX_NAME FOR A20; SQL> select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME, 2 INDEX_TYPE,UNIQUENESS ,STATUS 3 from dba_indexes 4 where owner='SCOTT';
INDEX_NAME TABLE_NAME TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS -------------------- ---------- --------------- ---------- --------- -------- PK_DEPT DEPT USERS NORMAL UNIQUE VALID PK_EMP EMP USERS NORMAL UNIQUE VALID EMP_ENAME_IDX EMP PIONEER_INDX NORMAL NONUNIQUE VALID EMP_JOB_IDX EMP PIONEER_INDX BITMAP NONUNIQUE VALID
|
| 11 | 获取索引信息的常用数据字典有哪些 SQL> desc dba_indexes; SQL> desc dba_ind_columns;
|
| 12 | 为什么要重建索引 重建后的索引数据块将消除那些已经删除的索引记录，同时索引树也将重新变得平衡，因此效率也将随之提高； |
| 13 | 如何重建索引 SQL> alter index scott.emp_ename_idx rebuild 2 pctfree 40 3 storage(next 300k);
Index altered. 下面查看以下是否重建成功，是否按照命令设置创建的。 SQL> select INDEX_NAME ,PCT_FREE,PCT_INCREASE , INITIAL_EXTENT, NEXT_EXTENT 2 from dba_indexes 3 where owner='SCOTT';
INDEX_NAME PCT_FREE PCT_INCREASE INITIAL_EXTENT NEXT_EXTENT -------------------- ---------- ------------ -------------- ----------- PK_DEPT 10 65536 1048576 PK_EMP 10 65536 1048576 EMP_JOB_IDX 20 106496 106496 EMP_ENAME_IDX 40 106496 311296
|
| 14 | 维护索引的一些常用的方法 在大规模装入书画家之前，为了避免索引段的自动扩展，可以使用命令手动的分配磁盘空间，在手工分配磁盘空间之前先查看一下当前索引段的磁盘分配情况； SQL> select SEGMENT_NAME ,SEGMENT_TYPE,TABLESPACE_NAME ,EXTENTS from dba_segments 2 where owner='SCOTT' 3 and segment_type='INDEX';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS -------------------- ------------------ --------------- ---------- EMP_JOB_IDX INDEX PIONEER_INDX 2 EMP_ENAME_IDX INDEX PIONEER_INDX 2 PK_EMP INDEX USERS 1 PK_DEPT INDEX USERS 1
下面使用DML语句为scott用户下的emp_ename_idx索引手工增加一个extent的磁盘空间； SQL> alter index scott.emp_ename_idx 2 allocate extent;
Index altered.
接下来验证一吓scott用户下的emp_ename_idx索引手工增加一个extent的磁盘空间是增加成功。 SQL> select SEGMENT_NAME ,SEGMENT_TYPE,TABLESPACE_NAME ,EXTENTS from dba_segments 2 where owner='SCOTT' 3 and segment_type='INDEX';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS -------------------- ------------------ --------------- ---------- EMP_JOB_IDX INDEX PIONEER_INDX 2 EMP_ENAME_IDX INDEX PIONEER_INDX 3 PK_EMP INDEX USERS 1 PK_DEPT INDEX USERS 1
当然也可以用DML语句来回收emp_ename_idx索引段没用的磁盘空间。 SQL> alter index scott.emp_ename_idx deallocate unused;
Index altered.
SQL> select SEGMENT_NAME ,SEGMENT_TYPE,TABLESPACE_NAME ,EXTENTS from dba_segments 2 where owner='SCOTT' 3 and segment_type='INDEX';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS -------------------- ------------------ --------------- ---------- EMP_JOB_IDX INDEX PIONEER_INDX 2 EMP_ENAME_IDX INDEX PIONEER_INDX 2 PK_EMP INDEX USERS 1 PK_DEPT INDEX USERS 1 由此实验可以发现，回收 了没用的磁盘空间之后，原来的3又变成了2；
同样也可以用DML语句合并碎片磁盘空间； SQL> alter index scott.emp_ename_idx coalesce;
Index altered.
|
| 15 | 如何标识索引的使用情况 为了查看scott用户下的emp_ename_idx索引是否使用过的步骤如下： 1、首先以scott用户登录数据库系统； SQL> alter user scott account unlock identified by oracle;
User altered.
SQL> connect scott/oracle; Connected.
2、之后输入开启监督索引使用的DML语句； SQL> alter index emp_ename_idx monitoring usage;----------开启对索引 emp_ename_idx的监督
Index altered.
3、可以发一个SQL查询语句影响Oracle系统使用所建的索引 emp_ename_idx； SQL> select ename,job,sal 2 from scott.emp 3 where ename like 'C%';
ENAME JOB SAL ---------- --------- ---------- CLARK MANAGER 2450
4、下面就可以使用数据字典 v$object_usage来获取索引 emp_ename_idx的使用情况。 SQL> select * 2 from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING -------------------- ---------- --- --- ------------------- ------------------- EMP_ENAME_IDX EMP YES YES 05/16/2016 11:18:33 由上面的实验结果可以看出，索引emp_ename_idx基于emp表，对索引的使用状态的监督已经开启（MON列为YES),该索引已经使用过（USE列为YES),开始监督的时间为2016年5月16号11点18分33秒；
5、当已经清楚的知道了索引emp_ename_idx已经使用过了之后，可以使用DML语句关闭对索引的监督； SQL> alter index emp_ename_idx nomonitoring usage;---------关闭对索引的监督
Index altered.
SQL> select * 2 from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING -------------------- ---------- --- --- ------------------- ------------------- EMP_ENAME_IDX EMP NO YES 05/16/2016 11:18:33 05/16/2016 11:29:58----------（MON列为NO，表示对索引的监督已经关闭）
|
| 16 | 如何删除索引 由于大规模的维护索引对数据库系统的效率冲击很大，所以一般在大规模装入数据库之前先删除索引然后再重建它们； SQL> drop index scott.emp_ename_idx;
Index dropped.
SQL> drop index scott.emp_job_idx;
Index dropped.
然后使用SQL语句查询验证一下索引是否被删除； SQL> select INDEX_NAME ,TABLE_NAME,TABLESPACE_NAME, 2 INDEX_TYPE,UNIQUENESS ,STATUS 3 from dba_indexes 4 where owner='SCOTT';
INDEX_NAME TABLE_NAME TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS -------------------- ---------- --------------- ---------- --------- -------- PK_DEPT DEPT USERS NORMAL UNIQUE VALID PK_EMP EMP USERS NORMAL UNIQUE VALID 由以上的实验结果明显的出scott用户下的索引emp_ename_idx和emp_job_idx已经被删除；
|
问题
| 1 | 目前大概了解这一章节的内容，就是需要多加练习熟练掌握索引知识； |
总结：
通过整章的学习，我了解了索引的概念，熟悉了B树索引与位图索引的区别，也学会了创建正常索引与位图索引，以及删除索引，甚至更多的小命令；我觉得整章的核心还是怎么创建正常索引、位图索引，以及怎么验证是否创建成功，是否按照命令设置创建的，及最后怎么删除，怎么验证。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30606702/viewspace-2120513/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30606702/viewspace-2120513/
269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
