jump to navigation

12.2 Introduction to Real-Time Materialized Views (The View) July 10, 2017

Posted by Richard Foote in 12c, 12c Rel 2, 12c Release 2 New Features, Oracle Indexes, Real-Time Materialized Views.
2 comments

the view

Although I usually focus on index related topics, I’ve always kinda considered Materialized Views (MVs) as an index like structure, which Oracle can automatically update and from which Oracle can efficiently retrieve data. The cost of maintaining a Materialized View Log is not unlike the cost of maintaining an index structure, the benefits of which can potentially far outweigh the overheads.

I just want to introduce a really cool new feature introduced in Oracle Database 12c Release 2 called Real-Time Materialized Views.

To best illustrate, a simple little demo. I first create a table and populate it with 1M rows.

SQL> create table bowie (id number primary key, name varchar2(42), sales number, text varchar2(42));

Table created.

SQL> insert into bowie select rownum, 'BOWIE' || to_char(mod(rownum,100)+1),
trunc(dbms_random.value(0,10000)), 'ZIGGY STARDUST' from dual connect by level<=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE');

PL/SQL procedure successfully completed.

I then run the following query which returns only those summary records where the total SALES exceeds some limit:

SQL> select name, sum(sales) from bowie group by name having sum(sales) > 50500000;

NAME                                       SUM(SALES)
------------------------------------------ ----------
BOWIE7                                       50570391
BOWIE55                                      50586083
BOWIE15                                      50636084

Execution Plan
----------------------------------------------------------
Plan hash value: 298288086

-----------------------------------------------------------------------------
| Id | Operation         | Name  | Rows  | Bytes | Cost (%CPU) | Time       |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |       |    50 |   600 |    1454 (4) | 00:00:01   |
|* 1 | FILTER            |       |       |       |             |            |
|  2 | HASH GROUP BY     |       |    50 |   600 |    1454 (4) | 00:00:01   |
|  3 | TABLE ACCESS FULL | BOWIE | 1000K |   11M |    1410 (1) | 00:00:01   |
-----------------------------------------------------------------------------

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

1 - filter(SUM("SALES")>50500000)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
5138 consistent gets
   0 physical reads
   0 redo size
 704 bytes sent via SQL*Net to client
 608 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   3 rows processed

I don’t have any filtering predicates before the summarisation of the table, meaning I’m currently forced to read the entire table first, before I can filter any summarisations that aren’t of interest.

As such, a full table scan is an expensive operation here (5138 consistent gets).

Now a method to reduce these FTS overheads is to create a Materialized View which has all the summary details pre-defined. Depending on the QUERY_REWRITE_INTEGRITY parameter, I can potentially use Query Rewrite to automatically use the MV to access the pre-summarised data rather than perform the FTS on the base Bowie table.

The MV could be kept fully up to date by performing a FAST REFRESH ON COMMIT but this adds additional overheads on the DMLs on the base table as they have to apply the actual changes to the MVs as part of the transaction. I could reduce these overheads by performing a FAST REFRESH ON DEMAND, but this means the MV may be stale and not fully up to date.

In Oracle Database 12.2, we get the best of both worlds with Real-Time Materialized Views, where we don’t have the additional overheads of a ON COMMIT refresh, but still guarantee fully up to date data by still (hopefully) accessing the MV rather than performing the expensive FTS.

We first create the Materialized View Log (necessary for MV fast refreshes):

SQL> create materialized view log on bowie with sequence, rowid (id, name, sales) including new values;

Materialized view log created.

But now create the MV with the required summary SQL definition, but with the new ENABLE ON QUERY COMPUTATION clause:

SQL> create materialized view bowie_mv
2 refresh fast on demand
3 enable query rewrite
4 enable on query computation
5 as
6 select name, sum(sales) from bowie group by name;

Materialized view created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_MV');

PL/SQL procedure successfully completed.

If we re-run the summary query again:

SQL> select name, sum(sales) from bowie group by name having sum(sales) > 50500000;

NAME                                       SUM(SALES)
------------------------------------------ ----------
BOWIE7                                       50570391
BOWIE55                                      50586083
BOWIE15                                      50636084

Execution Plan
----------------------------------------------------------
Plan hash value: 593592962

-----------------------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |   10 |   140 |       3 (0) | 00:00:01  |
|* 1 | MAT_VIEW REWRITE ACCESS FULL | BOWIE_MV |   10 |   140 |       3 (0) | 00:00:01  |
-----------------------------------------------------------------------------------------

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

1 - filter("BOWIE_MV"."SUM(SALES)">50500000)

Statistics
----------------------------------------------------------
  0 recursive calls
  2 db block gets
  8 consistent gets
  0 physical reads
  0 redo size
704 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  3 rows processed

We notice that Query Rewrite has taken place and the CBO has automatically used the MV (consisting of just 100 rows) to very efficiently access the required summary data (8 consistent gets).

If we look at the current Query Rewrite parameters:

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_query                       string      ENABLE
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

We notice that QUERY_REWRITE_INTEGRITY is set to ENFORCED meaning that Oracle enforces and guarantees consistency and integrity. So no stale accesses to the MV will be tolerated here.

If we now add and commit a new row (that effectively adds 1000 to the BOWIE7 summary):

SQL> insert into bowie values (1000001, 'BOWIE7', 1000, 'HUNKY DORY');

1 row created.

SQL> commit;

Commit complete.

And now again re-run the summary query:

SQL> select name, sum(sales) from bowie group by name having sum(sales) > 50500000;

NAME                                       SUM(SALES)
------------------------------------------ ----------
BOWIE55                                      50586083
BOWIE15                                      50636084
BOWIE7                                       50571391

We notice the returned data is fully up to date (the total for BOWIE7 has indeed increased by the 1000 added).

And it did so efficiently without having to perform a massive FTS on the base table. A look at the execution plan reveals how:

Execution Plan
----------------------------------------------------------
Plan hash value: 3454774452

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name                       | Rows | Bytes | Cost (%CPU)|     Time |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                            |  203 |  7308 |     22 (28)| 00:00:01 |
|   1 |  VIEW                                     |                            |  203 |  7308 |     22 (28)| 00:00:01 |
|   2 |   UNION-ALL                               |                            |      |       |            |          |
| * 3 |    VIEW                                   | VW_FOJ_0                   |  100 |  3900 |      8 (25)| 00:00:01 |
| * 4 |     HASH JOIN OUTER                       |                            |  100 |  2500 |      8 (25)| 00:00:01 |
|   5 |      VIEW                                 |                            |  100 |  1700 |       3 (0)| 00:00:01 |
|   6 |       MAT_VIEW ACCESS FULL                | BOWIE_MV                   |  100 |  1400 |       3 (0)| 00:00:01 |
|   7 |      VIEW                                 |                            |    1 |     8 |      5 (40)| 00:00:01 |
|   8 |       HASH GROUP BY                       |                            |    1 |    36 |      5 (40)| 00:00:01 |
|   9 |        VIEW                               |                            |    1 |    36 |      4 (25)| 00:00:01 |
|  10 |         RESULT CACHE                      | csfyggq82gxrn757upr194x2g2 |      |       |            |          |
|* 11 |          VIEW                             |                            |    1 |   100 |      4 (25)| 00:00:01 |
|  12 |           WINDOW SORT                     |                            |    1 |   191 |      4 (25)| 00:00:01 |
|* 13 |            TABLE ACCESS FULL              | MLOG$_BOWIE                |    1 |   191 |       3 (0)| 00:00:01 |
|* 14 |   VIEW                                    | VW_FOJ_1                   |  102 |  5304 |      8 (25)| 00:00:01 |
|* 15 |    HASH JOIN FULL OUTER                   |                            |  102 |  3774 |      8 (25)| 00:00:01 |
|  16 |     VIEW                                  |                            |    1 |    30 |      5 (40)| 00:00:01 |
|  17 |      HASH GROUP BY                        |                            |    1 |    36 |      5 (40)| 00:00:01 |
|  18 |       VIEW                                |                            |    1 |    36 |      4 (25)| 00:00:01 |
|  19 |        RESULT CACHE                       | csfyggq82gxrn757upr194x2g2 |      |       |            |          |
|* 20 |         VIEW                              |                            |    1 |   100 |      4 (25)| 00:00:01 |
|  21 |          WINDOW SORT                      |                            |    1 |   191 |      4 (25)| 00:00:01 |
|* 22 |           TABLE ACCESS FULL               | MLOG$_BOWIE                |    1 |   191 |       3 (0)| 00:00:01 |
|  23 |   VIEW                                    |                            |  100 |   700 |       3 (0)| 00:00:01 |
|  24 |    MAT_VIEW ACCESS FULL                   | BOWIE_MV                   |  100 |  1400 |       3 (0)| 00:00:01 |
|  25 | NESTED LOOPS                              |                            |    1 |    75 |      6 (34)| 00:00:01 |
|  26 |  VIEW                                     |                            |    1 |    52 |      5 (40)| 00:00:01 |
|  27 |   HASH GROUP BY                           |                            |    1 |    36 |      5 (40)| 00:00:01 |
|  28 |    VIEW                                   |                            |    1 |    36 |      4 (25)| 00:00:01 |
|  29 |     RESULT CACHE                          | csfyggq82gxrn757upr194x2g2 |      |       |            |          |
|* 30 |      VIEW                                 |                            |    1 |   100 |      4 (25)| 00:00:01 |
|  31 |       WINDOW SORT                         |                            |    1 |   191 |      4 (25)| 00:00:01 |
|* 32 |        TABLE ACCESS FULL                  | MLOG$_BOWIE                |    1 |   191 |       3 (0)| 00:00:01 |
|* 33 |  MAT_VIEW ACCESS BY INDEX ROWID           | BOWIE_MV                   |    1 |    23 |       1 (0)| 00:00:01 |
|* 34 |   INDEX UNIQUE SCAN                       | I_SNAP$_BOWIE_MV           |    1 |       |       0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

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

3 - filter("AV$0"."OJ_MARK" IS NULL AND "SNA$0"."SUM(SALES)">50500000)
4 - access(SYS_OP_MAP_NONNULL("SNA$0"."NAME")=SYS_OP_MAP_NONNULL("AV$0"."GB0"(+)))
11 - filter(SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('6E00') AND
"MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR (SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=H
EXTORAW('6F00') OR SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('7500'))
AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
13 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-03-31 15:47:28', 'syyyy-mm-dd hh24:mi:ss'))
14 - filter("SNA$0"."SNA_OJ_MARK" IS NULL AND DECODE("AV$0"."H0",0,TO_NUMBER(NULL),"AV$0"."D0")>50500000)
15 - access(SYS_OP_MAP_NONNULL("SNA$0"."NAME")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
20 - filter(SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('6E00') AND
"MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR (SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=H
EXTORAW('6F00') OR SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('7500'))
AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
22 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-03-31 15:47:28', 'syyyy-mm-dd hh24:mi:ss'))
30 - filter(SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('6E00') AND
"MAS$"."SEQ$$"="MAS$"."MAXSEQ$$" OR (SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=H
EXTORAW('6F00') OR SYS_OP_CSEE(NLSSORT("MAS$"."OLD_NEW$$",'nls_sort=''BINARY_CI'''))=HEXTORAW('7500'))
AND "MAS$"."SEQ$$"="MAS$"."MINSEQ$$")
32 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-03-31 15:47:28', 'syyyy-mm-dd hh24:mi:ss'))
33 - filter(DECODE(TO_CHAR("BOWIE_MV"."SUM(SALES)"),NULL,DECODE("AV$0"."H0",0,TO_NUMBER(NULL),"AV$0"."
D0"),"BOWIE_MV"."SUM(SALES)"+"AV$0"."D0")>50500000)
34 - access(SYS_OP_MAP_NONNULL("NAME")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))

Result Cache Information (identified by operation id):
------------------------------------------------------

10 - column-count=7; dependencies=(BOWIE.MLOG$_BOWIE); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_BOWIE"
19 - column-count=7; dependencies=(BOWIE.MLOG$_BOWIE); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_BOWIE"
29 - column-count=7; dependencies=(BOWIE.MLOG$_BOWIE); attributes=(ordered, session-lifetime); parameters=(nls); name="DMLTYPES:MLOG$_BOWIE"

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan

Statistics
----------------------------------------------------------
  0 recursive calls
  4 db block gets
 16 consistent gets
  0 physical reads
  0 redo size
704 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  3 rows processed

Oracle can now merge the data from the MV with the data still within the MV Log to generate the final, fully up to date result. At just 16 consistent gets, this is more expensive than the fully refreshed MV (8 consistent gets) but much less than the 5138 consistent gets when accessing the base BOWIE table via a FTS.

And providing the costs of doing so is calculated as less by the CBO than performing the FTS (or otherwise) on the base table, then Oracle will perform this new smart when accessing data from such created MVs.

Very nice ūüôā

12.2 Index Deferred Invalidation (Atomica) May 30, 2017

Posted by Richard Foote in 12c Rel 2, 12c Release 2 New Features, Deferred Invalidation, Oracle Indexes.
add a comment

One of the useful little features quietly introduced with Oracle Database 12c Release 2 is the ability to now defer the invalidation of dependent SQL cursors when an index is created or modified. This can be useful when you say create a new index which you know will have no impact on any existing SQL statements. Rather than automatically invalidating all dependent SQL cursors, you can chose to defer such invalidations and avoid the expense of unnecessarily re-parsing dependent cursors.

A simple demo. We first create a little table and index on the ID column:

SQL> create table bowie (id number, code number, name varchar2(42));
Table created.

SQL> insert into bowie select rownum, mod(rownum,10000), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE');

PL/SQL procedure successfully completed.

SQL> create index bowie_id_i on bowie(id);

Index created.

If we execute the following statement a number of times we’ll see there are no recursive calls, which¬†confirms no hard parsing or SQL invalidations. One can also look at the associated SQL within v$SQL to confirm no additional invalidations.

SQL> select * from bowie where id = 42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |       3 (0)|  00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  5 consistent gets
  0 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

If we say rebuild the index, we can see that we now have recursive calls due to the invalidation of this SQL. The new index structure “might” facilitate a change in the plan of this query and so Oracle will re-parse the statement:

SQL> alter index bowie_id_i rebuild;
Index altered.

SQL> select * from bowie where id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |      3 (0) |  00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  3 recursive calls
  0 db block gets
 12 consistent gets
  2 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

Oracle Database 12c Release 2 now gives us the option to defer these invalidations (with the DEFERRED INVALIDATION clause), as we might know for example that such a rebuild is not going to impact the plans of any existing SQL:

SQL> alter index bowie_id_i rebuild deferred invalidation;

Index altered.

SQL> select * from bowie where id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |      3 (0) |  00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  5 consistent gets
  2 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

We see the index rebuild this time did not result in any recursive calls due to the invalidation of the SQL statement.

If we run an SQL with a predicate on the non-indexed CODE column:

SQL> select * from bowie where code=42;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507

-----------------------------------------------------------------------------------
| Id | Operation                 | Name  | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |       |  100 |  2100 |     996 (1) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | BOWIE |  100 |  2100 |     996 (1) |  00:00:01 |
-----------------------------------------------------------------------------------

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

1 - storage("CODE"=42)
    filter("CODE"=42)

Statistics
----------------------------------------------------------
   1 recursive calls
   0 db block gets
3603 consistent gets
   0 physical reads
   0 redo size
2050 bytes sent via SQL*Net to client
 566 bytes received via SQL*Net from client
   8 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
 100 rows processed

The CBO has no choice but to perform a Full Table Scan. If we now create an index on the CODE column:

SQL> create index bowie_code_i on bowie(code);
Index created.

We notice the SQL that only has a predicate on the ID is impacted by default:

SQL> select * from bowie where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |      3 (0) |  00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  1 recursive calls
  0 db block gets
  5 consistent gets
 12 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

The new SQL with a predicate on the CODE column now uses the new index as we would hope due to it being invalidated and re-parsed:

SQL> select * from bowie where code=42;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 853003755

----------------------------------------------------------------------------------------------------
| Id | Operation                           | Name         | Rows | Bytes | Cost (%CPU) | Time      |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |              |  100 |  2100 |     103 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE        |  100 |  2100 |     103 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_CODE_I |  100 |       |       3 (0) |  00:00:01 |
----------------------------------------------------------------------------------------------------

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

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
   1 recursive calls
   0 db block gets
 110 consistent gets
   2 physical reads
   0 redo size
3540 bytes sent via SQL*Net to client
 566 bytes received via SQL*Net from client
   8 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
 100 rows processed

If we now drop this index on the CODE column, but with the new deferred invalidation clause:

SQL> drop index bowie_code_i deferred invalidation;

Index dropped.

Even though deferred invalidation was specified, this clause has no impact on the SQL with the predicate on the CODE column. It previously referenced the dropped index and so has no choice but to become invalidated and be re-parsed. The new execution plan performs a Full Table Scan as it now again has no other viable option:

SQL> select * from bowie where code=42;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507

-----------------------------------------------------------------------------------
| Id | Operation                 | Name  | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |       |  100 |  2100 |     996 (1) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | BOWIE |  100 |  2100 |     996 (1) |  00:00:01 |
-----------------------------------------------------------------------------------

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

1 - storage("CODE"=42)
    filter("CODE"=42)

Statistics
----------------------------------------------------------
  46 recursive calls
   0 db block gets
3641 consistent gets
   0 physical reads
   0 redo size
2050 bytes sent via SQL*Net to client
 566 bytes received via SQL*Net from client
   8 SQL*Net roundtrips to/from client
   6 sorts (memory)
   0 sorts (disk)
 100 rows processed

If we now re-create the index on the CODE column, again with deferred invalidation:

SQL> create index bowie_code_i on bowie(code) deferred invalidation;

Index created.

We notice the SQL with the predicate on the ID column has not been impacted by either the drop or the subsequent re-creation of the index on the CODE column. It still has no recursive calls. Any invalidation on this SQL would have been unnecessary as the execution plan¬†can’t be impacted by such an index on the CODE column:

SQL> select * from bowie where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |      3 (0) |  00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  5 consistent gets
  0 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

But we look at the SQL that does have a predicate on the CODE column, we notice that it likewise has not been invalidated (as we explicitly stated deferred invalidation when we re-created the index). The SQL still performs a Full Table Scan as evidenced by the number of consistent gets (3603, the same as with the Full Table Scan) and that there are no recursive SQL calls:

SQL> select * from bowie where code=42;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 853003755

---------------------------------------------------------------------------------------------------
| Id | Operation                           | Name         | Rows | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |              |  100 |  2100 |      03 (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE        |  100 |  2100 |      03 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_CODE_I |  100 |       |       3 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------

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

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
3603 consistent gets
   0 physical reads
   0 redo size
2050 bytes sent via SQL*Net to client
 566 bytes received via SQL*Net from client
   8 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
 100 rows processed

 

SQL*Plus autotrace which displays the current SQL plan (but not necessarily the actual execution plan) shows that the CBO would have chosen to use the new index if it had been allowed to invalidate this cursor.

So like many features, deferred invalidation¬†can be useful if used well but can be problematic if it’s not implemented and used appropriately ūüôā

12.2 Online Conversion of a Non-Partitioned Table to a Partitioned Table (A Small Plot Of Land) March 27, 2017

Posted by Richard Foote in 12c Release 2 New Features, Attribute Clustering, Clustering Factor, Online DDL, Oracle, Oracle Indexes, Partitioning.
1 comment so far

Image result for outside bowie

In my previous post, I discussed how you can now move heap tables online with Oracle Database 12.2 and how this can be very beneficial in helping to address issues with the Clustering Factor of key indexes.

A problem with this technique is that is requires the entire table to be effectively reorganised when most of the data might already be well clustered. It would be much more efficient if we could somehow only move and reorganise just the portion of a table that has poorly clustered data introduced to the table since the last reorg.

Partitioning the table appropriately would help to address this disadvantage but converting a non-partitioned table to be partitioned can be a pain. To do this online with as little complication as possible one could use the dbms_redefintion package which has improved with latter releases.

However, with Oracle Database 12.2, there is now an even easier, more flexible method of performing such a conversion.

Using the same table definition and data as from my previous post, I’m going to first create¬†a couple of additional¬†indexes (on the ID column and on the DATE_CREATED column) :


SQL> create unique index ziggy_id_i on ziggy(id);

Index created.

SQL> create index ziggy_date_created_i on ziggy(date_created);

Index created.

To convert a non-partitioned table to a partitioned table online, we can now use this new extension to the ALTER TABLE syntax:


SQL> alter table ziggy
2 modify partition by range (date_created)
3 (partition p1 values less than (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
4 partition p2 values less than (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
5 partition p3 values less than (maxvalue)) online;

Table altered.

How simple is that !! We now have a table that is range partitioned based on the DATE_CREATED column and this conversion was performed online.

We notice not only is the table now partitioned with all the indexes remaining Valid, but the index based on the partitioning key (DATE_CREATED) has also been implicitly converted to be a Local partitioned index:


SQL> select table_name, status, partitioned from dba_tables
where table_name='ZIGGY';

TABLE_NAME   STATUS   PAR
------------ -------- ---
ZIGGY        VALID    YES

SQL> select index_name, status, partitioned, num_rows
from dba_indexes where table_name='ZIGGY';

INDEX_NAME           STATUS   PAR   NUM_ROWS
-------------------- -------- --- ----------
ZIGGY_DATE_CREATED_I      N/A YES    2000000
ZIGGY_CODE_I VALID             NO    2000000
ZIGGY_ID_I VALID               NO    2000000

SQL> select index_name, partition_name, status, leaf_blocks from dba_ind_partitions
     where index_name like 'ZIGGY%';

INDEX_NAME           PARTITION_NAME  STATUS   LEAF_BLOCKS
-------------------- --------------- -------- -----------
ZIGGY_DATE_CREATED_I              P1   USABLE         865
ZIGGY_DATE_CREATED_I              P2   USABLE        1123
ZIGGY_DATE_CREATED_I              P3   USABLE        1089

SQL> select index_name, partitioning_type, partition_count, locality
from dba_part_indexes where table_name='ZIGGY';

INDEX_NAME           PARTITION PARTITION_COUNT LOCALI
-------------------- --------- --------------- ------
ZIGGY_DATE_CREATED_I     RANGE               3 LOCAL

As part of the table conversion syntax, we have the option to also update all the associated indexes and partition them in any manner we may want. For example:


SQL> alter table ziggy
2 modify partition by range (date_created)
3 (partition p1 values less than (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
4 partition p2 values less than (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
5 partition p3 values less than (maxvalue)) online
6 update indexes
7 (ziggy_code_i local,
8 ziggy_id_i global partition by range (id)
9 (partition ip1 values less than (maxvalue)));

Table altered.

In this example, not only are we converting the non-partitioned table to be partitioned, but we’re also explicitly converting the index on the CODE column to be a Locally partitioned index and the index on the ID column to be Globally partitioned in its own manner.

If we look at the definition of these indexes, we see that they also have all been converted to partitioned indexes online along with the table:


SQL> select table_name, status, partitioned from dba_tables
where table_name='ZIGGY';

TABLE_NAME   STATUS   PAR
------------ -------- ---
ZIGGY           VALID YES

SQL> select index_name, status, partitioned from dba_indexes
where table_name = 'ZIGGY';

INDEX_NAME           STATUS   PAR
-------------------- -------- ---
ZIGGY_CODE_I              N/A YES
ZIGGY_ID_I                N/A YES
ZIGGY_DATE_CREATED_I      N/A YES

SQL> select index_name, partitioning_type, partition_count, locality
from dba_part_indexes where table_name='ZIGGY';

INDEX_NAME           PARTITION PARTITION_COUNT LOCALI
-------------------- --------- --------------- ------
ZIGGY_CODE_I             RANGE               3 LOCAL
ZIGGY_ID_I               RANGE               1 GLOBAL
ZIGGY_DATE_CREATED_I     RANGE               3 LOCAL

If we look at the Clustering Factor of the important CODE column index, we see that all partitions have an excellent Clustering Factor as all partitions have just been created.


SQL> select partition_name, num_rows, clustering_factor from dba_ind_partitions
where index_name='ZIGGY_CODE_I';

PARTITION_NAME         NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- -----------------
P1                       490000              2275
P2                       730000              3388
P3                       780000              3620

However, if we now add new rows¬†to the table as would occur with a real application, the data from the “current” partition results in the Clustering Factor “eroding” over time for this partition.


SQL> insert into ziggy select 2000000+rownum, mod(rownum,100), sysdate, 'DAVID BOWIE'
from dual connect by level <= 500000; 500000 rows created. SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_index_stats(ownname=>null,indname=>'ZIGGY_CODE_I');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows, clustering_factor from dba_ind_partitions
     where index_name='ZIGGY_CODE_I';

PARTITION_NAME         NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- -----------------
P1                       490000              2275
P2                       730000              3388
P3                      1280000            238505

As discussed previously, the Clustering Attribute has no effect with standard DML operations. Therefore, the efficiency of the CODE index reduces over time in the partition where new data is being introduced. The Clustering Factor has now substantially increased from 3620 to 238505. Note for all the other partitions where there are no modifications to the data, the Clustering Factor remains excellent.

Having the table/index partitioned means we can therefore periodically reorg just the problematic partition:


SQL> alter table ziggy move partition p3 update indexes online;

Table altered.

SQL> select partition_name, num_rows, clustering_factor from dba_ind_partitions
     where index_name='ZIGGY_CODE_I';

PARTITION_NAME         NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- -----------------
P1                       490000              2275
P2                       730000              3388
P3                      1280000              5978

The Clustering Factor for this partition has now reduced substantially from 238505 to just 5978.

For those of you with the Partitioning database option, the ability in 12.2 to now so easily convert a non-partitioned table to be partitioned, along with its associated indexes is just brilliant ūüôā

12.2 Index Advanced Compression “High” Part III (I Can’t Give Everything Away) January 25, 2017

Posted by Richard Foote in 12c Rel 2, 12c Release 2 New Features, Advanced Index Compression, Oracle Indexes.
1 comment so far

If you like the look of the new Index Advanced Compression setting of “High” available with Oracle Database 12c Release 2 as I’ve discussed previously in Part I and Part II, well there’s a way to make this the default index compression method in your 12.2 database.

Let’s begin by creating a table and explicitly creating a NOCOMPRESS index in the BOWIE tablespace:


SQL> create table bowie (id number, code number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum,10), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index bowie_code_idx on bowie(code) tablespace bowie nocompress;

Index created.

If we look at the size and compression type of this index:


SQL> select index_name, tablespace_name, leaf_blocks, compression
from dba_indexes where index_name='BOWIE_CODE_IDX';

INDEX_NAME      TABLESPACE_NAME      LEAF_BLOCKS COMPRESSION
--------------- -------------------- ----------- -------------
BOWIE_CODE_IDX  BOWIE                       1939 DISABLED

We notice the index has 1939 leaf blocks and that index compression is indeed disabled as expected.

Let’s now drop the index and recreate again it in the BOWIE tablespace, but this time without explicitly stating any compression option:


SQL> drop index bowie_code_idx;

Index dropped.

SQL> create index bowie_code_idx on bowie(code) tablespace bowie;

Index created.

SQL> exec dbms_stats.gather_index_stats(ownname=>null, indname=>'BOWIE_CODE_IDX');

PL/SQL procedure successfully completed.

If we look at the index now:


SQL> select index_name, tablespace_name, leaf_blocks, compression from dba_indexes
where index_name='BOWIE_CODE_IDX';

INDEX_NAME      TABLESPACE_NAME      LEAF_BLOCKS COMPRESSION
--------------- -------------------- ----------- -------------
BOWIE_CODE_IDX  BOWIE                        355 ADVANCED HIGH

We notice the index now only has 355 leaf blocks¬†(down from 1939 leaf blocks) and that it has automatically used the new index advanced compression option of “HIGH”.

The secret lies with the following new settings.

Firstly, with the new db_index_compression_inheritance parameter, you can specify how during index creation the index inherits its index compression attributes (tablespace or table or not at all):


SQL> alter system set db_index_compression_inheritance=tablespace scope=both;

System altered.

Secondly, you can give a tablespace an index compression attribute on how indexes are compressed by default within the tablespace:


SQL> alter tablespace bowie default index compress advanced high;

Tablespace altered.

SQL> select tablespace_name, def_index_compression, index_compress_for
from dba_tablespaces where tablespace_name = 'BOWIE';

TABLESPACE_NAME      DEF_INDE INDEX_COMPRES
-------------------- -------- -------------
BOWIE                ENABLED  ADVANCED HIGH

So in this database, all indexes created within the BOWIE tablespace are automatically created with index advanced compression set to HIGH.

There are however some disadvantages with high index advanced compression that need to be considered.

More to come.

12.2 Index Advanced Compression “High” Part II (One Of My Turns) December 12, 2016

Posted by Richard Foote in 12c Rel 2, 12c Release 2 New Features, Advanced Index Compression, Oracle Indexes.
4 comments

In Part I, I introduced the new Index Advanced Compression default value of “HIGH”, which has the potential to significantly compress indexes much more than previously possible. This is due to new index compression algorithms that do more than simply de-duplicate indexed values within a leaf block.

Previously, any attempt to completely compress a Unique Index was doomed to failure as a Unique Index by definition only has unique values and so has nothing to de-duplicate. As such, you were previously restricted (quite rightly) to only being able to compress n-1 columns within a Unique Index. An attempt compress all columns in a Unique Index would only result in a larger index structure due to the associated overheads of the prefix-table within the leaf blocks.

But¬†what happens if¬†we now use Index Advanced Compression set to “HIGH” on a Unique Index ?

Let’s see.

Let’s first create a simple table with a unique ID column:

SQL> create table bowie (id number, code number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

Let’s start by creating an uncompressed unique index on the ID column:

SQL> create unique index bowie_id_i on bowie(id);

Index created.

SQL> select index_name, leaf_blocks, compression from user_indexes where index_name='BOWIE_ID_I';

INDEX_NAME   LEAF_BLOCKS COMPRESSION
------------ ----------- -------------
BOWIE_ID_I          2088 DISABLED

So the uncompressed unique index has 2088 leaf blocks.

If we try and use normal compression on the index:

SQL> alter index bowie_id_i rebuild compress;
alter index bowie_id_i rebuild compress
*
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key

We get an error saying we’re not allowed to compress a single column unique index. Doing so makes no sense, as there’s no benefit in de-duplicating¬†such an¬†index.

If we attempt to use advanced index compression with a value of “LOW”:

SQL> alter index bowie_id_i rebuild compress advanced low;
alter index bowie_id_i rebuild compress advanced low
*
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key

We get the same error. Although advanced index compression of LOW is clever enough to automatically compress only those leaf blocks where there is a benefit¬†in compression,¬†there can be no such index leaf block that benefits from compression via the de-duplication method. Therefore, the error is really there to just let you know that you’re wasting your time in attempting to do this on a unique index.

If however we use the new HIGH option with index advanced compression:

SQL> alter index bowie_code_i rebuild compress advanced high;

Index altered.

SQL> exec dbms_stats.gather_index_stats(ownname=>null, indname=>'BOWIE_ID_I');

PL/SQL procedure successfully completed.

SQL> select index_name, leaf_blocks, compression from user_indexes where index_name='BOWIE_ID_I';

INDEX_NAME   LEAF_BLOCKS COMPRESSION
------------ ----------- -------------
BOWIE_ID_I           965 ADVANCED HIGH

Not only does it not give us an error, but it has actually managed to successfully compress such a unique index containing nothing but a bunch of unique numbers to just 965 leaf blocks, down from 2088. The index is now less than half its previous size.

So any Oracle B-tree index, even if it’s a single column unique index, is a possible candidate to be compressed with “High” advanced index compression.

More to come.

12.2 Index Advanced Compression “High” – Part I (High Hopes) December 6, 2016

Posted by Richard Foote in 12c Rel 2, 12c Release 2 New Features, Advanced Index Compression, Oracle Indexes.
7 comments

Oracle first introduced Advanced Compression for Indexes in 12.1 as I’ve discussed here a number of times.

With Oracle Database 12c Release 2, you can now use Index Advanced Compression “High” to further (and potentially dramatically) improve the index compression ratio. ¬†Instead of simply de-duplicating the index entries within an index leaf block, High Index Compression uses more complex compression algorithms and stores the index entries in a Compression Unit (similar to that as used with Hybrid Columnar Compression). The net result is generally a much better level of compression, but at the potential cost of more CPU resources to both access and maintain the index structures.

To give you an idea on the possible compression improvements, let’s re-run the demo I used previously when I first discussed Advanced Index Compression.

So I first create a table, where the CODE column that has many distinct values, but a portion (25%) of data that is replicated:

SQL> create table bowie (id number, code number, name varchar2(30));

Table created.

SQL> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000;

1000000 rows created.

SQL> update bowie set code = 42 where id between 250000 and 499999;

250000 rows updated.

SQL> commit;

Commit complete.

I then create an index on the CODE column and check out its initial size:

SQL> create index bowie_code_i on bowie(code);

Index created.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

INDEX_NAME   LEAF_BLOCKS COMPRESSION
------------ ----------- -------------
BOWIE_CODE_I        2158 DISABLED

 

If I just use normal compression on this index:

SQL> alter index bowie_code_i rebuild compress;

Index altered.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

INDEX_NAME   LEAF_BLOCKS COMPRESSION
------------ ----------- -------------
BOWIE_CODE_I        2684 ENABLED

 

We notice the index actually increases in size (2684 up from 2158), as most (75%) of the CODE values are unique and so the overheads associated with the resultant prefix table in the leaf blocks used with normal index compression overrides the savings of compression on the 25% of the index where compression is beneficial.

If we use “Low” advanced index compression as introduced in 12.1:

SQL> alter index bowie_code_i rebuild compress advanced low;

Index altered.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

INDEX_NAME   LEAF_BLOCKS COMPRESSION
------------ ----------- -------------
BOWIE_CODE_I        2057 ADVANCED LOW

 

We notice the index has now indeed decreased in size (2057 down from 2158), as Oracle has automatically compressed just the 25% of the index where compression was beneficial and not touched the 75% of the index where compression wasn’t possible when de-duplicating values.

If we now however use the new 12.2 Advanced Index Compression “High” option:

SQL> alter index bowie_code_i rebuild compress advanced high;

Index altered.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

INDEX_NAME   LEAF_BLOCKS COMPRESSION
------------ ----------- -------------
BOWIE_CODE_I           0 ADVANCED HIGH

Wow, an index with now no leaf blocks, that’s unbelievably small. Actually, I don’t believe it as¬†this is due to bug 22094934. We need to gather index statistics to see the new index size:

 

SQL> exec dbms_stats.gather_index_stats(ownname=>null, indname=>'BOWIE_CODE_I');

PL/SQL procedure successfully completed.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

INDEX_NAME   LEAF_BLOCKS COMPRESSION
------------ ----------- -------------
BOWIE_CODE_I         815 ADVANCED HIGH

 

We notice that the index hasn’t just gone now a tad in size, but is now substantially smaller than before (down to just 815 leaf blocks, rather than the smaller 2057 from 2158 reduction we previously achieved with low index advanced compression.

So Index Advanced Compression, with the now default “HIGH” option can substantially reduce index sizes. Note this new capability of course requires the Advanced Compression Option.

More to come.

Oracle Database 12c Release 2: New Indexing Features – Long Identifiers (Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict) November 24, 2016

Posted by Richard Foote in 12c Rel 2, 12c Release 2 New Features, Long Identifiers, Oracle Indexes.
1 comment so far

Oracle Database 12c Release 2 has now been officially released (at least on the Oracle Database Cloud and Oracle Database Exadata Express Cloud Services).

In the coming weeks, I’ll be blogging about quite a number of new indexing features/capabilities/improvements that have been introduced in Oracle Database 12c Release 2. These include:

  • Advanced Index Compression
  • Tracking Index Usage
  • Online Table Move (and automatic Index maintenance)
  • Online Conversion to Partitioned Table (and Partitioned Indexes)
  • Deferred Invalidation of Cursors During Index Creation/Rebuild
  • Indexing JSON Improvements
  • Text Index Improvements

To start things off, how many times have you wanted to name an index something such as follows:

SQL> create table bowie (id number, code number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum,10), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index this_index_will_be_used_to_get_data_from_the_bowie_table_in_sc
enarios_when_the_code_column_is_used_in_predicates_idx on bowie(code);
create index this_index_will_be_used_to_get_data_from_the_bowie_table_in_scenari
os_when_the_code_column_is_used_in_predicates_idx on bowie(code)
*
ERROR at line 1:
ORA-00972: identifier is too long

Only to get an error that you’ve exceeded the 30 character identifier.

Well, with 12.2, the maximum length of an identifier has been increased to 128 characters, so now creating such an index works:


SQL> create index this_index_will_be_used_to_get_data_from_the_bowie_table_in_scenarios_when_the_code_column_is_used_in_predicates_idx on 
bowie(code);

Index created.

SQL> select index_name, leaf_blocks, status from dba_indexes where table_name='BOWIE';

INDEX_NAME                                         LEAF_BLOCKS STATUS
-------------------------------------------------- ----------- --------
THIS_INDEX_WILL_BE_USED_TO_GET_DATA_FROM_THE_BOWIE        1939 VALID
_TABLE_IN_SCENARIOS_WHEN_THE_CODE_COLUMN_IS_USED_I
N_PREDICATES_IDX

 

The possibilities are endless:

SQL> alter table bowie add constraint the_primary_key_of_the_bowie_table_is_the_
id_column_so_please_stop_trying_to_insert_a_duplicate_id_value_dumbo primary key
(id);

Table altered.

SQL> insert into bowie values (42, 42, 'David Bowie');
insert into bowie values (42, 42, 'David Bowie')
*
ERROR at line 1:
ORA-00001: unique constraint
(BOWIE.THE_PRIMARY_KEY_OF_THE_BOWIE_TABLE_IS_THE_ID_COLUMN_SO_PLEASE_STOP_TRYING _TO_INSERT_A_DUPLICATE_ID_VALUE_DUMBO) violated

Developers can now be, shall we say, more “expressive” with the database objects they create …