Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: TEXT INDEX error

    We've been using text indexes more & more, and everything works well for the most part. We came across a new error today though.

    Code:
    SELECT   *
      FROM   (SELECT   INSURED_NAME AS NAME,
                       INSURED_ADDR1 AS ADDR1,
                       INSURED_ADDR2 AS ADDR2,
                       INSURED_CITY AS CITY,
                       INSURED_STATE AS STATE,
                       INSURED_ZIP AS ZIP,
                       INSURED_FEIN AS FEIN,
                       'INSURED' AS TYPE
                FROM   POLICY_JN
              UNION ALL
              SELECT   EMPLOYER_NAME AS NAME,
                       EMPLOYER_ADDR1 AS ADDR1,
                       EMPLOYER_ADDR2 AS ADDR2,
                       EMPLOYER_CITY AS CITY,
                       EMPLOYER_STATE AS STATE,
                       EMPLOYER_ZIP AS ZIP,
                       EMPLOYER_FEIN AS FEIN,
                       'COVERED EMP' AS TYPE
                FROM   EMP_LOC_JN) IV
     WHERE       contains (NAME, 'ARBY''S 1393') > 0
             AND CITY LIKE 'ALBANY%'
             AND STATE = 'NY'
             AND FEIN = 123456789;
    
    Error at line 0
    ORA-20000: Oracle Text error:
    DRG-50857: oracle error in drdmdcnt
    ORA-20000: Oracle Text error:
    DRG-10502: index 1979 does not exist
    ORA-06512: at "CTXSYS.DRUE", line 160
    ORA-06512: at "CTXSYS.DRVXMD", line 120
    ORA-06512: at line 1
    Now, if I drop the index on "INSURED_FEIN" the first table in the UNION query ("POLICY_JN"), the query runs. It also has no problem if I comment out the "AND FEIN = 123456789". There's something about the combination of the the normal index and the text index in the same WHERE clause.

    Everything works fine also if I unravel the UNION ALL query and separate these into their own statements, with their own WHERE clause.

    If you follow up on the "DRG-10502: index % does not exist" error, the suggested course to correct your SQL is

    Code:
    DRG-10502: index string does not exist. 
    Cause: the specified index does not exist or you do not have access to it. 
    Action: Specify an existing index.
    We store all of our text indexes in another schema, away from our tables. I don't know that it matters though, because there's the same general situation going on in the second SQL in the UNION, with an index on the EMPLOYER_FEIN in that table.

    --=Chuck
    Last edited by chuck_forbes; 09-22-09 at 13:08.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    I tried moving the TEXT INDEX into the same schema as the FEIN indexes and the TABLES, but I get the same error (different index number, but same overall error):

    Code:
    ORA-20000: Oracle Text error:
    DRG-50857: oracle error in drdmdcnt
    ORA-20000: Oracle Text error:
    DRG-10502: index 2015 does not exist
    ORA-06512: at "CTXSYS.DRUE", line 160
    ORA-06512: at "CTXSYS.DRVXMD", line 120
    ORA-06512: at line 1

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I think there's something solvable going on here, I'm just not seeing it. There's another column on the POLICY_JN table called INSURER_FEIN (as opposed to INSURED_FEIN), same NUMBER(9) datatype. It is also indexed, although it's in a composite index. When I rewrite the query to include it instead, the query runs.

    Code:
    SELECT   *
      FROM   (SELECT   INSURED_NAME AS NAME,
                       INSURED_ADDR1 AS ADDR1,
                       INSURED_ADDR2 AS ADDR2,
                       INSURED_CITY AS CITY,
                       INSURED_STATE AS STATE,
                       INSURED_ZIP AS ZIP,
                       INSURER_FEIN AS FEIN,  -- changed to INSURER from INSURED
                       'INSURED' AS TYPE
                FROM   POLICY_JN
              UNION ALL
              SELECT   EMPLOYER_NAME AS NAME,
                       EMPLOYER_ADDR1 AS ADDR1,
                       EMPLOYER_ADDR2 AS ADDR2,
                       EMPLOYER_CITY AS CITY,
                       EMPLOYER_STATE AS STATE,
                       EMPLOYER_ZIP AS ZIP,
                       EMPLOYER_FEIN AS FEIN,
                       'COVERED EMP' AS TYPE
                FROM   EMP_LOC_JN) IV
     WHERE       contains (NAME, 'ARBY''S 1393') > 0
             AND CITY LIKE 'ALBANY%'
             AND STATE = 'NY'
             AND FEIN = 123456789;
    The query plan for both queries is the same. When the FEIN is included (INSURER or INSURED_FEIN), the index on that column is used.

    I tried juxtaposing those two FEIN columns, putting them each in the other's original index. Turns out, the DRG-10502 error follows the INSURED_FEIN no matter where you place it's index.

    The only difference I see between those two columns is that one allows NULLs, the other doesn't:

    Code:
    CREATE TABLE POLICY_JN
    (...
      INSURER_FEIN                  NUMBER(9)       NOT NULL,
      INSURED_FEIN                  NUMBER(9),
    ...);
    Again, though, what's weird is that this all works just fine if you do something like

    Code:
    SELECT   INSURED_NAME AS NAME,
             INSURED_ADDR1 AS ADDR1,
             INSURED_ADDR2 AS ADDR2,
             INSURED_CITY AS CITY,
             INSURED_STATE AS STATE,
             INSURED_ZIP AS ZIP,
             INSURED_FEIN AS FEIN,
             'INSURED' AS TYPE
      FROM   POLICY_JN
     WHERE   contains (INSURED_NAME, 'ARBY''S 1393') > 0
             AND INSURED_CITY LIKE 'ALBANY%'
             AND INSURED_STATE = 'NY'
             AND INSURED_FEIN = 123456789
    UNION ALL
    SELECT   EMPLOYER_NAME AS NAME,
             EMPLOYER_ADDR1 AS ADDR1,
             EMPLOYER_ADDR2 AS ADDR2,
             EMPLOYER_CITY AS CITY,
             EMPLOYER_STATE AS STATE,
             EMPLOYER_ZIP AS ZIP,
             EMPLOYER_FEIN AS FEIN,
             'COVERED EMP' AS TYPE
      FROM   COVERED_EMP_LOC_JN
     WHERE       contains (EMPLOYER_NAME, 'ARBY''S 1393') > 0
             AND EMPLOYER_CITY LIKE 'ALBANY%'
             AND EMPLOYER_STATE = 'NY'
             AND EMPLOYER_FEIN = 123456789

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Looks like the following SQL construction works as well. We're going this route. We converted the UNION query into a VIEW ...

    Code:
    SELECT   NAME,
             ADDR1,
             ADDR2,
             VIR_ADDR,
             CITY,
             STATE,
             ZIP,
             FEIN
      FROM   INSCV_V v1
     WHERE   EXISTS
                (SELECT   1
                   FROM   INSCV_V v2
                  WHERE   v1.NAME = v2.NAME
                          AND contains (v2.name, 'ARBY''S 1393') > 0)
             AND CITY LIKE 'ALBANY%'
             AND STATE = 'NY'
             AND FEIN = 123456789;
    
    ORDER BY NAME,
    VIR_ADDR,
    CITY,
    STATE,
    FEIN
    Taking away the EXISTS and placing the CONTAINS( ) directly within the WHERE clause still produced the error.

    --=Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •