Day 9: The Twelve Days of SQL: Physical database design matters

Follow @Oratweets

On the ninth day of Christmas, my true love gave to me
Nine ladies dancing.

Day 9: Physical database design matters (Day 8: Statistics are a double-edged sword)(Day 10: Sometimes the optimizer needs a hint)

Physical database design is the process of designing indexes, partitions, clusters, and materialized views. (Lightstone et al. 2007) Indexes help in restriction, projection, joins of all types, and sorting. Index-organized tables, clusters, and partitions and clusters can be used to organize data and thus reduce physical I/O. Materialized views can be used to perform joins and aggregations ahead of time. In his book 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.”

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.

Consider the simple exercise of Day 4: The Twelve Days of SQL: The way you write your query matters. We already have indexes on Empid and Salary and so it would seem that we cannot improve performance any further. But we can get an order of magnitude performance improvement by taking advantage of clusters and materialized views.

CREATE CLUSTER c_salary (salary integer)
SINGLE TABLE SIZE 8192 HASHKEYS 1009;

CREATE MATERIALIZED VIEW LOG ON personnel WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON payroll WITH ROWID;

CREATE MATERIALIZED VIEW mv_salary
CLUSTER c_salary (salary)
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT
  per.ROWID AS per_rowid,
  per.empid,
  per.lname,
  pay.ROWID AS pay_rowid,
  pay.salary
FROM
  personnel per,
  payroll pay
WHERE per.empid = pay.empid;

EXEC DBMS_STATS.gather_table_stats(ownname=>'HR',tabname=>'mv_salary');

SELECT /*+ Uncorrelated subquery with materialized view */ per.empid, per.lname
FROM personnel per
WHERE per.empid IN (SELECT pay.empid
FROM payroll pay
WHERE pay.salary = 199170);

The results are fabulous. The query optimizer automatically takes advantage of the materialized view (automatic query rewrite) and performance improves by an order of magnitude. It took only 13 microseconds to retrieve the data on my laptop running Oracle Database 11g Release 2 within the Developer Days Virtual Machine.

SQL_ID  0qfnuuf6xqjay, child number 0
-------------------------------------
SELECT /*+ Uncorrelated subquery with materialized view */ per.empid,
per.lname FROM personnel per WHERE per.empid IN (SELECT pay.empid FROM
payroll pay WHERE pay.salary = 199170)

Plan hash value: 4094247133

---------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows |E-Bytes| Cost  | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |       |     1 |      4 |00:00:00.01 |       2 |
|*  1 |  TABLE ACCESS HASH| MV_SALARY |      1 |     10 |   310 |       |      4 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------

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

   1 - access("MV_SALARY"."SALARY"=199170)

Note
-----
   - cpu costing is off (consider enabling it)

Here is how the execution time compares with those of the query plans you saw in Day 4: The Twelve Days of SQL: The way you write your query matters:

METHOD                                           SQL_ID        PLAN_HASH_VALUE EXECUTIONS LAST_ELAPSED_TIME LAST_CR_BUFFER_GETS LAST_DISK_READS LAST_OUTPUT_ROWS
------------------------------------------------ ------------- --------------- ---------- ----------------- ------------------- --------------- ----------------
Uncorrelated subquery with materialized view     0qfnuuf6xqjay      4094247133          2                13                   2               0                4
Uncorrelated subquery                            avhtrqsvaay7j      3342999746          2               129                  17               0                4
Aggregate function to check existence (unnested) 8bk6d3udbcbp4       864898783          2               135                  16               0                4
Correlated subquery                              gdazhxm5xdu44       864898783          2               405                  16               0                4
Relational algebra method                        cx451qsx2qfcv      3901981856          2               426                  16               0                4
Scalar subquery in the SELECT clause             6y4kznqkvq635       750911849          2               701                  16               0                4
Uncorrelated subquery (double negative)          67azvy1nw1am1      2202369223          2              7702                 241               0                4
Correlated subquery (double negative)            ayvvv10ah456y       103534934          2             14499                 241               0                4
Scalar subquery in the WHERE clause              ddgmw1whng5ah      3607962630          2            195999               10549               0                4
Aggregate function to check existence            9df084bq799p1      3561519015          2            310690               10554               0                4

P.S. Automatic query rewrite is only available with Enterprise Edition. Users of Standard Edition can query the materialized view directly.

Day 8: Statistics are a double-edged sword

Day 10: Sometimes the optimizer needs a hint