Oracle Text Index-part3

Part2中提到,当全文索引创建完成之后,Oracle会在user_tables和user_index下创建一些以DR为前缀的数据库对象,并且后缀也有不同的命名规则。
$I的表保存的是列的TOKEN信息,表示在collection中TOKEN被发现了多少次以及TOKEN被发现的$DOCUMENT的范围。

SQL> col CREATE_DATE for a25
SQL> col area for a18
SQL> select id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA from T_LEXER where contains(DATA_ECHO_AREA,'SMITH') > 0;

ID              CREATE_DATE            AREA
---------- ------------------------- ------------------
8             2016-12-29 20:43:35     Peter Smith-Smith
9             2016-12-29 20:43:35     John Smith
11            2016-12-29 20:43:35     Smith Blue
12            2016-12-29 20:43:35     Under_Smith

包含smith的记录ID为8,9,11,12,从DR$IDX1_T_LEXER$I中获得到的TOKEN起始为8-12,TOKEN_COUNT有4行,下面的输出记录了这些信息。

SQL> COL TOKEN_TEXT FOR A10
SQL> COL TOKEN_LASR FOR A10
SQL> select token_text,TOKEN_TYPE,TOKEN_FIRST,TOKEN_LAST,TOKEN_COUNT from DR$IDX1_T_LEXER$I WHERE TOKEN_TEXT = 'SMITH';

TOKEN_TEXT TOKEN_TYPE TOKEN_FIRST TOKEN_LAST TOKEN_COUNT
---------- ---------- ----------- ---------- -----------
SMITH          0          8         12           4

每一个document都会有一行存放在$K表中,映射CONTEXT document指向原表的ROWID加速表的访问。

SQL> SELECT k.docid, p.id,p.CREATE_DATE FROM DR$IDX1_T_LEXER$K k, T_LEXER p WHERE k.textkey = p.rowid;

     DOCID         ID CREATE_DATE
---------- ---------- -------------------
         1          1 2016-12-29 20:43:35
         2          2 2016-12-29 20:43:35
         3          3 2016-12-29 20:43:35
         4          4 2016-12-29 20:43:35
         5          5 2016-12-29 20:43:35
         6          6 2016-12-29 20:43:35
         7          7 2016-12-29 20:43:35
         8          8 2016-12-29 20:43:35
         9          9 2016-12-29 20:43:35
        10         10 2016-12-29 20:43:35
        11         11 2016-12-29 20:43:35
        12         12 2016-12-29 20:43:35

$N表是用于跟踪变化的document记录的negative list。$R表是ROWID表。

--查看下DR$IDX1_T_LEXER$N的内容,为空。
SQL> SELECT * FROM DR$IDX1_T_LEXER$N;
no rows selected

--删除T_LEXER的一条记录-
SQL> DELETE FROM T_LEXER WHERE ID = 8;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM DR$IDX1_T_LEXER$N;

 NLT_DOCID N
---------- -
         8 U

--更新一条记录
SQL> SELECT ID,DBMS_LOB.SUBSTR(DATA_ECHO_AREA) AREA FROM T_LEXER WHERE ID = 3;

        ID AREA
---------- -----------------------------------
         3 California is a state in the US

SQL> UPDATE T_LEXER SET DATA_ECHO_AREA = 'Shanghai is a city in the CHINA' WHERE ID = 3;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM DR$IDX1_T_LEXER$N;

 NLT_DOCID N
---------- -
         3 U
         8 U

SQL> SELECT ID,DBMS_LOB.SUBSTR(DATA_ECHO_AREA) AREA FROM T_LEXER WHERE ID = 3;

        ID AREA
---------- -----------------------------------
         3 Shanghai is a city in the CHINA

DR$IDX1_T_LEXER$N最初内容为空,当对全文索引所在的表进行删除和更新操作之后,这个$R表会记录下变更的信息。
当Text Index创建完成之后,除了从user_tables和User_indexes中查询到一些信息之外,还可以通过CTXSYS用户下的字典获取到关于全文索引的信息,下面的输出是CTXSYS.CTX_INDEXES、CTXSYS.CTX_INDEX_OBJECTS和CTXSYS.CTX_INDEX_VALUES中包含IDX1_T_LEXER的信息,包含了STOPLIST,FILTER,WORDLIST等信息。

SQL> COL IDX_TEXT_NAME FOR A25
SQL>
SQL> SELECT IDX_TABLE_OWNER,IDX_TABLE,IDX_TEXT_NAME FROM CTXSYS.CTX_INDEXES WHERE IDX_NAME = 'IDX1_T_LEXER';

IDX_TABLE_OWNER IDX_TABLE IDX_TEXT_NAME
------------------------------ ------------------------------ -------------------------
LEXER T_LEXER DATA_ECHO_AREA

SQL> SELECT * FROM CTXSYS.CTX_INDEX_OBJECTS T WHERE IXO_INDEX_NAME = 'IDX1_T_LEXER';

IXO_INDEX_OWNER      IXO_INDEX_NAME                 IXO_CLASS                      IXO_OBJECT
----------------- ------------------- ------------------------------ ------------------------------
LEXER                IDX1_T_LEXER                   DATASTORE                      DIRECT_DATASTORE
LEXER                IDX1_T_LEXER                   FILTER                         NULL_FILTER
LEXER                IDX1_T_LEXER                   SECTION_GROUP                  NULL_SECTION_GROUP
LEXER                IDX1_T_LEXER                   LEXER                          BASIC_LEXER
LEXER                IDX1_T_LEXER                   WORDLIST                       BASIC_WORDLIST
LEXER                IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST
LEXER                IDX1_T_LEXER                   STORAGE                        BASIC_STORAGE

SQL> SELECT * FROM CTXSYS.CTX_INDEX_VALUES T WHERE IXV_INDEX_NAME = 'IDX1_T_LEXER';

IXV_INDEX_OWNER                IXV_INDEX_NAME                 IXV_CLASS                      IXV_OBJECT                     IXV_ATTRIBUTE                  IXV_VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -----------
LEXER                          IDX1_T_LEXER                   WORDLIST                       BASIC_WORDLIST                 STEMMER                        ENGLISH
LEXER                          IDX1_T_LEXER                   WORDLIST                       BASIC_WORDLIST                 FUZZY_MATCH                    GENERIC
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      yours
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      your
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      you
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      yet
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      would
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      with
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      will
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      why
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      whose
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      who
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      while
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      which
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      whether
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      where
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      when
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      what
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      were
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      we
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      was
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      very
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      ve
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      until
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      too
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      to
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      thus
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      through
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      though
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      those
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      this
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      they
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      these
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      therefore
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      there
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      then
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      them
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      their
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      the
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      that
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      than
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      t
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      such
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      still
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      some
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      so
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      since
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      should
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      she
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      shall
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      s
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      ours
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      our
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      or
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      onto
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      only
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      one
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      on
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      of
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      not
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      nor
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      non
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      no
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      my
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      might
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      me
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      ll
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      just
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      its
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      it
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      is
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      into
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      in
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      if
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      i
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      however
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      how
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      his
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      him
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      hers
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      here
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      her
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      he
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      having
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      have
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      has
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      had
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      from
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      for
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      either
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      does
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      do
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      did
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      d
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      could
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      can
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      by
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      but
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      both
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      been
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      because
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      be
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      at
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      as
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      are
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      any
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      and
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      an
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      although
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      also
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      almost
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      all
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      a
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      Ms
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      Mrs
LEXER                          IDX1_T_LEXER                   STOPLIST                       BASIC_STOPLIST                 STOP_WORD                      Mr
LEXER                          IDX1_T_LEXER                   STORAGE                        BASIC_STORAGE                  R_TABLE_CLAUSE                 lob (data) store as (cache)
LEXER                          IDX1_T_LEXER                   STORAGE                        BASIC_STORAGE                  I_INDEX_CLAUSE                 compress 2
此条目发表在index分类目录。将固定链接加入收藏夹。

One Response to Oracle Text Index-part3

  1. Pingback引用通告: Oracle Text Index-part6-preference and stoplist | LEO Notes

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s