如何修复DRG-10599：列没有索引错误|Oracle 18g

ibrsph3r  于 2023-05-22  发布在  Oracle
关注(0)|答案(1)|浏览(122)

我有两个Oracle数据库（测试和生产）。两者都是从同一个第一数据库克隆的。我的查询在测试服务器上工作正常。但是生产服务器出现错误。

SELECT * FROM MY_VIEW_TABLE mliv
WHERE CATSEARCH (TITLE, 'law*', NULL) > 0

此查询返回的响应没有问题。但是生产服务器出现错误。

ORA-20000: Oracle Text error: DRG-10599: column is not indexed

我也尝试创建一个索引：

CREATE INDEX LACT_TITLE_TI ON MJ_LACTS(TITLE) 
       INDEXTYPE IS CTXSYS.CONTEXT;

然后我又试着运行查询。上面的错误。我还查看了现有的索引。一样的

SELECT *
FROM dba_ind_columns
WHERE TABLE_NAME = 'MJ_LACTS'

我也试过这些答案：

两台服务器具有相同的索引，两台服务器具有相同的版本。

更新1

我的表结构：

CREATE TABLE CR.MJ_LACTS (
  LACT_ID                  NUMBER(10, 0)       NOT NULL,
  INIT_LACT_ID             NUMBER(10, 0)       NOT NULL,
  EDITION_NUMBER           NUMBER(2, 0)        DEFAULT 0 NOT NULL,
  LANG_ID                  NUMBER(10, 0)       NOT NULL,
  STATUS_ID                NUMBER(10, 0)       NOT NULL,
  FORM_ID                  NUMBER(10, 0)       NOT NULL,
  TITLE                    VARCHAR2(4000 CHAR) NOT NULL,
  VALIDITY_ID              NUMBER(10, 0)       NOT NULL,
  STATE_ID                 NUMBER(10, 0)       NOT NULL,
  OWNER_ID                 VARCHAR2(30 CHAR)   NOT NULL,
  EFFECTIVE_DATE           DATE                NOT NULL,
  DISPLAY_NAME_FORM        VARCHAR2(4000 CHAR) NOT NULL,
  TOC_YN                   VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  MINOR_YN                 VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  ACTUALITY_YN             VARCHAR2(3 CHAR)    DEFAULT 'Y' NOT NULL,
  PUBLIC_YN                VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  PUBLISHED_YN             VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  CONDITIONAL_PUBLIC_YN    VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  ACCEPTANCE_DATE_RULE_YN  VARCHAR2(1 CHAR)    DEFAULT 'Y' NOT NULL,
  EFFECTIVE_DATE_RULE_YN   VARCHAR2(1 CHAR)    DEFAULT 'Y' NOT NULL,
  ELEMENT_TRANSITS_RULE_YN VARCHAR2(1 CHAR)    DEFAULT 'Y' NOT NULL,
  ELEMENT_EDITION_RULE_YN  VARCHAR2(1 CHAR)    DEFAULT 'Y' NOT NULL,
  ST_REG_NUMBER            VARCHAR2(8 CHAR),
  ST_REG_DATE              DATE,
  MJ_REG_NUMBER            VARCHAR2(15 CHAR),
  MJ_REG_DATE              DATE,
  OFFICIAL_SRC_ID          NUMBER(10, 0),
  OFFICIAL_SRC_DATE        DATE,
  OFFICIAL_SRC_NUMBER      VARCHAR2(30 CHAR),
  OFFICIAL_SRC_CLAUSE      VARCHAR2(30 CHAR),
  OFFICIAL_SRC_DATE_SGN    DATE,
  OBSOLETE_DATE            DATE,
  CANCEL_EVENT             VARCHAR2(2000 CHAR),
  QUANTITY_PAGES           NUMBER(6, 0),
  ACCEPTANCE_DATE          DATE,
  LACT_NUMBER              VARCHAR2(30 CHAR),
  LACT_CODE                VARCHAR2(100 CHAR),
  UPDATED_ON               DATE,
  UPDATED_BY               VARCHAR2(30 CHAR),
  REFERENCE                CLOB                DEFAULT EMPTY_CLOB(),
  OTHERS_INFO              CLOB                DEFAULT EMPTY_CLOB(),
  LACT_NUMBER_PREF         VARCHAR2(10 CHAR),
  LACT_NUMBER_SUFF         VARCHAR2(10 CHAR),
  INTERNAL_YN              VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  CONSTRAINT MJ_LACT_ACCEPTANCE_DATE_R_CHK CHECK (ACCEPTANCE_DATE_RULE_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_ACTUALITY_CHK CHECK (ACTUALITY_YN IN ('Y', 'N', 'Y/N', 'N/Y')),
  CONSTRAINT MJ_LACT_CONDITIONAL_PUBLIC_CHK CHECK (CONDITIONAL_PUBLIC_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_EDITION_NUM_CHK CHECK (EDITION_NUMBER >= -1),
  CONSTRAINT MJ_LACT_EFFECTIVE_DATE_R_CHK CHECK (EFFECTIVE_DATE_RULE_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_ELEMENT_EDITION_R_CHK CHECK (ELEMENT_EDITION_RULE_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_ELEMENT_TRANSITS_R_CHK CHECK (ELEMENT_TRANSITS_RULE_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_ID_CHK CHECK (LACT_ID > 0) DISABLE,
  CONSTRAINT MJ_LACT_MINOR_CHK CHECK (MINOR_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_PUBLIC_CHK CHECK (PUBLISHED_YN IN ('N', 'Y')),
  CONSTRAINT MJ_LACT_PUBLISHED_CHK CHECK (PUBLISHED_YN IN ('N', 'Y')),
  CONSTRAINT MJ_LACT_QUANTITY_PAGES_CHK CHECK (QUANTITY_PAGES IS NULL OR QUANTITY_PAGES > 0),
  CONSTRAINT MJ_LACT_ST_REG_NUM_CHK CHECK (ST_REG_NUMBER IS NULL OR LENGTH(ST_REG_NUMBER) = 8),
  CONSTRAINT MJ_LACT_TOC_CHK CHECK (TOC_YN IN ('N', 'Y')),
  CONSTRAINT MJ_LACT_PK PRIMARY KEY (LACT_ID) USING INDEX TABLESPACE MJ_INDX PCTFREE 2 STORAGE (INITIAL 80 K
                                                                                                MAXEXTENTS UNLIMITED),
  CONSTRAINT MJ_LACT_FORM_FK FOREIGN KEY (FORM_ID)
  REFERENCES CR.MJ_LACT_REL_SORTS_FORMS (REL_ID),
  CONSTRAINT MJ_LACT_INIT_FK FOREIGN KEY (INIT_LACT_ID)
  REFERENCES CR.MJ_LACTS (LACT_ID),
  CONSTRAINT MJ_LACT_LANG_FK FOREIGN KEY (LANG_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID),
  CONSTRAINT MJ_LACT_OFFICIAL_SRC_FK FOREIGN KEY (OFFICIAL_SRC_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID),
  CONSTRAINT MJ_LACT_STATE_FK FOREIGN KEY (STATE_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID),
  CONSTRAINT MJ_LACT_STATUS_FK FOREIGN KEY (STATUS_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID),
  CONSTRAINT MJ_LACT_VALIDITY_FK FOREIGN KEY (VALIDITY_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID)
)
TABLESPACE MJ_DATA
INITRANS 2
STORAGE (INITIAL 80 K
         MAXEXTENTS UNLIMITED)
LOGGING
ENABLE ROW MOVEMENT;

CREATE INDEX CR.MJ_LACT_ACCEPT_DATE_IDX ON CR.MJ_LACTS (ACCEPTANCE_DATE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_ACTUALITY_IDX ON CR.MJ_LACTS (ACTUALITY_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_CONDITIONAL_PUBLIC_IDX ON CR.MJ_LACTS (CONDITIONAL_PUBLIC_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_EDITION_NUM_IDX ON CR.MJ_LACTS (EDITION_NUMBER)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_EFFECTIVE_DATE_IDX ON CR.MJ_LACTS (EFFECTIVE_DATE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_ELEMENT_EDITION_R_IDX ON CR.MJ_LACTS (ELEMENT_EDITION_RULE_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_FORM_IDX ON CR.MJ_LACTS (FORM_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_INIT_IDX ON CR.MJ_LACTS (INIT_LACT_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_KEY_FBI ON CR.MJ_LACTS (SUBSTR("LACT_CODE", INSTR("LACT_CODE", '_') + 1))
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_LACT_NUMBER_IDX ON CR.MJ_LACTS (LACT_NUMBER)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_LANG_IDX ON CR.MJ_LACTS (LANG_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_MINOR_IDX ON CR.MJ_LACTS (MINOR_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_OBS_DATE_IDX ON CR.MJ_LACTS (OBSOLETE_DATE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_OFFICIAL_SRC_IDX ON CR.MJ_LACTS (OFFICIAL_SRC_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_OWNER_IDX ON CR.MJ_LACTS (OWNER_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_PUBLIC_IDX ON CR.MJ_LACTS (PUBLIC_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_PUBLISHED_IDX ON CR.MJ_LACTS (PUBLISHED_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_STATE_IDX ON CR.MJ_LACTS (STATE_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_STATUS_IDX ON CR.MJ_LACTS (STATUS_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_ST_REG_NUM_IDX ON CR.MJ_LACTS (ST_REG_NUMBER)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 80 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_TITLE_FBI ON CR.MJ_LACTS (UPPER("TITLE"))
TABLESPACE MJ_INDX
PCTFREE 5
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_TOC_IDX ON CR.MJ_LACTS (TOC_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE UNIQUE INDEX CR.MJ_LACT_UK2 ON CR.MJ_LACTS (LACT_CODE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_VALIDITY_IDX ON CR.MJ_LACTS (VALIDITY_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_MJ_REG_DATE_IDX ON CR.MJ_LACTS (MJ_REG_DATE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_MJ_REG_NUM_IDX ON CR.MJ_LACTS (MJ_REG_NUMBER)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

另一个有趣的事情是，前一段时间测试服务器也有类似错误的东西。但它工作了一段时间。我们没有做任何改变。我们重新启动了服务器，但没有任何变化。我该如何解决这个问题？

oracle

1条答案

按热度按时间
ndh0cuux

ndh0cuux1#

CATSEARCH的文档描述了它的用法：
使用CATSEARCH运算符搜索CTXCAT索引。

create table mj_lacts
as
select
  level as id,
  lpad(level, 100, '0') as title,
  lpad(level, 4000, '0') as large_col
from dual
connect by level < 10

insert into mj_lacts
values(1, 'some text and law word', 'a')
SELECT *
FROM Mj_lacts
WHERE CATSEARCH(TITLE, 'law*', NULL) > 0
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed

您需要创建CTXCAT索引，它可以工作。

CREATE INDEX LACT_TITLE_TI ON MJ_LACTS(TITLE) 
       INDEXTYPE IS ctxsys.CTXcat;
SELECT *
FROM Mj_lacts
WHERE CATSEARCH(TITLE, 'law*', NULL) > 0
ID职务大号_COL
1一些文本和法律词语a

fiddle
由于您没有提供关于这两个数据库的所有信息，因此很难解释为什么它在测试中有效而在生产中无效。但这对所有Oracle版本都是一样的。
您可以通过dbms_metadata包检索测试中的索引DDL：

select dbms_metadata.get_dependent_ddl(
  object_type => 'INDEX',
  base_object_name => 'MJ_LACTS'
  
)
from dual
DBMS_METADATA.GET_DEPENDENT_DDL（OBJECT_TYPE=>'INDEX'，BASE_OBJECT_NAME=>'MJ_LACTS'）
创建索引“FIDDLE_RMAAOVDFJEBDJXCLJUSU”.“LACT_TITLE_TI”ON“FIDDLE_RMAAOVDFJEBDJXCLJUSU”.“MJ_LACTs”（“TITLE”） 索引类型为“CTXSYS”.“CTXCAT”

fiddle

赞(0）分享  回复(0）举报  2023-05-22
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com