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 🙂

Advertisements

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 Some Cool Partitioning New Features (Big Wheels) April 5, 2017

Posted by Richard Foote in 12c Rel 2, Oracle Indexes, Partitioning.
2 comments

ELO-Out_of_the_Blue_Lp

I previously discussed just how easy it is to convert online a non-partitioned table to be partitioned with Oracle Database 12.2.

Thought I might run through a number of really cool new partitioning features and capabilities that were also introduced in 12.2.

To start, I’m just going to create a basic range-partitioning table and populate it with a year’s worth of data:

 

SQL> create table ziggy
2 (prod_id NUMBER,
3 cust_id NUMBER,
4 time_id DATE,
5 quantity NUMBER)
6 PARTITION BY RANGE (time_id)
7 (PARTITION z_2016_q1 VALUES LESS THAN (TO_DATE('01-APR-2016','dd-MON-yyyy')),
8 PARTITION z_2016_q2 VALUES LESS THAN (TO_DATE('01-JUL-2016','dd-MON-yyyy')),
9 PARTITION z_2016_q3 VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy')),
10 PARTITION z_2016_q4 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy')));

Table created.

SQL> insert into ziggy select mod(rownum,10), mod(rownum,100), sysdate-dbms_random.value(94, 454), 100
from dual connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

I’ll then create both a global, non-partitioned index and a locally partitioned index:

SQL> create index ziggy_prod_id_i on ziggy(prod_id);

Index created.

SQL> select index_name, num_rows, status from dba_indexes
where index_name='ZIGGY_PROD_ID_I';

INDEX_NAME             NUM_ROWS STATUS
-------------------- ---------- --------
ZIGGY_PROD_ID_I          100000 VALID

SQL> create index ziggy_time_id_i on ziggy(time_id) local;

Index created.

SQL> select index_name, partition_name, num_rows, status from
dba_ind_partitions
where index_name='ZIGGY_TIME_ID_I';

INDEX_NAME           PARTITION_NAME         NUM_ROWS STATUS
-------------------- -------------------- ---------- --------
ZIGGY_TIME_ID_I      Z_2016_Q1                 23941 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q2                 25276 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q3                 25522 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q4                 25261 USABLE

 

OK, the first 12.2 new feature is the capability to now “Split” a partition online (previously this was an offline only operation that resulted in invalid global indexes and invalid corresponding local indexes):

SQL> alter table ziggy
2 split PARTITION z_2016_q4 into
3 (PARTITION z_2016_oct VALUES LESS THAN (TO_DATE('01-NOV-2016','dd-MON-yyyy')),
4 PARTITION z_2016_nov VALUES LESS THAN (TO_DATE('01-DEC-2016','dd-MON-yyyy')),
5 PARTITION z_2016_dec) online;

Table altered.

SQL> select index_name, num_rows, status from dba_indexes
where index_name='ZIGGY_PROD_ID_I';

INDEX_NAME             NUM_ROWS STATUS
-------------------- ---------- --------
ZIGGY_PROD_ID_I          100000 VALID

SQL> select index_name, partition_name, num_rows, status from dba_ind_partitions where index_name='ZIGGY_TIME_ID_I';

INDEX_NAME           PARTITION_NAME         NUM_ROWS STATUS
-------------------- -------------------- ---------- --------
ZIGGY_TIME_ID_I      Z_2016_DEC                 8276 USABLE
ZIGGY_TIME_ID_I      Z_2016_NOV                 8298 USABLE
ZIGGY_TIME_ID_I      Z_2016_OCT                 8687 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q1                 23941 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q2                 25276 USABLE
ZIGGY_TIME_ID_I      Z_2016_Q3                 25522 USABLE

6 rows selected.

 

Nice !!

OK, let’s quickly check how many rows I have for each PROD_ID value that belongs within the Q1 partition:

SQL> select prod_id, count(*) from ziggy where time_id
between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;

   PROD_ID   COUNT(*)
---------- ----------
         0       2391
         1       2334
         2       2324
         3       2372
         4       2284
         5       2462
         6       2348
         7       2399
         8       2380
         9       2388

 

So we have a PROD_ID with values between 0 and 9 that have roughly the same number of rows.

Let’s now check the size of each table partition in blocks:

SQL> select partition_name, blocks from dba_tab_partitions
where table_name='ZIGGY';

PARTITION_NAME           BLOCKS
-------------------- ----------
Z_2016_DEC                   44
Z_2016_NOV                   44
Z_2016_OCT                   45
Z_2016_Q1                  1006
Z_2016_Q2                  1006
Z_2016_Q3                  1006

 

Note that the Q1 partition current has 1006 blocks allocated.

OK, the next cool new feature is to select which rows we make want to keep during a subsequent re-org operation. In the following example, I’m going to re-org the Q1 partition and compress the data, but I’m only going to keep those rows where the PROD_ID is between 1 and 8 (hence getting rid of all rows with PROD_ID that’s 0 or 9):

SQL> ALTER TABLE ziggy
2 MOVE PARTITION z_2016_q1 TABLESPACE users COMPRESS ONLINE
3 INCLUDING ROWS WHERE prod_id between 1 and 8;

Table altered.

 

The new INCLUDING ROWS clause explicitly states that I’m only going to include those rows where the PROD_ID is between 1 and 8.

If we now check the size of the partition and its contents:

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

PL/SQL procedure successfully completed.

SQL> select partition_name, blocks from dba_tab_partitions
where table_name='ZIGGY';

PARTITION_NAME           BLOCKS
-------------------- ----------
Z_2016_DEC                   44
Z_2016_NOV                   44
Z_2016_OCT                   45
Z_2016_Q1                    57
Z_2016_Q2                  1006
Z_2016_Q3                  1006

SQL> select prod_id, count(*) from ziggy where time_id
between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;

   PROD_ID    COUNT(*)
---------- ----------
         1       2334
         2       2324
         3       2372
         4       2284
         5       2462
         6       2348
         7       2399
         8       2380

 

We see the Q1 partition has indeed decreased in size (down to just 57 blocks from 1006 blocks) because it has now been compressed AND because it now only has rows where the PROD_ID is between 1 and 8.

Nice !!

The next cool new feature is that we now have new syntax (FOR EXCHANGE WITH TABLE) to more easily create a table by which we wish to subsequently perform a partition exchange. This ensures that the new table is entirely compatible for such an exchange, although note that associated index are NOT created as part of this process:

SQL> CREATE TABLE ziggy_exchange
2 TABLESPACE users
3 FOR EXCHANGE WITH TABLE ziggy;

Table created.

SQL> ALTER TABLE ziggy
2 EXCHANGE PARTITION z_2016_q1 WITH TABLE ziggy_exchange;

Table altered.

 

If we look at the contents of each object, we can see the partition exchange has been successful:

SQL> select prod_id, count(*) from ziggy where time_id
between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;

no rows selected

SQL> select prod_id, count(*) from ziggy_exchange
where time_id between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;

   PROD_ID   COUNT(*)
---------- ----------
         1       2334
         2       2324
         3       2372
         4       2284
         5       2462
         6       2348
         7       2399
         8       2380

Nice !!

The final new 12.2 partitioning feature I want to introduce is the ability now to make a particular partition (or sub-partition) read only:

SQL> alter table ziggy modify partition z_2016_q1 read only;

Table altered.

SQL> select table_name, partition_name, read_only
from dba_tab_partitions where table_name='ZIGGY';

TABLE_NAME           PARTITION_NAME       READ
-------------------- -------------------- ----
ZIGGY                Z_2016_DEC           NO
ZIGGY                Z_2016_NOV           NO
ZIGGY                Z_2016_OCT           NO
ZIGGY                Z_2016_Q1            YES
ZIGGY                Z_2016_Q2            NO
ZIGGY                Z_2016_Q3            NO

SQL> insert into ziggy values (1,1,'13-JAN-2016', 1);
insert into ziggy values (1,1,'13-JAN-2016', 1)
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.

 

Nice !!

There are lots of great new features introduced with Oracle Database 12c R2, but sometimes it’s these lesser know features that can so terribly useful.

12.2 Index Advanced Compression “High” Part IV (The Width of a Circle) March 10, 2017

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

DAVID_BOWIE_THE+MAN+WHO+SOLD+THE+WORLD-291998

A quick post (for me) with a long weekend beckoning…

In Part I, Part II and Part III of looking at the new Index Advanced Compression level of “High”, we discussed how it can significantly decrease the size of your indexes in a manner not previously possible. This can result in significant reductions of index storage and the potential subsequent reduction in IO and memory related overheads.

This is of course good.

However, if you have applications which have tables/indexes that are very sensitive regarding DML performance, you need to exercise caution before compressing indexes in this manner. This is due to the extra CPU overheads and file IO wait times that can result in maintaining the highly compressed index structures.

To quickly illustrate this point, let’s first look at the timings when performing DML with an associated index that has no compression:

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

Table created.

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

Index created.

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

1000000 rows created.
Elapsed: 00:00:06.61

SQL> commit;

Commit complete.

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

250000 rows updated.
Elapsed: 00:00:12.91

If we now repeat the same demo, but this time with an index that’s compressed with advanced compression set to “HIGH”:


SQL> drop table bowie;

Table dropped.

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

Table created.

SQL> create index bowie_code_idx on bowie(code) compress advanced high;

Index created.

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

1000000 rows created.
Elapsed: 00:00:39.08

SQL> commit;

Commit complete.

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

250000 rows updated.
Elapsed: 00:01:09.83

We see there’s a significant increase in timings when both inserting into the table and when updating the highly compressed indexed column.

Therefore you need to consider the detrimental impact on DML performance due to the additional resources required in maintaining the highly compressed indexes, as it might offset the potential benefits of having the smaller index structures. Your mileage may vary.

More to come 🙂

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 …

New Oracle Cloud Offering – Indexing as a Service (IDXaaS) (I Pity The Fool) April 1, 2016

Posted by Richard Foote in 12c Rel 2, IDXaaS, Oracle Cloud, Oracle Indexes.
12 comments

This of course is an April Fools joke. Sorry !!

A very exciting announcement !! I’ve recently been promoted within Oracle Corporation to lead their brand new Oracle Cloud offering “Indexing as a Service” (IDXaaS) team, based on my previous work and experience in the indexing space. Yes, I’m both thrilled and excited to be involved in this new venture 🙂

Promising both improved levels of service and reductions in maintenance and administration overheads, this new Oracle Cloud offering will take all indexing related hassles from on-premise databases and address them in the Cloud. No more index rebuilds, no more concerns you don’t have the right indexes in place and importantly no more worrying that indexes are taking up valuable TBs of storage. Oracle indexes can now be safely and seamlessly migrated and stored in the Oracle Cloud.

Relational databases depend heavily on indexes to ensure data can be retrieved quickly and efficiently, but indexes can be very difficult to administer, can use considerable resources to maintain and consume considerable amounts of disk space. They can be so problematic that some folk actually devote an entire blog on the subject !! Imagine if all these issues and overheads can be taken away from you when administrating Oracle databases…

Index DDL will be enhanced in the 12c Release 2 Oracle Database to optionally specify a Cloud Domian Identifier (CDI) instead of a tablespace. So you will be able to either build or rebuild an index into the Oracle IDXaaS Cloud with syntax such as follows: CREATE INDEX bowie_idx on bowie(id) CLOUD LOCATION bowies_cloud_domain; This index will fully index the bowie table that exists in an on premise database (or indeed a database that is also in the cloud) but the index itself will be created and maintained in the Oracle Cloud. The database is fully aware of the actual physical location of the index as it is when created in a traditional tablespace and will simply update the index structure as DML is applied to the base table as it has always done. All Oracle indexing types and options will be fully supported including Oracle Text and Spatial indexes.

Like indexes stored in a tablespace, these indexes will need to be rebuilt or coalesced every now and then, but you won’t need to worry about this as Oracle will now implicitly do this for you in the Oracle Cloud. I’m currently hiring a team of DBAs with expertise in the “art” of rebuilding indexes who will ensure all your indexes will be rebuilt automatically for customers as necessary. By default, these operations will usually be performed on Sundays within the Oracle Cloud, but as indexes can be rebuilt online and as these indexes are no longer physically stored within your database, it’s an operation that is now performed automatically and seamlessly for you. Customers no longer need concern themselves with these types of index operations or when they occur, so while “we” work over the weekend, your DBAs can instead relax and keep their gardens nice and weed free. Please leave a comment on the blog if you think you have what it takes to effectively rebuild Oracle indexes and interested in joining my elite IDXaaS team !!

For customers that choose to use the “Premium IDXaaS” offering, Oracle will automatically create or drop indexes as required in the Oracle Cloud. Customers no longer have to make the decision on what indexes should be created on which columns in which order; this will be performed automatically for you by Oracle’s IDXaaS. By running new advanced analysis routines on your database workloads, the Premium IDXaaS offering will create an index if it will help the efficiency of any of your current workloads. Conversely, if Oracle believes an index is redundant (for example if your database is running on Exadata), indexes will first be automatically made “invisible” and if there are no serious repercussions, will be automatically dropped 7 days later. DBAs and Developers will no longer need to worry about which indexes to create/drop, significantly reducing the complexities in managing both current and future Oracle database environments.

Oracle will also introduce a new package that will easily migrate all your existing indexes for you into the Oracle Cloud. The DBMS_INDEX_CLOUD package will automatically migrate all indexes for a specified Table/Partition/Schema/Pluggable Database/Database into the Oracle Cloud, which will be a fully online, seamless operation. Once completed, associated indexes within “on premise” tablespaces will effectively be dropped and be replaced with their Cloud equivalents. All that “expensive” storage that was previously tied up in storing all those Oracle indexes can now be freed up to store much more business rich data such as emails, selfie photos, David Bowie music catalogs, etc. Note that these significant storage savings will also translate in smaller backups, smaller Standby databases, smaller Production clones, etc. Importantly, the Oracle Cost Based Optimizer will be fully aware of the Cloud Indexes (as their definitions, statistics and other metadata are still stored within the database data dictionary) and will use the Cloud Indexes as appropriate. No changes to existing SQL is necessary, the CBO will decide to use an index in the cloud in the same manner as it chooses to use an index currently. There is also a new Cloud Index execution path option that allows data to be retrieved via the Cloud Index in a “good enough” manner which is much more efficient than a normal index scan, although it might not necessarily retrieve all the possible data that meets an SQL predicate. It effectively provides what we refer to as that “internet” data retrieval experience.

Oracle Open World 2016 will be the time of the official release for this exciting new Oracle Cloud offering. I will be presenting a number of sessions at OOW16 on this new IDXaaS and there will be a number of live demos to demonstrate its unique capabilities and the simplicity of the migration process. However, there will be a number of announcements before then, including the opportunity to be involved in a beta customer program. There will also be a number of hands-on workshops being conducted globally, with customers getting the chance to see for themselves how easily it is to move database indexes into the Oracle Cloud.

Lastly and perhaps most exciting of all, this new IDXaaS capability will initially be a free option with all current Oracle databases from 12c Release 2 onwards. You will be able to migrate all your existing 12.2 Oracle database indexes onto the Oracle Cloud and they will be stored, maintained and administrated for free as part of your current Oracle database licenses. All Oracle database editions will be supported. This free offer however is likely to end once the 12c Release 2 database is officially released but the additional pay as you go licensing constructs are proposed to be extremely competitive with yearly “indexed” priced increments. Also to be subsequently released will be Oracle IDXaaS support for most other commercial databases including DB2, SQL Server, NoSQL, MySQL, Sybase, Hana, Hadoop and Notepad.

So administrating Oracle (and other) Databases will soon change forever.  Oracle indexes will no longer have to be a concern for customers as all database indexes can instead be stored in the Oracle Cloud via the new IDXaaS offering, allowing Oracle to automatically store and manage these tricky database objects for you. Not only will index rebuilds be a thing of the past, but so will all concerns regarding index storage, creation and maintenance. I’m very excited to be involved in this new undertaking and indeed with my move to Oracle HQ in San Francisco in the coming months (I’ll up in the Clouds level of Building 1). I look forward to talking IDXaaS with you in the days to come 🙂

 

This of course is an April Fools joke. Sorry !!