Oracle查询(PROC)未给出所需结果

Question

在以下查询(存储过程内部(oracle。。

SELECT
index_name,
index_description,
ltrim(sys_connect_by_path(index_keys,','),',') as Index_Keys,
include_cols,
index_filter,
data_compression,
allow_page_locks,
table_name,
index_type
from
(
Select t.*,
row_number() over (partition by INDEX_NAME order by COLUMN_POSITION) as rn
from
(
SELECT
di.index_name AS "INDEX_NAME",
CASE
WHEN di.index_type = 'IOT - TOP' THEN 'clustered'
ELSE 'nonclustered'
END
||
CASE
WHEN di.uniqueness = 'UNIQUE' AND
substr (
di.index_name,
1,
3
) = 'PK_' THEN ', unique, primary key'
WHEN di.uniqueness = 'UNIQUE' THEN ', unique'
END
||
CASE
WHEN di.uniqueness = 'NONUNIQUE' THEN ''
ELSE ''
END
||
' located on PRIMARY' AS "INDEX_DESCRIPTION",
ci.column_name AS "INDEX_KEYS",
NULL AS "INCLUDE_COLS",
NULL AS "INDEX_FILTER",
NULL AS "DATA_COMPRESSION",
NULL AS "ALLOW_PAGE_LOCKS",
di.table_name AS "TABLE_NAME",
CASE
WHEN di.index_type = 'IOT - TOP' THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END
AS "INDEX_TYPE",ci.column_position
FROM
dba_indexes di
JOIN dba_ind_columns ci
ON ci.index_owner   = di.owner AND
ci.index_name    = di.index_name
WHERE
di.table_owner = '****' AND
di.table_name = '******' AND
partitioned = 'NO'
UNION ALL
SELECT
di.index_name AS "INDEX_NAME",
CASE
WHEN di.index_type = 'IOT - TOP' THEN 'clustered'
ELSE 'nonclustered'
END
||
CASE
WHEN di.uniqueness = 'UNIQUE' AND
substr (
di.index_name,
1,
3
) = 'PK_' THEN ', unique, primary key'
WHEN di.uniqueness = 'UNIQUE' THEN ', unique'
END
||
CASE
WHEN di.uniqueness = 'NONUNIQUE' THEN ''
ELSE ''
END
||
' located on PRIMARY' AS "INDEX_DESCRIPTION",
ci.column_name AS "INDEX_KEYS",
NULL AS "INCLUDE_COLS",
NULL AS "INDEX_FILTER",
NULL AS "DATA_COMPRESSION",
NULL AS "ALLOW_PAGE_LOCKS",
di.table_name AS "TABLE_NAME",
CASE
WHEN di.index_type = 'IOT - TOP' THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END
AS "INDEX_TYPE",ci.column_position
FROM
dba_indexes di
JOIN dba_ind_partitions dip
ON dip.index_owner   = di.owner AND
dip.index_name    = di.index_name
JOIN dba_ind_columns ci
ON ci.index_owner   = di.owner AND
ci.index_name    = di.index_name
WHERE
di.table_owner = '*******' AND
di.table_name = '******' AND
di.partitioned = 'YES' AND
dip.composite != 'YES'
UNION ALL
SELECT
di.index_name AS "INDEX_NAME",
CASE
WHEN di.index_type = 'IOT - TOP' THEN 'clustered'
ELSE 'nonclustered'
END
||
CASE
WHEN di.uniqueness = 'UNIQUE' AND
substr (
di.index_name,
1,
3
) = 'PK_' THEN ', unique, primary key'
WHEN di.uniqueness = 'UNIQUE' THEN ', unique'
END
||
CASE
WHEN di.uniqueness = 'NONUNIQUE' THEN ''
ELSE ''
END
||
' located on PRIMARY' AS "INDEX_DESCRIPTION",
ci.column_name AS "INDEX_KEYS",
NULL AS "INCLUDE_COLS",
NULL AS "INDEX_FILTER",
NULL AS "DATA_COMPRESSION",
NULL AS "ALLOW_PAGE_LOCKS",
di.table_name AS "TABLE_NAME",
CASE
WHEN di.index_type = 'IOT - TOP' THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END
AS "INDEX_TYPE",ci.column_position
FROM
dba_indexes di
JOIN dba_ind_partitions dip
ON dip.index_owner   = di.owner AND
dip.index_name    = di.index_name
JOIN dba_ind_subpartitions dis
ON dis.index_owner      = di.owner AND
dis.index_name       = di.index_name AND
dis.partition_name   = dip.partition_name
JOIN dba_ind_columns ci
ON ci.index_owner   = di.owner AND
ci.index_name    = di.index_name
WHERE
di.table_owner = '***' AND
di.table_name = '*****' AND
di.partitioned = 'YES' AND
dip.composite = 'YES' ) t
)
where connect_by_isleaf = 1
connect by index_name  = prior index_name
and rn = prior rn+1
start with rn =1 ;

它给出以下输出。。

[在此输入图像描述][1]

AI_TESTORACLE_TEMP_27977和AI_TESTORACLE_TEMP_27978的索引键当前显示函数索引的column_name，即dba_ind_columns中的SYS_NC00023$和SYS_NC00024$；TestInt，lower(TestShortString("；以及"；TestSmallInt，UPPER(TestShortString("；。。。。这是列表达式，可以从dbacolexpressions视图派生。。

Name                                                                    Null?    Type
----------------------------------------------------------------------- -------- -----------------------------
INDEX_OWNER                                                             NOT NULL VARCHAR2(128)
INDEX_NAME                                                              NOT NULL VARCHAR2(128)
TABLE_OWNER                                                             NOT NULL VARCHAR2(128)
TABLE_NAME                                                              NOT NULL VARCHAR2(128)
COLUMN_EXPRESSION                                                                LONG
COLUMN_POSITION                                                         NOT NULL NUMBER
SQL> desc dba_ind_Columns;
Name                                                                    Null?    Type
----------------------------------------------------------------------- -------- ----------------------
INDEX_OWNER                                                             NOT NULL VARCHAR2(128)
INDEX_NAME                                                              NOT NULL VARCHAR2(128)
TABLE_OWNER                                                             NOT NULL VARCHAR2(128)
TABLE_NAME                                                              NOT NULL VARCHAR2(128)
COLUMN_NAME                                                                      VARCHAR2(4000)
COLUMN_POSITION                                                         NOT NULL NUMBER
COLUMN_LENGTH                                                           NOT NULL NUMBER
CHAR_LENGTH                                                                      NUMBER
DESCEND                                                                          VARCHAR2(4)
COLLATED_COLUMN_ID                                                               NUMBER

只需要在这个查询中做一些小的更改，就可以将列表达式而不是列名输出为index_keys，仅用于函数索引(这有点棘手(。。在其他列中休息无变化。。寻求这里所有专家的帮助。。

INDEX_TYPE作为基于函数的NORMAL或NORMAL可以从DBA_INDEXES 获取

SQL> select column_Expression from dba_ind_expressions where table_name='TESTORACLE_TEMP' AND TABLE_OWNER='ROLLOUT';
COLUMN_EXPRESSION
--------------------------------------------------------------------------------
LOWER("TESTSHORTSTRING")
UPPER("TESTSHORTSTRING")
SQL> select column_name from dba_ind_columns where table_owner='ROLLOUT' and table_name='TESTORACLE_TEMP';
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------
KEYTESTORACLE
TESTTINYINT
TESTSMALLINT
TESTUNIQUEIDENTIFIER
TESTINT
SYS_NC00023$
TESTSMALLINT
SYS_NC00024$
UPDDATE
9 rows selected.

[！[在此输入图像描述][2]][2]

添加index_keys列(当前输出和预期([1] ：https://i.stack.imgur.com/fsg0z.png[2] ：https://i.stack.imgur.com/SeewU.jpg

Answer 1

使用user_ind_expressions进行左联接。如果不为null，则从该表中获取值，否则从user_index_columns中获取值。然后您可以进行分层查询，或者listagg()会更好。

一个问题。Column_expression是长数据类型的(至少我看到它是这样的形式(，所以我做了一个简单的函数将它转换为varchar(你可以改进它，请阅读适当的文章，例如在asktom网站上(。

create or replace function 
get_expr(i_tn in varchar2, i_in in varchar2, i_cp in number) return varchar2 as
l_data long;
begin
select column_expression into l_data from user_ind_expressions
where table_name = i_tn and index_name = i_in and column_position = i_cp;
return substr(l_data, 1, 4000);
end;

这是我的测试数据和查询：

create table emp(id primary key, fname, lname, dept) as (
select 707, 'Pete', 'Griffin', 'Sales' from dual);
create index idx_emp1 on emp(lower(dept));
create index idx_emp2 on emp(upper(lname), upper(fname));

和查询：

select index_name,
ltrim(sys_connect_by_path(index_keys,','),',') as Index_Keys
from (
select t.*, row_number() over (partition by index_name order by column_position) as rn
from (     
select index_name, column_name, column_position, 
nvl(get_expr(table_name, index_name, column_position), column_name) as index_keys
from user_indexes di
join user_ind_columns ci using (table_name, index_name)
left join user_ind_expressions using (table_name, index_name, column_position)
where table_name = 'EMP') t )
where connect_by_isleaf = 1
connect by index_name  = prior index_name and rn = prior rn+1
start with rn = 1

结果：

INDEX_NAME      INDEX_KEYS
--------------- --------------------------------
IDX_EMP1        LOWER("DEPT")
IDX_EMP2        UPPER("LNAME"),UPPER("FNAME")
SYS_C00148175   ID

正如您所看到的，ID取自user_ind_columns，其他密钥来自user_ind_expressions。