Quest Oracle Community
Join now
Home / Educational Content / Database & Technology / Oracle Database 20c New Features for DBAs: Tables and Indexes

Oracle Database 20c New Features for DBAs: Tables and Indexes

Harry E Fowler

By Julian Dontcheff

Oracle Database 20c came with 138 new features and three major ones, which are directly related to tables and indexes in the database: Blockchain Tables, Automatic Index Optimization and Automatic Zone Maps. The article covers all these three in detail with some examples.

Oracle Database 20c New Features

Blockchain Tables in Oracle Database 20c

Blockchain tables are insert-only tables that organize rows into a number of chains and is a new concept starting with Oracle 20c. Each row in a chain, except the first row, is chained to the previous row in the chain by using a cryptographic hash. For each Oracle RAC instance, a blockchain table contains thirty-two chains, ranging from 0 through 31.

This is an example of how a blockchain table is created in 20c:

Let me first point out the main restrictions:

  • Blockchain tables cannot be created in the root container and in an application root container:
    • Error – ORA-05729: blockchain table cannot be created in root container
  • You cannot update the rows:
    • Error – ORA-05715: operation not allowed on the blockchain table
  • In general, you cannot delete rows, truncate the table, or drop the blockchain table:
    • Error – ORA-05723: drop blockchain table NDA_RECORDS not allowed
  • Don’t even try to drop the tablespace containing blockchain tables, here is what happens:
    • DROP TABLESPACE BC_DATA INCLUDING CONTENTS AND DATAFILES;
    • ORA-00604: error occurred at recursive SQL level 1
    • ORA-05723: drop blockchain table NDA_RECORDS not allowed

The most important new view in 20c related to blockchain tables is DBA_BLOCKCHAIN_TABLES:

The four (non-trivial) columns of DBA_BLOCKCHAIN_TABLES contain the following information:

  1. ROW_RETENTION: The minimum number of days a row must be retained after it is inserted into the table – if the value of this column is NULL, then rows can never be deleted from the table. In the example above, the row can be deleted after 16 days. Otherwise, you will get: ORA-05715: operation not allowed on the blockchain table
  2. ROW_RETENTION_LOCKED: 2 possible values (YES and NO) showing if the row retention period for the blockchain table is locked.
    • YES: The row retention period is locked. You cannot change the row retention period.
    • NO: The row retention period is not locked. You can change the row retention period to a value higher than the current value with the SQL statement ALTER TABLE … NO DELETE UNTIL n DAYS AFTER INSERT.
  3. TABLE_INACTIVITY_RETENTION: Number of days for which the blockchain table must be inactive before it can be dropped, that is, the number of days that must pass after the most recent row insertion before the table can be dropped. A table with no rows can be dropped at any time, regardless of this column value. In the example above, a year of inactivity must pass before the table can be dropped.
  4. HASH_ALGORITHM: The algorithm used for computing the hash value for each table row.

For each row you add/insert to the blockchain table, Oracle adds values to the hidden columns of the blockchain table. Hidden columns are populated after you commit. They are used to implement sequencing of rows and verify that data is tamper resistant. You can create indexes on hidden columns. In order to view the values of the hidden columns, you should explicitly include their names in the SQL, just like this:

Hidden Columns in Blockchain Tables will give you more details about the subject.

The following additional operations are not allowed with blockchain tables:

  • Adding, dropping, and renaming columns
  • Dropping partitions
  • Defining BEFORE ROW triggers that fire for update operations (other triggers are allowed)
  • Direct-path loading
  • Inserting data using parallel DML
  • Converting a regular table to a blockchain table (or vice versa)

There is a new PL/SQL procedure DBMS_BLOCKCHAIN_TABLE which contains five procedures, one of which VERIFY_ROWS is used to validate the data in the blockchain table.

Use DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS to remove rows that are beyond the retention period of the blockchain table.

Hints For DBAs:

  • For each chain in a database instance, periodically save the current hash and the corresponding sequence number outside the database.
  • In an Oracle Data Guard environment, consider using the maximum protection mode or maximum availability mode to avoid loss of data.
  • You can use certificates to verify the signature of a blockchain table row. Check here on how to add and delete certificates to blockchain table rows.
  • You really have a good eye if you noticed the new 20c datatype I used in the table creation at the top of this blog post.

Automatic Index Optimization

Automatic Index Optimization is one of the new database features with Oracle Database 20c that is directly related to the indexes in the database.

For some DBAs, indexes in the database do not need extra care and don’t bother much about rebuilding, compressing, coalescing, or shrinking them. I have administered in the past a 24×7 mission-critical database with size of 5TB where indexes were occupying 4.5TB of all that.  Real data was less than 500GB that included SYSTEM, SYSAUX, tablespaces, etc.

Automatic Index Optimization does not mean optimization of the Automatic Indexes in the database but rather making Index Optimization an automatic process. Here is how it works and what you have to do in order to enable it and make it work.

First, in order to implement an ILM strategy, you will have to enable Heat Maps in the database to track data access and modification.  You can enable and disable heat map tracking at the system or session level with the ALTER SYSTEM or ALTER SESSION statement using the HEAT_MAP init.ora parameter, for example:

  • SQL> alter system set HEAT_MAP = ON;

Like Automatic Data Optimization (ADO) for data segments, Automatic Index Optimization works via ILM on indexes by enabling policies that automatically optimize indexes by compressing, shrinking, and rebuilding them. Oracle uses the existing Heat Maps and collects activity statistics on the indexes.

You will add ADO policies for indexes in order to enable their compression and optimization using the existing framework. You can do it for newly created indexes as well as for already existing indexes.

There are two options:

  • ADD POLICY TIER in order to perform the operation on a say low cost/ tier 2 tablespace when tier 1 storage is under space pressure
  • ADD POLICY OPTIMIZE in order to kick off the process after a certain number of days passes without accessing the index

Here are a few examples:

Note that the Oracle documentation has the tier syntax wrong: instead of “ILM ADD POLICY SEGMENT TIER” use “ILM ADD POLICY TIER.”

The optimization process includes actions such as compressing, shrinking, or rebuilding the indexes:

  • Compress: Compresses portions of the key values in an index segment (~3 times)
  • Shrink: Merges the contents of index blocks where possible to free blocks for reuse
  • Rebuild: Rebuilds an index to improve space usage and access speed

Notice that you cannot decide which of the 3 above to use. Oracle automatically determines which action is optimal for the index and implements that action as part of the optimization process.

But can we have Automatic Index optimization for Automatic Indexes and not have to rebuild them manually any longer? I did try indeed and here is the result:

Clearly not possible but the error message is sort of misleading because actually, you can alter and even drop automatic indexes:

How can you drop the Auto Index? Just rebuild it a new tablespace and then run “drop tablespace … including contents and datafiles” – and yes it will work.

Also, while administering ADO policies for indexes, you cannot manually disable these policies but you can delete an index policy. An ADO policy for indexes executes only one time. After the policy executes successfully, the policy is disabled and is not evaluated again.

  • SQL> alter index julian.price_idx ILM DELETE POLICY p1;

Policies for indexes on partition level are not yet supported. The ADO policy is cascaded to all partitions. So, if we have hybrid tables in the Cloud,  local indexes can’t be moved automatically to object storage but it should work for global indexes. Note that we can use ADO policies with hybrid partitioned tables under some conditions.

Here are some other limitations:

  • ADO does not perform checks for storage space in a target tablespace when using storage tiering
  • ADO is not supported on materialized views
  • ADO is not supported with index-organized tables or clusters
  • ADO concurrency depends on the concurrency of the Oracle scheduler meaning if a policy job for ADO fails more than two times, then the job is marked disabled and the job must be manually enabled later

The feature is available in almost all flavors of the database, i.e., EE, Database Cloud Service, Exadata, and ODA: but it requires the Oracle Advanced Compression option.

Automatic Zone Maps

A zone is a set of contiguous data blocks on disk.

A zone map is an index-like structure built on a table and stores information about the zones of that table.

There are two major differences between indexes and zone maps:

  • A zone map stores information per zone instead of per row which makes it much more compact than an index
  • A zone map is not actively managed the way an index is kept in sync with the DML on the table

Before going into how Automatic Zone Maps work in Oracle 20c, let me explain the concept with an example:

Consider a small table containing basic information about some relational databases from db-engines.com (rank, score, initial and last release, cloud-based):

The RDBMS_BRANDS table has six data blocks with two rows per block:

Let us now create the zone map on the RDBMS_BRANDS table (on three columns only):

We have now three zones and each zone contain two blocks and stores the minimum and maximum of db_engines_rank, db_engines_score and initial_release:

Next, let us run a query returning all RDBMS brands with ranking score more than 1000:

Looking at the execution plan below we see that Oracle is scanning only Zone 1 as the maximum score in all other zone is smaller than 1000:

That is how zone maps work … but what is new in Oracle 20c?

  • We can now enable automatic creation and maintenance of basic zone maps for both partitioned and non-partitioned tables. But for now, the creation is not available for join zone maps, IOTs, external tables or temporary tables!
  • In 20c, you can use the new package DBMS_AUTO_ZONEMAP to enable Automatic Zone Maps in the database. Automatic zone map creation is turned off by default.

The four values are allowed for this parameter:

  • ON: Turns on auto zone map feature completely. Both for foreground and background zone map creation and maintenance
  • OFF: Turns off auto zone map feature completely. Both for foreground and background zone map creation and maintenance
  • FOREGROUND: Turns on only for foreground zone map creation and maintenance
  • BACKGROUND: Turns on only for background zone map creation and maintenance

You may use the ACTIVITY_REPORT function to view auto zone map activity for a given time window. Note that the background job that performs automatic zone map processing starts once per hour and each run may last up to three hours.

These two zone maps related views show the most important information DBAs need:

On a final note: Automatic Zone Maps are available for now only on Exadata and requires the Oracle Partitioning option.

Resources

About the Author

Julian Dontcheff is the Global Database Lead of Accenture and has more than 30 years of database experience. He is the first Oracle Certified Master in Europe (back in 2002) and he is also an Oracle ACE Director.

Interested in Writing for SELECT?

Are you interested in getting your writing published and sharing your knowledge with other Oracle users? Quest Oracle Community is looking for writers to contribute to the SELECT Journal for our Quest IOUG Database & Technology Community audience. We are looking for writers with expertise in a variety of topics, including the following:

  • Database sharding
  • Database security
  • Database performance
  • Database development
  • Database auditing
  • And more!

For more information about how to get involved, please email shelby.klingerman@questoraclecommunity.org.