主页 / dba / 问题 / 330289
Accepted
deostroll
Asked: 2023-08-15 01:33:43 +0800 CST

获取与 Oracle 表索引相关的错误/误导性信息

  • 772
  1. 创建表如下：
CREATE TABLE "HOTEL3" 
   (    "NAMEX" VARCHAR2(4000 CHAR), 
    "CITY" VARCHAR2(4000 CHAR), 
    "PRICE" NUMBER, 
    "ID" VARCHAR2(50 BYTE) DEFAULT sys_guid()
   );
  1. 创建索引如下：
CREATE INDEX "IDX_HOTEL3_CITY_NAME" ON "HOTEL3" ("CITY" ASC, "NAMEX" DESC);
  1. 查询索引信息：
SELECT *
FROM   all_ind_columns ic
WHERE  ic.index_owner = 'FOO'
    AND ic.table_name = 'HOTEL3'
ORDER  BY ic.column_position;

我得到以下结果（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",

观察第二条记录；COLUMN_NAME是SYS_NC00005$代替NAMEX.

以下是一些旁注。在第 1 步之后，如果您查询，all_tab_cols您将获得所有 4 列。但是，在步骤 #2 之后，all_tab_cols现在有一个附加条目 - 与上面的查询结果显示的名称相同。这可能有些牵强；但ID已设置的字段DEFAULT可能就是为什么在步骤 #2 之后all_tab_cols有那个奇怪的条目。

那么我可以发出什么查询来获取正确的索引信息呢？

PS：我猜oracle服务器是19c。

oracle

2 个回答

  1. 这就是如何定义以降序定义的列上的索引。

    COLUMN_EXPRESSION您可以在视图的列中找到实际的表达式ALL_IND_EXPRESSIONS。

    • 2
  2. Best Answer

    当您定义NAMEX为 时，Oracle 使用您可以在（不是）中看到的内部隐藏列DESC创建基于函数的索引。索引位于该列上，该列具有系统生成的名称。将显示将传递到这些函数中的表达式，指示列名称。SYS_OP_DESCENDRAWall_tab_colsall_tab_columnsRAWall_ind_expressions

    任何时候你有任何类型的基于函数的索引，你都必须去*_ind_expressions获取实际的定义，并用这个表达式替换系统命名的列来表示你所期望的。

    现在这不能在 SQL 中完成，因为这需要使用 LONG，而 SQL 对此几乎无能为力。有一个解决方法，但是使用 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       
/

    现在您可以在 SQL 中使用该all_ind_expressions_ext表达式，并在您想要的任何函数中使用该表达式（CASE、REPLACE、WHERE子句、LISTAGG、 任何内容）。

    • 2

相关问题