Initialization of experimental data:
Copy Code code as follows:
CREATE TABLE T as select * from Hr.employees;
CREATE index inx_t1 on T (Employee_id,first_name Desc,last_name);
CREATE index inx_t2 on T (job_id,hire_date);
Displays information for all indexes in the table.
Log on as DBA
Copy Code code as follows:
set Linesize 300;
Set pagesize 100;
Col C1 format A20;
Col C2 format A20;
Col c3 format A20;
Col c4 format A20;
Col c5 format A20;
Col index_name format A20;
Select Index_name,
Max Decode (column_position,1,column_name| |, ' | | column_length| | ', ' | | Descend,null) C1,
Max (decode column_position,2,column_name| |, ' | | column_length| | ', ' | | Descend,null) c2,
Max (decode column_position,3,column_name| |, ' | | column_length| | ', ' | | Descend,null) C3,
Max (decode column_position,4,column_name| |, ' | | column_length| | ', ' | | Descend,null) C4,
Max (decode column_position,5,column_name| |, ' | | column_length| | ', ' | | DESCEND,NULL)) C5
from (
Select Index_name,column_name,column_length,column_position,descend
from dba_in D_columns
where table_owner= ' Lihuilin '
and table_name= ' T '
Order by index_name,column_position
) Group BY Index_name;
Log on as a normal user
Copy Code code as follows:
Set Linesize 300;
Set pagesize 100;
Col C1 format A20;
Col C2 format A20;
Col C3 format A20;
Col c4 format A20;
Col c5 format A20;
Col index_name format A20;
Select Index_name,
Max (decode column_position,1,column_name| |, ' | | column_length| | ', ' | | Descend,null)) C1,
Max (decode column_position,2,column_name| |, ' | | column_length| | ', ' | | Descend,null)) C2,
Max (decode column_position,3,column_name| |, ' | | column_length| | ', ' | | Descend,null)) C3,
Max (decode column_position,4,column_name| |, ' | | column_length| | ', ' | | Descend,null)) C4,
Max (decode column_position,5,column_name| |, ' | | column_length| | ', ' | | DESCEND,NULL)) C5
From (
Select Index_name,column_name,column_length,column_position,descend
From User_ind_columns
where Table_name= ' T '
ORDER BY Index_name,column_position
) Group by Index_name;
You can see, however, that indexed fields created in reverse order are named in Sys.
Oracle looks at the indexed field created by this reverse-backwards as a function index.
Its information is saved in the User_ind_expressions view.
The Column_expression field type of the User_ind_expressions view is a long type.
The Wang version can solve this problem.
Copy Code code as follows:
CREATE OR REPLACE FUNCTION Long_2_varchar (
P_index_name in User_ind_expressions.index_name%type,
P_table_name in User_ind_expressions.table_name%type,
P_column_position in User_ind_expressions.table_name%type)
Return VARCHAR2
As
L_column_expression LONG;
BEGIN
SELECT column_expression
Into L_column_expression
From User_ind_expressions
WHERE index_name = P_index_name
and table_name = P_table_name
and column_position = p_column_position;
Return SUBSTR (l_column_expression, 1, 4000);
End;
/
Copy Code code as follows:
Set Linesize 300;
Set pagesize 100;
Col C1 format A20;
Col C2 format A20;
Col C3 format A20;
Col c4 format A20;
Col c5 format A20;
Col index_name format A20;
SELECT Index_name,
MAX (DECODE (column_position, 1, column_name | | ' ' || Descend, NULL))
C1,
MAX (DECODE (column_position, 2, COLUMN_NAME | | ' ' || Descend, NULL))
C2,
MAX (DECODE (column_position, 3, COLUMN_NAME | | ' ' || Descend, NULL))
C3,
MAX (DECODE (Column_position, 4, COLUMN_NAME | | ' ' || Descend, NULL))
C4,
MAX (DECODE (column_position, 5, COLUMN_NAME | | ' ' || Descend, NULL))
C5
From (SELECT A.index_name,
REPLACE (
DECODE (
Descend,
' DESC ', Long_2_varchar (B.index_name,
B.table_name,
B.column_position),
A.column_name),
'"',
'')
COLUMN_NAME,
A.column_length,
A.column_position,
Descend
From User_ind_columns A
Left JOIN
User_ind_expressions b
On a.index_name = B.index_name
and a.table_name = B.table_name
WHERE a.table_name = ' T '
Order by index_name, column_position)
GROUP by Index_name;
