学习笔记十二managing indexes

After completing this lesson, you should be able to do the following:

• List the different types of indexes and their uses

• Create various types of indexes

• Reorganize indexes

• Maintain indexes

• Monitor the usage of an index

• Obtain index information

[@more@]

--managing indexes
index---independent physical structure是一个独立的物理结构
---poniter(可以认为是一些指针）---相当于书的目录(book catalog)
---最终指向表数据point to table data--靠指向rowid指向data
---speed query/overload data update
---OLTP：less indexes
---OLAP: more indexes
如果系统面向于读的则索引可以多建几个。如果系统面向于写的则索引就要少建几个
---b-tree index分nonleaf and leaf两种
---classification of indexes
1.Logical:
A.Single column or concatenated
B.Unique or nonunique
C.Function-based
D.Domain
2.Physical:
1.Partitioned or nonpartitioned
2.B-tree: Normal or reverse key
3.Bitmap
/
---B_Tree index
适用于取值唯一性高的table
b-tress index 的leaf实际上是指针指向表的rowid
index 除了leaf以外的branch and root我们称之为nonleaf 是查找到leaf位置的path
index entry包括
A.index entry header which stores the number of columns and locking information

B.Key column length

which define the size of a column in the key followed by the value for the column (The number of such pairs is a maximum of the number of columns in the index.)
C.key column value which contains the key values
D.rowid
(Key column length,key column value总是成对出现
/
---bitmap index
bitmap index适用于取值唯一性比较底的table，例如人事table里的性别
sql>create index testindex1 on scott.emp(ename) tablespace indexes（create b_tree index）
sql>create bitmap index testbit1 on scott.emp(sex) tablespace indexes;
/
---comparing b_tree and bitmap indexes
1.B-tree
A.Suitable for high-cardinality columns（适用于取值唯一性高的table）
B.Updates on keys relatively inexpensive(数据更新时代价比较低)
C.Inefficient for queries using OR predicates（在查询中用OR的话效率非常低，即在Where条件后用or的话很难用到b-tree index）
D.Useful for OLTP
2. Bitmap
A.Suitable for low-cardinality columns
B.Updates to key columns very expensive
C.Efficient for queries using OR predicates
D.Useful for data warehousing
/
---creating normal b-tree indexes
index 建立时其不能像table一样指定pctused ,index建立时不允许使用pctused参数
sql>create index testindex2
2 on emp(ename)
3 tablespace indexes
4 pctfree 20
5 pctused 40
6 storage(initial 100k
7 next 100k)
error ora-02158 无效的create index选项
sql>del 5
sql>run
则OK
/
---Creating Indexes: Guidelines
1 .Balance query and DML needs.
2 .Place in separate tablespace.
3 .Use uniform extent sizes: Multiples of five blocks or MINIMUM EXTENT size for tablespace.
4 .Consider NOLOGGING for large indexes.
5 .INITRANS should generally be higher on indexes than on the corresponding tables. (Index的initrans是比表的initrans大)
/
---creating bitmap indexes
CREATE_BITMAP_AREA_SIZE(可以加速bitmap索引的建立) parameter
The CREATE_BITMAP_AREA_SIZE initialization parameter determines the amount of space that will be used for storing bitmap segments in memory. The default value is 8 MB. A larger value may lead to a faster index creation. If cardinality is very small, this value can be set to a small value. For example, if cardinality is only two, then the value can be in the order of kilobytes rather than megabytes. As a general rule, for a higher cardinality, more memory is needed for optimal performance.
/
---changing storage parameters for indexes
sql>alter index test4 storage(next 200k pctincrease20)
/
---allocating and deallocating index space
allocateing
alter index test3 allocate_extent (size 200k datafile 'pathindex01.dbf);
需要注意的是这个datafile必须是之前test3 index所在的tablespace
sql>alter index test3 allocate extent(size 100k datafile 'd:oracleindex01.dbf');
deallocating
sql> alter index test3 deallocate unused;
/
---rebuilding indexes
当我们的db里面有数据被delete 掉了其实这种删除是逻辑的删除，也就是说只是将索引标记为被删除，物理空间上没有删除，如果要收缩这些空间则要对index重建，重建分两种:online creat and online rebuild ,
use the alter index command to:
1. move an index to a different tablespace
sql>alter index test1 rebuild tablespace users;
2.inprove space utilization by removing deleted entries
3.change a reverse key index to a normal b-tree index and vice versa
但是b-tree and bitmap index之间不能转化
sql>alter index test3 rebuild reverse;
/
---online rebuild of indexes
1.rebuilding indexes can be done with minimal table locking
sql>alter index test1 rebuild online
2.some restriction still apply
/
---coalescing indexes
sql>alter index test1 coalesce;碎片合并
/
---checking index validity
sql>analyze index test2 validate structure;其实是在更新index的统计信息，即改变纪录index统计信息的index_stats table里的相应信息改变了
/
---dropping indexes
1.drop and re-create an index before bulk loads.
2.drop indexes that are infrequently needed and build them when necessary
3.drop and re-create invalid indexes
/
---identifying unused indexes
1.to start monitoring the usage of an index
sql>alter index test2 monitoring usage;
2.to stop monitoring the usage of an index
sql>alter index test2 nomonitoring usage;

Identifying Unused Indexes
Beginning with Oracle9i, statistics about the usage of an index can be gathered and displayed in V$OBJECT_USAGE. If the information gathered indicates that an index is never used, the index can be dropped. In addition, eliminating unused indexes reduces the overhead required of the Oracle server for DML, thus improving performance. Each time the MONITORING USAGE clause is specified, V$OBJECT_USAGE will be reset for the specified index. The previous information is cleared or reset, and a new start time is recorded.
V$OBJECT_USAGE columns
INDEX_NAME: The index name
TABLE_NAME: The corresponding table
MONITORING: Indicates whether monitoring is ON or OFF
USED: Indicates YES or NO whether index has been used during the monitoring time
START_MONITORING: Time monitoring began on index
END_MONITORING: Time monitoring stopped on index

/
---obtaining index information
1.dba_indexes
2.dba_ind_columns
3.dba_ind_expressions
4.v$object_usage
建立函数index
sql>create index test4 on emp(in_date-out_date);
sql>select * from emp where (in_date-out_date)>10;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/202861/viewspace-799601/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/202861/viewspace-799601/