正文
in走索引oracle,oracle有索引不走索引的几种情况
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
请教:oracle中索引应用
一.索引介绍
1.1 索引的创建语法:
CREATE UNIUQE | BITMAP INDEX schema.index_name
ON schema.table_name
(column_name | expression ASC | DESC,
column_name | expression ASC | DESC,...)
TABLESPACE tablespace_name
STORAGE storage_settings
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESSnn
NOSORT | REVERSE
PARTITION | GLOBAL PARTITIONpartition_setting
相关说明
1) UNIQUE | BITMAP:指定UNIQUE为唯一值索引,BITMAP为位图索引,省略为B-Tree索引。
2)column_name | expression ASC | DESC:可以对多列进行联合索引,当为expression时即“基于函数的索引”
3)TABLESPACE:指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4)STORAGE:可进一步设置表空间的存储参数
5)LOGGING | NOLOGGING:是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
6)COMPUTE STATISTICS:创建新索引时收集统计信息
7)NOCOMPRESS | COMPRESSnn:是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8)NOSORT | REVERSE:NOSORT表示与表中相同的顺序创建索引,REVERSE表示相反顺序存储索引值
9)PARTITION | NOPARTITION:可以在分区表和未分区表上对创建的索引进行分区
1.2 索引特点:
第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
1.3 索引不足:
第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
1.4 应该建索引列的特点:
1)在经常需要搜索的列上,可以加快搜索的速度;
2)在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
3)在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
4)在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
5)在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
6)在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
1.5 不应该建索引列的特点:
第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
第三,对于那些定义为blob数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。
第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。
1.6 限制索引
限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题:
1.6.1 使用不等于操作符(、!=)
下面的查询即使在cust_rating列有一个索引,查询语句仍然执行一次全表扫描。
select cust_Id,cust_name from customers where cust_rating 'aa';
把上面的语句改成如下的查询语句,这样,在采用基于规则的优化器而不是基于代价的优化器(更智能)时,将会使用索引。
select cust_Id,cust_name from customers where cust_rating 'aa' or cust_rating 'aa';
特别注意:通过把不等于操作符改成OR条件,就可以使用索引,以避免全表扫描。
1.6.2 使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时,把需要索引的列设成 NOT NULL。如果被索引的列在某些行中存在NULL值,就不会使用这个索引(除非索引是一个位图索引,关于位图索引在稍后在详细讨论)。
1.6.3 使用函数
如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。 下面的查询不会使用索引(只要它不是基于函数的索引)
select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81';
把上面的语句改成下面的语句,这样就可以通过索引进行查找。
select empno,ename,deptno from emp where hiredate(to_date('01-MAY-81')+0.9999);
1.6.4 比较不匹配的数据类型
也是比较难于发现的性能问题之一。 注意下面查询的例子,account_number是一个VARCHAR2类型,在account_number字段上有索引。
下面的语句将执行全表扫描:
select bank_name,address,city,state,zip from banks where account_number = 990354;
Oracle可以自动把where子句变成to_number(account_number)=990354,这样就限制了索引的使用,改成下面的查询就可以使用索引:
select bank_name,address,city,state,zip from banks where account_number ='990354';
特别注意:不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
1.7 查询索引
查询DBA_INDEXES视图可得到表中所有索引的列表,注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。
1.8 组合索引
当某个索引包含有多个已索引的列时,称这个索引为组合(concatented)索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前,查询只能在有限条件下使用该索引。比如:表emp有一个组合索引键,该索引包含了empno、 ename和deptno。在Oracle9i之前除非在where之句中对第一列(empno)指定一个值,否则就不能使用这个索引键进行一次范围扫描。
特别注意:在Oracle9i之前,只有在使用到索引的前导索引时才可以使用组合索引!
1.9 ORACLE ROWID
通过每个行的ROWID,索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用,可以在任何表中使用和指定rowid列。
oracle 如何动态创建索引
create or replace procedure sp_crt_stg_index( p_src_tbl_name varchar2,p_tgt_tbl_name varchar2,p_tx_date varchar2)
is
v_crt_ind_sql varchar2(4000) := '';
v_drop_ind_sql varchar2(4000) := '';
v_ind_name varchar2(100) := '';
v_tgt_ind_name varchar2(100) := '';
v_col_list varchar2(1000) := '';
v_col_name varchar2(100) := '';
i_ind_col_cnt number(2,0) := 0;
i_tbl_cnt number(2,0) := 0;
i_ind_cnt number(2,0) := 0;
v_step_no varchar2(3) :='';
begin
--step 1 检查索引定义是否存在
v_step_no :='1';
select count(1) into i_ind_col_cnt from all_ind_columns
where table_name = p_src_tbl_name;
if(i_ind_col_cnt = 0 ) then
sp_log('sp_crt_stg_index',v_step_no,'3',p_tx_date,p_src_tbl_name||'不存在索引定义');
return;
end if;
--step 2 检查目标表是否存在
v_step_no :='2';
select count(1) into i_tbl_cnt from tabs
where table_name = p_tgt_tbl_name;
if(i_tbl_cnt = 0 ) then
sp_log('sp_crt_stg_index',v_step_no,'3',p_tx_date,p_src_tbl_name||'不存在');
return;
end if;
--step 3 创建索引
v_step_no :='3';
for cur_ind in (select index_name from all_indexes where table_name = p_src_tbl_name) loop
v_ind_name := cur_ind.index_name;
v_col_name := '';
v_col_list := '(';
for cur_ind_col in (select column_name from all_ind_columns where index_name = v_ind_name order by column_position) loop
v_col_name := cur_ind_col.column_name;
v_col_list := v_col_list||v_col_name||',';
end loop;
v_col_list := v_col_list||')';
v_col_list := replace(v_col_list,',)',')');
v_tgt_ind_name :=replace(v_ind_name,p_src_tbl_name,p_tgt_tbl_name);
v_tgt_ind_name := v_tgt_ind_name||substr(p_tx_date,7,21);
select count(1) into i_ind_cnt from all_indexes where index_name = v_tgt_ind_name;
if(i_ind_cnt 0) then
v_drop_ind_sql := 'drop index '||v_tgt_ind_name;
execute immediate v_drop_ind_sql;
end if;
sp_log('sp_crt_stg_index',v_step_no,'1',p_tx_date,v_tgt_ind_name||'创建开始');
v_crt_ind_sql := 'create index '||v_tgt_ind_name||' on '||p_tgt_tbl_name||v_col_list||' nologging';
if(v_crt_ind_sql is not null) then
execute immediate v_crt_ind_sql;
end if;
sp_log('sp_crt_stg_index',v_step_no,'1',p_tx_date,v_tgt_ind_name||'创建结束');
end loop;
exception
when others then
sp_log('sp_crt_stg_index',v_step_no,'3',p_tx_date,v_tgt_ind_name||'创建异常:'||SQLERRM);
end;
#########################
---- 日期类型转换
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS');
----Oracle检查分区
select count(1)
from ALL_TAB_PARTITIONS
where table_name = p_tgt_tbl_name
and PARTITION_NAME = v_prt_name;
----码表
Select * From ict_s_dic Where opttype = 'ID_TYPE';
decode 内部字段拼接
decode(Date_Nextvisit,'','下次跟进时间:['||Date_Nextvisit||']') RMK1,
----拼接字段,删除表
SELECT 'DROP TABLE '||TABLE_NAME||';' FROM TABS WHERE TABLE_NAME LIKE 'M_ICT%';
Select * From tabs ;
----交换分区,分区交换后数据是否交换?
'ALTER TABLE '|| p_tgt_tbl_name ||' EXCHANGE PARTITION '||v_prt_name||' WITH TABLE '||p_src_tbl_name;
----查询某表是否存在
select count(1) from tabs
where table_name = p_src_tbl_name;
----重建索引
ALTER index ind_id_idx rebuild
----创建索引nologging
create index I_ICT_CUST_INFO__0 on ICT_CUST_INFO_20141222 (CUST_NO)
nologging;
----查询分区内数据
select count(1) from stg_ict_trade_info partition(ICT_PRT_2014005);
Select * From User_Ind_Partitions;
Select * From User_Part_Indexes;
-----查询索引
select index_name from ALL_INDEXES WHERE TABLE_NAME=p_src_tbl_name;
-----查询分区
select * from ALL_TAB_PARTITIONS
where table_name = p_tgt_tbl_name and PARTITION_NAME = v_prt_name;
-----查询表名
select * from tabs where table_name = p_tgt_tbl_name;
-----清空表分区数据
'ALTER TABLE '||p_tgt_tbl_name||' TRUNCATE PARTITION ' || v_prt_name;
-----增加表分区
'ALTER TABLE '||p_tgt_tbl_name||' ADD PARTITION ' || v_prt_name||' VALUES LESS THAN (''' ||v_monthend||''') TABLESPACE ICLIENT_O_DATA01 ';
-----重建索引
'ALTER INDEX '||cur_ind.index_name|| ' REBUILD PARALLEL 128 COMPUTE STATISTICS NOLOGGING';
-----交换分区
'ALTER TABLE '|| p_tgt_tbl_name ||' EXCHANGE PARTITION '||v_prt_name||' WITH TABLE '||p_src_tbl_name||' INCLUDING INDEXES';
-----oralce创建同义词------------
create or replace public SYNONYM ICT_ORG_BPH for iclientodata.ICT_ORG_BPH;
-----oracle 赋权限
grant select, insert, update, delete on ICT_ORG_BPH to ICLIENTOOPR; --赋权限
---------------DBA查看表空间------------
select a.tablespace_name,
a.bytes / 1024 / 1024 / 1024 "Sum G",
(a.bytes - b.bytes) / 1024 / 1024 / 1024 "used G",
b.bytes / 1024 / 1024 / 1024 "free G",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc
-------------------------------------------------------------------------------------
--oracle 生成删表语句,
SELECT 'DROP TABLE '||TABLE_NAME||';' FROM TABS WHERE TABLE_NAME LIKE 'M_ICT%'
--查询oracle 中ICT开头的
SELECT * FROM tabs WHERE TABLE_NAME LIKE 'ICT%'
----oracle 表分析语句
'ANALYZE TABLE ' || v_ana_tbl_name || ' estimate system statistics';
例如:ANALYZE TABLE ICT_SUM_AST_DBT_CUST ESTIMATE SYSTEM STATISTICS;
-----------查看锁定对象及会话
SELECT OBJECT_NAME,MACHINE,S.SID,S.SERIAL#
FROM GV$LOCKED_OBJECT I,DBA_OBJECTS O,GV$SESSION S
WHERE I.OBJECT_ID=O.OBJECT_ID AND I.SESSION_ID=S.SID;
----------oralce解锁
ALTER SYSTEM KILL SESSION '280,219';
授权脚本生成方法:
select 'grant select on table dmccrm.'||tbl_name||' to public,ex_sdbods; ' from t_ict_tbl_type where tbl_type in('ALL','GP') and ETL_DIR IN( 'GP-ORACLE','无需同步');
-----------oracle批量生成同义词
SELECT 'create or replace public synonym '||table_name ||' for iclientodata.'||table_name FROM tabs WHERE table_name like 'ICT_%'
-----------oracle批量生成修改表字段的长度
select 'ALTER TABLE '||TABLE_NAME||' MODIFY '||COLUMN_NAME||' NUMBER(30,8);' from cols t
where t.DATA_TYPE = 'NUMBER'
AND T.DATA_SCALE 0 AND SUBSTR(TABLE_NAME,-8,8)'20140531'
---------oracle 批量创建同义词
select 'create or replace public synonym '||table_name||' for iclientodata.'||table_name||';' from user_tables WHERE table_name LIKE 'ICT_%' AND table_name NOT LIKE '%20140531';
-----------oracle 交换分区语句
ALTER TABLE ICT_CUST_LEVEL_HIS ADD PARTITION ICT_PRT_20140531 VALUES LESS THAN ('2014-06-01')
--------MERGE使用方法
MERGE INTO ICT_CUST_INFO_ALL a
USING
(
SELECT t.CUST_MNG_UM_NO ,t.CUST_NO
FROM ict_cust_mnger_rel t
WHERE t.CUST_NO IN
('600021562650', '600037441214', '600036874754', '600038507516',
'600020226746', '600038089420', '600041030403', '600038952992',
'600039468303')
)b
ON( a.cust_no= b.cust_no)
WHEN MATCHED THEN
UPDATE SET a.MAX_ASSET_INTRO_NO=b.CUST_MNG_UM_NO;
-------------oracle 树形查询,查询机构编号为‘9902’的和其下级子机构
select org_id,org_name,org_level from (
SELECT rownum rn, ioi.org_id , ioi.org_name,ioi.org_level
FROM ict_org_info ioi
START WITH ioi.org_id ='9902'
CONNECT BY PRIOR ioi.org_id = ioi.parent_org_id_b
order by ioi.org_level desc
) where rn=1
-------------oracle 树形查询,查询机构编号为‘9902’的和上级机构
select org_id,org_name,org_level from (
SELECT rownum rn, ioi.org_id , ioi.org_name,ioi.org_level
FROM ict_org_info ioi
START WITH ioi.org_id ='9902'
CONNECT BY ioi.org_id = PRIOR ioi.parent_org_id_b
order by ioi.org_level desc
) where rn=1
-------------oracle 树形查询,查询机构编号为‘9902’的上级机构
select org_id,org_name,org_level from (
SELECT rownum rn, ioi.org_id , ioi.org_name,ioi.org_level
FROM ict_org_info ioi
START WITH ioi.org_id ='9902'
CONNECT BY ioi.org_id = PRIOR ioi.parent_org_id_b
order by ioi.org_level desc
) where rn=1
----------查看表名与表空间
Select * From user_tables Where table_name = 'ICT_RMT_APPO';
Select * From User_Tablespaces;
----------查询SQL 预估时间
SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
------TYPE opty_cur IS REF CURSOR;
整体的意思是“创建一个类型变量cur,它引用游标”,除了cur外,其余全是关键字。
TYPE cur:定义类型变量 ,is ref cursor:相当于数据类型,不过是引用游标的数据类型。
这种变量通常用于存储过程和函数返回结果集时使用,
因为PL/SQL不允许存储过程或函数直接返回结果集,
但可以返回类型变量,于是引用游标的类型变量作为输出参数或返回值就应运而生了。
----查杀进程
SELECT dob.OBJECT_NAME Table_Name,
lo.LOCKED_MODE,
lo.SESSION_ID,
vss.SERIAL#,
vps.spid,
vss.action Action,
vss.osuser OSUSER,
vss.process AP_PID,
VPS.SPID DB_PID,
'alter system kill session ' || '''' || lo.SESSION_ID || ',' ||
vss.SERIAL# || ''';' kill_command
from v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS
where lo.OBJECT_ID = dob.OBJECT_ID
and lo.SESSION_ID = vss.SID
AND VSS.paddr = VPS.addr
order by 2, 3, DOB.object_name;
如何让oracle的select强制走索引
1
SELECT /*+INDEX(table index_name)*/ column1, column2 FROM table WHERE ...
使用 Oracle Hint,要求表必须有索引,且语句使用了索引,如使用索引比较等。
如何更好的使用Oracle全文索引
不使用Oracle text功能,也有很多方法可以在Oracle数据库中搜索文本.可以使用标准的INSTR函数和LIKE操作符实现。
SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') 0;
SELECT * FROM mytext WHERE thetext LIKE '%Oracle%';
有很多时候,使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候.然而通过这些文本定位的方法将导致全表扫描,对资源来说消耗比较昂贵,而且实现的搜索功能也非常有限,因此对海量的文本数据进行搜索时,建议使用oralce提供的全文检索功能 建立全文检索的步骤步骤一 检查和设置数据库角色首先检查数据库中是否有CTXSYS用户和CTXAPP脚色。如果没有这个用户和角色,意味着你的数据库创建时未安装intermedia功能。你必须修改数据库以安装这项功能。 默认安装情况下,ctxsys用户是被锁定的,因此要先启用ctxsys的用户。 步骤二 赋权 在ctxsys用户下把ctx_ddl的执行权限赋于要使用全文索引的用户,例:
grant execute on ctx_ddl to pomoho;
步骤三 设置词法分析器(lexer)
Oracle实现全文检索,其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元(Oracle 称为 term)找出来,记录在一组 以dr$开头的表中,同时记下该term出现的位置、次数、hash 值等信息。检索时,Oracle 从这组表中查找相应的term,并计算其出现频率,根据某个算法来计算每个文档的得分(score),即所谓的‘匹配率’。而lexer则是该机制的核心,它决定了全文检索的效率。Oracle 针对不同的语言提供了不同的 lexer, 而我们通常能用到其中的三个:
n basic_lexer: 针对英语。它能根据空格和标点来将英语单词从句子中分离,还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理,如if , is 等,具有较高的处理效率。但该lexer应用于汉语则有很多问题,由于它只认空格和标点,而汉语的一句话中通常不会有空格,因此,它会把整句话作为一个 term,事实上失去检索能力。以‘中国人民站起来了’这句话为例,basic_lexer 分析的结果只有一个term ,就是‘中国人民站起来了’。此时若检索‘中国’,将检索不到内容。
n chinese_vgram_lexer: 专门的汉语分析器,支持所有汉字字符集(ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 )。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话,会被它分析成如下几个term: ‘中’,‘中国’,‘国人’,‘人民’,‘民站’,‘站起’,起来’,‘来了’,‘了’。可以看出,这种分析方法,实现算法很简单,并且能实现‘一网打尽’,但效率则是差强人意。
n chinese_lexer: 这是一个新的汉语分析器,只支持utf8字符集。上面已经看到,chinese vgram lexer这个分析器由于不认识常用的汉语词汇,因此分析的单元非常机械,像上面的‘民站’,‘站起’在汉语中根本不会单独出现,因此这种term是没有意义的,反而影响效率。chinese_lexer的最大改进就是该分析器 能认识大部分常用汉语词汇,因此能更有效率地分析句子,像以上两个愚蠢的单元将不会再出现,极大 提高了效率。但是它只支持 utf8, 如果你的数据库是zhs16gbk字符集,则只能使用笨笨的那个Chinese vgram lexer.
如果不做任何设置,Oracle 缺省使用basic_lexer这个分析器。要指定使用哪一个lexer, 可以这样操作:
第一. 当前用户下下建立一个preference(例:在pomoho用户下执行以下语句)
exec ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');
第二. 在建立全文索引索引时,指明所用的lexer:
CREATE INDEX myindex ON mytable(mycolumn) indextype is ctxsys.context
parameters('lexer my_lexer');
这样建立的全文检索索引,就会使用chinese_vgram_lexer作为分析器。
步骤四 建立索引
通过以下语法建立全文索引
CREATE INDEX [schema.]index on [schema.]table(column) INDEXTYPE IS ctxsys.context [ONLINE]
LOCAL [(PARTITION [partition] [PARAMETERS('paramstring')]
[, PARTITION [partition] [PARAMETERS('paramstring')]])]
[PARAMETERS(paramstring)] [PARALLEL n] [UNUSABLE];
例:
CREATE INDEX ctx_idx_menuname ON pubmenu(menuname)
indextype is ctxsys.context parameters('lexer my_lexer')
步骤五 使用索引
使用全文索引很简单,可以通过:
select * from pubmenu where contains(menuname,'上传图片')0
全文索引的种类
建立的Oracle Text索引被称为域索引(domain index),包括4种索引类型:
l CONTEXT
2 CTXCAT
3 CTXRULE
4 CTXXPATH
依据你的应用程序和文本数据类型你可以任意选择一种。
对多字段建立全文索引
很多时候需要从多个文本字段中查询满足条件的记录,这时就需要建立针对多个字段的全文索引,例如需要从pmhsubjects(专题表)的 subjectname(专题名称)和briefintro(简介)上进行全文检索,则需要按以下步骤进行操作:
Ø 建议多字段索引的preference
以ctxsys登录,并执行:
EXEC ctx_ddl.create_preference(' ctx_idx_subject_pref',
'MULTI_COLUMN_DATASTORE');
Ø 建立preference对应的字段值(以ctxsys登录)
EXEC ctx_ddl.set_attribute(' ctx_idx_subject_pref ','columns','subjectname,briefintro');
Ø 建立全文索引
CREATE INDEX ctx_idx_subject ON pmhsubjects(subjectname)
INDEXTYPE ISctxsys.CONTEXT PARAMETERS('DATASTORE ctxsys.ctx_idx_subject_pref lexer my_lexer')
Ø 使用索引
select * from pmhsubjects where contains(subjectname,'李宇春')0
全文索引的维护
对于CTXSYS.CONTEXT索引,当应用程序对基表进行DML操作后,对基表的索引维护是必须的。索引维护包括索引同步和索引优化。
在索引建好后,我们可以在该用户下查到Oracle自动产生了以下几个表:(假设索引名为myindex):
DR$myindex$I、DR$myindex$K、DR$myindex$R、DR$myindex$N其中以I表最重要,可以查询一下该表,看看有什么内容:
SELECT token_text, token_count FROM dr$i_rsk1$I WHERE ROWNUM = 20;
这里就不列出查询接过了。可以看到,该表中保存的其实就是Oracle 分析你的文档后,生成的term记录在这里,包括term出现的位置、次数、hash值等。当文档的内容改变后,可以想见这个I表的内容也应该相应改变,才能保证Oracle在做全文检索时正确检索到内容(因为所谓全文检索,其实核心就是查询这个表)。这就用到sync(同步) 和 optimize(优化)了。
同步(sync): 将新的term 保存到I表;
优化(optimize): 清除I表的垃圾,主要是将已经被删除的term从I表删除。
当基表中的被索引文档发生insert、update、delete操作的时候,基表的改变并不能马上影响到索引上直到同步索引。可以查询视图 CTX_USER_PENDING查看相应的改动。例如:
SELECT pnd_index_name, pnd_rowid,
TO_CHAR (pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss') timestamp
FROM ctx_user_pending;
该语句的输出类似如下:
PND_INDEX_NAME PND_ROWID TIMESTAMP
------------------------------ ------------------ --------------------
MYINDEX AAADXnAABAAAS3SAAC 06-oct-1999 15:56:50
同步和优化方法: 可以使用Oracle提供的ctx_ddl包同步和优化索引
一. 对于CTXCAT类型的索引来说, 当对基表进行DML操作的时候,Oracle自动维护索引。对文档的改变马上反映到索引中。CTXCAT是事务形的索引。
索引的同步
在对基表插入,修改,删除之后同步索引。推荐使用sync同步索引。语法:
ctx_ddl.sync_index(
idx_name IN VARCHAR2 DEFAULT NULL
memory IN VARCHAR2 DEFAULT NULL,
part_name IN VARCHAR2 DEFAULT NULL
parallel_degree IN NUMBER DEFAULT 1);
idx_name 索引名称
memory 指定同步索引需要的内存。默认是系统参数DEFAULT_INDEX_MEMORY 。
指定一个大的内存时候可以加快索引效率和查询速度,且索引有较少的碎片
part_name 同步哪个分区索引。
parallel_degree 并行同步索引。设置并行度。
例如:
同步索引myindex:Exec ctx_ddl.sync_index ('myindex');
实施建议:建议通过oracle的job对索引进行同步
索引的优化
经常的索引同步将会导致你的CONTEXT索引产生碎片。索引碎片严重的影响了查询的反应速度。你可以定期优化索引来减少碎片,减少索引大小,提高查询效率。
当文本从表中删除的时候,Oracle Text标记删除的文档,但是并不马上修改索引。因此,就的文档信息占据了不必要的空间,导致了查询额外的开销。你必须以FULL模式优化索引,从索引中删除无效的旧的信息。这个过程叫做垃圾处理。当你经常的对表文本数据进行更新,删除操作的时候,垃圾处理是很必要的。
exec ctx_ddl.optimize_index ('myidx', 'full');
实施建议:每天在系统空闲的时候对全文索引进行相应的优化,以提高检索的效率
P.S.定时优化索引
3.定时优化同步域索引
创建定时任务,定期优化和同步域索引
SQL create or replace procedure hsp_sync_index as
2 begin
3 ctx_ddl.sync_index('id_cont_msg');
4 end;
5 /
Procedure created.
Elapsed: 00:00:00.08
SQL VARIABLE jobno number;
SQL BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'hsp_sync_index();',
3 SYSDATE, 'SYSDATE + (1/24/4)');
4 commit;
5 END;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.27
SQL create or replace procedure hsp_optimize_index as
2 begin
3 ctx_ddl.optimize_index('id_cont_msg','FULL');
4 end;
5 /
SQL VARIABLE jobno number;
SQL BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'hsp_optimize_index();',
3 SYSDATE, 'SYSDATE + 1');
4 commit;
5 END;
6 /
Procedure created.
Elapsed: 00:00:00.03
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL
关于in走索引oracle和oracle有索引不走索引的几种情况的介绍到此就结束了,不知道你从中找到你需要的信息了吗 ?如果你还想了解更多这方面的信息,记得收藏关注本站。