IOT Table

Una IOT mantiene i dati memorizzati in base al valore delle colonne che costituiscono la primary key della tabella.
Una IOT memorizza i dati come se l’intera tabella fosse memorizzata in un indice. Un indice normale memorizza solo le colonne indicizzate, un IOT memorizza tutte le colonne della tabella nell’indice.
Poiché i dati della tabella sono memorizzati come un indice, le righe della tabella non hanno ROWID fisici. Pertanto, non è possibile selezionare i valori della pseudocolonna ROWID da una IOT. Una IOT ha ROWID logiche.
Le ROWID logiche permettono di costruire indici secondari e di migliorare le prestazioni dell’indicizzazione consentendo alle IOT di avere performance simili agli indici regolari. Una IOT richiederà inoltre meno spazio di un table normale grazie all’assenza di ROWID.
Per limitare la quantità di gestione interna dell’IOT si dovrebbe creare una IOT solo se i dati sono molto statici. Se i dati della tabella cambiano spesso è necessario utilizzare una tabella normale indicizzata. In generale, le IOT sono più efficaci quando la chiave primaria costituisce gran parte delle colonne. Se la IOT contiene molte colonne ad accesso frequente che non fanno parte della chiave primaria bisognerà accedere ripetutamente all’area di overflow.
Qui di seguito verranno confrontate prestazioni e modalità di accesso ai dati tra tabelle IOT e tabelle heap.
IOT TABLE
SQL> CREATE TABLE my_iot (id INTEGER CONSTRAINT my_iot_pk PRIMARY KEY, value VARCHAR2(50)) SEGMENT CREATION IMMEDIATE ORGANIZATION INDEX;
Table created.
Possiamo vedere che nel db sono stati creati solo index segments.
SQL> SELECT table_name, iot_type, iot_name FROM user_tables;
TABLE_NAME IOT_TYPE IOT_NAME
—————————— ———— ——————————
MY_IOT IOT
SQL> SELECT index_name, index_type, table_name FROM user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME
—————————— ————————— ——————————
MY_IOT_PK IOT – TOP MY_IOT
SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
———- ——————– ——————-
71135 MY_IOT TABLE
71136 MY_IOT_PK INDEX
SQL> select segment_name,segment_type from user_segments ORDER BY 1;
SEGMENT_NAME SEGMENT_TYPE
——————– ——————
MY_IOT_PK INDEX
IOT popolata con 20 milioni di records
SQL> select count(*) from my_iot;
COUNT(*)
———-
20000000
performance IOT con filtered query usando colonna PK
SQL> set autotrace traceonly
SQL> select * from my_iot where id=45723;
Elapsed: 00:00:00.07
Execution Plan
———————————————————-
Plan hash value: 4057551070
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 40 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| MY_IOT_PK | 1 | 40 | 1 (0)| 00:00:01 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – access(“ID”=45723)
Statistics
———————————————————-
1 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
489 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
performance IOT con filtered query con clausola between
SQL> select * from my_iot where id between 48374 and 1896455;
1848082 rows selected.
Elapsed: 00:00:09.60
Execution Plan
———————————————————-
Plan hash value: 1125113513
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 672K| 25M| 311 (0)| 00:00:04 |
|* 1 | INDEX RANGE SCAN| MY_IOT_PK | 672K| 25M| 311 (0)| 00:00:04 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“ID”>=48374 AND “ID”<=1896455) Note —– - dynamic sampling used for this statement (level=2) Statistics ———————————————————- 5 recursive calls 2 db block gets 129626 consistent gets 6070 physical reads 0 redo size 52853388 bytes sent via SQL*Net to client 1355674 bytes received via SQL*Net from client 123207 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1848082 rows processed performance IOT con filtered query con clusola order by Se una clausola ORDER BY fa riferimento solo alla colonna chiave primaria o a una parte di essa, allora l'ottimizzatore evita il sovraccarico e le righe vengono restituite ordinate secondo le colonne della chiave primaria. SQL> select * from my_iot where id between 48374 and 1896874 order by id;
1848501 rows selected.
Elapsed: 00:00:09.80
Execution Plan
———————————————————-
Plan hash value: 1125113513
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 672K| 25M| 311 (0)| 00:00:04 |
|* 1 | INDEX RANGE SCAN| MY_IOT_PK | 672K| 25M| 311 (0)| 00:00:04 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“ID”>=48374 AND “ID”<=1896874) Note —– - dynamic sampling used for this statement (level=2) Statistics ———————————————————- 5 recursive calls 2 db block gets 129656 consistent gets 1672 physical reads 0 redo size 52865823 bytes sent via SQL*Net to client 1355982 bytes received via SQL*Net from client 123235 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1848501 rows processed Lo stesso metodo di accesso avviene nei casi non order con utilizzo di my_iot_pk. Dal trace file: SQL ID: 0cqyd8n935h3v Plan Hash: 1125113513 select * from my_iot where id between 48374 and 1896455 order by id call count cpu elapsed disk query current rows ——- —— ——– ———- ———- ———- ———- ———- Parse 1 0.01 0.06 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 123207 6.40 6.36 6006 128827 0 1848082 ——- —— ——– ———- ———- ———- ———- ———- total 123209 6.41 6.42 6006 128827 0 1848082 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 51 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ———- ———- ———- ————————————————— 1848082 1848082 1848082 INDEX RANGE SCAN MY_IOT_PK (cr=128827 pr=6006 pw=0 time=2685243 us cost=6325 size=36991280 card=1849564)(object id 68953) Lo stesso comportamento è implementato come previsto senza clausola order by, e nel trace file non c’è nessun overhead dovuto al fattore ordinamento. SQL ID: 9ncx9uf3n0tcu Plan Hash: 1125113513 select * from my_iot where id between 48374 and 1896455 call count cpu elapsed disk query current rows ——- —— ——– ———- ———- ———- ———- ———- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 123207 5.38 5.37 0 128827 0 1848082 ——- —— ——– ———- ———- ———- ———- ———- total 123209 5.38 5.37 0 128827 0 1848082 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 51 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ———- ———- ———- ————————————————— 1848082 1848082 1848082 INDEX RANGE SCAN MY_IOT_PK (cr=128827 pr=0 pw=0 time=2299570 us cost=6325 size=36991280 card=1849564)(object id 68953) performance IOT con filtered query e colonne non indicizzate 16:39:03 SQL> select value from my_iot where value=’values1907343′;
Elapsed: 00:00:01.73
Execution Plan
———————————————————-
Plan hash value: 3577746055
———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 15 | 18751 (2)| 00:03:46 |
|* 1 | INDEX FAST FULL SCAN| MY_IOT_PK | 1 | 15 | 18751 (2)| 00:03:46 |
———————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(“VALUE”=’values1907343′)
Statistics
———————————————————-
1 recursive calls
2 db block gets
69617 consistent gets
69240 physical reads
0 redo size
430 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
A queste condizioni su una colonna non appartenente alla chiave primaria viene effettuata una scansione completa degli indici.
performance IOT in un accesso full data
16:28:28 SQL> select * from my_iot;
20000000 rows selected.
Elapsed: 00:01:44.42
Execution Plan
———————————————————-
Plan hash value: 3577746055
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 17M| 665M| 18695 (1)| 00:03:45 |
| 1 | INDEX FAST FULL SCAN| MY_IOT_PK | 17M| 665M| 18695 (1)| 00:03:45 |
———————————————————————————-
Note
—–
– dynamic sampling used for this statement (level=2)
Statistic
———————————————————-
5 recursive calls
4 db block gets
1401507 consistent gets
68874 physical reads
0 redo size
601010631 bytes sent via SQL*Net to client
14667082 bytes received via SQL*Net from client
1333335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20000000 rows processed
Anche in caso di select * lo scan completo di una IOT è migliore che in una heap table.
HEAP TABLE
SQL>CREATE TABLE my_heap (id INTEGER CONSTRAINT my_heap_pk PRIMARY KEY, value VARCHAR2(50)) SEGMENT CREATION IMMEDIATE;
Table created.
SQL>select segment_name,segment_type from user_segments where segment_name like ‘%HEAP%’;
SEGMENT_NAME SEGMENT_TYPE
——————– ——————
MY_HEAP_PK INDEX
MY_HEAP TABLE
performance HEAP con filtered query usando colonna PK
SQL> select * from my_heap where id=45723;
Elapsed: 00:00:00.25
Execution Plan
———————————————————-
Plan hash value: 35804330
——————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_HEAP | 1 | 20 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | MY_HEAP_PK | 1 | | 2 (0)| 00:00:01 |
——————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“ID”=45723)
Statistics
———————————————————-
1 recursive calls
0 db block gets
5 consistent gets
3 physical reads
116 redo size
397 bytes sent via SQL*Net to client
408 bytes recived via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
performance HEAP con filtered query con clausola between
17:15:06 SQL> select * from my_heap where id between 48374 and 1896455;
1848082 rows selected.
Elapsed: 00:00:15.59
Execution Plan
———————————————————-
Plan hash value: 4034075989
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1847K| 35M| 19520 (2)| 00:03:55 |
|* 1 | TABLE ACCESS FULL| MY_HEAP | 1847K| 35M| 19520 (2)| 00:03:55 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“ID”<=1896455 AND “ID”>=48374)
Statistics
———————————————————-
1 recursive calls
0 db block gets
193077 consistent gets
70281 physical reads
0 redo size
52853388 bytes sent via SQL*Net to client
1355674 bytes received via SQL*Net from client
123207 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1848082 rows processed
Con una heap table, la ricerca con between non utilizza nessun indice.
performance HEAP con filtered query con clusola order by
17:18:51 SQL> select * from luca.my_heap where id between 48374 and 1896455 order by id;
1848082 rows selected.
Elapsed: 00:00:11.87
Execution Plan
———————————————————-
Plan hash value: 1911394047
——————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 1847K| 35M| 21924 (1)| 00:04:24 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_HEAP | 1847K| 35M| 21924 (1)| 00:04:24 |
|* 2 | INDEX RANGE SCAN | MY_HEAP_PK | 1847K| | 3599 (1)| 00:00:44 |
——————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“ID”>=48374 AND “ID”<=1896455) Statistics ———————————————————- 1 recursive calls 0 db block gets 266319 consistent gets 11972 physical reads 286540 redo size 60245660 bytes sent via SQL*Net to client 1355674 bytes received via SQL*Net from client 123207 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1848082 rows processed SQL ID: anpvrqvhq834z Plan Hash: 1911394047 select * from luca.my_heap where id between 48374 and 1896455 order by id call count cpu elapsed disk query current rows ——- —— ——– ———- ———- ———- ———- ———- Parse 1 0.01 0.12 2 52 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 123207 9.66 66.13 19565 262967 0 1848082 ——- —— ——– ———- ———- ———- ———- ———- total 123209 9.67 66.26 19567 263019 0 1848082 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 51 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ———- ———- ———- ————————————————— 1848082 1848082 1848082 TABLE ACCESS BY INDEX ROWID MY_HEAP (cr=262967 pr=19565 pw=0 time=52053332 us cost=21924 size=36963180 card=1848159) 1848082 1848082 1848082 INDEX RANGE SCAN MY_HEAP_PK (cr=126070 pr=3593 pw=0 time=1898739 us cost=3599 size=0 card=1848159)(object id 68957) performance HEAP con filtered query e colonne non indicizzate 17:17:00 SQL> select value from my_heap where value=’values1907343′;
Elapsed: 00:00:00.76
Execution Plan
———————————————————-
Plan hash value: 4034075989
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 15 | 19542 (3)| 00:03:55 |
|* 1 | TABLE ACCESS FULL| MY_HEAP | 1 | 15 | 19542 (3)| 00:03:55 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“VALUE”=’values1907343′)
Statistics
———————————————————-
1 recursive calls
0 db block gets
70286 consistent gets
70281 physical reads
0 redo size
430 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Di seguito la sintesi sui segmenti relativi a IOT e Heap Table + indici.
Si può notare come le tabelle IOT consumino meno spazio (640MB) rispetto a heap table + index segments tradizionale (560MB+320MB).
SEGMENT_NAME BYTES/1024/1024
————————————————– —————
MY_HEAP 560
MY_HEAP_PK 320
MY_IOT_PK 616

Potrebbero interessarti anche...