The Sleepiest Feature of Oracle Database

By Iggy Fernandez on January 14, 2014


A “sleeper feature” is a little known, under-appreciated, and easily overlooked feature of a product that actually has great potential. In this article, you will hear about the sleepiest feature in Oracle Database. It’s been around for a very long time, very few people know about it, very few people use it, and yet, it has the potential to be a giant-killer, the giant in this case being NoSQL (see The Twelve Days of NoSQL).

If you’ve been holidaying in Siberia or similar places for about a year, you have probably not talked to an Oracle Sales rep a NoSQL sales rep yet about RAC NoSQL. But you will no doubt find that there’s a voice mail waiting for you when you turn your mobile phone on again after returning home from the vacation.

You might hear something like this:

“SQL databases are a really good thing but they also have some problems and the most obvious problem is one that most application developers run into as they are working with them which is that we assemble structures of objects in memory often in terms of a kind of a cohesive whole of things and then in order to save it off to the database, we have to strip out into bits so that it goes into those individual rows in individual tables. A single logical structure for our user interface and for our processing in memory ends up being splattered across lots and lots of tables. This is referred to as the impedance mismatch problem; the fact that we have these two very different models of how to look at things and the fact that we have to match them causes difficulties.” (http://www.youtube.com/watch?v=qI_g07C_Q5I#t=110s)

The TL;DR summary is that the relational model causes all your performance problems.

Wrong! Wrong I say! The relational model is a logical model and has no performance implications.

But surely “splattering data” across lots and lots of tables causes performance problems?

Wrong again! Wrong again I say! Tables are certainly the way we talk about data in the relational world but the relational model does not dictate that each table correspond to a separate bucket of physical storage.

What’s that you say? That’s how your database management system works? You’re not using Oracle Database are you? Consider the following example taken from the very first paper on the relational model by its inventor Dr. Edgar Codd.

employee (employee#, name, birthdate)
jobhistory (employee#, jobdate, title)
salaryhistory (employee#, jobdate, salarydate, salary)
children (employee#, childname, birthyear)

It certainly appears that we have to assemble employee information into a single structure in the user interface and then “splatter” it across four tables for storage.

Or do we?

Oracle Database is unique among mainstream database management systems in providing a storage construct called the “table cluster” for hierarchical schemas such as the ones talked up by NoSQL sales reps. In Dr. Codd’s example, employee# would be the cluster key, and rows corresponding to the same cluster key from all four tables could be stored in the same physical block on disk thus avoiding the “join penalty.” If the cluster was a “hash cluster,” no indexes would be required for the use case of retrieving records belonging to a single cluster key.

Even though few application developers have heard of table clusters, they are to be found in every single Oracle database on the planet because data dictionary tables like TAB$ (tables), COL$ (table columns), IND$ (indexes), and ICOL$ (index columns) are stored in table clusters.

SQL> select cluster_name, table_name from dba_tables
  2  where owner='SYS' and cluster_name is not null
  3  order by cluster_name, table_name;

CLUSTER_NAME                   TABLE_NAME
------------------------------ ------------------------------
C_COBJ#                        CCOL$
                               CDEF$

C_FILE#_BLOCK#                 SEG$
                               UET$

C_MLOG#                        MLOG$
                               SLOG$

C_OBJ#                         ASSEMBLY$
                               ATTRCOL$
                               CLU$
                               COL$
                               COLTYPE$
                               ICOL$
                               ICOLDEP$
                               IND$
                               LIBRARY$
                               LOB$
                               NTAB$
                               OPQTYPE$
                               REFCON$
                               SUBCOLTYPE$
                               TAB$
                               TYPE_MISC$
                               VIEWTRCOL$

C_OBJ#_INTCOL#                 HISTGRM$

C_RG#                          RGCHILD$
                               RGROUP$

C_TOID_VERSION#                ATTRIBUTE$
                               COLLECTION$
                               METHOD$
                               PARAMETER$
                               RESULT$
                               TYPE$

C_TS#                          FET$
                               TS$

C_USER#                        TSQ$
                               USER$

SMON_SCN_TO_TIME_AUX           SMON_SCN_TIME


37 rows selected.

 

I can prove that clusters have been a part of Oracle Database since at least Oracle Version 6. The Oracle Version 6 documentation is not available online but the Oracle7 Server SQL Reference Manual lists the enhancements to the cluster feature in Oracle7 Server, the implication being that clusters were a feature of Oracle Version 6 (released in 1988 according to Wikipedia).

Here is a fully-worked example of table clusters using the tables in Dr. Codd’s paper.

First we create the four normalized tables listed in Dr. Codd’s paper and prove that all the records of a single employee including job history, salary history, and children are stored in a single database block so that there is never any join-penalty when assembling employee data. The following demonstration was performed using a pre-built developer VM for Oracle VM VirtualBox. The version of Oracle Database is 11.2.0.2.

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jul 28 19:44:23 2013
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

First, we create a table cluster and add our four tables to the cluster.

SQL> CREATE CLUSTER employees (employee# INTEGER) hashkeys 1000;
 
Cluster created.
 
SQL> CREATE TABLE employees
  2  (
  3    employee# INTEGER NOT NULL,
  4    name VARCHAR2(16),
  5    birth_date DATE,
  6    CONSTRAINT employees_pk PRIMARY KEY (employee#)
  7  )
  8  CLUSTER employees (employee#);
 
Table created.
 
SQL> CREATE TABLE job_history
  2  (
  3    employee# INTEGER NOT NULL,
  4    job_date DATE NOT NULL,
  5    title VARCHAR2(16),
  6    CONSTRAINT job_history_pk PRIMARY KEY (employee#, job_date),
  7    CONSTRAINT job_history_fk1 FOREIGN KEY (employee#) REFERENCES employees
  8  )
  9  CLUSTER employees (employee#);
 
Table created.
 
SQL> CREATE TABLE salary_history
  2  (
  3    employee# INTEGER NOT NULL,
  4    job_date DATE NOT NULL,
  5    salary_date DATE NOT NULL,
  6    salary NUMBER,
  7    CONSTRAINT salary_history_pk PRIMARY KEY (employee#, job_date, salary_date),
  8    CONSTRAINT salary_history_fk1 FOREIGN KEY (employee#) REFERENCES employees,
  9    CONSTRAINT salary_history_fk2 FOREIGN KEY (employee#, job_date) REFERENCES job_history
 10  )
 11  CLUSTER employees (employee#);
 
Table created.
 
SQL> CREATE TABLE children
  2  (
  3    employee# INTEGER NOT NULL,
  4    child_name VARCHAR2(16) NOT NULL,
  5    birth_date DATE,
  6    CONSTRAINT children_pk PRIMARY KEY (employee#, child_name),
  7    CONSTRAINT children_fk1 FOREIGN KEY (employee#) REFERENCES employees
  8  )
  9  CLUSTER employees (employee#);
 
Table created.

Then we insert data into all four tables, nine records in total. We then use the DBMS_ROWID.ROWID_BLOCK_NUMBER function to prove that all nine records have been stored in the same database block even though they belong to four different tables. Therefore the “join penalty” has been completely eliminated. Queries involving only one employee won’t need indexes since we used the hash flavor of table clusters. Therefore any query involving only one employee will have to retrieve only one database block.

SQL> INSERT INTO employees VALUES (1, 'IGNATIUS', '01-JAN-1970');
 
1 row created.
 
SQL> INSERT INTO children VALUES (1, 'INIGA', '01-JAN-2001');
 
1 row created.
 
SQL> INSERT INTO children VALUES (1, 'INIGO', '01-JAN-2002');
 
1 row created.
 
SQL> INSERT INTO job_history VALUES (1, '01-JAN-1991', 'PROGRAMMER');
 
1 row created.
 
SQL> INSERT INTO job_history VALUES (1, '01-JAN-1992', 'DATABASE ADMIN');
 
1 row created.
 
SQL> INSERT INTO salary_history VALUES (1, '01-JAN-1991', '1-FEB-1991', 1000);
 
1 row created.
 
SQL> INSERT INTO salary_history VALUES (1, '01-JAN-1991', '1-MAR-1991', 1000);
 
1 row created.
 
SQL> INSERT INTO salary_history VALUES (1, '01-JAN-1992', '1-FEB-1992', 2000);
 
1 row created.
 
SQL> INSERT INTO salary_history VALUES (1, '01-JAN-1992', '1-MAR-1992', 2000);
 
1 row created.
 
SQL> SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS block_number FROM employees where employee# = 1;
 
BLOCK_NUMBER
------------
       22881
 
SQL> SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS block_number FROM children where employee# = 1;
 
BLOCK_NUMBER
------------
       22881
 
SQL> SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS block_number FROM job_history where employee# = 1;
 
BLOCK_NUMBER
------------
       22881
 
SQL> SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS block_number FROM salary_history where employee# = 1;
 
BLOCK_NUMBER
------------
       22881

 

Let’s kick it up a notch. We can create an object-relational view that assembles employee information into a single unnormalized structure and perform CRUD operations on it as if it were a single table. We’ll need an INSTEAD OF trigger.

SQL> CREATE OR REPLACE TYPE children_rec AS OBJECT (child_name VARCHAR2(16), birth_date DATE)
  2  /
 
Type created.
 
SQL> CREATE OR REPLACE TYPE children_tab AS TABLE OF children_rec
  2  /
 
Type created.
 
SQL> CREATE OR REPLACE TYPE salary_history_rec AS OBJECT (salary_date DATE, salary NUMBER)
  2  /
 
Type created.
 
SQL> CREATE OR REPLACE TYPE salary_history_tab AS TABLE OF salary_history_rec
  2  /
 
Type created.
 
SQL> CREATE OR REPLACE TYPE job_history_rec AS OBJECT (job_date DATE, title VARCHAR2(16), salary_history SALARY_HISTORY_TAB)
  2  /
 
Type created.
 
SQL> CREATE OR REPLACE TYPE job_history_tab AS TABLE of job_history_rec
  2  /
 
Type created.
 
SQL> create or replace view employees_view as
  2  SELECT
  3    employee#,
  4    name,
  5    birth_date,
  6    CAST
  7    (
  8      MULTISET
  9      (
 10        SELECT
 11          child_name,
 12          birth_date
 13        FROM children
 14        WHERE employee#=e.employee#
 15      )
 16      AS children_tab
 17    ) children,
 18    CAST
 19    (
 20      MULTISET
 21      (
 22        SELECT
 23          job_date,
 24          title,
 25          CAST
 26          (
 27            MULTISET
 28            (
 29              SELECT salary_date, salary
 30              FROM salary_history
 31              WHERE employee#=e.employee#
 32              AND job_date=jh.job_date
 33            )
 34            AS salary_history_tab
 35          ) salary_history
 36        FROM job_history jh
 37        WHERE employee#=e.employee#
 38      )
 39      AS job_history_tab
 40    ) job_history
 41  FROM employees e;
 
View created.
SQL> alter session set "_rowsource_execution_statistics"=true;
 
Session altered.
 
SQL> SELECT * FROM employees_view WHERE employee# = 1;
 
 EMPLOYEE# NAME             BIRTH_DAT
---------- ---------------- ---------
CHILDREN(CHILD_NAME, BIRTH_DATE)
------------------------------------------------------------------------------------------------------------------------------------
JOB_HISTORY(JOB_DATE, TITLE, SALARY_HISTORY(SALARY_DATE, SALARY))
------------------------------------------------------------------------------------------------------------------------------------
         1 IGNATIUS         01-JAN-70
CHILDREN_TAB(CHILDREN_REC('INIGA', '01-JAN-01'), CHILDREN_REC('INIGO', '01-JAN-02'))
JOB_HISTORY_TAB(JOB_HISTORY_REC('01-JAN-91', 'PROGRAMMER', SALARY_HISTORY_TAB(SALARY_HISTORY_REC('01-FEB-91', 1000), SALARY_HISTORY_
REC('01-MAR-91', 1000))), JOB_HISTORY_REC('01-JAN-92', 'DATABASE ADMIN', SALARY_HISTORY_TAB(SALARY_HISTORY_REC('01-FEB-92', 2000), S
ALARY_HISTORY_REC('01-MAR-92', 2000))))
 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'TYPICAL IOSTATS LAST'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aaxmaqz947aa0, child number 0
-------------------------------------
SELECT * FROM employees_view WHERE employee# = 1
 
Plan hash value: 2117652374
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Starts | E-Rows |E-Bytes| Cost  | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |      1 |        |       |     1 |      1 |00:00:00.01 |       1 |
|*  1 |  TABLE ACCESS HASH| CHILDREN       |      1 |      1 |    32 |       |      2 |00:00:00.01 |       1 |
|*  2 |  TABLE ACCESS HASH| SALARY_HISTORY |      2 |      1 |    44 |       |      4 |00:00:00.01 |       3 |
|*  3 |  TABLE ACCESS HASH| JOB_HISTORY    |      1 |      1 |    32 |       |      2 |00:00:00.01 |       1 |
|*  4 |  TABLE ACCESS HASH| EMPLOYEES      |      1 |    845 | 27040 |       |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("EMPLOYEE#"=:B1)
   2 - access("EMPLOYEE#"=:B1)
       filter("JOB_DATE"=:B1)
   3 - access("EMPLOYEE#"=:B1)
   4 - access("EMPLOYEE#"=1)
 
Note
-----
   - cpu costing is off (consider enabling it)
   - dynamic sampling used for this statement (level=2)
 
30 rows selected.

 

Next, let’s create an “INSTEAD OF” trigger so that we can insert into the above view directly; that is, use a single insert statement instead of multiple insert statements. The trigger will do all the heavy-lifting behind the scenes.

SQL> CREATE OR REPLACE TRIGGER employees_view_insert
  2  INSTEAD OF INSERT ON employees_view
  3  REFERENCING NEW AS n
  4  FOR EACH ROW
  5  DECLARE
  6    i NUMBER;
  7  BEGIN
  8    INSERT INTO employees
  9    VALUES
 10    (
 11      :n.employee#,
 12      :n.name,
 13      :n.birth_date
 14    );
 15
 16    FOR i IN :n.children.FIRST .. :n.children.LAST
 17    LOOP
 18      INSERT INTO children
 19      VALUES
 20      (
 21        :n.employee#,
 22        :n.children(i).child_name,
 23        :n.children(i).birth_date
 24      );
 25    END LOOP;
 26
 27    FOR i IN :n.job_history.FIRST .. :n.job_history.LAST
 28    LOOP
 29      INSERT INTO job_history VALUES
 30      (
 31        :n.employee#,
 32        :n.job_history(i).job_date,
 33        :n.job_history(i).title
 34      );
 35      FOR j IN :n.job_history(i).salary_history.FIRST .. :n.job_history(i).salary_history.LAST
 36      LOOP
 37        INSERT INTO salary_history
 38        VALUES
 39        (
 40          :n.employee#,
 41          :n.job_history(i).job_date,
 42          :n.job_history(i).salary_history(j).salary_date,
 43          :n.job_history(i).salary_history(j).salary
 44         );
 45      END LOOP;
 46    END LOOP;
 47  END;
 48  /
 
Trigger created.
SQL> INSERT INTO employees_view
  2  VALUES
  3  (
  4    2,
  5    'YGNACIO',
  6    '01-JAN-70',
  7    CHILDREN_TAB
  8    (
  9      CHILDREN_REC('INIGA', '01-JAN-01'),
 10      CHILDREN_REC('INIGO', '01-JAN-02')
 11    ),
 12    JOB_HISTORY_TAB
 13    (
 14      JOB_HISTORY_REC
 15      (
 16        '01-JAN-91',
 17        'PROGRAMMER',
 18        SALARY_HISTORY_TAB
 19        (
 20          SALARY_HISTORY_REC('01-FEB-91', 1000),
 21          SALARY_HISTORY_REC('01-MAR-91', 1000)
 22        )
 23      ),
 24      JOB_HISTORY_REC
 25      (
 26        '01-JAN-92',
 27        'DATABASE ADMIN',
 28        SALARY_HISTORY_TAB
 29        (
 30          SALARY_HISTORY_REC('01-FEB-92', 2000),
 31          SALARY_HISTORY_REC('01-MAR-92', 2000)
 32        )
 33      )
 34    )
 35  );
 
1 row created.
 
SQL> SELECT * FROM employees_view WHERE employee# = 2;
 
 EMPLOYEE# NAME             BIRTH_DAT
---------- ---------------- ---------
CHILDREN(CHILD_NAME, BIRTH_DATE)
------------------------------------------------------------------------------------------------------------------------------------
JOB_HISTORY(JOB_DATE, TITLE, SALARY_HISTORY(SALARY_DATE, SALARY))
------------------------------------------------------------------------------------------------------------------------------------
         2 YGNACIO          01-JAN-70
CHILDREN_TAB(CHILDREN_REC('INIGA', '01-JAN-01'), CHILDREN_REC('INIGO', '01-JAN-02'))
JOB_HISTORY_TAB(JOB_HISTORY_REC('01-JAN-91', 'PROGRAMMER', SALARY_HISTORY_TAB(SALARY_HISTORY_REC('01-FEB-91', 1000), SALARY_HISTORY_
REC('01-MAR-91', 1000))), JOB_HISTORY_REC('01-JAN-92', 'DATABASE ADMIN', SALARY_HISTORY_TAB(SALARY_HISTORY_REC('01-FEB-92', 2000), S
ALARY_HISTORY_REC('01-MAR-92', 2000))))

The above example proves that you can use the relational model of data for hierarchical schemas—such as the ones talked up by NoSQL sales reps—without incurring a performance penalty. There are certainly some cases—such as Microsoft Word documents and Excel spreadsheets—where the relational model doesn’t add much value and for these cases mainstream database management systems provide the XML data type.

Oracle table clusters can be partitioned with all the benefits of partition pruning and parallel query but we’ll need to use another very sleepy feature of Oracle Database. I’ll leave that for a follow-up article.

On page 379 of Effective Oracle by Design, Tom Kyte quotes Steve Adams as saying:

“If a schema has no IOTs or clusters, that is a good indication that no thought has been given to the matter of optimizing data access.”

I’ll leave you with that thought.

P.S. If you’re trying to get a handle on the NoSQL phenomenon, I recommend that you read The Twelve Days of NoSQL.

 

Related Posts

Leave a Reply