oracle创建索引

创建一个用户
set sqlprompt "_USER > "
create user jsx identified by 123; //创建用户jsx
grant all privileges to jsx; //给用户授权
conn jsx/123;
//创建books表
CREATE TABLE books(
bookid NUMBER(6) PRIMARY KEY,
booknum VARCHAR2(6),
bookname VARCHAR2(60),
author VARCHAR(50),
publish VARCHAR2(50),
bookprice NUMBER(8,2),
category CHAR(10),
booktime DATE DEFAULT SYSDATE
)
STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 20 MAXEXTENTS 15)
TABLESPACE users;
创建索引 在bookname上创建B-树索引
CREATE INDEX book_name_idx ON books(bookname) TABLESPACE USERS;
创建唯一索引
CREATE UNIQUE INDEX book_name_idx ON books(bookname);
创建位图索引 适合基数少 重复多
CREATE BITMAP INDEX order_pay_idx ON orders(payterms);
创建基于函数的索引  加快速度
CREATE INDEX author_lname_idx ON authors(UPPER(author_lname));
SELECT * FROM authors WHERE UPPER(author_lname) LIKE 'SM%';
创建反向键值索引  可以避免不平衡的索引
CREATE INDEX author_phn_idx ON authors(phone) REVERSE;


修改索引  
合并索引
ALTER INDEX index_name COALESCE [DEALLOCATE UNUSED];//DEALLOCATE UNUSED合并索引后释放空间
重构索引
ALTER INDEX book_name_idx REBUILD TABLESPACE BOOKSPUB;
重命名索引
ALTER INDEX book_name_idx RENAME TO bk_idx;


删除索引
DROP INDEX bk_idx;


索引的监视和查询
监视索引
ALTER INDEX booknum_idx MONITORING USAGE;
SELECT INDEX_NAME,used,start_monitoring from V$OBJECT_USAGE; //查询索引使用情况


SELECT * FROM books WHERE booknum='DB1002';
SELECT INDEX_NAME,used,start_monitoring from V$OBJECT_USAGE;


关闭监视
ALTER INDEX booknum_idx NOMONITORING USAGE;


查询索引信息
SELECT INDEX_NAME,INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME ='BOOKS'; //表名大写