intermedia Text Tuning Guide Creation Date: Mar 12, 2001 Last Update: Apr 19, 2001 Version: 1.1
4 intermedia Text 4 5 5 5 6 6 intermedia 6 6 6 7 7 7 7 8 8 8 8 R816 9 10 10 10 12 12 13 14 15 catalog index R817 16 ORDER BY 16 17 catalog index R817 17 Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 2
19 PREFIX INDEX R817 19 21 21 $X 22 R816 22 shared_pool_size log_buffer 23 Disk CPU 25 27 28 28 28 R817 28 30 Disk CPU 31 31 32 34 34 $R 34 URL 34 35 35 35 Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 3
intermedia Text imt Oracle R815R817 imt imt WORD EXCEL URL DB M Oracle RDBMS intermedia Text imt OTN Japan http://technet.oracle.co.jp/) imt OTN Japan > > > Oracle8i > Oracle intermedia Oracle intermedia Text Oracle intermedia Text intermedia Text imt Oracle SQL SQL 2000 Oracle SQL SQL imt Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 4
Oracle imt imt Oracle RDBMS imt Oracle RDBMS imt 2 SQL 1 Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 5
CPU INSERT DISK intermedia imt Dog DOG dog BASIC_LEXER BASIC_LEXER < >... Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 6
BASIC_LEXER 1 9i Oracle9i ORA % ORA% % imt R816 SUBSTRING_INDEX SUBSTRING_INDEX imt B*TREE imt Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 7
B*TREE B*TREE imt imt B*TREE ABC123DEF 3 5 imt imt 1 123 ABC123DEF V-GRAM V-GRAM V-GRAM Oracle Corporation ORACLE CORPORATION 03-1111-2222 03 1111 2222 123.345 123.345 345 123.345 345 1111 03-1111-2222 03-1111-2222 03-1111-2222 YES 03 1111 2222 Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 8
03 1111 2222 3 03 1111 2222 1 03-1111-2222 MINUS MINUS {} BASIC_LEXER BASIC_LEXER BASIC_LEXER BASIC_LEXER JAPANESE_VGRAM_LEXER BASIC_LEXER BASIC_LEXER 1 1 JAPANESE_VGRAM_LEXER V-GRAM BASIC_LEXER R816 R816 1 BASIC_LEXER CHINESE_LEXER JAPANESE_VGRAM_LEXER Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 9
NLS_LANG NLS_LANG=JAPANESE_xxx.yyy JAPANESE_VGRAM_LEXER NLS_LANG=AMERICAN_xxx.yyy BASIC_LEXER NLS_LANG Web Web NLS_LANG OTN imt 1 SQL CONTAINS 1 WHERE CONTAINS ORDER BY 1 ORDER BY SCORE(n) DESCEND FIRST ROWS 10 20 http://technet.oracle.co.jp/products/iserver/intermedia/intermedia.html Q&A imt SQL 1 CONTAINS 1 1 Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 10
SELECT... FROM... WHERE CONTAINS(, )>0 AND CONTAINS(, SCOTT )>0; Scott SELECT... FROM... WHERE CONTAINS(, within and scott within )>0; Scott < > </ >< >Scott</ > 1 WHERE 1 CONTAINS 2 2 1 USER_DATASTORE USER_DATASTORE OUT OUT MULTI_COLUMN_DATASTORE Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 11
R8.1.7 MULTI_COLUMN_DATASTORE USER_DATASTORE 1 Disk USER_DATASTORE visible invisible visible invisible 64 invisible visible 2 visible within invisible within Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 12
<TAG1>Oracle Japan</TAG1> TAG1 visible Oracle within TAG1 TAG1invisible Oracle Oracle within TAG1 visible invisible <NAME> </NAME><TITLE> </TITLE> NAME TITLE visible contains invisible within NAME or within TITLE contains visible visible invisible 2 2 2 invisible $I 1 TOKEN_TEXT ORACLE TOKEN_TYPE n n NAME Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 13
visible $I 2 TOKEN_TEXT ORACLE TOKEN_TYPE n 0 WITHIN TOKEN_TYPE=n WITHIN TOKEN_TYPE=0 visible 2 1999 SQL WHERE SQL 1. 2. 1 1 20 20 10 1 1999 10 10 100 90 1999 Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 14
100 1999 20 100 1999 100 <TITLE>Java </TITLE><YEAR>1999</YEAR><MONTH>3</MONTH> JAVA 1999 1999 3 4 Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 15
2 15 5 12 within catalog index R817 imt R817 ORDER BY catalog index B*TREE B*TREE DR$TOKEN DR$INFO CAMERA CAR TELEPHONE BOOK 100 200 100 50 BOOK CAMERA CAR TELEPHONE 50 100 200 100............ CONTEXT ORDER BY 2 1. 20 2. 20 1 20 2 Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 16
ORDER BY SQL ORDER BY catalog index R817 R817 catalog index order by catalog index SQL JavaScript 1000 1 order by order by score(n) desc order by score(n) desc imt Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 17
... order by price imt price... order by score(n) desc imt order by score(n) imt order by score(n) desc, price imt /*+ DOMAIN_INDEX_SORT */ /*+ DOMAIN_INDEX_NO_SORT */ FIRST_ROWS FIRST_ROWS imt FIRST_ROWS DOMAIN_INDEX_NO_SORT Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 18
Web 1 2 imt R817 20 DB ctx_query.count_hits count(*) ctx_query.count_hits count(*) rownum PREFIX INDEX R817 PREFIX INDEX R817 PREFIX INDEX SUBSTRING INDEX PREFIX INDEX doc Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 19
$I TOKEN_INFO (1-1)1 1 (2-1)2 2 T% $I like TOKEN_TEXT T TOKEN TOY 2 PREFIX INDEX TOKEN,TOY TOKEN T,TO,TOK,TOKE,TOKEN TOKEN_INFO=6 $I TOY TOKEN_INFO PRIFIX INDEX T% token_text='t' and TOKEN_TYPE=6 1 PREFIX INDEX $I Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 20
% PREFIX INDEX 1 B*TREE Disk 2G 2G OS 1.6G 100M 16 16 2 1 50M 2 200M 2 1 70% Disk Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 21
$X $I $X $X $X BASIC_STORAGE I_index_clause begin ctx_ddl.create_preference( my_store, BASIC_STORAGE ); ctx_ddl.set_attribute( my_store, I_index_clause, parallel 4 ); end; B*TREE R816 R816 B*TREE OK job_queue_processes job_queue_interval R817 $I 8 8 $I 8 R817 $I Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 22
$I I/O Buffer Busy Wait $I $I $I TOKEN_TEXT BASIC_STORAGE 500 TOKEN_TEXT 1.6G 100M 16 16 2 1 4k $I 2 8k $I 32 2 1 58% 2G shared_pool_size log_buffer shared_pool_size log_buffer Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 23
1.6G 100M 16 16 2 1 shared_pool_size=50m log_buffer=160k 2 shared_pool_size=250m log_buffer=15m 2 1 90% bstat/estat 1 bstat/estat log_buffer OS (iostat,vmstat,sar,mpstat )ORACLE bstat/estat $I INSERT $X B*TREE bstat/estat Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 24
Disk CPU create index 1. SELECT 2. $I $I INSERT 3. 1 2 4. $I $X CREATE INDEX 2 COMMIT RBS COMMIT CTXSYS RESUME 1 CPU mpstat USR 100% CPU 100% 2 4 $I B*TREE $I Disk $I $I $X Disk Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 25
8G 8 iostat(read/sec,write/sec) I/O y I/O x 2 Disk1 Disk2 RAID0 Disk1 : $I $K $R $N $X Disk2 : RBS,Redo C B A D E x 177 Disk2 Disk1 Disk2 Disk1 REDO RBS Disk1 Disk2 I/O A B 8 8 $I 8 x 77117 $X $I $I Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 26
$X vmstat CPU x iostat 10CPU 8 8 CPU CPU 80% x 177 CPU 80% CPU x 6077 CPU CPU CPU CPU x 77120 $X $X 1 CPU 10% $X Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 27
2 $I $I Disk alter index optimize optimize R817 )3 $I $I $I $I HWM $I update delete 10 R817 $I V-GRAM Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 28
3 $I TOKEN_TYPE=0 INVISIBLE VISIBLE TOKEN_TYPE>0 ONLINE ZONE TOKEN_TYPE=0 FIELD invisible visible TOKEN_TYPE=0 FIELD TOKEN_TYPE=0 FIELD 1. TOKEN_TYPE0 update $I TOKEN_TYPE=0 update 2. 3. TOKEN_TYPE ZONE Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 29
$I $I 1 TOKEN_INFO 4000 1 $I 1 $I DR$DOC_IDX$I /* $I */ create table dr$doc_idx$i_sort as select * from dr$doc_idx$i order by token_text,token_type,token_first; /* $I */ alter table dr$doc_idx$i rename to dr$doc_idx$i_original; /* $I */ alter table dr$doc_idx$i_sort rename to dr$doc_idx$i; /* B*TREE */ create index dr$doc_idx$x_sort on dr$doc_idx$i (token_text,token_type,token_first,token_last,token_count); $I $I TOKEN_INFO BLOB DML R817 $I R816 R817 Disk Disk $I Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 30
Disk CPU Disk CPU 100 CPU 3 3 SQL 3 16CPU 1 2 3 1 2 3 Disk CPU 100% DB CPU imt alter index sync ctx_ddl.sync_index Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 31
R817 catalog index commit catalog index 1 2 2 A A SELECT... FROM... WHERE CONTAINS... B B 3 4 THE,THIS,ONLY Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 32
560M 100 1M $I $I $I BLOB LOB 1618M INROW LOB 1M LOB LOB 1M $R $R $R BLOB LOB 1M LOB 1M 14byte LOB 15M $N $N $N 1M $K $K 23M $X B*TREE $X 357M $I B*TREE $I $I BLOB 1 4000bytes BLOB INROW Disk I/O BLOB LOB 1M $R DOCID ROWID BLOB $I LOB $R $I LOB $R 14bytes $N $K $N $K ROWID DOCID Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 33
$X $I B*TREE 2018M 560M3.6 40G 120G 3 CONTEXT catalog index PREFIX INDEX ctx_output.start_log $R $R DATA BLOB LRU DB LRU URL URL URL URL Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 34
Disk CPU or or contains = = ctx_query.explain contains CREATE INDEX JOB_QUEUE_INTERVAL SGA Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 35
DB Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 36
Oracle Oracle8Oracle8iNet8