用全文索引做了一个根据表名查找SQL语句的功能. 在Statspack中有一个表存放了数据库中执行过的SQL, 虽然不是全部, 但也有差不多99.9%了. 只是由于它是分行存贮的, 不能直接用普通的SQL语句(like)来查找, 表名有可能被折分存放在两行中. 解决的方法有两种, 一是写过程, 将多行的串在一起. 二是用全文索引中的主从存贮方式, 建立全文索引来查询. 我选择的是全文索引的方式。
首先，将里面的数据拷出来, 分成两个表, 主表和从表:
CREATE TABLE SQLS AS
SELECT DISTINCT HASH_VALUE,'X' BODY FROM STATS$SQLTEXT;
ALTER TABLE SQLS ADD PRIMARY KEY (HASH_VALUE);
CREATE TABLE SQL_DETAILS AS
SELECT HASH_VALUE,PIECE,SQL_TEXT FROM STATS$SQLTEXT;
ALTER TABLE SQL_DETAILS ADD PRIMARY KEY (HASH_VALUE, PIECE);
创建全文索引的存贮方式及Lexer属性:
begin
ctx_ddl.create_preference('sqltext_pref', 'DETAIL_DATASTORE');
ctx_ddl.set_attribute('sqltext_pref', 'binary', 'true');
ctx_ddl.set_attribute('sqltext_pref', 'detail_table', 'sql_details');
ctx_ddl.set_attribute('sqltext_pref', 'detail_key', 'hash_value');
ctx_ddl.set_attribute('sqltext_pref', 'detail_lineno', 'piece');
ctx_ddl.set_attribute('sqltext_pref', 'detail_text', 'sql_text');
end;
/
begin
ctx_ddl.create_preference('sqltext_lex', 'BASIC_LEXER');
ctx_ddl.set_attribute ('sqltext_lex', 'continuation', '_');
ctx_ddl.set_attribute ('sqltext_lex', 'printjoins', '_');
ctx_ddl.set_attribute ('sqltext_lex', 'index_themes', 'NO');
ctx_ddl.set_attribute ('sqltext_lex', 'index_text', 'YES');
end;
/
接下来创建全文索引：
CREATE INDEX SQL_CTX on SQLS(body) indextype is ctxsys.context
parameters('datastore sqltext_pref LEXER sqltext_lex');
最后可以查询了:
SELECT HASH_VALUE FROM SQLS WHERE CONTAINS(BODY,'tablename and ...') > 0
