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. Diese Website verwendet Cookies. Mit der Nutzung der Website erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Computer gespeichert werden. Außerdem bestätigen Sie, dass Sie unsere Datenschutzerklärung gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website. Weitere Information
"Autor: Gunther Pipperr"
dba/oracle_index_organised_table.txt · Zuletzt geändert: 2014/08/01 11:01 von gpipperr