Xml 

Oracle:


, .

. , . . .

Oracle Text Oracle , , . . Oracle Text CTXSYS.CONTEXT CONTAINS.

Oracle , 90- . 7.3 : .

Oracle (SQL*TextRetrieval -> Text Server -> Oracle ConText -> Oracle Text) . 9 Oracle, , , . DBCA dr0inst. sql ( 9 ) catctx.sql ( 10) [ORACLE_HOME]/ctx/admin.

Oracle , Oracle Ultra Search, Content Management ( iFS) XML DB.

Oracle , (domain index), . Oracle Text :

- CTXSYS.CONTEXT - ;

- CTXSYS.CTXCAT - (- );

- CTXSYS.CTXRULE - , .

CTXSYS.CONTEXT. , ( , ).

:

> CONNECT / AS SYSDBA

SYS> CREATE USER ctx IDENTIFIED BY ctx DEFAULT TABLESPACE users;

SYS> GRANT connect, resource, ctxapp TO ctx;

SYS> CONNECT ctx/ctx

CTX>

CONNECT RESOURCE CTX , ; CTXAPP , CTX CTXSYS. :

CREATE TABLE docs ( doc_id NUMBER ( 10 ), vc2doc VARCHAR2 ( 4000 ) );

INSERT INTO docs VALUES ( 1, 'Mary had a little lamb' );

INSERT INTO docs VALUES ( 2, 'Twinkle, twinkle little star' );

INSERT INTO docs VALUES ( 3, 'This Lamb is my lamb' );

CREATE INDEX docs_vc2doc_idx ON docs ( vc2doc ) INDEXTYPE IS ctxsys.context;

: DOCS_VC2DOC_IDX - , (domain); - CTXSYS.CONTEXT, . ( ), .

CTXSYS.CONTEXT CONTAINS. Oracle SQL . CONTAINS , , (relevance).

. :

SELECT CONTAINS ( vc2doc, '&1' ) AS score, vc2doc FROM docs.

SAVE simplequestion REPLACE

COLUMN vc2doc FORMAT A60

SET VERIFY OFF

:

CTX> @simplequestion 'star'

SCORE VC2DOC

---------- ------------------------------------------------------------

0 Mary had a little lamb

4 Twinkle, twinkle little star

0 This Lamb is my lamb

CTX> @simplequestion 'little'

SCORE VC2DOC

---------- ------------------------------------------------------------

4 Mary had a little lamb

4 Twinkle, twinkle little star

0 This Lamb is my lamb

CTX> @simplequestion 'twinkle'

SCORE VC2DOC

---------- ------------------------------------------------------------

0 Mary had a little lamb

9 Twinkle, twinkle little star

0 This Lamb is my lamb

CTX> @simplequestion 'lamb'

SCORE VC2DOC

---------- ------------------------------------------------------------

4 Mary had a little lamb

0 Twinkle, twinkle little star

7 This Lamb is my lamb

CTX> @simplequestion 'mary AND lamb'

SCORE VC2DOC

---------- ------------------------------------------------------------

4 Mary had a little lamb

0 Twinkle, twinkle little star

0 This Lamb is my lamb

CTX> @simplequestion 'mary lamb'

SCORE VC2DOC

---------- ------------------------------------------------------------

0 Mary had a little lamb

0 Twinkle, twinkle little star

0 This Lamb is my lamb

, . , . Oracle (http://www.ra.pae.osd.mil/doclib/servlet/HowWorksSalton). , , 0 100.

, , CONTAINS :

@simplequestion 'MARY AND LAMB'

@simplequestion 'MaRy AnD lAmB'

@simplequestion '%le'

@simplequestion 'lamb NOT mary'

@simplequestion 'NEAR ((lamb, mary) ,3)'

@simplequestion 'NEAR ((lamb, mary) ,2)'

@simplequestion 'mary ACCUM lamb'

@simplequestion 'mary ACCUM little'

@simplequestion 'mary ACCUM little lamb'

@simplequestion 'lamb OR little'

( CONTAINS) Oracle.

CONTAINS SELECT . - () SCORE, , CONTAINS, . CONTAINS , , SCORE CONTAINS SQL. ( ) . :

CTX> SELECT SCORE ( 1 ), vc2doc

2 FROM docs

3 WHERE CONTAINS ( vc2doc, 'lamb', 1 ) > 0

4 ORDER BY SCORE ( 1 ) DESC

5 ;

SCORE(1) VC2DOC

---------- ------------------------------------------------

7 This Lamb is my lamb

4 Mary had a little lamb

CTX> SELECT SCORE ( 1 ), SCORE ( 15 ), vc2doc

2 FROM docs

3 WHERE

4 CONTAINS ( vc2doc, 'lamb', 1 ) > 0

5 OR CONTAINS ( vc2doc, 'lamb AND mary', 15 ) > 0

6 ORDER BY

7 SCORE ( 15 ) DESC

8 ;

SCORE(1) SCORE(15) VC2DOC

---------- ---------- ----------------------------------

4 4 Mary had a little lamb

7 0 This Lamb is my lamb

Oracle Text . , <, >. , .

B-, , . :

CTX> COLUMN object_name FORMAT A30

CTX> COLUMN object_type FORMAT A30

CTX> COLUMN segment_name FORMAT A30

CTX> COLUMN segment_type FORMAT A30

CTX> SELECT object_name, object_type FROM user_objects ORDER BY 2, 1;

OBJECT_NAME OBJECT_TYPE

------------------------------ ------------------------------

DOCS_VC2DOC_IDX INDEX

DR$DOCS_VC2DOC_IDX$X INDEX

SYS_IOT_TOP_51619 INDEX

SYS_IOT_TOP_51624 INDEX

SYS_LOB0000051616C00006$$ LOB

SYS_LOB0000051621C00002$$ LOB

DOCS TABLE

DR$DOCS_VC2DOC_IDX$I TABLE

DR$DOCS_VC2DOC_IDX$K TABLE

DR$DOCS_VC2DOC_IDX$N TABLE

DR$DOCS_VC2DOC_IDX$R TABLE

CTX> SELECT segment_name, segment_type FROM user_segments ORDER BY 2, 1;

SEGMENT_NAME SEGMENT_TYPE

------------------------------ ------------------------------

DR$DOCS_VC2DOC_IDX$X INDEX

SYS_IOT_TOP_51619 INDEX

SYS_IOT_TOP_51624 INDEX

SYS_IL0000051616C00006$$ LOBINDEX

SYS_IL0000051621C00002$$ LOBINDEX

SYS_LOB0000051616C00006$$ LOBSEGMENT

SYS_LOB0000051621C00002$$ LOBSEGMENT

DOCS TABLE

DR$DOCS_VC2DOC_IDX$I TABLE

DR$DOCS_VC2DOC_IDX$R TABLE

, DOCS, . , CREATE INDEX docs_vc2doc_idx ... DOCS_VC2DOC_IDX, :

- DR$DOCS_VC2DOC_IDX$X;

- , DR$DOCS_VC2DOC_IDX$I DR$DOCS_VC2DOC_IDX$R, BLOB ( LOB-);

- - , DR$DOCS_VC2DOC_IDX$K DR$DOCS_VC2DOC_IDX$N.

, DR$DOCS_VC2DOC_IDX$I ( BLOB; ):

CTX> SELECT token_text, token_count FROM dr$docs_vc2doc_idx$i;

TOKEN_TEXT TOKEN_COUNT

------------------------------------------------- -----------

LAMB 2

LITTLE 2

MARY 1

STAR 1

TWINKLE1

, . :

CTX> UPDATE docs SET vc2doc = 'This Land is my land' WHERE doc_id = 3;

1 row updated.

CTX> COMMIT;

Commit complete.

CTX> SELECT token_text, token_count FROM dr$docs_vc2doc_idx$i;

TOKEN_TEXT TOKEN_COUNT

------------------------------------------------- -----------

LAMB2

LITTLE2

MARY1

STAR 1

TWINKLE1

, :

CTX> SELECT pnd_index_name, pnd_rowid FROM ctx_user_pending;

PND_INDEX_NAME PND_ROWID

---------------------------- ------------------

DOCS_VC2DOC_IDX AAAMm2AAEAAAABAAAC

CTX> EXECUTE ctx_ddl.sync_index ( 'docs_vc2doc_idx' )

PL/SQL procedure successfully completed.

CTX> /

no rows selected

CTX> SELECT token_text, token_count FROM dr$docs_vc2doc_idx$i;

TOKEN_TEXT TOKEN_COUNT

------------------------------------------------- -----------

LAMB 2

LAND1

LITTLE 2

MARY 1

STAR 1

TWINKLE 1

( ALTER INDEX, Oracle ).

- .

( ) . . EXPLAIN PLAN , () :

CTX> EXPLAIN PLAN FOR

2 SELECT * FROM docs

3 WHERE CONTAINS ( vc2doc, 'twinkle AND star' ) > 0;

Explained.

CTX> SELECT * FROM TABLE ( dbms_xplan.display );

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------

Plan hash value: 3477406887

--------------------------------------------------------------------------------------

/ Id / Operation / Name /Rows/Bytes/Cost (%CPU)/Time /

--------------------------------------------------------------------------------------

/ 0/ SELECT STATEMENT / / 1/ 2027/ 4 (0)/00:00:01/

/ 1/ TABLE ACCESS BY INDEX ROWID/ DOCS / 1/ 2027/ 4 (0)/00:00:01/

/* 2/ DOMAIN INDEX / DOCS_VC2DOC_IDX / / / 4 (0)/00:00:01/

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("CTXSYS"."CONTAINS"("VC2DOC",'twinkle AND star')>0)

Note

-----

- dynamic sampling used for this statement

( 10, ).

( SQL) , PLAN_TABLE. :

CREATE GLOBAL TEMPORARY TABLE ctx_explain (

explain_id VARCHAR2 ( 30 )

, id NUMBER

, parent_id NUMBER

, operation VARCHAR2 ( 30 )

, options VARCHAR2 ( 30 )

, object_name VARCHAR2 ( 64 )

, position NUMBER

, cardinality NUMBER

)

ON COMMIT PRESERVE ROWS

;

CTX_QUERY:

BEGIN

ctx_query.explain (

index_name => 'docs_vc2doc_idx'

, text_query => 'twinkle AND star'

, explain_table => 'ctx_explain'

, explain_id => 'twinkle star'

);

END;

/

CTX_EXPLAIN :

CTX> SELECT

2 explain_id

3 , id

4 , parent_id

5 , operation

6 , options

7 , object_name

8 , position

9 FROM

10 ctx_explain

11 ORDER BY

12 id

13 /

EXPLAIN_ID ID PARENT_ID OPERATION OPTIONS OBJECT_NAME POSITION

-------------- --- ---------- --------- ------- -------------- ----------

twinkle star 1 0 AND 1

twinkle star 2 1 WORD TWINKLE1

twinkle star 3 1 WORD STAR2



 

 FactoryTalk Tools Rockwell Software.
  : .
  Geronimo: OpenEJB 3.0 ().
  Ajax: 8. XML ().
 pureXML DB2 9: XML-? ().


 Xml 

2022 Team.Furia.Ru.
.