[分享]ORACLE全文索引测试2_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2193 | 回复: 1

主题： [分享]ORACLE全文索引测试2

game

注册用户

等级：新兵
经验：36
发帖：61
精华：0
注册：2011-7-23
状态：离线
发送短消息息给game

加好友发送短消息息给game

发消息

发表于：

2015-1-20 17:47:14 | [全部帖] [楼主帖]

楼主

创建词分析器

BEGIN
CTX_DDL.DROP_PREFERENCE('MY_CHINESE_LEXER');
CTX_DDL.DROP_PREFERENCE('MY_CHINESEWORDLIST');
CTX_DDL.CREATE_PREFERENCE('MY_CHINESE_LEXER','CHINESE_VGRAM_LEXER');
CTX_DDL.CREATE_PREFERENCE('MY_CHINESEWORDLIST','BASIC_WORDLIST');
CTX_DDL.SET_ATTRIBUTE('MY_CHINESEWORDLIST','PREFIX_INDEX','TRUE');
CTX_DDL.SET_ATTRIBUTE('MY_CHINESEWORDLIST','PREFIX_MIN_LENGTH',1);
CTX_DDL.SET_ATTRIBUTE('MY_CHINESEWORDLIST','PREFIX_MAX_LENGTH',5);
CTX_DDL.SET_ATTRIBUTE('MY_CHINESEWORDLIST','SUBSTRING_INDEX','YES');
END;
/

创建索引：时间消耗40分钟

CREATE INDEX IND_JYC ON JR_COVER_ADDRESS_JYC(TOTAL_ADDRESS) INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS ('LEXER MY_CHINESE_LEXER WORDLIST MY_CHINESEWORDLIST');

删除索引：drop index IND_JYC;

SQL> conn gistar/gistar

已连接。

SQL> select status from user_indexes where index_name='IND_JYC';
STATUS
--------
INPROGRS
SQL> select table_name from user_tables where table_name like 'DR%';
TABLE_NAME
------------------------------
DR$IND_JYC$I
DR$IND_JYC$P
DRAG_LINE
SQL> select status from user_indexes where index_name='IND_JYC';
STATUS
--------
INPROGRS
SQL> select status from user_indexes where index_name='IND_JYC';
STATUS
--------
INPROGRS
SQL> select count(*) from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'雅世合金',null)>0 and catsearch
(TOTAL_ADDRESS,'1',null)>0;
select count(*) from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'雅世合金',null)>0 and catsearch
(TOTAL_ADDRESS,'1',null)>0
*

ERROR 位于第 1 行:

ORA-20000: Oracle Text error:

DRG-10849: catsearch 不支持函数调用

SQL> select status from user_indexes where index_name='IND_JYC';
STATUS
--------
INPROGRS
SQL> /
STATUS
--------
INPROGRS
SQL> /
STATUS
--------
VALID
SQL> select count(*) from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'雅世合金',null)>0 and catsearch
(TOTAL_ADDRESS,'1',null)>0;
COUNT(*)
----------
284
SQL> select count(*) from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'雅世合金',null)>0;
COUNT(*)
----------
513
SQL> select count(*) from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'1',null)>0;
COUNT(*)
----------
52583
SQL>

SQL> select count(*) from JR_COVER_ADDRESS where total_address like '%雅世合金%';

COUNT(*)
----------
513
SQL> select count(*) from JR_COVER_ADDRESS where total_address like '%1';
COUNT(*)
----------
21039

SQL> select count(*) from JR_COVER_ADDRESS where total_address like '%雅世合金%' and total_address like '%1%';

COUNT(*)
----------
360
SQL> select count(*) from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'雅世合金 1',null)>0;
COUNT(*)
----------
284
SQL> select count(*) from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'1 雅世合金',null)>0;
COUNT(*)
----------
284

SQL> select count(*) from JR_COVER_ADDRESS where total_address like '%雅世合金 1%';

COUNT(*)
----------
0

SQL> select count(*) from JR_COVER_ADDRESS where total_address like '%1 雅世合金%';

COUNT(*)
----------
0
SQL> select table_name from user_tables where table_name like 'DR%';
TABLE_NAME
------------------------------
DR$IND_JYC$I
DR$IND_JYC$P
DRAG_LINE
SQL> select status from user_indexes where index_name='IND_JYC';
STATUS
--------
VALID
SQL> select sum(bytes)/1024/1024 from user_segments where segment_name='DR$IND_JYC$I';
SUM(BYTES)/1024/1024
--------------------
138
SQL> select sum(bytes)/1024/1024 from user_segments where segment_name='DR$IND_JYC$P';
SUM(BYTES)/1024/1024
--------------------
SQL> select sum(bytes)/1024 from user_segments where segment_name='DR$IND_JYC$P';
SUM(BYTES)/1024
---------------
SQL> select sum(bytes) from user_segments where segment_name='DR$IND_JYC$P';
SUM(BYTES)
----------
SQL> select count(*) from DR$IND_JYC$P;
COUNT(*)
----------
7673
SQL> select count(*) from DR$IND_JYC$I;
COUNT(*)
----------
4915909
SQL> explain plan for select id,total_address from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'雅世合金',null)>0 and
catsearch(TOTAL_ADDRESS,'1',null)>0;

已解释。

SQL> set line 132
SQL> set * from table(dbms_xplan.display);

SP2-0158: 未知的SET选项"*"

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
-----
------------------------------------------------------------------------------------------
| Id  | Operation                        |  Name                 | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                       |   490 |   265K|   175 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | JR_COVER_ADDRESS_JYC  |   490 |   265K|   175 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                       |       |       |       |
|   3 |    BITMAP AND                    |                       |       |       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                       |       |       |       |
|   5 |      SORT ORDER BY               |                       |       |       |       |
|   6 |       DOMAIN INDEX               | IND_JYC               |       |       |       |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
-----
|   7 |     BITMAP CONVERSION FROM ROWIDS|                       |       |       |       |
|   8 |      SORT ORDER BY               |                       |       |       |       |
|   9 |       DOMAIN INDEX               | IND_JYC               |       |       |       |
------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version

已选择17行。

SQL> explain plan for select id,total_address from JR_COVER_ADDRESS where total_address like '%雅世合金%' and total_address

like '%1%';

已解释。

已选择9行。

SQL> explain plan for select id,total_address from JR_COVER_ADDRESS_JYC where total_address like '%雅世合金%' and total_address

like '%1%';

已解释。

已选择9行。

SQL> set timing on
SQL> select count(*) from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'雅世合金 1',null)>0;
COUNT(*)
----------
284

已用时间: 00: 00: 00.22
SQL> select count(*) from JR_COVER_ADDRESS where total_address like '%雅世合金%' and total_address like '%1%';

COUNT(*)
----------
360

已用时间: 00: 00: 00.88

SQL> select count(*) from JR_COVER_ADDRESS_JYC where catsearch(TOTAL_ADDRESS,'雅世合金 1',null)>0;
COUNT(*)
----------
284