IOT Secondary Indexes – The Logical ROWID Guess Component Part II (Move On) May 8, 2012
Posted by Richard Foote in Index Block Size, Index Organized Tables, IOT, ROWID, Secondary Indexes.trackback
Having mentioned a couple of dangers associated with IOT Secondary Indexes, thought I might discuss a couple of their nicer attributes.
In the previous post, we saw how 50-50 index block splits on the ALBUM_SALES_IOT IOT table caused rows to move to new leaf blocks, resulting in a degradation in the PCT_DIRECT_ACCESS value of the associated ALBUM_SALES_IOT_TOTAL_SALES_I secondary index, which in turn resulted in poorer performance when using this index. We had to rebuild the secondary index (or update block references) to make all the “guess” components accurate and the index efficient again and so point to the correct locations within the parent IOT.
So, if you have 50-50 block splits occurring in your IOT, this will degrade the efficiency of the associated IOT Secondary indexes over time.
However, if you don’t have 50-50 block splits and the entries in the IOT don’t move from leaf block to leaf block, then this will not be an issue. Remembering of course that many Primary Key values are based on a sequence which monotonically increases and results in 90-10 block splits rather than 50-50 block splits. 90-10 block splits don’t move data around, Oracle leaves the full blocks alone and simply adds a new block in the IOT Btree structure into which new values are added. Therefore, with IOT data not moving around, the “guess” component of the logical ROWIDS remain valid and don’t go stale over time and so the associated secondary indexes remain nice and efficient.
If we look at the current state of the ALBUM_SALES_IOT_TOTAL_SALES_I secondary index:
SQL> SELECT index_name, pct_direct_access, iot_redundant_pkey_elim FROM dba_indexes WHERE index_name = 'ALBUM_SALES_IOT_TOTAL_SALES_I'; INDEX_NAME PCT_DIRECT_ACCESS IOT ------------------------------ ----------------- --- ALBUM_SALES_IOT_TOTAL_SALES_I 100 NO
We notice the PCT_DIRECT_ACCESS is currently nice and efficient at 100%.
If we now add a bunch of new rows into the IOT, but this time with PK values that monotonically increase:
SQL> BEGIN 2 FOR i IN 5001..10000 LOOP 3 FOR c IN 201..300 LOOP 4 INSERT INTO album_sales_iot VALUES(i,c,ceil(dbms_random.value(1,5000000)), 'Yet more new rows'); 5 END LOOP; 6 END LOOP; 7 COMMIT; 8 END; 9 / PL/SQL procedure successfully completed.
And collect fresh statistics:
SQL> exec dbms_stats.gather_table_stats(ownname=>'BOWIE', tabname=>'ALBUM_SALES_IOT', estimate_percent=>null, cascade=>true, method_opt=> 'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed. SQL> SELECT index_name, pct_direct_access, iot_redundant_pkey_elim FROM dba_indexes WHERE index_name = 'ALBUM_SALES_IOT_TOTAL_SALES_I'; INDEX_NAME PCT_DIRECT_ACCESS IOT ------------------------------ ----------------- --- ALBUM_SALES_IOT_TOTAL_SALES_I 100 NO
We notice that the PCT_DIRECT_ACCESS value remains unchanged. So, no 50-50 block split, no PCT_DIRECT_ACCESS degradation with regard the secondary indexes.
OK, another nice feature with IOT Secondary Indexes.
With a “normal” Heap table, if we were to MOVE and reorganise the table, all associated indexes become invalid as the Move results in all the rows being relocated and the indexes are not maintained during this process (as this would add considerably to the overhead in the Move process). All associated indexes have to be rebuilt after the Move operation completes, which is both expensive and adds considerably to the availability issues associated with the whole table reorg process as the table is locked during the Move operation. In short, moving a heap table is an expensive and an availability unfriendly process.
As this little demo illustrates, moving a heap table results in all indexes becoming unusable:
SQL> create table radiohead (id number constraint radiohead_pk primary key, code number, name varchar2(30)); Table created. SQL> create index code_i on radiohead(code); Index created. SQL> insert into radiohead select rownum, rownum, 'OK COMPUTER' from dual connect by level <= 100000; 100000 rows created. SQL> commit; Commit complete. SQL> select index_name, status from dba_indexes where table_name = 'RADIOHEAD'; INDEX_NAME STATUS ------------------------------ -------- RADIOHEAD_PK VALID CODE_I VALID SQL> alter table radiohead move; Table altered. SQL> select index_name, status from dba_indexes where table_name = 'RADIOHEAD'; INDEX_NAME STATUS ------------------------------ -------- RADIOHEAD_PK UNUSABLE CODE_I UNUSABLE
However, moving an IOT has a number of advantages over a heap table.
Firstly, as it’s an index structure, it can be reorganised and rebuilt in much the same way as we can rebuild any btree index. Remembering, an index can be rebuilt “online” (on Enterprise Edition), overcoming many of the locking issues associated with moving heap tables.
Additionally, although the physical locations of all the rows in the IOT change following a Move operation, the PK values themselves don’t change. Therefore, although the PCT_DIRECT_ACCESS value becomes 0, the indexes themselves are still Valid and usable as the PK component can still be used to access the relevant data.
So the syntax to move an IOT table can be expanded to be performed “Online” and all the secondary indexes will remain “Valid”:
SQL> select index_name, status, PCT_DIRECT_ACCESS from dba_indexes where table_name = 'ALBUM_SALES_IOT'; INDEX_NAME STATUS PCT_DIRECT_ACCESS ------------------------------ -------- ----------------- ALBUM_SALES_IOT_PK VALID 0 ALBUM_SALES_IOT_TOTAL_SALES_I VALID 100 ALBUM_SALES_IOT_COUNTRY_ID_I VALID 100 SQL> alter table album_sales_iot move online; Table altered. SQL> select index_name, status, PCT_DIRECT_ACCESS from dba_indexes where table_name = 'ALBUM_SALES_IOT'; INDEX_NAME STATUS PCT_DIRECT_ACCESS ------------------------------ -------- ----------------- ALBUM_SALES_IOT_PK VALID 0 ALBUM_SALES_IOT_TOTAL_SALES_I VALID 0 ALBUM_SALES_IOT_COUNTRY_ID_I VALID 0
So although the PCT_DIRECT_ACCESS values for the secondary indexes has gone down to 0, making them less efficient as a result, they do at least remain valid and usable by the CBO:
SQL> select * from album_sales_iot where total_sales between 424242 and 424343; 26 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1433198708 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 858 | 68 (0)| 00:00:01 | |* 1 | INDEX UNIQUE SCAN| ALBUM_SALES_IOT_PK | 33 | 858 | 68 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN| ALBUM_SALES_IOT_TOTAL_SALES_I | 33 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TOTAL_SALES">=424242 AND "TOTAL_SALES"<=424343) 2 - access("TOTAL_SALES">=424242 AND "TOTAL_SALES"<=424343) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 83 consistent gets 53 physical reads 0 redo size 1655 bytes sent via SQL*Net to client 534 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 26 rows processed
The secondary index is still used by the CBO, although at 83 consistent gets in this example, it’s not as efficient as it could be.
The rebuild of the secondary index can be performed subsequently to repair the stale guesses and improve the efficiency of the index as desired:
SQL> alter index album_sales_iot_total_sales_i rebuild online; Index altered. SQL> select index_name, status, PCT_DIRECT_ACCESS from dba_indexes where table_name = 'ALBUM_SALES_IOT'; INDEX_NAME STATUS PCT_DIRECT_ACCESS ------------------------------ -------- ----------------- ALBUM_SALES_IOT_PK VALID 0 ALBUM_SALES_IOT_TOTAL_SALES_I VALID 100 ALBUM_SALES_IOT_COUNTRY_ID_I VALID 0 SQL> select * from album_sales_iot where total_sales between 424242 and 424343; 26 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1433198708 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 858 | 36 (0)| 00:00:01 | |* 1 | INDEX UNIQUE SCAN| ALBUM_SALES_IOT_PK | 33 | 858 | 36 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN| ALBUM_SALES_IOT_TOTAL_SALES_I | 33 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TOTAL_SALES">=424242 AND "TOTAL_SALES"<=424343) 2 - access("TOTAL_SALES">=424242 AND "TOTAL_SALES"<=424343) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 31 consistent gets 0 physical reads 0 redo size 1655 bytes sent via SQL*Net to client 534 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 26 rows processed
Following the rebuild of the secondary index and getting the PCT_DIRECT_ACCESS back to 100%, the example query is now more efficient, with a reduction of consistent gets down from 83 to just 31.
So IOTs can be less problematic to reorganise and if 90-10 block splits are performed, the impact on the secondary indexes is minimised.
Neat. The first point is excellent.
The second is less relevant (to me), but good to know. 🙂
LikeLike
Thanks Brian 🙂
LikeLike
Hi Richard,
I guess that the rules for rebuilding IOTs are similar to the rules for rebuilding indexes, so they are only necessary under special circumstances. Is this correct?
Regards
Martin
LikeLike
Hi Martin
That’s indeed correct. In most cases, IOT look after themselves in much the same way as other B*Tree indexes.
However, the same may not be the case for any of their secondary indexes as having ever increasing stale logical rowids may lead to decrease performance over time. So they may need additional watching and attention as required. That said, updating the block references is generally a cheaper ultimate that actually rebuilding the whole secondary index.
LikeLike
Hi Richard,
that makes sense. Thank you
LikeLike
[…] So we have a table with a couple of indexes. We can’t move the table using the ONLINE option as it’s only applicable for Index Organized Tables: […]
LikeLike
[…] Secondary indexes (logical rowid) – pt.2 […]
LikeLike