Skip to Main Content
  • Questions
  • Asynchronous Global Indexing does not work as expected in 12c in a Database upgraded from 11g to 12c

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Deepal.

Asked: June 02, 2017 - 5:53 am UTC

Last updated: June 06, 2017 - 8:12 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked


We observed following.
1) 11g database is upgraded to 12c. Set the COMPATIBLE parameter to 12c version. Restart the DB. Created a table, partitioned the table and insert data into that table. Then dropped the partition. This works as expected in 12c.

2) Table and the partitions were there in the 11g database. 11g database is upgraded to 12c. COMPATIBLE parameter is set to 12c version. Restart the DB. Then dropped the existing partition. Then the partition drop does not work as expected in 12c. It works in the old 11g manner.

We want to make sure scenario given in point 2 above works as expected in 12c.

What is the issue with database with tables, data and partitions upgraded to 12c ? Why the partition drop does not work after setting the COMPATIBLE parameter to 12c version (These tables, partitions and Global indexes were there in 11g database and database upgraded to 12c)? Is there anything else need to be done in order to make the partitioned tables and global indexes compatible to 12c?

11g version: 11.2.0.4
12c version: 12.1.0.2

Upgrade done using DBUA

and Connor said...

I think you'll need to get in touch with Support with a full test case, because I cant reproduce that behaviour. Here's my test case

--
-- 11.2.0.4 database
--
SQL> select sys.database_name from dual;

DATABASE_NAME
----------------------------------------------------------------------------------------------------------------------------------
DBPAR

1 row selected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
SQL>
SQL> create table t ( x int, y1 int, y2 int, z char(200) )
  2  partition by range ( x )
  3  (
  4    partition p1 values less than ( 500000 ),
  5    partition p2 values less than ( 1000000 ),
  6    partition p3 values less than ( 9999999)
  7  );

Table created.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rownum, rownum, rownum, rownum
  3  from
  4  ( select 1 from dual connect by level <= 2000 ),
  5  ( select 1 from dual connect by level <= 2000 )
  6  ;

4000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index ix1 on t ( y1 );

Index created.

SQL>
SQL> create index ix2 on t ( y2 )
  2  global partition by range ( y2 )
  3  (
  4    partition p1 values less than ( 800000 ),
  5    partition p2 values less than ( maxvalue )
  6  );

Index created.

SQL>
SQL> conn mcdonac/*******
Connected.

SQL> select n.name, s.value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and n.name in ('redo size','db block gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                             3
redo size                                                               792

2 rows selected.

SQL> set timing on
SQL> alter table t drop partition p2 update global indexes;

Table altered.

Elapsed: 00:00:01.63
SQL> set timing off
SQL> select n.name, s.value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and n.name in ('redo size','db block gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                         25831
redo size                                                          21849936

2 rows selected.

SQL>

--
-- So you can see from above...big redo cost, and 'slow' (ie, 1.5 seconds) 
-- to drop the partition.  
--
-- Now I'll create an identical copy of that table, but not do the index maintenance
-- until we've upgrade to 12c
--

SQL> create table t1 ( x int, y1 int, y2 int, z char(200) )
  2  partition by range ( x )
  3  (
  4    partition p1 values less than ( 500000 ),
  5    partition p2 values less than ( 1000000 ),
  6    partition p3 values less than ( 9999999)
  7  );

Table created.

SQL>
SQL> insert /*+ APPEND */ into t1
  2  select rownum, rownum, rownum, rownum
  3  from
  4  ( select 1 from dual connect by level <= 2000 ),
  5  ( select 1 from dual connect by level <= 2000 )
  6  ;

4000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index ixt1 on t1 ( y1 );

Index created.

SQL>
SQL> create index ixt2 on t1 ( y2 )
  2  global partition by range ( y2 )
  3  (
  4    partition p1 values less than ( 800000 ),
  5    partition p2 values less than ( maxvalue )
  6  );

Index created.


--
-- So now I upgraded the database to 12.1.0.2 using standard dbua.  By default, the 
-- database is 12c but compatible is 11, so I need to adjust that and bounce 
-- the database to get the change
--

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
noncdb_compatible                    boolean     FALSE
SQL>
SQL> alter system set compatible = '12.1.0.2.0' scope=spfile;

System altered.

SQL> conn / as sysdba
Connected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size                  3047568 bytes
Variable Size             603983728 bytes
Database Buffers         1476395008 bytes
Redo Buffers               13725696 bytes
Database mounted.
Database opened.

SQL> conn mcdonac/******
Connected.


SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE
SQL>
SQL>

--
-- So now I am an 12c version and 12c compatible.  Let's redo that same
-- test now with table T1
--

SQL> select n.name, s.value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and n.name in ('redo size','db block gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                             6
redo size                                                              1496

SQL>
SQL> set timing on
SQL> alter table t1 drop partition p2 update global indexes;

Table altered.

Elapsed: 00:00:00.11
SQL> set timing off
SQL>
SQL> select n.name, s.value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and n.name in ('redo size','db block gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                           137
redo size                                                             20844



As you can see - minimal redo and very fast. The index maintenance has been deferred.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.