索引体积比表体积大还是小

转载

mob64ca13f83523 2024-11-26 14:36:57

文章标签 索引体积比表体积大还是小数据库 sql SQL 字段 文章分类 数据仓库大数据

--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_nameBEGIN --设置词法分析器 
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,>,;<, =,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%';

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。