索引示例

--0.CREATE TABLE
drop user zyj cascase;
CREATE USER ZYJ IDENTIFIED BY zyj;
GRANT DBA TO ZYJ;

--索引创建后搜集统计信息
begin
dbms_stats.gather_table_stats('zyj','objects',cascade=>true);
end;
/

begin
dbms_stats.gather_table_stats('zyj','PRODUCT_INFORMATION_PART',cascade=>true);
end;
/

drop table zyj.objects;
create table zyj.objects as SELECT * FROM dba_objects;
alter table zyj.objects add v_rownum number;

update zyj.objects t set t.v_rownum=rownum;
commit;

alter table zyj.objects add SEX VARCHAR2(20);
UPDATE ZYJ.OBJECTS T SET T.SEX=DECODE(MOD(T.V_ROWNUM,2),1,'FEMALE',0,'MALE');
commit;

alter table zyj.objects add a number;
alter table zyj.objects add b number;
update zyj.objects t set t.a=t.v_rownum;
update zyj.objects t set t.b=abs(t.v_rownum-75705-1);
commit;

SELECT t.a,t.b,t.* FROM zyj.objects t ORDER BY 1;

drop table zyj.PRODUCT_INFORMATION_PART;
create table zyj.PRODUCT_INFORMATION_PART(
PRODUCT_ID NUMBER(10),
PROD_NAME VARCHAR2(200))
partition by range(product_id)
(
partition part1 values less than(1000),
partition part2 values less than(2000)
);

insert into zyj.product_information_part SELECT t.object_id,t.object_name FROM zyj.objects t where t.object_id<=1999;
commit;

SELECT * FROM zyj.objects t;
SELECT * FROM zyj.product_information_part t;

--1.B*TREE INDEX
--1.1单个字段,若此字段有单个字段的索引和复合索引，则单字段索引优先级高
DROP INDEX zyj.idx_objects;
create index zyj.idx_objects on zyj.objects(object_type);

SELECT * FROM zyj.objects t where t.object_type='CLUSTER';

--组合索引 多个字段
DROP INDEX zyj.idxcom_objects;
create index zyj.idxcom_objects on zyj.objects(object_type,OBJECT_NAME);

SELECT * FROM zyj.objects t where t.object_type='CLUSTER';

--2.FUNCTION INDEX
DROP INDEX zyj.idxf_objects;
create index zyj.idxf_objects on zyj.objects(upper(object_type));

SELECT * FROM zyj.objects t where upper(t.object_type)='CLUSTER';
--3.REVERSE INDEX
DROP INDEX zyj.idx_v_rownum;
create index zyj.idx_v_rownum on zyj.objects(v_rownum) reverse compress;

SELECT t.v_rownum,t.* FROM zyj.objects t where t.v_rownum=5;

--4.DESC INDEX
drop index zyj.ind_general;
create index zyj.ind_general on zyj.objects(a,b);

select * from zyj.objects t where a between 1 and 100 order by a desc,b asc;

--
drop index zyj.ind_desc;
create index zyj.ind_desc on zyj.objects(a desc,b asc);

analyze index zyj.ind_desc compute statistics;

select * from zyj.objects t where a between 1 and 100 order by a desc,b asc;

--5.BITMAP INDEX
DROP INDEX zyj.idx_bit_sex;
create bitmap index zyj.idx_bit_sex on zyj.objects(SEX);

--若evolve执行计划到sql_plan_baselines
alter session set optimizer_capture_sql_plan_baselines=true;

SELECT TO_CHAR(T.sql_text),T.enabled,t.accepted,t.fixed,T.* FROM dba_sql_plan_baselines t WHERE TO_CHAR(T.sql_text) LIKE '%sex%';

var lc_rtn clob;
exec :lc_rtn:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_09350aa8200b0ad9',plan_name=>'SQL_PLAN_0kd8ap0h0q2qt80d0906e');
set long 99999999
print lc_rtn;

--修改baseline，禁用sql_plan
--SYS_SQL_09350aa8200b0ad9 SQL_PLAN_0kd8ap0h0q2qt80d0906e
DECLARE
v_text PLS_INTEGER;
BEGIN
v_text := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SYS_SQL_32bf452b82d4671d',
plan_name => 'SQL_PLAN_35gu55f1d8tsx1a37e93d',
attribute_name => 'ENABLED',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || v_text );
END;
/

--设置baseline优先级
DECLARE
v_text PLS_INTEGER;
BEGIN
v_text := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SYS_SQL_09350aa8200b0ad9',
plan_name => 'SQL_PLAN_0kd8ap0h0q2qt80d0906e',
attribute_name => 'FIXED',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || v_text );
END;
/

--SELECT * FROM zyj.objects t where t.sex='MALE';
SELECT /*+ index(t,idx_bit_sex) */ * FROM zyj.objects t where t.sex='MALE';

--6.organization index
drop table zyj.orgindex;
create table zyj.orgindex(id,name,constraint pk_orgindex primary key(id,name)) organization index
as SELECT t.object_id,t.object_name FROM dba_objects t where rownum<=1000;

SELECT * FROM zyj.orgindex t where t.id=22;

 

--7.FULL INDEX
--步骤一：授权，ctxsys登陆并对oratext用户授权：
alter user ctxsys identified by oracle account unlock;
create user oratext identified by oracle;

GRANT resource, connect, ctxapp TO oratext;
GRANT execute ON ctxsys.ctx_cls TO oratext;
GRANT execute ON ctxsys.ctx_ddl TO oratext;
GRANT execute ON ctxsys.ctx_doc TO oratext;
GRANT execute ON ctxsys.ctx_output TO oratext;
GRANT execute ON ctxsys.ctx_query TO oratext;
GRANT execute ON ctxsys.ctx_report TO oratext;
GRANT execute ON ctxsys.ctx_thes TO oratext;
GRANT execute ON ctxsys.ctx_ulexer TO oratext;

--步骤二：设置词法分析器，使用chinese_vgram_lexer作为分析器：
conn oratext/oracle@service_name

BEGIN --设置词法分析器
ctx_ddl.create_preference ('oratext_lexer', 'chinese_vgram_lexer');
END;
/

--可以通过下面的语句查看系统默认及设置的oracle text参数：
--可以看到我刚刚设置的语法分析器参数oratext_lexer，（默认的有一个MY_LEXER的语法分析器参数）。
SELECT pre_name, pre_object FROM ctx_preferences;

--步骤三：建立测试表，插入测试数据：
CREATE TABLE textdemo(
id number NOT NULL PRIMARY KEY,
book_author varchar2(20),--作者
publish_time date,--发布日期
title varchar2(400),--标题
book_abstract varchar2(2000),--摘要
path varchar2(200)--路径
);

INSERT INTO textdemo VALUES(1,'宫琦峻',to_date('2008-10-07','yyyy-mm-dd'),' 移动城堡','故事发生在19世纪末的欧洲，善良可爱的苏菲被恶毒的女巫施下魔咒，从18岁的女孩变成90岁的婆婆，孤单无助的她无意中走入镇外的移动城堡，据说它的主人哈尔以吸取女孩的灵魂为乐，但是事情并没有人们传说的那么可怕，性情古怪的哈尔居然收留了苏菲，两个人在四脚的移动城堡中开始了奇妙的共同生活，一段交织了爱与痛、乐与悲的爱情故事在战火中悄悄展开','E:\textsearch\moveingcastle.doc');

INSERT INTO textdemo VALUES(2,'莫贝克曼贝托夫',to_date('2008-10-07','yyyy-mm-dd'),' 子弹转弯','这部由俄罗斯导演提莫贝克曼贝托夫执导的影片自6 月末在北美上映以来，已经在全球取得了超过3亿美元的票房收入。在亚洲上映后也先后拿下日本、韩国等地的票房冠军宝座。虽然不少网友在此之前也相继通过各种渠道接触到本片，但相信影片凭着在大银幕上呈现出的超酷的视听效果，依然能够吸引大量影迷前往影院捧场。','E:\textsearch\catch.pdf');

INSERT INTO textdemo VALUES(3,'袁泉',to_date('2008-10-07','yyyy-mm-dd'),'主演吴彦祖和袁泉现身','电影《如梦》在上海同乐坊拍摄，主演吴彦祖和袁泉现身。由于是深夜拍摄，所以周围并没有过多的fans注意到，给了剧组一个很清净的拍摄环境，站在街头的袁泉低着头，在寒冷的夜里看上去还真有些像女鬼，令人毛骨悚然。','E:\textsearch\dream.txt');
commit;

--步骤四：在book_abstract字段建立索引使用刚刚设置的ORATEXT_LEXER ：chinese_vgram_lexer作为分析器。
CREATE INDEX demo_abstract ON textdemo(book_abstract) indextype IS ctxsys.context parameters('lexer ORATEXT_LEXER');

--之后如上所述多出很多dr$开头的表和索引，系统会创建四个相关的表：
DR$DEMO_ABSTRACT$I(分词后的TOKEN表)\
DR$DEMO_ABSTRACT$K\
DR$DEMO_ABSTRACT$N \
DR$DEMO_ABSTRACT$R

--下面的语句可以查看索引创建过程中是否发生了错误：
SELECT * FROM ctx_USER_index_errors
附：对于建立索引的类型（例如ctxsys.context），包括四种：context,ctxcat,ctxrule,ctxxpath。

CONTEXT用于对含有大量连续文本数据进行检索。支持word、html、xml、text等很多数据格式。支持范围（range）分区，支持并行创建索引（Parallel indexing）的索引类型。

支持类型：VARCHAR2, CLOB, BLOB, CHAR, BFILE, XMLType, and URIType.DML。操作后，需要CTX_DDL.SYNC_INDEX手工同步索引如果有查询包含多个词语，直接用空格隔开(如 oracle itpub)。

查询标识符CONTAINS
CTXCAT适用于混合查询语句（如查询条件包括产品id，价格，描述等）。适合于查询较小的具有一定结构的文本段。具有事务性。DML 操作后，索引会自动进行同步。

操作符：and,or,&gt,;<, =,between,in
查询标识符CATSEARCH
CTXRULE查询标识符MATCHES。
CTXXPATH（这两个索引没有去更多搜索相关内容）
一般来说我们建立CONTEXT类型的索引（CONTAINS来查询）。

--步骤五：查询测试
--查询或
SELECT score(20),t.* FROM oratext.textdemo t WHERE contains(book_abstract,'移动城堡 or 俄罗斯',20)>0;
SELECT score(20),t.* FROM oratext.textdemo t WHERE contains(book_abstract,'移动城堡 or 欧洲',20)>0;
--基本查询
SELECT score(20),t.* FROM oratext.textdemo t WHERE contains(book_abstract,'移动城堡',20)>0;
--查询包含多个词语and测试通过
SELECT score(20),t.* FROM oratext.textdemo t WHERE contains(book_abstract,'移动城堡 and 欧洲',20)>0;

--8.GLOBAL HASH INDEX | GLOBAL INDEX | LOCAL INDEX
--8.1.HASH GLOBAL INDEX
DROP index zyj.PROD_IDX;
create index zyj.PROD_IDX on zyj.PRODUCT_INFORMATION_PART(PRODUCT_ID)
global partition by hash(product_id)
partitions 4;

SELECT * FROM zyj.product_information_part t where t.product_id=999;

--8.2.或普通的全局索引
DROP index zyj.PROD_IDX;
create index zyj.PROD_IDX on zyj.PRODUCT_INFORMATION_PART(PRODUCT_ID);

SELECT * FROM zyj.product_information_part t where t.product_id=999;

--8.3.LOCAL INDEX
DROP index zyj.PROD_IDX;
DROP index zyj.PROD_IDX_local;
create index zyj.PROD_IDX_local on zyj.PRODUCT_INFORMATION_PART(PRODUCT_ID) local;

SELECT * FROM zyj.product_information_part t where t.product_id=999;

--8.4.或完整写法
DROP index zyj.PROD_IDX;
DROP index zyj.PROD_IDX_local;
DROP index zyj.PROD_LOCALALL_IDX;
CREATE INDEX zyj.PROD_LOCALALL_IDX ON zyj.PRODUCT_INFORMATION_PART(PRODUCT_ID)
LOGGING LOCAL (
PARTITION part1 LOGGING NOCOMPRESS,
PARTITION part2 LOGGING NOCOMPRESS
);

SELECT * FROM zyj.product_information_part t where t.product_id=999;

--9.UNIQUE INDEX
DROP index zyj.IDXU_OBJECTS;
CREATE UNIQUE INDEX ZYJ.IDXU_OBJECTS ON ZYJ.OBJECTS(OBJECT_ID);

SELECT * FROM ZYJ.OBJECTS T WHERE T.OBJECT_ID=46;

--10.非分区表的 HASH INDEX
DROP index zyj.IDXU_OBJECTS;
DROP index zyj.idxhash_OBJECTS;
create index zyj.idxhash_OBJECTS on zyj.OBJECTS(OBJECT_ID)
global partition by hash(OBJECT_ID)
partitions 4;

SELECT * FROM ZYJ.OBJECTS T WHERE T.OBJECT_ID=46;

--11.EXTENSIBLE INDEX
select *from ex.v_ttt where rpersionid='123' and rname = '李爱';
select *from ex.v_ttt where rpersionid like '123%';
select *from ex.v_ttt where rname='li';
select *from ex.v_ttt where rname like 'abc%';