One of the really cool new features introduced in Oracle 12c
release 2 is the capability to move tables ONLINE, while maintain all the associated
index structures. This was possible for table partitions in Oracle 12c release
1, but only for index organized tables before then.
I will start by creating a table and specifically populate a
CODE column with 100 distinct values that are distributed throughout the entire
table structure.
demo@ORA12C> create table t as
2 select rownum as id ,
3 mod(rownum,100) as code,
4 sysdate - mod(rownum,1000) as created,
5 object_name
6 from all_objects ,
7 all_users
8 where rownum <= 2000000;
Table created.
demo@ORA12C>
If we create an index on the CODE column, we will see the index has a
terrible clustering factor, as the values basically appear throughout the
entire table structure in each of the table blocks.
demo@ORA12C> create index t_idx on t(code) nologging;
Index created.
demo@ORA12C> select i.index_name,
i.clustering_factor,i.status,
2 t.num_rows, t.blocks
3 from user_tables t ,
4 user_indexes i
5 where t.table_name = i.table_name
6 and t.table_name ='T';
INDEX_NAME
CLUSTERING_FACTOR STATUS
NUM_ROWS BLOCKS
------------- ----------------- -------- ---------- ----------
T_IDX 1583789 VALID 2000000 16663
If we now run a query that returns rows for just one CODE values (i.e. just
1% of the table).
demo@ORA12C> set autotrace traceonly explain statistics
demo@ORA12C> select * from t where code = 42;
20000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 20000 |
1054K| 4542 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL|
T | 20000 | 1054K|
4542 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("CODE"=42)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
16636 consistent gets
16492
physical reads
0 redo size
1153360 bytes sent via SQL*Net to client
2071 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20000 rows processed
demo@ORA12C> set autotrace off
We can see the 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 are only interested in just 1% of the table.
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. So reorganize the table such that data in the table is sorted / clustered
in CODE order.
Oracle 12c release 1 (12.1.0.2) introduced the concept of table
attribute clustering, by which table after a reorg or bulk load will store the
data based on this clustering attribute.
demo@ORA12C> alter table t add clustering by linear
order(code);
Table altered.
demo@ORA12C>
This now ensures that data within the table will be in CODE order after
a table re-org, but we need to reorg this table for this to take effect.
However prior to 12.2 it wasn’t possible to subsequently MOVE the table ONLINE.
Online Table move is not possible prior to 12.2 (moving a table online
in 11g (11.2.0.4) database, produces this error)
demo@ORA11G> alter table t
move ONLINE;
alter table t move ONLINE
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
Moving a table previously caused locking issues and resulted in all
associated index being unusable and having to rebuilt, further adding to the
effective unavailability of the table.
demo@ORA11G> alter table t
move ;
alter table t move
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT
specified or timeout expired
demo@ORA11G> alter table t
move ;
Table altered.
demo@ORA11G> select
i.index_name, i.clustering_factor,i.status,
2 t.num_rows, t.blocks
3 from user_tables t ,
4 user_indexes i
5 where t.table_name = i.table_name
6 and t.table_name ='T';
INDEX_NAME
CLUSTERING_FACTOR STATUS
NUM_ROWS BLOCKS
------------- ----------------- -------- ---------- ----------
T_IDX
1301875 UNUSABLE 2000000
13174
demo@ORA11G>
This now got changes in 12c release 2, where we can now move tables
online while fully maintaining all associated indexes.
demo@ORA12C> alter table t move ONLINE;
Table altered.
demo@ORA12C> select i.index_name,
i.clustering_factor,i.status,
2 t.num_rows, t.blocks
3 from user_tables t ,
4 user_indexes i
5 where t.table_name = i.table_name
6 and t.table_name ='T';
INDEX_NAME
CLUSTERING_FACTOR STATUS NUM_ROWS
BLOCKS
-------------- ----------------- -------- ---------- ----------
T_IDX
16491 VALID
2000000 16663
After the table re-org, we note that not only is the index still a valid
state, but because the table re-org effectively sorted the data in CODE order
due to the attribute clustering, it has resulted in much more reduced
clustering factor (just 16,491 reduced down from 1,583,789)
If we re-run our original query:
demo@ORA12C> select * from t where code = 42;
20000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 767293772
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 20000 | 1054K|
206 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
20000 | 1054K| 206
(0)| 00:00:01 |
|* 2 | INDEX RANGE
SCAN | T_IDX | 20000 | |
41 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("CODE"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
476 consistent gets
0 physical reads
0 redo size
1214141 bytes sent via SQL*Net to client
2071 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20000 rows processed
demo@ORA12C>
We noticed that index is now used and has resulted in a significant
reduction of consistent gets (just 476 down from 16636), the query is much more
efficient than it was previously.
No comments:
Post a Comment