dba:oracle_index_organised_table
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende ÜberarbeitungNächste ÜberarbeitungBeide Seiten der Revision | ||
dba:oracle_index_organised_table [2014/07/31 22:08] – [Quellen] gpipperr | dba:oracle_index_organised_table [2014/08/01 10:27] – [Script für die Analyse einer IOT] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
=====Eine Oracle Tabelle als IOT - "index organised table" aufbauen und überwachen===== | =====Eine Oracle Tabelle als IOT - "index organised table" aufbauen und überwachen===== | ||
+ | ====Eine einfache IOT ==== | ||
- | Eine einfache IOT anlegen und mit Daten befüllen, abfragen: | + | Die index organised table besteht inkl. aller Spalten nur aus einem Index. |
+ | IOT anlegen und mit Daten befüllen, abfragen: | ||
<code sql> | <code sql> | ||
CREATE TABLE T_IOT1( | CREATE TABLE T_IOT1( | ||
Zeile 138: | Zeile 140: | ||
<code sql> | <code sql> | ||
- | ET pagesize 300 | + | SET pagesize 300 |
- | SET linesize | + | SET linesize |
SET VERIFY OFF | SET VERIFY OFF | ||
Zeile 156: | Zeile 158: | ||
where upper(table_name) like upper('& | where upper(table_name) like upper('& | ||
and upper(owner) = upper('& | and upper(owner) = upper('& | ||
- | / | + | / |
ttitle left "IOT Name and Table space for Overflow Segments" | ttitle left "IOT Name and Table space for Overflow Segments" | ||
- | + | column owner format a10 heading " | |
- | column owner format a10 heading " | + | |
column overflow_table | column overflow_table | ||
- | column IOT_TYPE | + | column IOT_TYPE |
- | column IOT_NAME | + | column IOT_NAME |
column tablespace_name | column tablespace_name | ||
column overFlowTabspace format a12 heading " | column overFlowTabspace format a12 heading " | ||
- | column index_name | + | column index_name |
column iot_name_table | column iot_name_table | ||
- | select | + | select i.owner |
- | , i.table_name as iot_name_table | + | |
- | | + | |
- | , nvl(i.index_name,' | + | |
- | , nvl(t.IOT_TYPE,' | + | |
- | , i.tablespace_name | + | |
- | , t.tablespace_name as overFlowTabspace | + | |
from dba_tables | from dba_tables | ||
- | , dba_indexes i | + | , dba_indexes i |
where t.IOT_NAME (+) = i.table_name | where t.IOT_NAME (+) = i.table_name | ||
and upper(i.table_name) like upper('&& | and upper(i.table_name) like upper('&& | ||
Zeile 184: | Zeile 185: | ||
/ | / | ||
- | ttitle left "IOT SIZE " skip 2 | + | ttitle left " |
- | select | + | set heading off |
- | , (select sum(bytes) from dba_segments where segment_name=i.index_name) as IndexSize | + | |
- | , | + | column index_nameMB |
- | , | + | column IndexSizeMB |
- | , (select sum(bytes) from dba_segments where segment_name=i.index_name)+nvl((select sum(bytes) from dba_segments where segment_name=t.table_name), | + | column overflow_tableMB |
- | from dba_tables | + | column OverFlowSizeMB |
- | , dba_indexes i | + | column totalMB |
- | where t.IOT_NAME (+) = i.table_name | + | |
- | and upper(i.table_name) like upper('&& | + | select rpad(' |
- | and upper(i.owner) | + | , |
+ | , | ||
+ | , | ||
+ | , rpad(' | ||
+ | from ( | ||
+ | select nvl(i.index_name,' | ||
+ | , (select sum(bytes) from dba_segments where segment_name=i.index_name) | ||
+ | , nvl(t.table_name,' | ||
+ | , nvl((select sum(bytes) from dba_segments where segment_name=t.table_name), | ||
+ | from dba_tables | ||
+ | , dba_indexes i | ||
+ | where t.IOT_NAME (+) = i.table_name | ||
+ | | ||
+ | | ||
+ | ) | ||
/ | / | ||
+ | set heading on | ||
ttitle left "Check if the columns are in the overflow segment of the IOT Table" skip 2 | ttitle left "Check if the columns are in the overflow segment of the IOT Table" skip 2 | ||
- | + | ||
select c.table_name | select c.table_name | ||
, c.column_name | , c.column_name | ||
- | , case | + | , case |
- | | + | |
- | | + | |
- | | + | |
from dba_tab_columns c | from dba_tab_columns c | ||
, dba_indexes | , dba_indexes | ||
- | where i.table_name (+) = c.table_name | + | where i.table_name (+) = c.table_name |
and upper(c.table_name) like upper('& | and upper(c.table_name) like upper('& | ||
and upper(c.owner) = upper('& | and upper(c.owner) = upper('& | ||
Zeile 215: | Zeile 231: | ||
, column_id | , column_id | ||
/ | / | ||
- | + | ||
- | ttitle | + | ttitle |
</ | </ | ||
dba/oracle_index_organised_table.txt · Zuletzt geändert: 2014/08/01 11:01 von gpipperr