Orace Text Index-part7-Memory parameter

创建Text Index的时候,在将in-memory中的token表和mapping表的内容写到DR前缀的表之前,Text Index生成的TOKEN信息是cache在内存中的。从cache中写出到物理磁盘上后再次生成新的token信息和mapping信息保存在内存中,这样反复进行直到完成Text Index的创建,其中控制cache多少数据由memory相关参数来决定,所以在创建Text Index的时候分配的memory参数就很重要了。
Oracle建议将参数设置的越大越好,一方面可以加速创建的过程,另一方面可以减少最终INDEX的碎片化,但是太大了也会造成swapping,并且越大的设置也就相应的需要更多的资源来完成回滚。
关于Text Index memory的系统参数包括DEFAULT_INDEX_MEMORY和 MAX_INDEX_MEMORY,在CREATE TEXT INDEX的时候也可以指定memory参数来定义使用的内存大小,文中的测试部分使用指定memory参数大小的方式进行了对比。

Text Index的参数可以通过查询CTX_PARAMETERS视图来获得。

11.2.0.4环境的输出
SQL> SELECT PAR_NAME, PAR_VALUE / 1024 / 1024
  2    FROM CTXSYS.CTX_PARAMETERS
  3   WHERE PAR_NAME IN ('DEFAULT_INDEX_MEMORY', 'MAX_INDEX_MEMORY');

PAR_NAME                       PAR_VALUE/1024/1024
------------------------------ -------------------
DEFAULT_INDEX_MEMORY                            12       --12M
MAX_INDEX_MEMORY                              1024      --1024M

12.1.0.2和12.1.0.1的输出
SQL> SELECT PAR_NAME, PAR_VALUE / 1024 / 1024
  2    FROM CTXSYS.CTX_PARAMETERS
  3   WHERE PAR_NAME IN ('DEFAULT_INDEX_MEMORY', 'MAX_INDEX_MEMORY');
PAR_NAME                       PAR_VALUE/1024/1024
------------------------------ -------------------
DEFAULT_INDEX_MEMORY                            64        --64M
MAX_INDEX_MEMORY                            262144       --256G

增大MAX_INDEX_MEMORY参数和DEFAULT_INDEX_MEMORY可以通过使用ctxsys用户的ctx_adm包

begin
 ctxsys.ctx_adm.set_parameter('MAX_INDEX_MEMORY','1024M');
 ctxsys.ctx_adm.set_parameter('DEFAULT_INDEX_MEMORY','512M');
end;
/

测试下memory参数对Text Index的创建效率和对应情况下生成的TOKEN表大小的影响。
测试环境为Linux 11.2.0.4文件系统非归档模式,开启异步IO,CLOB内数据格式比较复杂,CLOB列的大小大概是4539.8M,创建Text Index开启并行度为4。
测试脚本:

SET TIMING ON
SET SERVEROUTPUT ON
DROP INDEX IDX1_T_LEXER;
CREATE INDEX  IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 64M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

DROP INDEX IDX1_T_LEXER;
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 128M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

DROP INDEX IDX1_T_LEXER;
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 256M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

DROP INDEX IDX1_T_LEXER;
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 512M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

DROP INDEX IDX1_T_LEXER;
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 1024M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

测试结果如下:

MEMORY参数设置 DR$IDX1_T_LEXER$I大小(MB) DR$IDX1_T_LEXER$X大小(MB) 创建使用时间(seconds)
64M 4781 1833 4443.189
128M 4582 1769 3594.388
256M 4439 1698 3017.122
512M 4295 1634 1932.463
1024M 4025 1563 2089.415

FOOTNOTE:
2017.2.27 增加被创建列的CLOB大小,使用ROUND(DBMS_LOB.GETLENGTH(DATA_ECHO_AREA) / 1024, 2)计算大小为4539.8 MB。

发表在 Domain Index, index, LOB | 发表评论

Oracle 12cR2 -Part1-table with json column overview

Oracle12开始支持JavaScript Object Notation (JSON) 数据,支持包括事务、索引、查询和视图等关系数据库的特性。
在Oracle 12.2版本下对json进行了很多增强,增加的特性请参考changes in Release 12.2
测试环境为Linux 86_64下的Oracle 12.2.0.1,测试脚本如下

drop table t_json purge;

create table t_json
(id varchar2(32) NOT NULL PRIMARY KEY,
data_echo_area CLOB
constraint data_echo_area_json check (data_echo_area is json)
);

insert into t_json(id,data_echo_area) values
(sys_guid(),
'{
    "employee_id": 100, 
    "first_name": "Steven", 
    "last_name": "King", 
    "email": "SKING",
    "job_id": "AD_PRES", 
    "salary": 24000.0, 
    "manager_id": null, 
    "department_id": 90,
    "address":{"city": "Oxford",
              "STATE_PROVINCE": "Oxford",
              "STREET_ADDRESS": "Magdalen Centre, The Oxford Science Park"
              }
   }'
);

insert into t_json(id,data_echo_area) values
(sys_guid(),
'{
    "employee_id": 101, 
    "first_name": "Neena", 
    "last_name": "Kochhar", 
    "email": "NKOCHHAR", 
    "job_id": "AD_VP", 
    "salary": 17000.0, 
    "manager_id": 100, 
    "department_id": 90,
    "address":{"city": "South Brunswick",
               "STATE_PROVINCE": "New Jersey",
               "STREET_ADDRESS": "2007 Zagora St"
               }
   }'
);
commit;

首先创建包含json类型的字段并添加check约束保证加载的数据都是符合json格式的数据,当然也可以不加这个约束,但是需要保证INSERT的数据都是完全符合json格式的数据。当存在约束并加载不符合json格式数据的时候会报ORA-2290错误。
生成json格式数据可以参考get json from table using python

查询json类型数据的时候使用dot标识,例如

SQL> col employee_id for a15
SQL> col LAST_NAME for a20
SQL> col DEPARTMENT_ID for a10
SQL> select t.id,t.data_echo_area.employee_id,t.data_echo_area.last_name,t.data_echo_area.department_id from T_JSON t;

ID                               EMPLOYEE_ID     LAST_NAME            DEPARTMENT
-------------------------------- --------------- -------------------- ----------
487897D071801468E0530B01A8C03972 100             King                 90
487897D071811468E0530B01A8C03972 101             Kochhar              90

当查询address这个not-scalar的数据的时候,返回的也是json格式的文本。

SQL> select t.id,t.data_echo_area.address from T_JSON t;

ID                               ADDRESS
-------------------------------- ------------------------------------------------------------
4878DB92BD00152AE0530B01A8C0C923 {"city":"Oxford","STATE_PROVINCE":"Oxford","STREET_ADDRESS":
                                 "Magdalen Centre, The Oxford Science Park"}

4878DB92BD01152AE0530B01A8C0C923 {"city":"South Brunswick","STATE_PROVINCE":"New Jersey","STR
                                 EET_ADDRESS":"2007 Zagora St"}

查询包含json数据类型的列的数据字典有DBA_JSON_COLUMNS, USER_JSON_COLUMNS和ALL_JSON_COLUMNS.
下面测试下json check constraint的几种形式和json相关视图的关系

SQL> col TABLE_NAME for a8
SQL> col COLUMN_NAME for a15
SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

OWNER TABLE_NA COLUMN_NAME     FORMAT    DATA_TYPE
----- -------- --------------- --------- -------------
JSON  T_JSON   DATA_ECHO_AREA  TEXT      CLOB


SQL> alter table T_JSON disable constraint data_echo_area_json;

Table altered.

SQL> insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

OWNER TABLE_NA COLUMN_NAME     FORMAT    DATA_TYPE
----- -------- --------------- --------- -------------
JSON  T_JSON   DATA_ECHO_AREA  TEXT      CLOB

SQL> 
SQL> 
SQL> alter table t_json drop constraint data_echo_area_json;

Table altered.

SQL> insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0));

1 row created.

SQL> commit; 

Commit complete.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

no rows selected

SQL> 
SQL> alter table t_json add constraint data_echo_area_json check (data_echo_area is json or length(data_echo_area) > 1000);

Table altered.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

no rows selected

SQL> alter table t_json drop constraint data_echo_area_json;

Table altered.

SQL> alter table t_json add constraint data_echo_area_json check (data_echo_area is json);
alter table t_json add constraint data_echo_area_json check (data_echo_area is json)
                                  *
ERROR at line 1:
ORA-02293: cannot validate (JSON.DATA_ECHO_AREA_JSON) - check constraint violated


SQL> alter table t_json add constraint data_echo_area_json check (data_echo_area is json) novalidate;

Table altered.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

no rows selected


SQL>  insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0));
 insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0))
*
ERROR at line 1:
ORA-02290: check constraint (JSON.DATA_ECHO_AREA_JSON) violated


SQL> insert into t_json(id,data_echo_area) values
  2  (sys_guid(),
  3  '{
  4      "employee_id": 101, 
  5      "first_name": "Neena", 
  6      "last_name": "Kochhar", 
  7      "email": "NKOCHHAR", 
  8      "job_id": "AD_VP", 
  9      "salary": 17000.0, 
 10      "manager_id": 100, 
 11      "department_id": 90,
 12      "address":{"city": "South Brunswick",
 13                 "STATE_PROVINCE": "New Jersey",
 14                 "STREET_ADDRESS": "2007 Zagora St"
 15                 }
 16     }'
 17  );

1 row created.

SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';
OWNER      TABLE_NA COLUMN_NAME     FORMAT    DATA_TYPE
---------- -------- --------------- --------- -------------
JSON       T_JSON   DATA_ECHO_AREA  TEXT      CLOB

当disable掉check约束的时候,此时可以从json的数据字典表查看到jsob字段的信息;
当drop掉check约束的时候,json的数据字典视图中不再显示t_json表的json字段信息;
当增加json约束和其他的非json约束的时候,json的数据字典中不显示json的字段信息;
在constaint为novalidate的时候,非json格式的数据无法INSERT到存在json格式的表中,且此时json相关的数据字典中没有json字段信息;
当INSERT到表中json格式的数据后,json的数据字典信息中记录了json字段的信息。

发表在 12c, json, LOB | 发表评论

ORA-28040: No matching authentication protocol in version 12.2.0.1 using 11g client

今天在体验Oracle 12.2.0.1的时候,遇到使用Oracle 11.2.0.1客户端连接数据库的时候报错ORA-28040 No Matching Authentication Protocol。
Oracle的版本是下载的exadata版本,文件为V839960-01.zip。
按照mos文档12c: ORA-28040 After Upgrade: No Matching Authentication Protocol (Doc ID 1957995.1)的描述,在数据库从11g升级到12c或者直接安装12c之后,从低版本的客户端或JDBC连接数据库后会报ORA-28040 No Matching Authentication Protocol错误。这个问题是由于在12c数据库中的默认设置造成的,涉及到的参数为:
SQLNET.ALLOWED_LOGON_VERSION_SERVER=n
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=n
查阅了一下12.1和12.2.文档的关于SQLNET的说明,在两个版本中默认都是11,也就是允许11g的客户端进行连接,但是我的测试过程中使用11g的客户端依然报错,等到3月15日发行Linux版本再测试下。
对参数ALLOWED_LOGON_VERSION_CLIENT的说明,12.1中描述为

(1) 12a for Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later;
(2) 12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended);
(3) 11 for Oracle Database 11g authentication protocols (default);
(4) 10 for Oracle Database 10g authentication protocols;
(5) 8 for Oracle8i authentication protocol

12.2中描述为

(1) 12a for Oracle Database 12c Release 1 (12.1.0.2) or later (strongest protection)
Note:Using this setting, the clients can only authenticate using a de-optimized password version. For example, the 12C password version;
(2) 12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (stronger protection)
Note:Using this setting, the clients can only authenticate using a password hash value that uses salt. For example, the 11G or 12C password versions;
(3) 11 for Oracle Database 11g authentication protocols (default);
(4) 10 for Oracle Database 10g authentication protocols;
(5) 8 for Oracle8i authentication protocol

不同版本客户端和服务器之间的支持矩阵说明请查阅MOS文档
Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)。

发表在 12c, sqlnet | 发表评论

Oracle Text Index-part6-preference and stoplist

前面的part1,part2,part3,part4,part5中都是使用的Oracle的默认的基础全文索引创建index和谓词查询,但是实际的数据可能会包含一下特殊字符或者间隔符等,比如我们想将”_”这个连接符跟两端的单词一起作为一个TOKEN的时候,就需要更高级一点的语法了。
本次实验中删除部分初始化数据中的中文记录所在的行,目前创建完INDEX后的TOKEN信息如下:

文中的环境信息为Oracle 11.2.0.4

SQL> select token_text from DR$IDX1_T_LEXER$I t;

TOKEN_TEXT
----------------------------------------------------------------
14895956868
515.124.4169
650.121.2019
BERRY
BLUE
CHINA
CITY
FIVE
FUZZY
INDEX
JOHN
LEO
MINUTES
NANJING
PETER
SHANGHAI
SMITH
TESTING
TEXT
TOMAS
UNDER

TOKEN信息中记录的单词都是被拆开的单词,但是对于其中的一些记录,比如Under_Smith,我们是希望作为一个独立的单词存在并被直接检索出来的,对于这种情况,普通创建Text Index的语法就不能很好的满足需求了,这个时候我们可以借助Oracle提供的CTX_DDL的其他子存储过程或者函数来更细致的定制索引的TOKEN拆分规则。

我们使用基本的lexer,也就是basic_lexer。首先创建一个preference叫做mylex来使用basic_lexer,然后设置一个attribute叫做printjoins来处理“_-”连接符。
语法如下

DROP INDEX IDX1_T_LEXER;
begin
ctx_ddl.create_preference('mylex', 'basic_lexer');
ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
end;
/
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX SYNC(ON COMMIT)');

执行过程输出
SQL> DROP INDEX IDX1_T_LEXER;

索引已删除。

已用时间:  00: 00: 00.03
SQL>
SQL>
SQL>
SQL> begin
  2  ctx_ddl.create_preference('mylex', 'basic_lexer');
  3  ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
  4  end;
  5  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.09
SQL>
SQL>
SQL> CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX SYNC(ON COMMIT)');

索引已创建。

已用时间:  00: 00: 01.14

此时的TOKEN信息

SQL> select token_text from DR$IDX1_T_LEXER$I t;

TOKEN_TEXT
----------------------------------------------------------------
14895956868
515.124.4169
650.121.2019
BERRY
BLUE
CHINA
CITY
FIVE
FUZZY
INDEX
JOHN
LEO
MINUTES
NANJING
PETER
SHANGHAI
SMITH
SMITH-SMITH   --此处’-’生效
TESTING
TEXT
TOMAS
UNDER_SMITH   --下划线作为TOKEN显示出来

此时查询Under_smith就可以直接查询到

SQL> select score(99), id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
  2   from T_LEXER
  3    where contains(DATA_ECHO_AREA, 'under_smith', 99) > 0
  4   order by score(99) desc;

 SCORE(99)         ID CREATE_DATE         AREA
---------- ---------- ------------------- ------------------------------
         7         12 2016-12-28 10:47:58 Under_Smith

已用时间:  00: 00: 00.02
SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dc94zdqu5g80n, child number 0
-------------------------------------
select score(99), id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
 from T_LEXER   where contains(DATA_ECHO_AREA, 'under_smith', 99) > 0
order by score(99) desc

Plan hash value: 3809827892

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |        |       |     5 (100)|       |       |       |       |          |
|   1 |  SORT ORDER BY                      |              |      1 |    98 |     5  (20)|       |       |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T_LEXER      |      1 |    98 |     4   (0)| ROWID | ROWID |       |       |          |
|*  3 |    DOMAIN INDEX                     | IDX1_T_LEXER |        |       |     4   (0)|       |       |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("DATA_ECHO_AREA",'under_smith',99)>0)

对于现实中的数据,很多单词或者字母没有实际的业务含义,这样的信息如果作为TOKEN提供搜索并没有意义并且会占据大量的空间,对查询的效率也会有一定的影响。

比如加载下面这样的数据

SQL> insert into t_lexer
  2    (id, create_date, DATA_ECHO_AREA)
  3    values
  4    (seq_t_lexer.nextval,
  5      sysdate,
  6      '--------test stoplist and dashes------
  7      select id,create_date,data_echo_area from t_lexer;');

已创建 1 行。

已用时间:  00: 00: 00.00
SQL> commit;

提交完成。
SQL> insert into t_lexer
  2    (id, create_date, DATA_ECHO_AREA)
  3    values
  4    (seq_t_lexer.nextval,
  5      sysdate,
  6      '-----------------------------');

已创建 1 行。

已用时间:  00: 00: 00.06

SQL> commit;
提交完成。

这个时候TOKEN信息表DR$IDX1_T_LEXER$I中记录的信息包含了SELECT,”—–”这样的信息,这样的TOKEN信息在业务场景下是没有意义的,这个时候我们可以借助与CTX_DDL中的create_stoplist过程来添加不需要创建TOKEN的字符串来达到过滤的目的。
在测试的过程中对于SQL文本中带”——”这种注释的情况,会发生与partjoin冲突造成添加的这项stoplist不生效。
下面是脚本中追加了stoplist内容

exec ctx_ddl.drop_preference ('mylex');
exec ctx_ddl.drop_stoplist('my_stoplist');
DROP INDEX IDX1_T_LEXER;
begin
ctx_ddl.create_preference('mylex', 'basic_lexer');
ctx_ddl.set_attribute('mylex', 'printjoins', '_');
ctx_ddl.create_stoplist('my_stoplist');
ctx_ddl.add_stopword('my_stoplist', '---');
ctx_ddl.add_stopword('my_stoplist', 'select');
ctx_ddl.add_stopword('my_stoplist', 'from');
ctx_ddl.add_stopword('my_stoplist', 'and');
ctx_ddl.add_stopword('my_stoplist', 'not');
ctx_ddl.add_stopword('my_stoplist', 'or');
end;
/
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST SYNC(ON COMMIT)');

此时查询TOKEN信息,这些STOPLIST将不会再显示。
此时表中的数据和TOKEN的信息

SQL> select id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA from T_LEXER t;

        ID CREATE_DATE         AREA
---------- ------------------- ------------------------------------------------------------------------------------------
        12 2016-12-28 10:47:58 Under_Smith
        11 2016-12-28 10:41:41 Smith Blue
         3 2016-12-28 10:32:03 Shanghai is a city in the CHINA
         5 2016-12-28 10:32:03 Nanjing is a city in the CHINA
         6 2016-12-28 10:32:03 515.124.4169
         7 2016-12-28 10:32:03 14895956868
         8 2016-12-28 10:32:03 Peter Smith-Smith
         9 2016-12-28 10:32:03 John Smith
        10 2016-12-28 10:32:03 650.121.2019
       103 2017-01-23 15:04:36 --------test stoplist and dashes------
                                   select id,create_date,data_echo_area from t_lexer;

       104 2017-01-23 15:16:11 -----------------------------
        41 2017-01-10 12:53:30 Berry Blue
        61 2017-01-10 13:14:14 and
        62 2017-01-10 13:15:45 tomas and leo
        63 2017-01-10 13:16:28 and or fuzzy not
        81 2017-01-22 14:45:45 Text Index Testing
        82 2017-01-22 15:02:39 Five Minutes

已选择17行。

已用时间:  00: 00: 00.06
SQL> select token_text from DR$IDX1_T_LEXER$I t;

TOKEN_TEXT
----------------------------------------------------------------
14895956868
515.124.4169
650.121.2019
A
BERRY
BLUE
CHINA
CITY
CREATE_DATE
DASHES
DATA_ECHO_AREA
FIVE
FUZZY
ID
IN
INDEX
IS
JOHN
LEO
MINUTES
NANJING
PETER
SHANGHAI
SMITH
STOPLIST
TEST
TESTING
TEXT
THE
TOMAS
T_LEXER
UNDER_SMITH

已选择32行。

更多的定制请查看Oracle关于的官方手册关于CTX_DDL部分的内容。

发表在 index, Index | 发表评论

Oracle Text Index-part5-sync index

Oracle的CTX_DDL package包含了很多存储过程和函数用于创建和管理preference、section group、stoplists以及索引维护等。当创建全文索引的表中有新记录INSERT后,索引是没有更新的,所以需要对新记录的索引进行维护。正如在orace-text-index-part4-contains-query文中使用的execute ctx_ddl.sync_index(‘IDX1_T_LEXER’)是手动进行同步索引操作。
如果不想使用这种方式,可以使用选择commit后自动同步或者选择一定的间隔时间进行同步。

COMMIT后自动刷新

SQL> DROP INDEX IDX1_T_LEXER;

索引已删除。

SQL> CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC(ON COMMIT)');

索引已创建。

SQL> insert into t_lexer
  2    (id, create_date, DATA_ECHO_AREA)
  3  values
  4    (seq_t_lexer.nextval, sysdate, 'Text Index Testing');

已创建 1 行。

SQL> select score(99), id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
  2  from T_LEXER
  3  where contains(DATA_ECHO_AREA, 'testing', 99) > 0
  4  order by score(99) desc;

未选定行

SQL> COMMIT;

提交完成。

SQL> col area for a30
SQL> select score(99), id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
  2  from T_LEXER
  3  where contains(DATA_ECHO_AREA, 'testing', 99) > 0
  4  order by score(99) desc;

 SCORE(99)         ID CREATE_DATE         AREA
---------- ---------- ------------------- ------------------------------
         7         81 2017-01-22 14:45:45 Text Index Testing

间隔时间刷新

COMMIT后进行刷新的方式对于较少的UPDATE和INSERT操作来说影响相对不大,但是对于大表并且DML操作量也比较大的情况,这种方式就不理想了。因为这种方式会造成索引的碎片化而降低性能,比较好的方式是间隔一定的时间进行同步索引,比如每5分钟进行一次索引的同步。

SQL> DROP INDEX IDX1_T_LEXER;

索引已删除。

SQL> CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC(EVERY SYSDATE+5/1440)');

索引已创建。

SQL> insert into t_lexer
  2    (id, create_date, DATA_ECHO_AREA)
  3  values
  4    (seq_t_lexer.nextval, sysdate, 'Five Minutes');

已创建 1 行。

SQL>
SQL> COMMIT;

提交完成。

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-01-22 15:02:52

SQL> select score(99), id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
  2  from T_LEXER
  3  where contains(DATA_ECHO_AREA, 'Five', 99) > 0
  4  order by score(99) desc;

未选定行

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-01-22 15:07:53

SQL> select score(99), id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
  2  from T_LEXER
  3  where contains(DATA_ECHO_AREA, 'Five', 99) > 0
  4  order by score(99) desc;

 SCORE(99)         ID CREATE_DATE         AREA
---------- ---------- ------------------- ------------------------------
         7         82 2017-01-22 15:02:39 Five Minutes

存在索引的同步就需要进行索引的优化处理,对于全文索引,Oracle提供了OPTIMIZE_INDEX存储过程进行优化,文档中提到可以可以基于fast, full, rebuild, token, or token-type这5种模式进行索引的优化。
本文引用文档中的前三种:FAST,FULL和REBUILD。
“Fast mode compacts data but does not remove rows.

Full mode compacts data and removes rows.

Optimize in rebuild mode rebuilds the $I table (the inverted list table) in its entirety. Rebuilding an index is often significantly faster than performing a full optimization, and is more likely to result in smaller indexes, especially if the index is heavily fragmented.
Rebuild optimization creates a more compact copy of the $I table, and then switches the original $I table and the copy. The rebuild operation will therefore require enough space to store the copy as well as the original. (If redo logging is enabled, then additional space is required in the redo log as well.) At the end of the rebuild operation, the original $I table is dropped, and the space can be reused.”

针对文档中的描述,FAST方式相对后面2种来说,效果不是很大,FULL模式和REBUILD模式的好处比较明显,但是考虑到实际数据量的千差万别,可能需要我们针对实际的情况定制自己的模式选择和时间调度策略。比如每5分钟进行同步一次,业务相对空闲期(比如夜里)进行FULL模式或者REBUILD模式,亦或者夜里FULL模式,每7天进行REBUILD或者半个月REBUILD,当然方案的选择都是基于实际的数据量和业务的情况。

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

已更新 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM DR$IDX1_T_LEXER$N;

 NLT_DOCID N
---------- -
         5 U

SQL> execute ctx_ddl.optimize_index('IDX1_T_LEXER', 'FULL');

PL/SQL 过程已成功完成。

SQL> SELECT * FROM DR$IDX1_T_LEXER$N;   --remove rows

未选定行

对于调度方式的选择可以采用Oracle的scheduler进行。

发表在 index, Index | 发表评论

get partition definition using function

获取所有RANGE分区的表分区的定义,脚本运行环境为Oracle 11.2.0.4,Oracle 10g去掉INTERVAL字段。

--创建函数
create or replace function long_to_varchar(p_table_owner    in all_tab_partitions.table_owner%type,
                                          p_table_name     in all_tab_partitions.table_name%type,
                                          p_partition_name in all_tab_partitions.partition_name%type)
  return varchar2 as
  l_high_value LONG;
begin
  select high_value
    into l_high_value
    from all_tab_partitions
   where table_owner = p_table_owner
     and table_name = p_table_name
     and partition_name = p_partition_name;
  return substr(l_high_value, 1, 4000);
end;
/

--查询并保存excel
select t.table_name,
       t.partition_name,
       t.tablespace_name,
       t.num_rows,
       t.blocks,
       t.interval,
       long_to_varchar(user, t.table_name, t.partition_name) partition_def
  from user_tab_partitions t ;

--删除函数
drop function long_to_varchar;

发表在 PLSQL | 发表评论

insert log data into clob using plsql

今天开发的一个同事想用PLSQL插入一些数据到CLOB字段,字符串较长,报错PLS-00172: string literal too long。
SQL有4000字节长度的限制,PLSQL中有32k的限制。
所以需要使用dbms_lob包来处理完成。

样例代码如下

declare
  avc_str1 clob;
  avc_str2 varchar2(32000);
begin
  avc_str1 := 'abcdefg';
  avc_str2 := 'text length <= 32000';
  dbms_lob.append(avc_str1, avc_str2);
  avc_str2 := 'text length <= 32000';
  dbms_lob.append(avc_str1, avc_str2);
  insert into test_wcl values ('a', avc_str1, 0, '');
  commit;
end;
/
发表在 PLSQL | 发表评论