Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:index_column_usage

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
dba:index_column_usage [2013/08/05 17:12]
gpipperr [Skript für die Analyse]
dba:index_column_usage [2013/08/05 17:18] (aktuell)
gpipperr [Skript für die Analyse]
Zeile 24: Zeile 24:
    
 SELECT replace(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'.html','\','_' 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 .-(+--' resolve syntax highlight bug FROM my editer .-(
   AS SPOOL_NAME_COL   AS SPOOL_NAME_COL
 FROM dual FROM dual
Zeile 50: Zeile 50:
  
 select  select 
-    o.owner +      o.owner 
-  , o.object_name +    , o.object_name 
-  , c.name  as column_name +    , c.name  as column_name 
-  , u.equality_preds +    , u.equality_preds 
-  , u.range_preds +    , u.range_preds 
-  , u.like_preds +    , u.like_preds 
-  , u.null_preds +    , u.null_preds 
-  , u.equijoin_preds +    , u.equijoin_preds 
-  , u.nonequijoin_preds+    , u.nonequijoin_preds
 from  from 
-   sys.col_usage$ u +      sys.col_usage$ u 
- , dba_objects    o       +    , dba_objects    o       
-    , sys.col$         +    , sys.col$            
 where u.obj#    = o.OBJECT_ID where u.obj#    = o.OBJECT_ID
   and u.obj#    = c.obj#    and u.obj#    = c.obj# 
Zeile 70: Zeile 70:
                     from dba_ind_columns i                      from dba_ind_columns i 
                    where i.table_owner = o.owner                     where i.table_owner = o.owner 
-      and i.table_name  = o.object_name  +                     and i.table_name  = o.object_name  
-  and i.column_name = c.name )+                     and i.column_name = c.name )
  order by  o.owner, o.object_name,c.name  order by  o.owner, o.object_name,c.name
 /   /  
 +
  
 ttitle center "Index with more then one Columns" SKIP 2 ttitle center "Index with more then one Columns" SKIP 2
Zeile 79: Zeile 80:
 SET linesize 130 pagesize 2000 recsep OFF SET linesize 130 pagesize 2000 recsep OFF
  
-column index_owner format a25 +column   index_owner format a25 
-column index_name  format a25 +column   index_name  format a25 
-column table_name  format a25 +column   table_name  format a25 
-column column_name format a25 +column   column_name format a25 
-column  pos1 format a25+column  pos1      format a25
 column  pos2        format a25  column  pos2        format a25 
 column  pos3        format a25 column  pos3        format a25
Zeile 92: Zeile 93:
 column  pos8        format a25 column  pos8        format a25
 column  pos9        format a25 column  pos9        format a25
- +      
 select * from ( select * from (
- select * from ( +   select * from ( 
-   select   +        select   
-       index_owner +               index_owner 
-     , table_name +             , table_name 
-     , index_name +             , index_name 
-     , column_name +             , column_name 
-     , column_position +             , column_position 
-    from dba_ind_columns +         from dba_ind_columns 
-   where index_owner like '&&USERNAME.%'   +        where index_owner like '&&USERNAME.%'   
-   order by index_owner,table_name +        order by index_owner,table_name 
- )   +   )   
- pivot (  +   pivot (  
-  min (column_name) +       min (column_name) 
- for column_position +         for column_position 
- in  ('1' as pos1 +         in  ('1' as pos1 
- ,'2' as pos2 +             ,'2' as pos2 
- ,'3' as pos3 +             ,'3' as pos3 
- ,'4' as pos4 +             ,'4' as pos4 
- ,'5' as pos5 +             ,'5' as pos5 
- ,'6' as pos6 +             ,'6' as pos6 
- ,'7' as pos7 +             ,'7' as pos7 
- ,'8' as pos8 +             ,'8' as pos8 
- ,'9' as pos9 +             ,'9' as pos9 
- +            
- )+   )
  
 where pos2 is not null where pos2 is not null
Zeile 130: Zeile 131:
 -- autostart of the result in a browser window -- autostart of the result in a browser window
 host &&SPOOL_NAME host &&SPOOL_NAME
 +
  
 </code> </code>
"Autor: Gunther Pipperr"
dba/index_column_usage.txt · Zuletzt geändert: 2013/08/05 17:18 von gpipperr