Oracle Database 12c Deferred Global Index

    Apr 7, 2017 11:00:00 AM by Deiby Gomez

    Introduction

    Before Oracle 12.1.0.1, whenever a table partition was dropped all the global indexes that were created on that table became UNUSABLE unless we specified the clause UPDATE INDEXES in the ALTER TABLE (…) DROP PARTITION sentence. If we didn’t specify the clause UPDATE INDEX then the index had to be rebuilt. Why? Look at the following picture:

    In this picture we have a table with 2 columns, 5 rows. And an index created on that table, the index was created on the column “ID” of the table and its index entries include the row address (ROWID). The ROWID is an address to the physical location of a row (which datafile, which block, which position in a block).

    Now, think what would happen if we dropped the table partition 2, which includes the values “Argentina” and “Colombia: It would result in something like the following:

    We see that the values “Agentina” and “Colombia” don’t exist anymore because they were deleted when the partition was dropped. However, we see on the left side that the Index entries related to the values of the dropped table partition still exist into the index. Now they are orphaned index entries because the rows in the table where they are pointing don’t exist anymore. So in this situation what the index needs is: Maintenance.

    For small indexes the performance overhead related to the index maintenance could be acceptable, but when we are using big tables and create global indexes on them, our indexes could also be also big (GBs). Rebuilding an index impacts performance since it uses several IO resources to scan the table and creates B-tree (or bitmap) branch and leaf nodes. The performance problem increases if the table is large, as we said previously. In the other hand, if we use the UPDATE INDEXES clause we get the following benefits:

    • The indexes are updated with the base table operation. You are not required to update later or rebuild the indexes.
    • The global indexes are more highly available, because they are not marked UNUSABLE. These indexes can be used with a small performance overhead while the index completes its maintenance (all the orphaned entries are fixed).
    • You don’t have to determine which indexes are dependent on the table from which we are dropping the partition. All the dependent indexes get maintained automatically without DBA intervention.

    There is a trick you can use to drop a table partition without making the global indexes unusable; however, that trick is not always possible. You can read about this trick in my article “The trick of dropping a table partition without impact the Global Index”.

    Oracle has fixed this downside of performing the index maintenance immediately after to drop a table partition and in Oracle Database 12.1.0.1 has introduced the feature “Deferred Global Index”, which allows you to drop a table partition, to keep the dependent global indexes available and in USABLE state, and leave the maintenance for later. The DBA can decide when the index maintenance is performed via a job “PMO_DEFERRED_GIDX_MAINT_JOB” that exists by default. Oracle is able to use an index that has orphaned entries to recover rows from a table without compromising the reliability of the rows returned.

    To explain how this works, I will drop a table partition in a database version 11.2.0.3 and we will see that when we specify UPDATE INDEXES, the index maintenance is performed immediately. For large indexes this could cause performance degradation in the database.

     

    Dropping a table partition in Oracle Database 11.2.0.3:

    Creating a database for testing purposes: 

    SQL> create table dgomez.table1 (col1 varchar2(20))
    partition by list (col1)
    (partition dgomez_table1_p1 VALUES ('guatemala'),
    partition dgomez_table1_p2 VALUES ('brasil'),
    partition dgomez_table1_p3 VALUES ('colombia'));

    Table created.

     Inserting some rows into the table:

    SQL> insert into dgomez.table1 values ('guatemala');
    SQL> insert into dgomez.table1 values ('brasil');
    SQL> insert into dgomez.table1 values ('colombia');
    SQL> commit;

    Gathering stats so that we can query the right metadata:

    SQL> exec dbms_stats.gather_table_stats('DGOMEZ','TABLE1');
    PL/SQL procedure successfully completed.

    Checking how many rows there are in each table partition. One row was created in one specific partition. The partition “DGOMEZ_TABLE_P1” has the value “guatemala”, the next partition the value “brasil”, and the last partition the value “colombia”.

    SQL> select TABLE_NAME, PARTITION_NAME,NUM_ROWS from dba_tab_partitions where TABLE_OWNER='DGOMEZ' and table_name='TABLE1'

    TABLE_NAME PARTITION_NAME       NUM_ROWS
    ---------- -------------------- ----------
    TABLE1     DGOMEZ_TABLE1_P1     1
    TABLE1     DGOMEZ_TABLE1_P2     1
    TABLE1     DGOMEZ_TABLE1_P3     1

     

    Creating a Global Index

    SQL> create index dgomez.index1 on dgomez.table1 (col1) global ;

    Index created.

    Looking at the index internals, we can see that the the values were successfully indexed.

    Leaf block dump
    ===============
    header address 140176766509668=0x7f7d725fa264
    kdxcolev 0
    KDXCOLEV Flags = - - -
    kdxcolok 0
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 0
    kdxconro 3
    kdxcofbo 42=0x2a
    kdxcofeo 7967=0x1f1f
    kdxcoavs 7925
    kdxlespl 0
    kdxlende 0
    kdxlenxt 0=0x0
    kdxleprv 0=0x0
    kdxledsz 0
    kdxlebksz 8032
    row#0[8012] flag: ------, lock: 0, len=20
    col 0; len 6; (6): 62 72 61 73 69 6c -- brasil
    col 1; len 10; (10): 00 01 2d f8 01 00 05 32 00 00
    row#1[7990] flag: ------, lock: 0, len=22
    col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
    col 1; len 10; (10): 00 01 2d f9 01 00 09 32 00 00
    row#2[7967] flag: ------, lock: 0, len=23
    col 0; len 9; (9): 67 75 61 74 65 6d 61 6c 61 -- guatemala
    col 1; len 10; (10): 00 01 2d f7 01 00 01 32 00 00
    ----- end of leaf block dump -----

    Now we will drop a table partition using the UPDATE INDEXES clause and we will review the result:

    SQL> alter table dgomez.table1 drop partition dgomez_table1_p1 update indexes;
    Table altered.

    Looking at the Index internals after dropping a table partition allows us to see that the index maintenance was performed immediately. The index entry “guatemala” was marked with the tag “D”, which means the index entry was “deleted” and it can be reused by another index entry. If you want to know more about what this tag means you can read my presentation “Oracle Indexes: From the Concept to Internals”.

    Leaf block dump
    ===============
    header address 140655176049252=0x7fecd5cde264
    kdxcolev 0
    KDXCOLEV Flags = - - -
    kdxcolok 0
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 0
    kdxconro 3
    kdxcofbo 42=0x2a
    kdxcofeo 7967=0x1f1f
    kdxcoavs 7925
    kdxlespl 0
    kdxlende 1
    kdxlenxt 0=0x0
    kdxleprv 0=0x0
    kdxledsz 0
    kdxlebksz 8032
    row#0[8012] flag: ------, lock: 0, len=20
    col 0; len 6; (6): 62 72 61 73 69 6c -- brasil
    col 1; len 10; (10): 00 01 2d f8 01 00 05 32 00 00
    row#1[7990] flag: ------, lock: 0, len=22
    col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
    col 1; len 10; (10): 00 01 2d f9 01 00 09 32 00 00
    row#2[7967] flag: ---D--, lock: 2, len=23
    col 0; len 9; (9): 67 75 61 74 65 6d 61 6c 61 -- guatemala
    col 1; len 10; (10): 00 01 2d f7 01 00 01 32 00 00
    ----- end of leaf block dump -----

    In this case the index maintenance had as a result only one index entry marked as “D”. But think about large indexes: How long would index maintenance take to fix orphaned indexes in a global index of 600GB? how many session would be impacted with the performance degradation of this operation? We will see how Oracle enhancements have fixed this situation in the next section.

     

    Dropping a table partition in Oracle Database 12.1.0.2

    Creating a database for testing purposes:

    SQL> create table dgomez.table1 (col1 varchar2(20))
    partition by list (col1)
    (partition dgomez_table1_p1 VALUES ('guatemala'),
    partition dgomez_table1_p2 VALUES ('brasil'),
    partition dgomez_table1_p3 VALUES ('colombia'));

    Table created.

    Inserting some rows into the table: 

    SQL> insert into dgomez.table1 values ('guatemala');
    SQL> insert into dgomez.table1 values ('brasil');
    SQL> insert into dgomez.table1 values ('colombia');
    SQL> commit;

    Gathering stats so that we can query the right metadata:

    SQL> exec dbms_stats.gather_table_stats('DGOMEZ','TABLE1');

    PL/SQL procedure successfully completed.

     

    Checking how many rows there are in each table partition:

    SQL> select TABLE_NAME, PARTITION_NAME,NUM_ROWS from dba_tab_partitions where TABLE_OWNER='DGOMEZ' and table_name='TABLE1'

    TABLE_NAME PARTITION_NAME       NUM_ROWS
    ---------- -------------------- ----------
    TABLE1     DGOMEZ_TABLE1_P1     1
    TABLE1     DGOMEZ_TABLE1_P2     1
    TABLE1     DGOMEZ_TABLE1_P3     1

     

    Creating a Global Index:

    SQL> create index dgomez.index1 on dgomez.table1 (col1) global ;
    Index created.

    Looking at the index internals. The three values were successfully indexed. 

    Leaf block dump
    ===============
    header address 140082935337572=0x7f6799999264
    kdxcolev 0
    KDXCOLEV Flags = - - -
    kdxcolok 0
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 0
    kdxconro 3
    kdxcofbo 42=0x2a
    kdxcofeo 7967=0x1f1f
    kdxcoavs 7925
    kdxlespl 0
    kdxlende 0
    kdxlenxt 0=0x0
    kdxleprv 0=0x0
    kdxledsz 0
    kdxlebksz 8032
    row#0[8012] flag: -------, lock: 0, len=20
    col 0; len 6; (6): 62 72 61 73 69 6c -- brasil
    col 1; len 10; (10): 00 02 11 a2 01 80 08 8e 00 00
    row#1[7990] flag: -------, lock: 0, len=22
    col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
    col 1; len 10; (10): 00 02 11 a3 01 80 0c 8e 00 00
    row#2[7967] flag: -------, lock: 0, len=23
    col 0; len 9; (9): 67 75 61 74 65 6d 61 6c 61 -- guatemala
    col 1; len 10; (10): 00 02 11 a1 01 80 04 8e 00 00
    ----- end of leaf block Logical dump -----

     

    I will enable autotrace on my session to see that the index is indeed used:

    SQL> select * from dgomez.table1 where col1='brasil';

    COL1
    --------------------
    brasil

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1498168486

    ---------------------------------------------------------------------------
    | Id | Operation        | Name   | Rows | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |        | 1    | 7     | 1 (0)      | 00:00:01 |
    |* 1 | INDEX RANGE SCAN | INDEX1 | 1    | 7     | 1 (0)      | 00:00:01 |
    ---------------------------------------------------------------------------

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

    1 - access("COL1"='brasil')

    Look at the end of the output above, only one filter was used: COL1=’brasil’. It is important to note this because we will talk about it later.

    Reviewing if the index has orphaned entries. In Oracle 12.1.0.2 the column ORPHANED_ENTRIES was introduced. This column says if an index has orphaned index entries, the index is marked as a candidate for maintenance on the next execution of the job “PMO_DEFERRED_GIDX_MAINT_JOB”. In this case the index doesn’t have orphaned index entries, as we can see with the following query:

    SQL> select index_name, status,orphaned_entries from dba_indexes where owner='DGOMEZ' and index_name='INDEX1';

    INDEX_NAME STATUS   ORP
    ---------- -------- ---
    INDEX1     VALID    NO

    Now I will proceed to drop a table partition using the UPDATE INDEXES clause:

    SQL> alter table dgomez.table1 drop partition dgomez_table1_p1 update indexes;
    Table altered.

    Immediately after dropping the table partition, we can see that the indexes that depend on the table have orphaned index entries:

    SQL> select index_name, status,orphaned_entries from dba_indexes where owner='DGOMEZ' and index_name='INDEX1';

    INDEX_NAME STATUS   ORP
    ---------- -------- ---
    INDEX1     VALID    YES

    However, the behavior in Oracle 12.1.0.2 is different, because if we look at the index internals, the index entries have been not touched as they were in Oracle 11.2.0.3.

    Leaf block dump
    ===============
    header address 140533172339300=0x7fd06dd10264
    kdxcolev 0
    KDXCOLEV Flags = - - -
    kdxcolok 0
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 0
    kdxconro 3
    kdxcofbo 42=0x2a
    kdxcofeo 7967=0x1f1f
    kdxcoavs 7925
    kdxlespl 0
    kdxlende 0
    kdxlenxt 0=0x0
    kdxleprv 0=0x0
    kdxledsz 0
    kdxlebksz 8032
    row#0[8012] flag: -------, lock: 0, len=20
    col 0; len 6; (6): 62 72 61 73 69 6c -- brasil
    col 1; len 10; (10): 00 02 11 a2 01 80 08 8e 00 00
    row#1[7990] flag: -------, lock: 0, len=22
    col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
    col 1; len 10; (10): 00 02 11 a3 01 80 0c 8e 00 00
    row#2[7967] flag: -------, lock: 0, len=23
    col 0; len 9; (9): 67 75 61 74 65 6d 61 6c 61 -- guatemala
    col 1; len 10; (10): 00 02 11 a1 01 80 04 8e 00 00
    ----- end of leaf block Logical dump -----

    This means that Index maintenance was not performed immediately. This leads us to the question: Is the index usable if it has orphaned index entries? Yes, it is! I will run another query to confirm it:

    SQL> select * from dgomez.table1 where col1='guatemala';

    COL1
    --------------------
    guatemala


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1498168486

    ---------------------------------------------------------------------------
    | Id | Operation        | Name   | Rows | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |        | 1    | 7     | 1 (0)      | 00:00:01 |
    |* 1 | INDEX RANGE SCAN | INDEX1 | 1    | 7     | 1 (0)      | 00:00:01 |
    ---------------------------------------------------------------------------

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

    1 - access("COL1"='guatemala')
    filter(TBL$OR$IDX$PART$NUM("DGOMEZ"."TABLE1",0,8,0,"TABLE1".ROWID)=1)

    The query was able to use the index “INDEX1” even though the index has orphaned index entries and, more interestingly, the values were returned correctly. This confirms that an index with orphaned entries can be used to recover rows from a table without compromising the reliability of the data returned. But how? This is a good question; you can see at the end of AUTOTRACE that a new filter was used. This filter used the undocumented function called “TBL$OR$IDX$PART$NUM” which in fact is responsible for the magic here. Unfortunately, there is not much documentation on it, but I will provide some insights:

    Definition:

    TBL$OR$IDX$PART$NUM function is used to find which partition a particular row would belong to. This function is undocumented. It has the format TBL$OR$IDX$PART$NUM(PARTITIONED_TABLE_NAME,0,d#,p#,COLUMN_NAME) .

    I tried to use the function without knowing how it worked, but it was unsuccessful:

    SQL> select TBL$OR$IDX$PART$NUM("DGOMEZ"."TABLE1",0,8,0,'ROWID') from dual;
    select TBL$OR$IDX$PART$NUM("DGOMEZ"."TABLE1",0,8,0,'ROWID') from dual
    *
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [evapnum:dim8rowid], [], [], [], [],
    [], [], [], [], [], [], []

    What I saw from my tests is that the function change its functionality depending on the value of the parameter “d#”. In some cases of “d#”, such a function requires even more values. Unfortunately, which value is for which functionality is not documented.

    With some traces I was able to capture the following SQL statements that we used while the ALTER TABLE (…) DROP PARTITON was executed with the clause UPDATE INDEXES:

    select count(*) from SYS.INDEX_ORPHANED_ENTRY_V$ ioe

                     where ioe.index_object_id = :1 and ioe.type != 'H' and NOT EXISTS (select * from SYS.INDEX_ORPHANED_ENTRY_V$ ioe2

    select text from view$ where rowid=:1

    select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property,1024),0,0,1), o.obj# from ind$ i,obj$ o,
    user$ u where i.obj#=:1 and o.obj#=i.bo# and o.owner#=u.user#


    select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property,1024),0,0,1), o.obj# from ind$ i,obj$ o,
    user$ u where i.obj#=:1 and o.obj#=i.bo# and o.owner#=u.user#


    select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property,1024),0,0,1), o.obj# from ind$ i,obj$ o,
    user$ u where i.obj#=:1 and o.obj#=i.bo# and o.owner#=u.user#


    delete from index_orphaned_entry$ where indexobj#=:1 and           tabpartdobj# = :2

    delete from index_orphaned_entry$ where indexobj#=:1

    delete from superobj$ where subobj# = :1

    delete from tab_stats$ where obj#=:1

    It seems the tables SYS.INDEX_ORPHANED_ENTRY_V$ and SYS.INDEX_ORPHANED_ENTRY$ are used to track which indexes have orphaned entries. What I was not able to see is how Oracle tracks exactly which index entries are orphaned. Somewhere Oracle has information about which index entries must be marked as “D” when it performs the index maintenance. 

    Looking at the tables related to orphaned index entries:

    SQL> desc SYS.INDEX_ORPHANED_ENTRY_V$
    Name              Null? Type
    ----------------- -------- ----------------------------
    INDEX_OWNER       NOT NULL VARCHAR2(128)
    INDEX_NAME        NOT NULL VARCHAR2(128)
    INDEX_SUBNAME              VARCHAR2(128)
    INDEX_OBJECT_ID   NOT NULL NUMBER
    TABLE_OWNER       NOT NULL VARCHAR2(128)
    TABLE_NAME        NOT NULL VARCHAR2(128)
    TABLE_SUBNAME              VARCHAR2(128)
    TABLE_OBJECT_ID   NOT NULL NUMBER


    SQL> select * from SYS.INDEX_ORPHANED_ENTRY_V$;

    INDEX_OWNE INDEX_NAME INDEX_SUBN INDEX_OBJECT_ID TABLE_OWNE TABLE_NAME TABLE_SUBN TABLE_OBJECT_ID T
    ---------- ---------- ---------- --------------- ---------- ---------- ---------- --------------- -
    DGOMEZ     INDEX1                136728          DGOMEZ     TABLE1                136724          O

    SQL> desc sys.index_orphaned_entry$;
    Name          Null? Type
    ------------- -------- --------------------
    INDEXOBJ#     NOT NULL NUMBER
    TABPARTDOBJ#  NOT NULL NUMBER
    HIDDEN                 VARCHAR2(1)

    SQL> select * from index_orphaned_entry$;

    INDEXOBJ#  TABPARTDOBJ# H
    ---------- ------------ -
    136728     136727       O

    I will delete another table partition in order to see if the filter using the function TBL$OR$IDX$PART$NUM changes.

    SQL> alter table dgomez.table1 drop partition dgomez_table1_p3 update indexes;
    Table altered.

    Executing a query with Autotrace:

    SQL> select * from dgomez.table1 where col1='colombia';
    no rows selected

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1498168486

    ---------------------------------------------------------------------------
    | Id | Operation        | Name   | Rows  | Bytes      | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |        | 1     | 8          | 1 (0)      | 00:00:01 |
    |* 1 | INDEX RANGE SCAN | INDEX1 | 1     | 8          | 1 (0)      | 00:00:01 |
    ---------------------------------------------------------------------------

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

    1 - access("COL1"='colombia')
    filter(TBL$OR$IDX$PART$NUM("DGOMEZ"."TABLE1",0,8,0,"TABLE1".ROWID)=1)

    We see that the filter didn’t change. This is even more interesting. We have confirmed that the filter doesn’t depend on how many table partitions we drop.

    Reviewing when the next execution of the job that performs index maintenance will be:

    SQL> select job_name, enabled,run_count, to_char(NEXT_RUN_DATE,'mm-dd-yyyy hh24:mi')
    from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB'


    JOB_NAME                       ENABL RUN_COUNT TO_CHAR(NEXT_RUN
    ------------------------------ ----- ---------- ----------------
    PMO_DEFERRED_GIDX_MAINT_JOB    TRUE  2          03-26-2017 02:00

    Reviewing what the job executes:

    SQL> select source, PROGRAM_OWNER, PROGRAM_NAME from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB'

    SOURCE     PROGRAM_OWNER        PROGRAM_NAME
    ---------- -------------------- ------------------------------
               SYS                  PMO_DEFERRED_GIDX_MAINT

    Looking at the procedure that is executed by the job:

    SQL>select program_action from dba_SCHEDULER_PROGRAMS where program_name='PMO_DEFERRED_GIDX_MAINT'

    PROGRAM_ACTION
    --------------------------------------------------------------------------------
    dbms_part.cleanup_gidx_internal(noop_okay_in => 1);

     

    Checking the functions included in the package DBMS_PART:

    SQL> DESC DBMS_PART
    PROCEDURE CLEANUP_GIDX
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA_NAME_IN     VARCHAR2 IN DEFAULT
    TABLE_NAME_IN      VARCHAR2 IN DEFAULT
    PROCEDURE CLEANUP_GIDX_INTERNAL
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA_NAME_IN     VARCHAR2   IN DEFAULT
    TABLE_NAME_IN      VARCHAR2   IN DEFAULT
    ORPHANS_ONLY_IN    NUMBER(38) IN DEFAULT
    NOOP_OKAY_IN NUMBER(38) IN DEFAULT
    PROCEDURE CLEANUP_ONLINE_OP
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    SCHEMA_NAME        VARCHAR2 IN DEFAULT
    TABLE_NAME         VARCHAR2 IN DEFAULT
    PARTITION_NAME     VARCHAR2 IN DEFAULT

    Performing an index maintenance manually:

    SQL> exec dbms_part.cleanup_gidx_internal(noop_okay_in => 1);
    PL/SQL procedure successfully completed.

     

    Checking out if the index has orphaned entries:

    SQL> select index_name, status,orphaned_entries from dba_indexes where owner='DGOMEZ' and index_name='INDEX1'

    INDEX_NAME           STATUS   ORP
    -------------------- -------- ---
    INDEX1               VALID    NO

    We can see that after index maintenance, the index internals changed; now we have only 1 index entry. It is interesting to note that the two orphaned index entries were not marked as “D” as in 11.2.0.3, they were in fact deleted from the index.

    Leaf block dump
    ===============
    header address 140442250240612=0x7fbb426fe264
    kdxcolev 0
    KDXCOLEV Flags = - - -
    kdxcolok 0
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 0
    kdxconro 1
    kdxcofbo 38=0x26
    kdxcofeo 7967=0x1f1f
    kdxcoavs 7974
    kdxlespl 0
    kdxlende 0
    kdxlenxt 0=0x0
    kdxleprv 0=0x0
    kdxledsz 0
    kdxlebksz 8032
    row#0[8012] flag: -------, lock: 0, len=22
    col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
    col 1; len 10; (10): 00 02 11 a3 01 80 0c 8e 00 00
    ----- end of leaf block Logical dump ---- 

     

    Conclusion

    Starting with Oracle Database 12.1.0.1 we can drop table partitions without worrying about index maintenance (at least immediately). The database will be working normally, without performance overhead, becaise the index maintenance is not performed immediately. The DBA can decide when the index maintenance will be performed via a scheduled job and even better, has the option to perform the index maintenance manually. We also saw a comparison between 11.2.0.3 and 12.1.0.2 of this behavior and we looked at index internals so that we could see how the concept is linked to internals. 

    Tags: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator