Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:oracle_index_organised_table

Eine Oracle Tabelle als IOT - "index organised table" aufbauen und überwachen

Eine einfache IOT

Die index organised table besteht inkl. aller Spalten nur aus einem Index.

IOT anlegen und mit Daten befüllen, abfragen:

CREATE TABLE T_IOT1( 
   ID NUMBER 
 , wert varchar2(20) 
 , CONSTRAINT T_IOT1_PK PRIMARY KEY (ID) ENABLE 
) ORGANIZATION INDEX
/
 
# mit den ersten Daten füllen:
 
BEGIN
 FOR i IN 1..100 
 loop
   INSERT INTO T_IOT1 VALUES (i,to_char(i)||'er Wert');
 END loop;
 commit;
END;
/
 
SELECT * FROM  T_IOT1
/
 
# alles wird aus dem INDEX gelesen:
 
Ausf³hrungsplan
----------------------------------------------------------
Plan hash VALUE: 2327634795
 
----------------------------------------------------------------------------------
| Id  | Operation            | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |   100 |  5500 |     2   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| T_IOT1_PK |   100 |  5500 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Wie groß ist die Tabelle nun geworden?

Typ der Tabelle ermitteln über Spalte IOT_TYPE in DBA_TABLES
select 'This Table is'||decode(nvl(IOT_TYPE,'-'),'IOT',' index organised','heap organised') as TABLE_TYPE
  from dba_tables 
 where table_name  like upper('&ENTER_TABLE.')
   and owner       = upper('&ENTER_OWNER.')
/


TABLE_TYPE
-----------------------------
This Table is index organised


	
Wieviel Platz benötigt nun die Tabelle im Tablespace?

Eine Abfrage über DBA_SEGMENTS liefert KEINEN Treffer!

select count(*) from DBA_SEGMENTS where segment_name like 'T_IOT1';

   COUNT(*)
-----------
          0
          
select count(*) from DBA_EXTENTS where segment_name like 'T_IOT1';

    COUNT(*)
------------
           0
                     

Wo belegt nun diese Tabelle Ihren Platz?

Nur der PK Index wurde in der DB anlegt und enthält damit auch alle Daten!

SEGMENT_NAME                                                                      SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
T_IOT1_PK                                                                         INDEX
 
SELECT segment_name,segment_type,bytes FROM user_segments WHERE segment_name LIKE 'T_IOT1%';
 
SEGMENT_NAME         SEGMENT_TYPE              BYTES
-------------------- ------------------ ------------
T_IOT1_PK            INDEX                     65536

IOT mit Overflow Segment

TestCase mit INCLUDING <spalte> OVERFLOW :

CREATE TABLE T_IOT2(   
 ID NUMBER  
, wert varchar2(20)  
, CONSTRAINT T_IOT2_PK PRIMARY KEY (ID) ENABLE ) 
ORGANIZATION INDEX 
INCLUDING wert OVERFLOW;
 
BEGIN
 FOR i IN 1..100 
 loop
   INSERT INTO T_IOT2 VALUES (i,to_char(i)||'er Wert');
 END loop;
 commit;
END;
/
 
 
SELECT segment_name,segment_type,bytes FROM user_segments;
 
SEGMENT_NAME         SEGMENT_TYPE              BYTES
-------------------- ------------------ ------------
SYS_IOT_OVER_155213  TABLE                     65536
T_IOT2_PK            INDEX                     65536

Ein zweites Segment wird für die Overflow Daten angelegt!

Script für die Analyse einer IOT

tab_iot.sql
SET pagesize 300
SET linesize 120
SET VERIFY OFF
 
define ENTER_OWNER='&1'
define ENTER_TABLE='&2'
 
prompt
prompt Parameter 1 = USER Name     => &&ENTER_OWNER.
prompt Parameter 2 = TABLE Name    => &&ENTER_TABLE.
prompt
 
ttitle LEFT  "Check if the table is a IOT Table" skip 2
 
SELECT 'This Table is'||decode(nvl(IOT_TYPE,'-'),'IOT',' index organised','heap organised') AS TABLE_TYPE
  FROM dba_tables 
 WHERE UPPER(TABLE_NAME) LIKE UPPER('&ENTER_TABLE.')
   AND UPPER(owner) = UPPER('&ENTER_OWNER.')
/              
 
ttitle LEFT  "IOT Name and Table space for Overflow Segments" skip 2
 
COLUMN owner            format a10 heading "Owner"
COLUMN overflow_table   format a20 heading "OverFlow Table|Name"
COLUMN IOT_TYPE         format a14  heading "IOT|Type"
COLUMN IOT_NAME         format a20 heading "IOT|Name"
COLUMN tablespace_name  format a12 heading "IDX|TBS Name"
COLUMN overFlowTabspace format a12 heading "OverFlow|TBS Name"
COLUMN index_name       format a20 heading "IOT Index|Name"
COLUMN iot_name_table   format a20 heading "IOT Tab|Name"
 
SELECT i.owner 
     , i.table_name AS iot_name_table
     , nvl(t.table_name,'-') AS overflow_table
     , nvl(i.index_name,'-') AS index_name
     , nvl(t.IOT_TYPE,'-') AS IOT_TYPE
     , i.tablespace_name 
     , t.tablespace_name AS overFlowTabspace        
 FROM dba_tables  t
    , dba_indexes i            
WHERE t.IOT_NAME (+) = i.table_name
  AND UPPER(i.table_name) LIKE UPPER('&&ENTER_TABLE.')
  AND UPPER(i.owner)       =  UPPER('&&ENTER_OWNER.')                      
/  
 
ttitle LEFT  "IOT Table SIZE " skip 2
 
SET heading off
 
COLUMN index_nameMB      fold_after
COLUMN IndexSizeMB       fold_after
COLUMN overflow_tableMB  fold_after
COLUMN OverFlowSizeMB    fold_after
COLUMN totalMB           fold_after
 
SELECT rpad('Index Name',30,' ')   ||'::'||lpad(index_name,22,' ')                                                             AS index_nameMB
    ,  rpad('Index Size',30,' ')   ||'::'||to_char(round((IndexSize/1024/1024),3),'999G999G999G999D99')                ||' MB' AS IndexSizeMB
	 ,  rpad('Overflow Name',30,' ')||'::'||lpad(overflow_table,22,' ')                                                         AS overflow_tableMB
	 ,  rpad('Overflow Size',30,' ')||'::'||to_char(round((OverFlowSize/1024/1024),3),'999G999G999G999D99')             ||' MB' AS OverFlowSizeMB
	 ,  rpad('Total',30,' ')        ||'::'||to_char(round(((IndexSize+OverFlowSize)/1024/1024),3),'999G999G999G999D99') ||' MB' AS totalMB
  FROM ( 
	SELECT nvl(i.index_name,'-') AS index_name
		, (SELECT SUM(bytes) FROM dba_segments WHERE segment_name=i.index_name AND owner=i.owner) AS IndexSize
		, nvl(t.table_name,'-') AS overflow_table
		, nvl((SELECT SUM(bytes) FROM dba_segments WHERE segment_name=t.table_name AND owner=t.owner ),0)   AS OverFlowSize       						 
	FROM dba_tables  t
		 , dba_indexes i            
	WHERE t.IOT_NAME (+) = i.table_name
	  AND UPPER(i.table_name) LIKE UPPER('&&ENTER_TABLE.')
	  AND UPPER(i.owner)       =  UPPER('&&ENTER_OWNER.')  
)  
/  
 
SET heading ON
 
ttitle LEFT  "Check if the columns are in the overflow segment of the IOT Table" skip 2
 
SELECT c.table_name
     , c.column_name
     , CASE 
         WHEN i.include_column != 0 THEN  ( CASE WHEN c.column_id < i.include_column THEN 'TOP' ELSE 'OVERFLOW' END ) 
       ELSE 'TOP' 
       END AS segment
  FROM dba_tab_columns c
     , dba_indexes     i
WHERE  i.table_name (+) = c.table_name
   AND i.owner (+) = c.owner
   AND UPPER(c.table_name) LIKE UPPER('&enter_table.')
   AND UPPER(c.owner) = UPPER('&enter_owner.')     
 ORDER BY TABLE_NAME
        , column_id
/
 
ttitle off

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/oracle_index_organised_table.txt · Zuletzt geändert: 2014/08/01 11:01 von Gunther Pippèrr