Auswahl  

 

Oracle
DBA:SQL
RDBMS 11.2:RDBMS 12.1:RDBMS 12.2:RDBMS 18.3:RDBMS 19.1:12.1, 12.2
Index
12.12.18
MP
12.12.18
MP

Body

Indizes sind ein spannendes Thema, manchmal hat man zu viele, oder zu wenige. Wir wollen uns in diesem Tipp mal anschauen, wie man diese Probleme löst.

Der nachfolgende SELECT ermittels, welche Spalten am häufigsten im Einsatz waren, aber nicht indiziert worden sind. Dabei werden folgende Spalten ausgegeben:

  • SIZE_M Größe des Index in Megabyte
  • =  Spalte wurde verwendet z.B. in der Form: ename='KING'
  • E-Join Spalte wurde verwendet z.B. in der Form: e.deptno=d.deptno
  • NE-Join Spalte wurde verwendet z.B. in der Form: e.deptno<>d.deptno
  • RANGE Spalte wurde verwendet z.B. in der Form: e.deptno>10
  • LIKE Spalte wurde verwendet z.B. in der Form: ename LIKE 'K%'
  • NULL Spalte wurde verwendet z.B. in der Form: ename IS NULL
  • SUMME Summe der Zugriffsspalten
  • INSERTS Wie viele Inserts wurden seit der letzen Statistikerzeugung auf der Tabelle durchgeführt
  • UPDATES Wie viele Updates wurden seit der letzen Statistikerzeugung auf der Tabelle durchgeführt
  • DELETS Wie viele Deletes wurden seit der letzen Statistikerzeugung auf der Tabelle durchgeführt

Es wird sortiert nach der Benutzungshäufigkeit. Sie können nun entscheiden, ob Sie einen Index auf die Spalte legen möchten.

Bedenken Sie aber, dass ein Index auch Wartungszeit für Inserts/Updates und Deletes benötigt

/*****************************************************************************/
/* Oracle Index Vorschläge                     */
/*****************************************************************************/

SELECT * FROM (
SELECT tc.owner||'.'||o.object_name as segment_name,tc.column_name,
round(s.bytes/1024/1024) Size_M,
cu.equality_preds "=",
cu.equijoin_preds "E-JOIN",cu.nonequijoin_preds "NE-JOIN",cu.range_preds range,cu.like_preds "LIKE", cu.null_preds "NULL",
cu.equality_preds+cu.equijoin_preds+cu.nonequijoin_preds+cu.range_preds+cu.like_preds+cu.null_preds as summe,
dtm.inserts,dtm.updates,dtm.deletes
FROM sys.col_usage$ cu, dba_objects o,
dba_tab_columns tc, dba_ind_columns ic, dba_segments s, dba_tab_modifications dtm
WHERE o.object_id=cu.obj#
AND o.object_name=tc.table_name
AND o.owner=tc.owner
AND cu.equality_preds>0
AND cu.intcol#= tc.column_id
AND tc.owner=ic.table_owner(+)
AND tc.table_name=ic.table_name(+)
AND tc.column_name=ic.column_name(+)
AND ic.index_name is NULL
AND o.owner=s.owner(+)
AND o.object_name=s.segment_name(+)
AND o.owner=dtm.table_owner
AND o.object_name=dtm.table_name
AND o.owner IN (select username from dba_users where oracle_maintained='N' ) -- alternativ für Oracle <12.1: o.owner IN ('SCOTT','X')
ORDER BY 10 desc,1,2)
WHERE rownum <30
/

oder anders herum betrachtet: Welche Indizes sind evtl unnötig, weil eine Spalte durch mehrere Indizes abgedeckt wird.

REM ###################################################################
REM Welche Spalten haben einen Index (alle Indizes in einer Spalte ausgeben)
REM ###################################################################
 
SELECT  owner,table_name,column_name, index_names FROM (
SELECT owner,table_name,column_name, index_names,
RANK() OVER (PARTITION BY owner,table_name,column_name ORDER BY len DESC NULLS LAST) AS col_Rank
FROM(
SELECT owner,table_name,column_name,rtrim(index_name||','||i2||','||i3||','||i4||','||i5||','||i6||',',',') index_names,
length(index_name||i2||i3||i4||i5||i6) len
FROM (
select c.owner,c.table_name,c.column_name,i.index_name||'('|| i.column_position||')' index_name,
CASE WHEN
lead(c.owner||'#'||c.table_name||'#'||i.column_name,1) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
THEN lead(i.index_name||'('|| i.column_position||')',1) OVER (order by c.owner,c.table_name,c.column_name) END i2,
CASE WHEN
lead(c.owner||'#'||c.table_name||'#'||i.column_name,2) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
THEN lead(i.index_name||'('|| i.column_position||')',2) OVER (order by c.owner,c.table_name,c.column_name) END i3,
CASE WHEN
lead(c.owner||'#'||c.table_name||'#'||i.column_name,3) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
THEN lead(i.index_name||'('|| i.column_position||')',3) OVER (order by c.owner,c.table_name,c.column_name) END i4,
CASE WHEN
lead(c.owner||'#'||c.table_name||'#'||i.column_name,4) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
THEN lead(i.index_name||'('|| i.column_position||')',4) OVER (order by c.owner,c.table_name,c.column_name) END i5,
CASE WHEN
lead(c.owner||'#'||c.table_name||'#'||i.column_name,5) OVER (order by c.owner,c.table_name,c.column_name)=c.owner||'#'||c.table_name||'#'||c.column_name
THEN lead(i.index_name||'('|| i.column_position||')',5) OVER (order by c.owner,c.table_name,c.column_name) END i6
FROM all_tab_columns c,all_ind_columns i
WHERE c.owner=i.table_owner
AND c.table_name= i.table_name
AND c.column_name= i.column_name
AND c.owner='SCOTT' )))
WHERE col_rank=1
AND index_names like '%,%'; /* Die letzte Klausel gibt nur Spalten mit mehr als 2 Indizes aus */

Man sollte jetzt nicht gleich alle Indizes löschen, nur weil Sie eine Spalte mehrfach indiziert haben. Evtl. hatte der Index genau eine spezielle Funktion, die ohne ihn nicht mehr funktioniert.
Aber Sie haben durch die beiden Select´s eine Diskussionsgrundlage.

Weitere tolle Tipps erhalten Sie in einem unserer Oracle Kurse. Ich hoffe wir sehen uns bald dort :-)