12.2 Moving Tables Online (Real Cool World)

One of the really cool new features introduced in Oracle Database 12c Release 2 is the capability to MOVE tables ONLINE, while maintaining all the associated index structures. This was possible for table partitions in Oracle Database 12c Release 1 but only for Indexed Organized Tables before then.

Rather than show a demo that just moves a table, thought I might also provide a classic example of when moving a table might be extremely beneficial.

I’ll start by creating a table and specifically populate a CODE column with 100 distinct values that are distributed throughout the entire table structure:

SQL> create table ziggy (id number, code number, date_created date, name varchar2(42));

Table created.

SQL> insert into ziggy select rownum, mod(rownum,100), sysdate – mod(rownum,1000), DAVID BOWIE’ from dual connect by level <= 2000000; 2000000 rows created. SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'ZIGGY', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

If I create an index on the CODE column, we’ll see the index has a terrible Clustering Factor (a massive 923,900), as the values basically appear throughout the entire table in each of the table blocks:

SQL> create index ziggy_code_i on ziggy(code);

Index created.

SQL> select index_name, clustering_factor, num_rows from user_indexes
where index_name='ZIGGY_CODE_I';

INDEX_NAME           CLUSTERING_FACTOR   NUM_ROWS
-------------------- ----------------- ----------
ZIGGY_CODE_I                    923900    2000000

If we now run a query that returns rows from just one CODE value (i.e. just 1% of the table):

SQL> set arraysize 5000
SQL> select * from ziggy where code = 42;

20000 rows selected.

Execution Plan
-----------------------------------------------------------------------------------
| Id | Operation                 | Name  | Rows  | Bytes | Cost (%CPU) | Time     |
-----------------------------------------------------------------------------------
| 0  | SELECT STATEMENT          |       | 20000 |  546K |    2750 (1) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | ZIGGY | 20000 |  546K |    2750 (1) | 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE"=42)
filter("CODE"=42)

Statistics
----------------------------------------------------------
     0 recursive calls
     0 db block gets
  9354 consistent gets
     0 physical reads
     0 redo size
367165 bytes sent via SQL*Net to client
   533 bytes received via SQL*Net from client
     5 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
 20000 rows processed

We see the Cost Based Optimizer (CBO) ignores the index and opts for a Full Table Scan. With such a terrible Clustering Factor and with each block in the table having to be accessed, the CBO is making the correct decision here. The index is effectively useless, even though we’re only interested in just 1% of the table. At 9,354 consistent gets, it’s the best we can do.

If this query was important to us and executed frequently, we might want to look at improving the Clustering Factor and hence the efficiency of this index. Unlike rumors to the contrary (which unfortunately still exist, believe), the key here is not to rebuild the index, but to reorganise the table such that data in the table is sorted/clustered in CODE order.

Oracle database 12.1.0.2 introduced the concept of the table Clustering Attribute, by which the table after a reorg or bulk load will store the data based on this Clustering Attribute (as I’ve discussed previously). For example:

SQL> alter table ziggy add clustering by linear order(code) without materialized zonemap;

Table altered.

This now ensures that the data within the table will be in CODE order after a table re-org, but we need to re-org the table for this to take effect. However, prior to 12.2, it wasn’t possible to subsequently MOVE the table ONLINE:

SQL> alter table ziggy move online;
alter table ziggy move online
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Moving a heap table previously caused locking issues and resulted in all associated indexes beoming unusable and having to be rebuilt, further adding to the effective unavailability of the table:

SQL> alter table ziggy move;
alter table ziggy move
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> alter table ziggy move;

Table altered.

SQL> select index_name, leaf_blocks, status from dba_indexes where table_name = 'ziggy';

INDEX_NAME      LEAF_BLOCKS STATUS
--------------- ----------- --------
ZIGGY_CODE_I           1936 UNUSABLE

That’s now changed with Oracle Database 12c Release 2, where we can now move heap tables online while fully maintaining all associated indexes:

SQL> alter table ziggy move online;

Table altered.

SQL> select index_name, clustering_factor, num_rows, status from user_indexes
where index_name='ZIGGY_CODE_I';

INDEX_NAME      CLUSTERING_FACTOR   NUM_ROWS STATUS
--------------- ----------------- ---------- ------
ZIGGY_CODE_I                 9277    2000000 VALID

After the table re-org, we note that not only is the index still in a valid state, but because the table re-org effectively sorted the data in CODE order due to the Clustering Attribute, it has result in a much much reduced Clustering Factor (just 9,277 reduced down from 923,900).

If we now re-run our original query:

SQL> select * from ziggy where code=42;

20000 rows selected.

Execution Plan
----------------------------------------------------------------------------------------------------
| Id | Operation                           | Name         | Rows  | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT                    |              | 20000 |  546K |     126 (0) | 00:00:01 |
| 1  | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY        | 20000 |  546K |     126 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN                    | ZIGGY_CODE_I | 20000 |       |      33 (0) | 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
     0 recursive calls
     0 db block gets
   136 consistent gets
     0 physical reads
     0 redo size
670725 bytes sent via SQL*Net to client
   533 bytes received via SQL*Net from client
     5 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
 20000 rows processed

We notice the index is now used and has resulted in a significant reduction of consistent gets (just 136, down from 9,354). The query is now much much more efficient than it was previously.

An issue here though is that we have to re-org the entire table in order to address the clustering of any new rows that are subsequently added with standard DML (as the Clustering Attribute is ineffective in this case).

Wouldn’t it be nice if we could easily convert this table (and associated indexes) to be partitioned and hence only have to concern ourselves with subsequently reorganising just those partitions that have new/changed data.

Topic of my next post 🙂