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 | ----------------------------------------------------------------------------------
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
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
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!
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