|
80端口悄悄提示：数据载入中…… |
|
【索引(index) 】
索引是为提高数据检索的性能而建立
索引可建立在一表的一列或多列上
【索引分类】
逻辑上
单列或组合索引
唯一非唯一索引
注意：组合索引最多可以为32个列，并且，所有列的大小不能超过块的1/3
物理上
分区或非分区
B 树
正常或反向键
位图
【DML 操作对索引的影响】
插入操作导致在适当的块中插入索引项
删除行只导致逻辑删除索引项，删除的行所占用的空间不能用于新项，直到删除块中的所有项
PCTFREE 对索引没有影响
【索引行格式】
每个索引行有唯一的表示
create unique index iname on tname (a,b,c)
create index iname on tname (a,b,c)
Label：存储列数和锁定信息
【索引分类】
逆向键值索引
Bitmap索引
B 树索引和位图索引
【建立索引的原则】
平衡查询和DML 需求
放在一个单独的表空间内（非 SYSTEM, RBS, TABLE）
使用统一的区大小(经验值： 五块的倍数或表空间MININUM EXTENT 大小的倍数）
考虑对大索引使用NOLOGGING
如果新的关键字值有可能位于当前范围内, 则设置高PCTFREE
【建立通常的B-Tree 索引】
CREATE [ UNIQUE ]INDEX [schema.] index
ON [schema.] table
(column [ ASC | DESC ] [ , column [ASC | DESC ] ] ...)
[TABLESPACE tablespace]
[ PCTFREE integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ storage-clause ]
[ LOGGING| NOLOGGING ]
[ NOSORT ]
【建立B-TREE的例子】
CREATE INDEX test_idx
ON test(a)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;
【索引和空值】
B*Tree索引不存储完全NULL(空)的条目，但是位图索引和聚族索引存储完全NULL(空)的条目。
oracle中除了group by会认为(NULL,NULL)=(NULL,NULL)，其他任何情况下的空值条目比较都视为不同的空值，即(NULL,NULL) <> (NULL,NULL)
为了保证真正的唯一性，每个唯一约束（unique index）应该至少有一个NOT NULL字段。
【一些子句说明】
INITRANS 指定每块中预先分配的事务项的数目，缺省值和最小值为2
MAXTRANS 限制分配给每块的事务项的数目，缺省值为255
【创建基于函数的索引】
SQL> CREATE INDEX TEST_BFI ON TEST(A + B);
索引已创建。
SQL> SELECT * FROM TEST WHERE A+B < 10;
未选定行
【分配释放索引空间】
SQL> ALTER INDEX test_bm_idx
2 ALLOCATE EXTENT (SIZE 200K
3 DATAFILE 'E:\ORACLE\ORA92\TEST\INDX01.DBF');
索引已更改。
SQL>
SQL> ALTER INDEX test_bm_idx
2 DEALLOCATE UNUSED;
索引已更改。
SQL>
注意：释放HWM之上的
【重建索引】
重建索引命令可用来:
将索引移到另一个表空间
改进空间利用率
将反向索引改为通常的 B-tree 索引，或者将B-tree 索引改为反向索引
ALTER INDEX test_bm_idx REBUILD TABLESPACE users;
【重建索引的特点】
使用现有索引作为数据源建立新索引
使用现有索引建立索引时无须排序从而使性能更佳
在建立新索引期间需要有足够的空间以容纳新旧索引
在建立新索引的过程中查询可继续使用现有索引
【重建索引的语法】
ALTER INDEX [schema.] index REBUILD
[TABLESPACE tablespace]
[ PCTFREE integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ storage-clause ]
[ LOGGING | NOLOGGING ]
[ REVERSE | NOREVERSE ]
【联机创建或者重建索引】
可以在最低限度的表锁定下完成重建索引，例如：
ALTER INDEX test_bm_idx REBUILD ONLINE;
或者
CREATE INDEX TEST_BT_IDX ON TEST (A,B) ONLINE;
不能在临时表中重建索引
不能重建整个分区索引，必须分别重建各分区
【考虑UNIQUE 和PRIMARY KEY约束】
手工控制UNIQUE 和PRIMARY KEY的相应索引。
【创建大索引】
分配一个较大的临时表空间或者创建一个新的大尺寸的临时表空间
用ALTER USER 语句让用户使用该临时表空
用CREATE INDEX 语句创建索引
用DROP TABLESPACE 语句删除该表空间，然后将用户的临时表空间还原成以前的临时表空间
【监视索引是否被使用】
开始监视：
ALTER INDEX index MONITORING USAGE
停止监视：
ALTER INDEX index NOMONITORING USAGE
查询视图V$OBJECT_USAGE 来查询正在被监视的索引
【验证索引】
ANALYZE INDEX TEST_BFI
VALIDATE STRUCTURE;
【删除索引】
在批量装载之前删除
删除不常需要的索引引
删除和重建无效（INVALID）索引
DROP INDEX test_bm_idx;
【查询当前用户的索引及索引列的信息】
SQL> COLUMN COLUMN_NAME FORMAT A15
SQL> COLUMN INDEX_NAME FORMAT A15
SQL> COLUMN TABLE_NAME FORMAT A15
SQL> SELECT c.COLUMN_NAME, COLUMN_POSITION,
2 i.INDEX_NAME, i.TABLE_NAME, i.TABLE_OWNER
3 FROM user_ind_columns c, user_indexes i
4 where c.INDEX_NAME=i.INDEX_NAME
5 /
COLUMN_NAME COLUMN_POSITION INDEX_NAME TABLE_NAME TABLE_OWNE
--------------- --------------- --------------- --------------- ----------
A1 1 AI A LUNAR
B1 1 BI B LUNAR
B2 2 BI B LUNAR
C1 1 CI C LUNAR
C2 2 CI C LUNAR
C 1 TEST_BFI TEST LUNAR
已选择6行。
SQL>
【查询当前用户的索引信息】
SQL> column INDEX_NAME format a30
SQL> column INDEX_TYPE format a10
SQL> column TABLE_NAME format a10
SQL> column TABLE_OWNER format a10
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_OWNER
2 from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME TABLE_OWNE
------------------------------ ---------- ---------- ----------
AI NORMAL A LUNAR
BI NORMAL B LUNAR
CI NORMAL C LUNAR
SYS_IL0000028348C00003$$ LOB JY_RENWU LUNAR
SYS_IL0000028348C00004$$ LOB JY_RENWU LUNAR
TEST_BM_IDX BITMAP TEST LUNAR
已选择6行。
SQL>
【检查索引及其有效性】
SQL> SELECT index_name, tablespace_name, index_type,
2 uniqueness, status
3 FROM dba_indexes
4 WHERE owner='SUMMIT';
INDEX_NAME TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS
------------ --------------- ---------- --------- ------
EMPLOYEE_LAST_.. INDX NORMAL NONUNIQUE VALID
ORDERS_ID_IDX INDX NORMAL UNIQUE VALID
ORDERS_REGION_.. INDX02 BITMAP NONUNIQUE VALID
3 rows selected.
【列出所有反向键索引的名称】
SQL> SELECT o.object_name
2 FROM dba_objects o
3 WHERE owner='SUMMIT'
4 AND o.object_id IN (SELECT i.obj#
5 FROM ind$ i
6 WHERE BITAND(i.property,4) = 4);
OBJECT_NAME
-----------------
ORDERS_ID_IDX
1 row selected
【列出某个用户所拥有的全部索引及相关信息】
SQL> SELECT index_name, table_owner, table_name, column_name
2 FROM dba_ind_columns
3 WHERE index_owner = 'SUMMIT'
4 ORDER BY index_name, column_position;
INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME
----------------- ------------- --------------- ----------
EMPLOYEE_LAST_NAME.. SUMMIT EMPLOYEE LAST_NAME
ORDERS_ID_IDX SUMMIT ORDERS ID
ORDERS_REGION_ID.. SUMMIT ORDERS REGION_ID
3 rows selected. |
|
