jump to navigation

12.1.0.2 Released With Cool Indexing Features (Short Memory) July 25, 2014

Posted by Richard Foote in 12c, Advanced Index Compression, Attribute Clustering, Database In-Memory, Zone Maps.
2 comments

Oracle Database 12.1.0.2 has finally been released and it has a number of really exciting goodies from an indexing perspective which include:

  • Database In-Memory Option, which enables specific portions of the database to be in dual format, in both the existing row based format and additionally into an efficient memory only columnar based format. This in turn enables analytical based processing to access the real-time data in the In-Memory Store extremely fast, potentially faster and more effectively than via standard analytical based database indexes.
  • Advanced Index Compression, which allows Oracle to automatically choose the appropriate compression method for each individual leaf block, rather than having to manually select a single compression method across the whole index. This makes compressing an index a breeze and much more effective than previously possible.
  • Zone Maps, which enables Storage Index like capabilities to be manually configured and physically implemented inside the database, to eliminate unnecessary accesses of table storage via much smaller objects than conventional database indexes.
  • Attribute Clustering, a new table attribute which enables much better clustering of table data and we all know how both compression and index structures love table data to be well clustered.

These are all topics I’ll be covering in the coming weeks so stay tuned 🙂

Presenting at UKOUG Tech14 Conference (Ian Fish, U K Heir) July 15, 2014

Posted by Richard Foote in Richard Presentations, UKOUG Tech14.
add a comment

I’ve been lucky enough to present at various conferences, seminars and user group events over the years in some 20 odd countries. But somewhere I’ve never quite managed to present at before is the place of my birth, the UK. Well this year, I’ve decided end my drought and submitted a number of papers for the UKOUG Tech14 Conference and the parallel OakTable World UK14 Conference which were all thankfully accepted. So the land of David Bowie, Coronation Street, Bruce Forsyth, televised darts and a touch of drizzle, here I come.

The conference this year is being held in balmy Liverpool on 8-10 December 2014.

I’ll be presenting (times subject to change):

Oracle Indexes Q&A Session With Richard Foote (at OakTable World UK14)
(08/12/2014 09:00 – 09:50)

Oracle Database 12c New Indexing Features
(09/12/2014 12:00 – 12:50)

Indexing In Exadata
(10/12/2014 11:30 – 12:20)

 

Hopefully I’ll get to meet some of you there 🙂

12c Index Like Table Statistics Collection (Wearing The Inside Out) July 9, 2014

Posted by Richard Foote in 12c, Automatic Table Statistics, Index statistics, Oracle Indexes.
2 comments

This change introduced in 12c has caught me out on a number of occasions.

If you were to create a new table:

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

Table created.

And then populate it with a conventional insert:

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

1000000 rows created.

SQL> commit;

Commit complete.

We find there are no statistics associated with the table until we explicitly collect them:

SQL> select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed from dba_tables where table_name='THIN_WHITE_DUKE1';

TABLE_NAME         NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
---------------- ---------- ---------- ---------- ----------- ---------
THIN_WHITE_DUKE1

But if we were to now create an index on this table:

SQL> create index thin_white_duke1_code_i on thin_white_duke1(code);

Index created.

We find that we now do indeed have index statistics collected by default (since 9i days anyways):

SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys from dba_indexes where index_name='THIN_WHITE_DUKE1_CODE_I';

INDEX_NAME                NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
----------------------- ---------- ---------- ----------- -------------
THIN_WHITE_DUKE1_CODE_I    1000000          2        1936            10

OK, if we now create another table and index:

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

Table created.

SQL> create index thin_white_duke2_code_i on thin_white_duke2(code);

Index created.

And populate it with a bulk load parallel, append insert:

SQL> insert /*+ append */ into thin_white_duke2 select rownum, mod(rownum,10), 'DAVID BOWIE' from dual connect by level <=1000000;

1000000 rows created.

 SQL> commit;

Commit complete.

If we now look at the table statistics:

SQL> select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed
from dba_tables where table_name='THIN_WHITE_DUKE2';

TABLE_NAME         NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
---------------- ---------- ---------- ---------- ----------- ---------
THIN_WHITE_DUKE2    1000000       3511          0          20 09-JUL-14

SQL> select column_name, num_distinct, density, num_buckets from dba_tab_columns
 where table_name='THIN_WHITE_DUKE2';

COLUMN_NAME NUM_DISTINCT    DENSITY NUM_BUCKETS
----------- ------------ ---------- -----------
NAME                   1          1           1
CODE                  10         .1           1
ID               1000000    .000001           1

SQL> select column_name, num_distinct, density, histogram, notes
from dba_tab_col_statistics where table_name='THIN_WHITE_DUKE2';

COLUMN_NAME NUM_DISTINCT    DENSITY HISTOGRAM  NOTES
----------- ------------ ---------- ---------- -------------
NAME                   1          1 NONE       STATS_ON_LOAD
CODE                  10         .1 NONE       STATS_ON_LOAD
ID               1000000    .000001 NONE       STATS_ON_LOAD

 

We notice that in 12c, they’re automatically populated and accurate. The NOTES column in dba_tab_col_statistics highlights that the statistics were collected via STATS_ON_LOAD.

If however we look at the current state of the index statistics:

SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys from dba_indexes where index_name='THIN_WHITE_DUKE2_CODE_I';

INDEX_NAME                NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
----------------------- ---------- ---------- ----------- -------------
THIN_WHITE_DUKE2_CODE_I          0          0           0             0

We noticed they haven’t changed since the index was initially created. So by populating an empty table with an append insert, table statistics are now collected on the fly, but not the statistics on any existing indexes.

If we want to now collect just the index statistics and potentially any useful histograms on the table (as histograms are not collect during the bulk load operation), we can use the new GATHER_AUTO option with the dbms_stats.gather_table_stats procedure without having to re-gather base table statistics:

SQL> exec dbms_stats.gather_table_stats(ownname=>user,
tabname=>'THIN_WHITE_DUKE2', options=>'GATHER AUTO');

PL/SQL procedure successfully completed.

SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys
from dba_indexes where index_name='THIN_WHITE_DUKE2_CODE_I';

INDEX_NAME                NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
----------------------- ---------- ---------- ----------- -------------
THIN_WHITE_DUKE2_CODE_I    1000000          2        1739            10

 The same automatic table statistics gathering occurs when we create a table via a sub-select clause:

SQL> create table thin_white_duke3
as select rownum id, mod(rownum,1000) code, 'DAVID BOWIE' name
from dual connect by level <= 1000000;

Table created.

SQL> select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed
from dba_tables where table_name='THIN_WHITE_DUKE3';

TABLE_NAME         NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
---------------- ---------- ---------- ---------- ----------- ---------
THIN_WHITE_DUKE3    1000000       3787          0          22 09-JUL-14

Of course, sometimes when creating test tables as I regularly do, one sometimes forgets these statistics are now automatically collected !!

It was 12 years ago today… July 8, 2014

Posted by Richard Foote in Oracle Indexes.
4 comments

It was exactly 12 years ago today that I first presented my Index Internals – Rebuilding The Truth presentation at a local ACT Oracle User Group event.

And so my association with Oracle indexes started. It would be an interesting statistic to know how many people have subsequently read the presentation 🙂 It would no doubt result in a few 50-50 block splits !!

How time flies 🙂