153 lines
5.5 KiB
MySQL
153 lines
5.5 KiB
MySQL
@@header
|
|
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Similar Tables indexes
|
|
* Parameters : 1 - Owner (Use % as wild card, \ as ESCAPE)
|
|
* 2 - TableName (Use % as wild card, \ as ESCAPE)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 25-Sep-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
/*
|
|
|
|
PROMPT *************************
|
|
PROMPT Similar Indexes
|
|
PROMPT *************************
|
|
|
|
column column_name format a30
|
|
break on TABLE_OWNER on TABLE_NAME skip 1
|
|
BREAK ON INDEX_NAME
|
|
|
|
select TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
|
|
FROM ALL_IND_COLUMNS
|
|
WHERE (TABLE_OWNER, TABLE_NAME, INDEX_NAME ) IN
|
|
(SELECT TABLE_OWNER
|
|
, TABLE_NAME
|
|
, INDEX_NAME
|
|
FROM ALL_IND_COLUMNS ind
|
|
WHERE COLUMN_POSITION = 1
|
|
AND TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','XDB','WMSYS','SYSMAN')
|
|
AND (TABLE_OWNER, TABLE_NAME, COLUMN_NAME) IN (SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME
|
|
FROM (SELECT TABLE_OWNER , TABLE_NAME, COLUMN_NAME,
|
|
COUNT (*) TCOUNT
|
|
FROM ALL_IND_COLUMNS
|
|
WHERE COLUMN_POSITION = 1
|
|
AND TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','XDB','WMSYS','SYSMAN')
|
|
GROUP BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME
|
|
HAVING COUNT (*) > 1
|
|
)
|
|
)
|
|
)
|
|
ORDER BY 1,2,3,5
|
|
/
|
|
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
|
|
set term off
|
|
SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 END) "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name'))
|
|
END "_table_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT *************************
|
|
PROMPT Similar Indexes
|
|
PROMPT *************************
|
|
|
|
COLUMN owner HEADING "Index Owner" FORMAT a20
|
|
COLUMN table_name HEADING "Table Name" FORMAT a40
|
|
COLUMN index_owner HEADING "Index Owner" FORMAT a20
|
|
COLUMN index_name HEADING "Index Name" FORMAT a40
|
|
COLUMN index_type HEADING "Index Type" FORMAT a10
|
|
COLUMN status HEADING "Status" FORMAT a8
|
|
COLUMN visibility HEADING "Visible" FORMAT a10
|
|
COLUMN uniqueness HEADING "Uniqueness" FORMAT a15
|
|
COLUMN Columns HEADING "Index columns" FORMAT a90
|
|
|
|
BREAK ON TABLE_NAME
|
|
|
|
WITH Indexes as
|
|
(
|
|
SELECT /*+ */
|
|
i.table_owner || '.' || i.table_name table_name
|
|
, i.owner || '.' || i.index_name index_name
|
|
, i.uniqueness
|
|
, i.index_type
|
|
--, i.status
|
|
--, i.visibility
|
|
, SUBSTR(SYS_CONNECT_BY_PATH ( ic.column_name || DECODE(ic.descend,'ASC','',' (desc) ') , ' , ' ),4) Columns
|
|
FROM dba_indexes i
|
|
, dba_ind_columns ic
|
|
, dba_ind_expressions ie
|
|
WHERE i.owner = ic.index_owner
|
|
AND i.index_name = ic.index_name
|
|
AND i.table_owner = ic.table_owner
|
|
AND i.table_name = ic.table_name
|
|
AND ic.index_owner = ie.index_owner (+)
|
|
AND ic.index_name = ie.index_name (+)
|
|
AND ic.table_owner = ie.table_owner (+)
|
|
AND ic.table_name = ie.table_name (+)
|
|
AND ic.column_position = ie.column_position (+)
|
|
AND i.table_owner LIKE upper('&&owner') ESCAPE '\'
|
|
AND i.table_name LIKE upper('&&table_name') ESCAPE '\'
|
|
AND CONNECT_BY_ISLEAF = 1
|
|
AND LEVEL = ic.column_position
|
|
CONNECT BY ic.table_owner = PRIOR ic.table_owner
|
|
AND ic.table_name = PRIOR ic.table_name
|
|
AND ic.index_owner = PRIOR ic.index_owner
|
|
AND ic.index_name = PRIOR ic.index_name
|
|
AND ic.column_position - 1 = PRIOR ic.column_position
|
|
START WITH ic.table_owner LIKE upper('&&owner') ESCAPE '\'
|
|
AND ic.table_name LIKE upper('&&table_name') ESCAPE '\'
|
|
)
|
|
, indexes_similar as
|
|
(
|
|
SELECT a.*
|
|
FROM indexes a
|
|
WHERE EXISTS (SELECT 1 from indexes b
|
|
WHERE b.table_name = a.table_name
|
|
AND b.columns LIKE a.columns || '%'
|
|
AND LENGTH(b.columns) > LENGTH(a.columns)
|
|
)
|
|
)
|
|
SELECT i.*
|
|
FROM indexes i
|
|
, indexes_similar s
|
|
WHERE i.table_name = s.table_name
|
|
AND i.columns like s.columns || '%'
|
|
ORDER BY i.table_name
|
|
, i.index_name
|
|
, i.uniqueness
|
|
|
|
;
|
|
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
|
|
|
|
@@footer
|