Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:index_column_usage

Spalten in Oracle Tabellen auf Indizierung prüfen

Spalten finden die öfters in SQL Statements verwendet werden

Mit Hilfe der Tabelle sys.col_usage$ kann überprüft werden, ob und wie oft auf Spalten einer Tabelle in SQL Statements zugegriffen wird.

Spalten finden, die mehr als einmal in einem Index verwendet werden

Die Einfüge Performance kann erheblich unter der Mehrfach Indizierung von zusammengesetztem Indexen leiden. Mit dem Analyse Script werden all Indexes aufgelistet, die mehr als eine Spalte verwenden.

Skript für die Analyse

Mit folgenden Script kann überprüft werden, ob Spalten, die in SQL Verwendung finden, nicht indiziert oder aber mehrfach indiziert sind.

Das Script erzeugt einen HTML Bericht um eine bessere Lesbarkeit der Ausgabe zu erlauben.

In einer MS Windows Umgebung wird der Bericht direkt nach dem Anlegen im Browser anzeigt, in Linux Umgebungen Host Kommando aus kommentieren.

check_col_usage.sql
col SPOOL_NAME_COL new_val SPOOL_NAME
 
SELECT REPLACE(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'.html','\','_') 
--' resolve syntax highlight bug FROM my editer .-(
  AS SPOOL_NAME_COL
FROM dual
/
 
spool &&SPOOL_NAME
 
SET markup html ON
 
ttitle center "Columns usesd in SQL Queries but not indexed" SKIP 2
 
SET verify off
SET linesize 130 pagesize 200 recsep OFF
 
COLUMN  owner        format a20
COLUMN  object_name  format a30
COLUMN  column_name  format a25
 
COLUMN equality_preds    format 999   heading "equ"
COLUMN equijoin_preds    format 999   heading "Jequ"
COLUMN nonequijoin_preds format 999   heading "Jnoe"
COLUMN range_preds       format 999   heading "ran"
COLUMN like_preds        format 999   heading "lik"
COLUMN null_preds        format 999   heading "nul"
 
SELECT 
      o.owner
    , o.object_name
    , c.name  AS column_name
    , u.equality_preds
    , u.range_preds
    , u.like_preds
    , u.null_preds
    , u.equijoin_preds
    , u.nonequijoin_preds
FROM 
      sys.col_usage$ u
    , dba_objects    o      
    , sys.col$       c      
WHERE u.obj#    = o.OBJECT_ID
  AND u.obj#    = c.obj# 
  AND u.intcol# = c.col#
  AND o.owner   LIKE '&USERNAME.%'  
  AND NOT EXISTS (SELECT 1 
                    FROM dba_ind_columns i 
                   WHERE i.table_owner = o.owner 
                     AND i.table_name  = o.object_name 
                     AND i.column_name = c.name )
 ORDER BY  o.owner, o.object_name,c.name
/  
 
 
ttitle center "Index with more then one Columns" SKIP 2
 
SET linesize 130 pagesize 2000 recsep OFF
 
COLUMN   index_owner format a25
COLUMN   index_name  format a25
COLUMN   TABLE_NAME  format a25
COLUMN   column_name format a25
COLUMN  pos1      format a25
COLUMN  pos2        format a25 
COLUMN  pos3        format a25
COLUMN  pos4        format a25
COLUMN  pos5        format a25
COLUMN  pos6        format a25
COLUMN  pos7        format a25
COLUMN  pos8        format a25
COLUMN  pos9        format a25
 
SELECT * FROM (
   SELECT * FROM (
        SELECT  
               index_owner
             , TABLE_NAME
             , index_name
             , column_name
             , column_position
         FROM dba_ind_columns
        WHERE index_owner LIKE '&&USERNAME.%'  
        ORDER BY index_owner,TABLE_NAME
   )  
   pivot ( 
       MIN (column_name)
         FOR column_position
         IN  ('1' AS pos1
             ,'2' AS pos2
             ,'3' AS pos3
             ,'4' AS pos4
             ,'5' AS pos5
             ,'6' AS pos6
             ,'7' AS pos7
             ,'8' AS pos8
             ,'9' AS pos9
            )
   )
) 
WHERE pos2 IS NOT NULL
/
 
SET markup html off
spool off
ttitle off
 
-- works only in a ms windows enviroment
-- autostart of the result in a browser window
host &&SPOOL_NAME

Quellen

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
dba/index_column_usage.txt · Zuletzt geändert: 2013/08/05 17:18 von Gunther Pippèrr