Oracle Text error: DRG-10599: the colum is not indexed-CodePudding

SELECT
    c.ID AS "REFERENCIA",
    c.CIF_NIF AS "CIF",
    c.NUMERO_COMERCIO AS "COMERCIO",
    CASE
        WHEN c.RIESGO_REPUTACIONAL = 1 THEN 'Anexo 2B'
        WHEN c.RIESGO_REPUTACIONAL = 3 THEN 'Anexo 2A'
    END AS "RIESGO REPUTACIONAL",
    tp.NOMBRE AS "TECNOLOGIA",
    c.FECHA_INSERCION AS "FECHA CONTRATO",
    CASE
        WHEN av.FECHA_INSERCION IS NOT NULL THEN TO_CHAR(av.FECHA_INSERCION, 'YYYY-MM-DD HH24:MI:SS')
        ELSE 'No se ha validado todavía'
    END AS "FECHA VALIDACION",
    c.DENOMINACION_COMERCIAL_TICKET AS "DENOMINACION COMERCIAL",
    c.COD_SECTOR_ACTIVIDAD_VISAMC AS "CAI",
    (SELECT c2.CODIGO FROM CNAE c2 WHERE CONTAINS(c2.LISTA_CAIS, c.COD_SECTOR_ACTIVIDAD_VISAMC, 1) > 0)
FROM
    CONTRATO c
LEFT JOIN ANRI_VALORACION av ON
    av.ID_CONTRATO = c.ID
INNER JOIN PRODUCTO p ON
    c.ID_PRODUCTO = p.ID
INNER JOIN REL_PRODUCTO_TIPOPRODUCTO rpt ON
    rpt.ID_PRODUCTO = p.ID
INNER JOIN TIPO_PRODUCTO tp ON
    tp.ID = rpt.ID_TIPO_PRODUCTO
WHERE
    c.RIESGO_REPUTACIONAL IN (1, 3)
    AND c.ESTADO = 'CONTRATO'
    AND TO_DATE(c.FECHA_INSERCION) >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1)
ORDER BY c.FECHA_INSERCION,c.ID ASC

So here, it's failing my query and it's throwing an error like the column is not indexed in the line (SELECT c2.CODIGO FROM CNAE c2 WHERE CONTAINS(c2.LISTA_CAIS, c.COD_SECTOR_ACTIVIDAD_VISAMC, 1) > 0) . My query is in Oracle, what can be the problem?

CodePudding user response：

"CONTAINS(c2.LISTA_CAIS" means there has to be a domain index defined on the LISTA_CAIS column.

Query ALL_INDEXES for TABLE_NAME = 'CNAE' (assuming that is a table, not a view or synonym) to find the indexes for that table. This also tells you the index type which will tell if you it is a Text (domain) index.

Then query ALL_IND_COLUMNS for those indexes to see what columns that the index is defined for.

CodePudding user response：

Looks like you'll have to create index as

create index i1_cnae_lista on cnae (lista_cais) indextype is ctxsys.context;

On the other hand, maybe you don't want to use Oracle Text. Is contains intentional, or ...?

Because, if you don't need Oracle Text, maybe something like this would do (i.e. the INSTR function):

SELECT c2.CODIGO FROM CNAE c2 
WHERE INSTR (c2.LISTA_CAIS, c.COD_SECTOR_ACTIVIDAD_VISAMC) > 0