imt_817_tuning_11_1822.PDF

Size: px

Start display at page:

Download "imt_817_tuning_11_1822.PDF"

せせらさわい
7 years ago
Views:

1 intermedia Text Tuning Guide Creation Date: Mar 12, 2001 Last Update: Apr 19, 2001 Version: 1.1

index R817 16 ORDER BY 16 17 catalog index R817 17

R817 28 30 Disk CPU 31 31 32 34 34 $R 34 URL 34 35 35

4 intermedia Text imt Oracle R815R817 imt imt WORD EXCEL URL DB M Oracle RDBMS intermedia Text imt OTN Japan 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

jp/) imt OTN Japan > > > Oracle8i > Oracle intermedia Oracle intermedia Text Oracle

9 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

BASIC_LEXER 1 1 JAPANESE_VGRAM_LEXER V-GRAM BASIC_LEXER R816 R816 1 BASIC_LEXER

10 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 Q&A imt SQL 1 CONTAINS 1 1 Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 10

ORDER BY SCORE(n) DESCEND FIRST ROWS 10 20 http://technet.oracle.co.

11 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 USER_DATASTORE USER_DATASTORE OUT OUT MULTI_COLUMN_DATASTORE Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 11

12 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

invisible visible 2 visible within invisible within

13 <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 invisible $I 1 TOKEN_TEXT ORACLE TOKEN_TYPE n n NAME Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 13

within NAME or within TITLE contains visible visible invisible 2 2 2 invisible $I 1

16 within catalog index R817 imt R817 ORDER BY catalog index B*TREE B*TREE DR$TOKEN DR$INFO CAMERA CAR TELEPHONE BOOK BOOK CAMERA CAR TELEPHONE CONTEXT ORDER BY Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 16

BOOK CAMERA CAR TELEPHONE 50 100 200 100............ CONTEXT ORDER BY 2 1.

17 ORDER BY SQL ORDER BY catalog index R817 R817 catalog index order by catalog index SQL JavaScript order by order by score(n) desc order by score(n) desc imt Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 17

by order by score(n) desc order by score(n) desc imt

18 ... 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

19 Web 1 2 imt R 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

20 $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

TOKEN_INFO PRIFIX INDEX T% token_text='t' and TOKEN_TYPE=6 1 PREFIX

22 $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

23 $I I/O Buffer Busy Wait $I $I $I TOKEN_TEXT BASIC_STORAGE 500 TOKEN_TEXT 1.6G 100M k $I 2 8k $I % 2G shared_pool_size log_buffer shared_pool_size log_buffer Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 23

24 1.6G 100M shared_pool_size=50m log_buffer=160k 2 shared_pool_size=250m log_buffer=15m % 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

25 Disk CPU create index 1. SELECT 2. $I $I INSERT $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

26 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 $X $I $I Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 26

29 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 TOKEN_TYPE ZONE Copyright 2001 Oracle Corporation Japan. All Rights Reserved. 29

30 $I $I 1 TOKEN_INFO $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

33 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 bytes 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

34 $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

37 Oracle Oracle8Oracle8iNet8

untitled

untitled Oracle Direct Seminar SQL Agenda SQL SQL SQL SQL 11g SQL FAQ Oracle Direct SQL Server MySQL PostgreSQL Access Application Server Oracle Database Oracle Developer/2000 Web Oracle Database