Oracle 12.2 tables, indexes new features

The Oracle 12.2.0.1 version has some interesting new features  concerning tables or indexes.

The first new feature is about the online table move.

In 12.2 version Oracle offers now the possibility to move non-partitioned tables without blocking any DML operations.

To realise this operation, we must use the ONLINE keyword and/or the UPDATE_INDEXES clause. If you remember, in the previous Oracle version, we encountered the classical ORA-01502 error.

If you remember in version 12.1, we have the following behaviour, we create a table with a constraint and we insert some values:

SQL> create table emp (name varchar2(10), salary number );
Table created.
SQL> alter table emp add constraint emp_pk primary key (name);
Table altered.
SQL> insert into emp values ('Bill', 100000);
1 row created.
SQL> insert into emp values ('Larry', 10000000);
1 row created.
SQL> commit;
Commit complete.

SQL> select * from emp;
NAME           SALARY
 ---------- ----------
 Bill           100000
 Larry         10000000

Then if  we move the table to another tablespace, the index become unusable, and if we try to insert some data we receive the ORA-1502 error:

SQL> alter table emp move tablespace PSI;
Table altered.
 
SQL> select index_name, status from user_indexes;
 
INDEX_NAME        STATUS
EMP_PK           UNUSABLE
 
SQL> insert into emp values ('Pierre', 99999);
insert into emp values ('Pierre', 99999)
*
ERROR at line 1:
ORA-01502: index 'PSI.EMP_PK' or partition of such index is in unusable state

Now in 12.2 version, we do not have this problem anymore:

SQL> create table emp (name varchar2(10), salary number);
Table created.
 
SQL> alter table emp add constraint emp_pk primary key (name);
Table altered.
 
SQL> insert into emp values ('Bill', 100000);
1 row created.
 
SQL> insert into emp values ('Larry', 999999); 
1 row created.
 
SQL> commit;
Commit complete.
 
SQL> select * from emp;
 
NAME       SALARY
---------- ----------
Bill         100000
Larry        999999
 
 
SQL> select index_name, status from user_indexes;
 
INDEX_NAME        STATUS
EMP_PK            VALID
 
SQL> alter table emp move tablespace PSI2 update indexes;
Table altered.
 
SQL> select index_name, status from user_indexes;
INDEX_NAME        STATUS
EMP_PK            VALID
 
SQL> insert into emp values ('Pierre', 99999);
1 row created.

Using this way, we also can move indexes in different tablespaces:

SQL> alter table emp move online tablespace PSI
  2  update indexes
  3  (emp_pk tablespace psi_ix1,
  4  emp_ix2 tablespace psi_ix1);
 
Table altered.
 
SQL> select index_name, status, tablespace_name from user_indexes;
 
INDEX_NAME.   STATUS TABLESPACE_NAME
EMP_PK        VALID  PSI_IX1
EMP_IX2       VALID  PSI_IX1

Another interesting new feature is about the conversion to a partitioned table. Before the Oracle 12.2 version, the methods used to convert a non-partitioned table to a partitioned table were not online or were using dbms_redefinition. Now in 12.2 we have the possibility to realize the operation in online mode:

SQL> create table emp (name varchar2(10), emp_id number, salary number);

Table created.

SQL> insert into emp values(‘Larry’, 1, 1000000);

1 row created.

SQL> insert into emp values (‘Bill’, 100, 999999);

1 row created.

SQL> insert into emp values (‘Steve’, 1000, 1000000);

1 row created.

SQL> alter table emp modify

partition by range (emp_id) interval (100)

(partition p1 values less than (50),

partition p2 values less than (500),

partition p3 values less than (5000)

) online;

Table altered.

SQL> select table_name , partition_name, high_value from user_tab_partitions;

TABLE_NAME.     PARTITION_NAME      HIGH_VALUE

EMP                                  P1                                      50

EMP                                  P2                                     500

EMP                                  P3.                                    5000

As you can see, this is really an easy way to move a non partitioned table to a partitioned table.

The next new feature I will talk about is concerning the advanced compression. For example, we have the possibility to create a tablespace with such an argument:

SQL> create tablespace psi_ix_compress
  2  default index compress advanced high
  3  datafile '/u01/oradata/db1/db1pdb1/psi_ix_comp01.dbf' size 10M;
 
Tablespace created.

Every new index created in this tablespace will use high advanced compression. But at first approach it does not seem to work very well:

SQL> create index psi_ix2 on emp(salary) tablespace psi_ix_compress;
 
Index created.
 
SQL> select index_name, compression from user_indexes;
 
INDEX_NAME             COMPRESSION
EMP_PK                  DISABLED
EMP_IX2                 DISABLED
PSI_IX2                 DISABLED

But if you have a more precise look, there is a parameter you have also to modify:

SQL> show parameter db_index_compression_inheritance
 
NAME                                  TYPE VALUE
db_index_compression_inheritance     string NONE

SQL> alter system set db_index_compression_inheritance = 'TABLESPACE';
 
System altered.

And finally, it works fine:

SQL> create index psi_ix1_comp on emp (name, emp_id,salary) tablespace psi_ix_compress;
Index created.
 
SQL> select index_name, compression from user_indexes where index_name like '%COMP';
INDEX_NAME.      COMPRESSION
PSI_IX1_COMP     ADVANCED HIGH

We have the possibility to specify the ADVANCED COMPRESS HIGH or LOW argument in the create or rebuild statement:

SQL> create index psi_ix1 on emp (salary) compress advanced low;
 
Index created.
 
SQL> select index_name, compression from user_indexes;
 
INDEX_NAME         COMPRESSION
EMP_PK              DISABLED
PSI_IX1             ADVANCED LOW
PSI_NEW             DISABLED

And we can use alter index rebuild to modify to high advanced compression:

SQL> alter index psi_ix1 rebuild compress advanced high;
Index altered.
 
SQL> select index_name, compression from user_indexes;
 
INDEX_NAME.        COMPRESSION
EMP_PK              DISABLED
PSI_IX1.            ADVANCED HIGH
PSI_NEW             DISABLED

Enjoy using those Oracle 12.2.0.1 new features !