Início / dba / Perguntas / 330289
Accepted
deostroll
Asked: 2023-08-15 01:33:43 +0800 CST

Obtendo informações erradas/enganosas relacionadas ao índice da tabela oracle

  • 772
  1. Criando uma tabela da seguinte forma:
CREATE TABLE "HOTEL3" 
   (    "NAMEX" VARCHAR2(4000 CHAR), 
    "CITY" VARCHAR2(4000 CHAR), 
    "PRICE" NUMBER, 
    "ID" VARCHAR2(50 BYTE) DEFAULT sys_guid()
   );
  1. Criando um índice da seguinte forma:
CREATE INDEX "IDX_HOTEL3_CITY_NAME" ON "HOTEL3" ("CITY" ASC, "NAMEX" DESC);
  1. Consultando as informações do índice:
SELECT *
FROM   all_ind_columns ic
WHERE  ic.index_owner = 'FOO'
    AND ic.table_name = 'HOTEL3'
ORDER  BY ic.column_position;

Estou obtendo o resultado abaixo (csv)

"INDEX_OWNER","INDEX_NAME","TABLE_OWNER","TABLE_NAME","COLUMN_NAME","COLUMN_POSITION","COLUMN_LENGTH","CHAR_LENGTH","DESCEND","COLLATED_COLUMN_ID"
"FOO","IDX_HOTEL3_CITY_NAME","FOO","HOTEL3","CITY",1,4000,4000,"ASC",
"FOO","IDX_HOTEL3_CITY_NAME","FOO","HOTEL3","SYS_NC00005$",2,2000,0,"DESC",

Observe o segundo registro; COLUMN_NAMEé SYS_NC00005$em vez de NAMEX.

Aqui estão algumas notas laterais. Após a etapa 1, se você consultar, all_tab_colsobterá todas as 4 colunas. No entanto, após a etapa 2, all_tab_colsagora há uma entrada adicional - o mesmo nome mostrado acima do resultado da consulta. Isso pode ser exagerado; mas o IDcampo definido DEFAULTpode ser o motivo, após a etapa 2, all_tab_colster essa entrada estranha.

Então, qual consulta posso emitir para obter as informações de índice corretas?

PS: o servidor oracle é 19c, eu acho.

oracle

2 respostas

  1. É assim que um índice em uma coluna definida com ordem decrescente é definido.

    Você pode encontrar a expressão real na COLUMN_EXPRESSIONcoluna da ALL_IND_EXPRESSIONSexibição.

    • 2
  2. Best Answer

    Quando você definiu NAMEXcomo DESC, o Oracle cria um índice baseado em função usando SYS_OP_DESCENDuma RAWcoluna oculta internamente que você pode ver em all_tab_cols(não em all_tab_columns). O índice está nesta RAWcoluna que tem um nome gerado pelo sistema. all_ind_expressionsmostrará a expressão que será passada para essas funções, indicando o nome da coluna.

    Sempre que você tiver qualquer tipo de índice baseado em função, você deve ir *_ind_expressionspara obter a definição real e trocar a coluna nomeada pelo sistema por esta expressão para representar o que você espera.

    Agora, isso não pode ser feito em SQL porque isso exigiria trabalhar com LONGs, com os quais o SQL pode fazer muito pouco. Existe uma solução alternativa, porém usando PL/SQL:

    CREATE OR REPLACE TYPE all_ind_expressions_rectype IS OBJECT (
COLUMN_EXPRESSION   VARCHAR2(4000),
COLUMN_POSITION NUMBER
)
/
CREATE OR REPLACE TYPE all_ind_expressions_tabtype IS TABLE OF all_ind_expressions_rectype
/
CREATE OR REPLACE FUNCTION f_all_ind_expressions_ext(in_index_owner IN varchar2, in_index_name IN varchar2)
  RETURN all_ind_expressions_tabtype PIPELINED
AS
BEGIN
  FOR rec_expr IN (SELECT *
                     FROM all_ind_expressions
                    WHERE index_owner = in_index_owner
                      AND index_name = in_index_name)
  LOOP
    DECLARE
      var_column_expression varchar2(4000);
    BEGIN
      var_column_expression := rec_expr.column_expression;
      PIPE ROW(all_ind_expressions_rectype(var_column_expression,rec_expr.column_position));
    END;
  END LOOP;
  
  RETURN;
END;  
/
CREATE OR REPLACE VIEW all_ind_expressions_ext
AS
SELECT i.owner index_owner,
       i.index_name,
       i.table_owner,
       i.table_name,
       x.*
  FROM all_indexes i
       CROSS APPLY TABLE(f_all_ind_expressions_ext(i.owner,i.index_name)) x
 ORDER BY table_owner,table_name,index_owner,index_name,column_position       
/

    Agora você pode usar all_ind_expressions_extem seu SQL e usar a expressão em qualquer função que desejar ( CASE, REPLACE, WHEREcláusula, LISTAGG, qualquer coisa).

    • 2

relate perguntas