set linesize 500
col OWNER for a20
col TABLE_NAME for a20
col INDEX_NAME for a20
col CONSTRAINT_NAME for a20
col COLS for a30
define schema='SCOTT'
SELECT CASE
WHEN ind.index_name IS NOT NULL
THEN
CASE
WHEN ind.index_type IN ('BITMAP') THEN '** Bitmp idx **'
ELSE 'indexed'
END
ELSE
'** Check idx **'
END
checker,
ind.index_type,
cons.owner,
cons.table_name,
ind.index_name,
cons.constraint_name,
cons.cols
FROM ( SELECT c.owner,
c.table_name,
c.constraint_name,
LISTAGG (cc.column_name, ',')
WITHIN GROUP (ORDER BY cc.column_name)
cols
FROM dba_constraints c, dba_cons_columns cc
WHERE c.owner = cc.owner
AND c.owner = UPPER ('&schema')
AND c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
GROUP BY c.owner, c.table_name, c.constraint_name) cons
LEFT OUTER JOIN
( SELECT table_owner,
table_name,
index_name,
index_type,
cbr,
LISTAGG (column_name, ',') WITHIN GROUP (ORDER BY column_name)
cols
FROM ( SELECT ic.table_owner,
ic.table_name,
ic.index_name,
ic.column_name,
ic.column_position,
i.index_type,
CONNECT_BY_ROOT (ic.column_name) cbr
FROM dba_ind_columns ic, dba_indexes i
WHERE ic.table_owner = UPPER ('&schema')
AND ic.table_owner = i.table_owner
AND ic.table_name = i.table_name
AND ic.index_name = i.index_name
CONNECT BY PRIOR ic.column_position - 1 = ic.column_position
AND PRIOR ic.index_name = ic.index_name)
GROUP BY table_owner,
table_name,
index_name,
index_type,
cbr) ind
ON cons.cols = ind.cols
AND cons.table_name = ind.table_name
AND cons.owner = ind.table_owner
ORDER BY checker, cons.owner, cons.table_name;
CHECKER INDEX_TYPE OWNER TABLE_NAME INDEX_NAME CONSTRAINT_NAME COLS
--------------- --------------------------- -------------------- -------------------- -------------------- -------------------- ------------------------------
indexed NORMAL SCOTT EMP IX_DEPTNO FK_DEPTNO DEPTNO
Monday, 9 August 2010
Foreign Key Indexes
accept s_schema prompt 'Schema : '
declare
type r_data is record ( name varchar2( 30 ), definition varchar2( 1000 ), used varchar2(1) );
type t_data is table of r_data index by binary_integer;
li_counter number := 0;
cursor csr_tables is
select *
from dba_tables tbl
where owner = upper( '&&s_schema' )
and exists
( select 1
from dba_constraints
where table_name = tbl.table_name
and constraint_type = 'R' )
order by table_name;
cursor csr_fks( x_table_name in dba_tables.table_name%type ) is
select *
from dba_constraints
where owner = upper( '&&s_schema' )
and table_name = x_table_name
and constraint_type = 'R'
order by table_name;
cursor csr_fk_cols ( x_owner in dba_constraints.owner%type, x_table_name in dba_tables.table_name%type, x_cons_name in dba_constraints.constraint_name%type ) is
select * from dba_cons_columns
where owner = x_owner
and table_name = x_table_name
and constraint_name = x_cons_name
order by position;
cursor csr_tbl_inds ( x_t_owner in dba_indexes.table_owner%type, x_table_name in dba_tables.table_name%type ) is
select * from dba_indexes
where table_owner = x_t_owner
and table_name = x_table_name;
cursor csr_ind_cols( x_i_owner in dba_indexes.owner%type, x_index in dba_indexes.index_name%type ) is
select * from dba_ind_columns
where index_owner = x_i_owner
and index_name = x_index
order by column_position;
ls_fk_output varchar2( 2000 );
ls_ix_output varchar2( 2000 );
ls_fk_cols varchar2( 2000 );
ls_ix_cols varchar2( 2000 );
begin
for i_csr_tables in csr_tables loop
declare
fk_data t_data;
ix_data t_data;
lb_match_found boolean := false;
begin
dbms_output.put_line( 'Table : ' || i_csr_tables.table_name );
dbms_output.put_line( '--------' || rpad( '-', length( i_csr_tables.table_name ), '-' ) );
li_counter := 1;
for i_csr_fks in csr_fks( i_csr_tables.table_name ) loop
ls_fk_output := 'FK : ' || rpad( i_csr_fks.constraint_name, 30 );
ls_fk_cols := '';
fk_data( li_counter ).name := i_csr_fks.constraint_name;
for i_csr_fk_cols in csr_fk_cols( i_csr_fks.owner, i_csr_fks.table_name, i_csr_fks.constraint_name ) loop
ls_fk_output := ls_fk_output || ' ' || i_csr_fk_cols.column_name;
ls_fk_cols := ls_fk_cols || ' ' || i_csr_fk_cols.column_name;
end loop;
-- dbms_output.put_line( ls_fk_output );
fk_data( li_counter ).definition := ls_fk_cols;
li_counter := li_counter + 1;
end loop;
li_counter := 1;
for i_csr_tbl_inds in csr_tbl_inds( i_csr_tables.owner, i_csr_tables.table_name ) loop
ls_ix_output := 'IX : ' || rpad( i_csr_tbl_inds.index_name, 30 );
ls_ix_cols := '';
ix_data( li_counter ).name := i_csr_tbl_inds.index_name;
for i_csr_ind_cols in csr_ind_cols( i_csr_tbl_inds.owner, i_csr_tbl_inds.index_name ) loop
ls_ix_output := ls_ix_output || ' ' || i_csr_ind_cols.column_name;
ls_ix_cols := ls_ix_cols || ' ' || i_csr_ind_cols.column_name;
end loop;
-- dbms_output.put_line( ls_ix_output );
ix_data( li_counter ).definition := ls_ix_cols;
ix_data( li_counter ).used := 'N';
li_counter := li_counter + 1;
end loop;
for i in 1..fk_data.count() loop
lb_match_found := false;
dbms_output.put_line( 'FK : ' || rpad( fk_data( i ).name, 30 ) || chr(9) || fk_data( i ).definition );
for j in 1..ix_data.count() loop
if substr( ix_data( j ).definition, 1, length( fk_data( i ).definition ) ) = fk_data( i ).definition then
dbms_output.put_line( chr(9) || 'IX : ' || rpad( ix_data( j ).name, 30 ) || ix_data( j ).definition );
ix_data( j ).used := 'Y';
lb_match_found := true;
end if;
end loop;
if not lb_match_found then
dbms_output.put_line( '* No matching index *' );
end if;
dbms_output.put_line( ' ' );
end loop;
dbms_output.put_line( ' ' );
dbms_output.put_line( 'Other indexes :' );
for i in 1..ix_data.count() loop
if ix_data( i ).used = 'N' then
dbms_output.put_line( 'IX : ' || rpad( ix_data( i ).name, 30 ) || ix_data( i ).definition );
end if;
end loop;
end;
dbms_output.put_line( chr(9) );
end loop;
end;
/
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment