SQL Queries - CAST Central Base - Queries on Indexes - How to get the List of Indexes

Purpose of Query

This document provides a query to get the list of Indexes in the Central schema. It is useful when you have performance issues during snaphot computation and want to check the integrity of the Central Base. If some indexes are missing, the performance can be seriously degraded.

Applicable CAST Version

Release	Yes/No
8.3.x
8.2.x

Applicable RDBMS

RDBMS	Yes/No
Oracle Server
Microsoft SQL Server
CSS

Query for CSS

SET SEARCH_PATH=<CENTRAL_NAME>;


SELECT    c.relname  AS "Name",
          c2.relname AS "Table"
FROM      pg_catalog.pg_class c
3         JOIN pg_catalog.pg_index i
          ON        i.indexrelid = c.oid
          JOIN pg_catalog.pg_class c2
          ON        i.indrelid = c2.oid
          LEFT JOIN pg_catalog.pg_user u
          ON        u.usesysid = c.relowner
          LEFT JOIN pg_catalog.pg_namespace n
          ON        n.oid = c.relnamespace
WHERE     c.relkind IN ('i',
                        '')
AND       n.nspname NOT IN ('pg_catalog',
                            'pg_toast')
AND       pg_catalog.pg_table_is_visible(c.oid)
ORDER BY  1,
          2;

Query result example

"adg_deltas_idx";"adg_delta_snapshots"
"adg_diag_sev_idx";"adg_diag_severity"
"adg_diag_sev_idx2";"adg_diag_severity"
"adg_fmod_mod_idx";"adg_func_module_modules"
"adg_fmt_mod_idx";"adg_techno_module_links"
"adg_func_mod_idx";"adg_func_module_list"
"adg_func_tech_idx";"adg_func_mod_and_techno_list"
"adg_mod_par_idx";"adg_func_module_parents"
"adg_mod_par_lev";"adg_func_module_parents"
"adg_sys_tree_idx";"adg_system_tree"

Query result interpretation

Interpretation of row 1 : Table ADX_DELTA_SNAPSHOTS is indexed by index ADG_DELTAS_IDX

Query for Oracle

SELECT index_name,
       table_name
FROM   dba_indexes
WHERE  owner LIKE '%<CENTRAL_NAME>%'

Query result example

INDEX_NAME TABLE_NAME
------------------------------ ---------------------------------

ADG_DELTAS_IDX ADG_DELTA_SNAPSHOTS
ADG_DIAG_SEV_IDX ADG_DIAG_SEVERITY
ADG_DIAG_SEV_IDX2 ADG_DIAG_SEVERITY
ADG_FUNC_TECH_IDX ADG_FUNC_MOD_AND_TECHNO_LIST
ADG_FUNC_MOD_IDX ADG_FUNC_MODULE_LIST
ADG_FMOD_MOD_IDX ADG_FUNC_MODULE_MODULES
ADG_MOD_PAR_IDX ADG_FUNC_MODULE_PARENTS

[...]

241 record(s) selected [Fetch MetaData: 1/ms] [Fetch Data: 21/ms]

[Executed: 14/02/17 09:18:55 GMT ] [Execution: 708/ms]

Query result interpretation

Interpretation of row 1 : Table ADX_DELTA_SNAPSHOTS is indexed by index ADG_DELTAS_IDX

Notes/comments

Related Pages