11g logo

Oracle Database 11g:
The Top Features for DBAs and Developers

by Arup Nanda Oracle ACE Director

Data Warehousing and OLAP

Database-resident Cube Organized Materialized Views that marry the power of OLAP cubes with the simplicity of SQL without any special tools, easier identification of refresh with partition change tracking, a new Analytic Workspace Manager, Query Rewrite extended to subqueries and remote tables, and many more new features make Oracle Database an even more compelling platform for data warehousing.

See Series TOC

Cube-Organized Materialized Views

In the mid-1990s, the concept of Online Analytic Processing (OLAP), which has been around since the 1970s, entered the mainstream, with the term "OLAP" coined by Ted Codd himself in 1992. Being somewhat esoteric, OLAP was something most businesses didn't know how to properly leverage at the time.

Years later, the technology has been sufficiently perfected to make OLAP against large data warehouses feasible, truly bringing the "intelligence" to business intelligence. A huge departure from traditional relational design, OLAP allows the data to be stored and accessed in the most efficient manner—allowing end-users to traverse the edges of a hypothetical "cube" of many dimensions. (See below for an example of such a data cube).

Figure 1

The cube's dimensions are associated with facts (also called "measures"). In relational terms, the facts have a many-to-one relationship with the dimensions. For example, Acme Computer Supplies may have a database for sales. Dimensions are usually Customers, Products, and Time Element (month, quarter, etc.). The sales figure for a specific product (Cat5e cables) to a specific customer (Oracle Corp.) during a specific time period (Aug 2008) is one measure. The dimensions are stored on individual tables and so are the facts—i.e. the sales figure. So the fact table, in relational terminology, is a child table of the dimension tables.

But that's where the analogy ends. The access to the measures in relational design would have been through indexes created on the customer, product, or time columns of the fact table. In the OLAP approach, specific cells (the measures) are accessed by traversing the cube: in this example, by going to the slice containing the time - Aug 08; then product - Cat5e; and finally the customer - Oracle.

Oracle knows how to go to these slices by calculating the destination as in an array, not a table. For instance, suppose the dimensions are organized as shown below:

Dimension Time := {'May','Jun','Jul','Aug'}
Dimension Customer := {'Microsoft','IBM','Oracle','HP'}
Dimension Product := {'Fiber','Cat6e','Cat5e','Serial'}


To find the measure for Oracle + Aug + Cat5e, the OLAP engine performs the navigation like this:

  1. Aug 08 is the fourth element of the array called Time, so travel to the fourth cell along the time dimension of the cube.
  2. Cat5e is the third element of the Product array, so travel to the third element.
  3. Oracle is the third element of the Customer array, so travel to the third element.

That's it; now you've arrived at the measure you want. This is done without indexes since the dimension values serve as array pointers. Similarly, if you want to calculate the total sales to all customers in Aug 08, you do the same thing as above, except that in Step 3 you total the measures of the elements of the array without going to a specific cell.

Contrast that approach with the relational access in a pure relational form of data stored in a classic star schema, as shown below.

Figure 2

In the relational database approach, you will have to join this "fact" table to all the dimensions. Every time you need data, you will need to select the appropriate data from the fact table, possibly via indexes, and join it with each dimension one by one, again via indexes. Although technically possible, this approach is quite infeasible in large databases.

As an alternative, what about creating Materialized Views (MVs) for all those selections? A user could use any combination of elements in the dimensions:

  • Sales for Cat5e in Aug to all customers

  • Sales to Oracle for Serial Cable in Aug as a percentage of sales to IBM, for the same product and month

  • Sales of Fiber Cables to HP as a percentage of Serial Cables to Microsoft

and so on. But how many MVs would you need to create? In theory, one for each combination (4 x 4 x 4 = 64 MVs). In addition to the space, you would need time and database resources to refresh the MVs when the data changes—and there will likely be thousands of elements involved. Thus, the number of

MVs to be created and managed becomes outrageously high.

In contrast, a cube is a single segment that can allow any types of queries with equal ease. Although both of them are used in data warehouse designs for faster processing of summarized data (as opposed to OLTP data), there is a huge fundamental difference: whereas MVs store pre-computed results to avoid joins and aggregations, cubes store raw data and compute most summaries on the fly. (The stress is on the word "most" as some summaries are created. The cube decides which aggregations are useful and creates only those aggregations. In all other cases, the summaries are computed on the fly.) As the access path is through array bases arithmetic, the data retrieval is much faster in cubes compared to relational tables like MVs.

The OLAP objects such as cubes are stored in special areas of the database called an Analytic Workspace (AW). A database may have one or more AW. These AWs are stored as BLOBs on tables specially named with an AW$ prefix.

While data cubes in Oracle Database are not new, in versions previous to Oracle Database 11g, they are accessed slightly differently. (Under the covers Oracle is, after all, a predominantly relational database engine.) A cube representation of data in these versions is a non-native concept, especially where MVs are concerned.

MVs offer some very interesting features: automatic query rewrite where a user's query is rewritten automatically, incremental refreshes where only parts of the MV are refreshed, and so on. On the flip side, MVs are a relational feature, not native to OLAP cubes.

Now imagine, if you would, the best of both worlds: the rewrite feature of MVs combined with the performance benefits of OLAP cubes. In Oracle Database 11g, you have it. Perhaps the most important new functionality in this area is the ability to represent OLAP cubes as MVs, via a new feature called Cube Organized Materialized Views. One new function, CUBE_TABLE, accomplishes searching through the cube in plain SQL. Since the MV is actually an OLAP cube, the Query Rewrite feature will rewrite the queries to use the cube, without you even knowing it. That should allow you to take advantage of the performance of cubes from any tool that supports SQL: Oracle Business Intelligence Enterprise Edition, Cognos, Business Objects, Oracle Apex, SQL*Plus, or custom Java programs; the list is endless. In fact you don't even have to know any special syntax. (Cube Organized MVs are named with a CB$ prefix for easy identification.)

I will explain this new feature via Oracle's official sample schema. After downloading it, unzip the zip file:

$ unzip global_11g_schema.zip
Archive:  global_11g_schema.zip
  inflating: global_11g_remove.sql  
  inflating: global_11g_source.dmp  
  inflating: Templates/CHANNEL.XML  
  inflating: Templates/CUSTOMER.XML 
  inflating: Templates/GLOBAL.XML   
  inflating: Templates/GLOBAL_MV.XML 
  inflating: Templates/PRICE_CUBE.XML 
  inflating: Templates/PRODUCT.XML  
  inflating: Templates/TIME.XML     
  inflating: Templates/UNITS_CUBE.XML 
  inflating: global_11g_install.sql 
  inflating: global_11g_readme.html 

Install the global schema by running the script from SQL*Plus or SQL Developer. You should know the password of the SYSTEM user:

SQL> @global_11g_install
Enter the password for the user GLOBAL: 
Enter the password for the user SYSTEM: 
Connected.
 
User created.
 
 
Grant succeeded.
 
Connected.
 
Import: Release 11.1.0.6.0 - Production on Sat Jun 28 17:08:22 2008
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
 
Export file created by EXPORT:V11.01.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing GLOBAL's objects into GLOBAL
. . importing table                      "ACCOUNT"         24 rows imported
. . importing table                  "CHANNEL_DIM"          3 rows imported
. . importing table                 "CUSTOMER_DIM"         61 rows imported
. . importing table                   "PRICE_FACT"       2523 rows imported
. . importing table         "PRODUCT_CHILD_PARENT"         48 rows imported
. . importing table                  "PRODUCT_DIM"         36 rows imported
. . importing table                     "TIME_DIM"        120 rows imported
. . importing table                   "UNITS_FACT"     299446 rows imported
About to enable constraints...
Import terminated successfully without warnings.
 
SQL> exit


The file global_11g_readme.html contains more information on the schema. It also has a script to drop the schema after your testing is done. The file imports the dimensions, facts, and all other pertinent data from a dump file.

It may be easiest to explain the concept of the Cube Organized MV with an example. There are two cubes included in the dumpfile; one is PRICE_CUBE. Let's see how to query that cube using plain SQL. I also want to show the access path to be followed by Oracle, so I used the autotrace command before running the query.

SQL> set autotrace on explain
SQL> select * from table(cube_table('GLOBAL.PRICE_CUBE'))
  2> /

...
... 
                             
the data comes here
...
...
Execution Plan
----------------------------------------------------------
Plan hash value: 3184667476

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |  2000 |   195K|    29   (0)| 00:00:01 |
|   1 |  CUBE SCAN PARTIAL OUTER| PRICE_CUBE |  2000 |   195K|    29   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

                           

This function CUBE_TABLE is new in Oracle Database 11g. It allows a cube stored in the AW to be queried as a relational object. Note that the access path shows CUBE SCAN—meaning the cube was scanned as a cube, even though you used SQL. In this example, we used the most rudimentary of tools; SQL*Plus is completely ignorant of OLAP or analytic transformations, yet we could pull the data in an analytic manner anyway.

The function CUBE_TABLE can be used on both tables and dimensions. The general form is

select * from table(cube_table('<schema>.<dimension>;<hierarchy>'))

The hierarchy part is optional; you can omit it. Here is an example from the sample GLOBAL schema you installed earlier, where you want to select the hierarchy PRIMARY from the dimension called PRODUCT.

select * from table(cube_table('GLOBAL.PRODUCT;PRIMARY'))


A view is automatically generated for each cube and dimension. For instance, there is a view called PRICE_CUBE_VIEW for the cube PRICE_CUBE. If you examine the view definition, you will see that is as seen here:

CREATE OR REPLACE VIEW "PRICE_CUBE_VIEW" ("UNIT_PRICE", "UNIT_COST", "TIME", "PRODUCT") AS 
  SELECT 
    "UNIT_PRICE", 
    "UNIT_COST", 
    "TIME", 
    "PRODUCT"
  FROM TABLE(CUBE_TABLE('GLOBAL.PRICE_CUBE') ) 


Views are useful where the GUI tools do not know about the CUBE function and the users are prompted to choose an object type, typically a view. Here is an example of where Oracle SQL Developer is used to select data from the view.

Figure 3


SQL Developer shows the views and you can select the data tab and filter just like any other view. Although the data type is multidimensional, the CUBE_TABLE table function allows it to be accessed as a relational object. Behind the scenes the database's multidimensional engine performs the necessary calculations—aggregates or calculates measures, for example—transparently. So you get the best of both worlds. You can use the power of the OLAP functionality built into Oracle Database and yet be in the comfort zone of the applications you are familiar with, even if it's SQL*Plus.

Although you can administer the cubes in plain SQL, the easiest method is through the Analytic Workspace Manager tool (described later). The view DBA_CUBES shows the information on cubes.

So, let's examine what you get from Cube Organized MVs. First, the cube (and cube-organized MV) can dramatically improve the performance of queries requiring summary data due to the analytic access of the cube. Second, the cube is very efficient in fast, incremental refreshes. Finally, since a single cube can replace many MVs, it makes it much easier to manage and maintain.

Note that the cube-organized MV is simply a metadata object. The summary data is managed by the cube and the cube-organized MV simply supports automatic query rewrite and automatic refresh of the cube. The data is stored in a cube, not inside the cube-organized MV, so this is not a replication strategy. Think of it as similar to an MV on a prebuilt table, which is akin to the cube.

Query Rewritten More Often

You should be already familiar with Query Rewrite functionality, introduced in Oracle Database a couple of releases ago. In summary, when a user writes a query that matches with the defining query of an MV, Oracle chooses to select from the MV instead of executing the query in the database. The term "matches" means either a partial result set of the query can be satisfied by the stored MV or that the query can be sped up using the existing data in the MV. In other words, Oracle rewrites the query (or portions of it) to select from the MV instead of the tables specified in the query. This eliminates the database doing the task of accessing the base tables and doing the computations and returns the data faster to the user. All these occur automatically without the user even knowing that such a MV existed and the query was rewritten.

Of course, the user must choose to accept such as a substitution in the query. The session parameter query_rewrite_enabled must be set to TRUE and query_rewrite_integrity should be either trusted or stale_tolerated based on the staleness of the MV (the parameter controls the level of data integrity enforcement that is provided by the kernel). The MV itself also must be available for query rewrite as well.

The query rewrite occurs when the users query similar to the defining query of the MV. In past versions, if the query was not similar, the query was not rewritten. But in Oracle Database 11g, the rules are more relaxed. Consider the MV shown below:

create materialized view mv4
        enable query rewrite
as
select   prod_id, cust_id, avg (rate) tot_qty
    from (select s.prod_id, cust_id, amount_sold / quantity_sold rate
            from sales s, products p
           where s.prod_id = p.prod_id) sq
group by prod_id, cust_id

/

It uses an inline query, where the row source is actually another query (the clause in FROM is actually an inline query). If you write a query similar to the defining query of the MV, where you use the same inline view, now you will see rewrite occurring. Use AUTOTRACE to check the execution path.

SQL> alter session set query_rewrite_enabled = true;
 
Session altered.
 
SQL> alter session set query_rewrite_integrity = stale_tolerated;
 
Session altered.

SQL> set autotrace traceonly explain
SQL> select pid, cid, avg(item_rate) avg_item_rate
  2  from (select s.prod_id pid, cust_id cid, amount_sold/quantity_sold item_rate
  3  from sales s, products p
  4  where p.prod_id = s.prod_id)
  5  group by cid, pid;

Execution Plan
----------------------------------------------------------
Plan hash value: 3944983699
 
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |   287K|    10M|   226   (2)| 00:00:03 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV4  |   287K|    10M|   226   (2)| 00:00:03 |
-------------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement


Note the Id=1 above; the query was rewritten with the Materialized View MV4—the one we created earlier. The query rewrite took place even if the MV and the query used inline views (or subqueries).

Query rewrite also occurs for remote tables in Oracle Database 11g.

Staleness Checking in Partitions

If you have a partitioned detail table on which an MV has been created, you can take advantage of Partition Change Tracking (PCT; introduced with Oracle Database 10g) to refresh for only a specific partition and not the entire table. The PCT feature allows you to refresh with the FAST option even if there is no MV Log on the detail table. But how do you know which partitions have changed?

In Oracle Database 11g, you have a great new view: DBA_MVIEW_DETAIL_PARTITION. This view shows the partitions that have been updated and will be used in PCT refresh later. The column FRESHNESS shows if the partition is fresh for the MV. Like any other view, there are USER_* and ALL_* versions as well.

Let's use the schema SH provided on the Companion CD of Oracle Database 11g. First, check the freshness:

SQL> select detailobj_name, detail_partition_name, detail_partition_position, freshness
  2> from user_mview_detail_partition
  3> where MVIEW_NAME = 'MV1';

DETAILOBJ_NAME                 DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
SALES                          SALES_1995                                             1 FRESH
SALES                          SALES_1996                                             2 FRESH
SALES                          SALES_H1_1997                                          3 FRESH
SALES                          SALES_H2_1997                                          4 FRESH
SALES                          SALES_Q1_1998                                          5 FRESH
SALES                          SALES_Q2_1998                                          6 FRESH
SALES                          SALES_Q3_1998                                          7 FRESH
SALES                          SALES_Q4_1998                                          8 FRESH
SALES                          SALES_Q1_1999                                          9 FRESH
SALES                          SALES_Q2_1999                                         10 FRESH
SALES                          SALES_Q3_1999                                         11 FRESH
SALES                          SALES_Q4_1999                                         12 FRESH
SALES                          SALES_Q1_2000                                         13 FRESH
SALES                          SALES_Q2_2000                                         14 FRESH
SALES                          SALES_Q3_2000                                         15 FRESH
SALES                          SALES_Q4_2000                                         16 FRESH
SALES                          SALES_Q1_2001                                         17 FRESH
SALES                          SALES_Q2_2001                                         18 FRESH
SALES                          SALES_Q3_2001                                         19 FRESH
SALES                          SALES_Q4_2001                                         20 FRESH
SALES                          SALES_Q1_2002                                         21 FRESH
SALES                          SALES_Q2_2002                                         22 FRESH
SALES                          SALES_Q3_2002                                         23 FRESH
SALES                          SALES_Q4_2002                                         24 FRESH
SALES                          SALES_Q1_2003                                         25 FRESH
SALES                          SALES_Q2_2003                                         26 FRESH
SALES                          SALES_Q3_2003                                         27 FRESH
SALES                          SALES_Q4_2003                                         28 FRESH

Now insert a row in the table:

insert into sales values (13,987,'1-jan-98',3,999,1,1232.16)


Now, if you issue the same query to check freshness:

DETAILOBJ_NAME                 DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------                ------------------------------                            ------------------------- -----
SALES                          SALES_1995                                             1 FRESH
SALES                          SALES_1996                                             2 FRESH
SALES                          SALES_H1_1997                                          3 FRESH
SALES                          SALES_H2_1997                                          4 FRESH
SALES                          SALES_Q1_1998                                          5 STALE
SALES                          SALES_Q2_1998                                          6 FRESH
... and so on ...


The partition where the row went—SALES_Q1_1998—is now listed as STALE. When you fast refresh the MV, it will use PCT to refresh from that partition alone instead of the entire table. And the FAST refresh will occur even if there is no MV Log on the table.

Now, if you refresh the MV with FAST option:

SQL> exec dbms_mview.refresh('MV1','F')

PL/SQL procedure successfully completed.

And check the freshness again:

DETAILOBJ_NAME                 DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------                ------------------------------                            ------------------------- -----
SALES                          SALES_1995                                             1 FRESH
SALES                          SALES_1996                                             2 FRESH
SALES                          SALES_H1_1997                                          3 FRESH
SALES                          SALES_H2_1997                                          4 FRESH
SALES                          SALES_Q1_1998                                          5 FRESH
SALES                          SALES_Q2_1998                                          6 FRESH


The partition is marked FRESH; the subsequent fast refreshes will not pick that partition. This view allows you to easily identify which partitions will be refreshed in a PCT refresh and estimate the work involved. This feature works for Cube Organized MVs as well.

Analytic Workspace Manager

As I mentioned previously, the special area where the OLAP objects are stored is called an Analytic Workspace. The OLAP objects are manipulated via a special tool called Analytic Workspace Manager (AWM), which you can download from the Oracle OLAP Option homepage on OTN.

AWM comes in two flavors: the standalone version or a DBClient. The standalone version is a full JAR file that you can run from the java command line. The DBClient has a few DLLs, which should be copied over to the appropriate directory in Oracle Home. In this section we will explore the standalone version ( 11.1.0.7A) of the tool.

After unzipping the files, start AWM by going to the directory where you unzipped it and issuing the following command:

java -mx512m -jar awm11.1.0.6.0A.jar

After the GUI opens, click on File -> Connect Database and choose the database you want to connect to. Choose the username as GLOBAL. Once the database connection is established, click on the + sign before Schemas and expand all the elements. The result will be the screen shown below.

Figure 4

Let's try a common operation: refreshing the cube. Right-click on PRICE_CUBE on the left-hand panel. A pop-up menu comes up as shown below:

Figure 5

Choose Maintain Cube PRICE_CUBE from the menu. It bring up a small window shown below:

Figure 6

Click on Next several times and eventually you will see a screen similar to this:

Figure 7

Here you can decide to run the task immediately or schedule it for later. Click Finish to complete the task. Eventually you will see a confirmation screen as shown below:

Figure 8

Note the column named OUTPUT, which shows the SQL statement. Double-clicking the column shows the complete output. The output for the PRICE_CUBE is shown below:

<CommandOutput>
<CubeLoad
Name="MAP1"
SQL="SELECT /*+  bypass_recursive_check  cursor_sharing_exact  no_expand */ 
  T16_MONTH_ID ALIAS_114, 
  T13_ITEM_ID ALIAS_115, 
  SUM(T19_UNIT_PRICE)  ALIAS_116, 
  SUM(T19_UNIT_COST)  ALIAS_117 
FROM 
  (
  SELECT 
    T1.ITEM_ID T19_ITEM_ID, 
    T1.MONTH_ID T19_MONTH_ID, 
    T1.UNIT_PRICE T19_UNIT_PRICE, 
    T1.UNIT_COST T19_UNIT_COST 
  FROM 
    GLOBAL.PRICE_FACT T1   ) 
  T19, 
  (
  SELECT 
    T1.MONTH_ID T16_MONTH_ID 
  FROM 
    GLOBAL.TIME_DIM T1   ) 
  T16, 
  (
  SELECT 
    T1.ITEM_ID T13_ITEM_ID 
  FROM 
    GLOBAL.PRODUCT_DIM T1   ) 
  T13 
WHERE 
  ((T16_MONTH_ID = T19_MONTH_ID) 
    AND (T13_ITEM_ID = T19_ITEM_ID) 
    AND (T16_MONTH_ID = T19_MONTH_ID) 
    AND (T13_ITEM_ID = T19_ITEM_ID) )  
GROUP BY 
  (T13_ITEM_ID, T16_MONTH_ID)  
ORDER BY 
  T13_ITEM_ID ASC NULLS LAST , 
  T16_MONTH_ID ASC NULLS LAST "
LOADED="2523"
REJECTED="0"/>
</CommandOutput>


Another important task is making an MV out of a cube. Let's see how it is done for the UNITS_CUBE cube. Click on UNITS_CUBE, which brings up the details of the cube on the righthand panel. On the detail panel you will notice several tabs. Click on the tab Materialized Views, as shown below:

Figure 9

This panel shows the different checks to be done before the cube can be stored as an MV. For instance, the first line shows "User must have create Materialized View Privilege". This is self explanatory. The user must have the Create MV privilege to create an MV. This check was done and it was found that the user does have that privilege. The status is shown as a green tick mark, meaning the condition is satisfied.

Note the following in the upper left corner of the panel:

Figure 10

Check the box to enable MV style refresh of the cube. Then check the Enable Query Rewrite checkbox at the bottom. Next, press the Apply button. The cube is now ready as a Cube Organized MV.

Now that this cube is a regular MV, you can refresh it as such.

begin
   dbms_mview.refresh (
       list => 'CB$PRICE_CUBE',
       method => 'c'
   );
end;


You can also use the cube rebuilding approach:

begin
   dbms_cube.build('PRICE_CUBE');
end;


After populating (or rebuilding), you should collect stats:


begin
   dbms_aw_stats.analyze (
       'PRICE_CUBE'
   )
end;


As I said previously, AWs are stored as BLOBs in the database. In this example:

SQL> desc aw$global
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------
 PS#                                                NUMBER(10)
 GEN#                                               NUMBER(10)
 EXTNUM                                             NUMBER(8)
 AWLOB                                              BLOB
 OBJNAME                                            VARCHAR2(256)
 PARTNAME                                           VARCHAR2(256)


The BLOB column holds the data on the cube. So, the cube is stored in a relational table, in a tablespace, just like any other table. The space is managed in the tablespace just like any other tablespace so the DBAs needn't do anything special after the initial AW creation. Cube Organized MVs are just like any other MVs, so the refresh process is familar to most DBAs.

Other New Features Covered Elsewhere

There are several other new features in Oracle Database 11g that help in efficient design and operation of a data warehouse. These have already been covered in other installments of this series. Instead of repeating, I will give a very concise overview and direct you to those installments for details:

  • Partitioning. Oracle Database 11g allows a new type of range partitioning called Interval Partitioning, which allows you to merely define a value as an interval—for example, Oracle automatically creates partitions as needed every month based on the record getting inserted. Another type, Reference Partitioning, allows you to create partitions on the child tables based on a column that is present in parent table only, not in child tables. For instance, you can partition the EMP table based on department location, which is on the parent table (DEPT), but not in the EMP table. In addition, you can now create LIST-LIST, LIST-RANGE, and RANGE-RANGE composite partitioning schemes.

  • Virtual Columns. Furthermore, you can make a column "virtual"; that is, not stored in the table yet the user can access it like any other column. The column value is calculated during runtime. For instance, a virtual column called COMMISSION could be defined in the table that shows the commission as 20% of SALARY column. You can define indexes on this column and even partition on it; the database will also start collecting statistics on a virtual column, just like it does on regular ones. In a data warehouse, you can use a virtual column to partition a table to reflect real life instead of the real column, saving considerable space in the process. When you exchange partitions with another table, the exchanged table need not have the virtual column. How sweet is that? 

  • Advanced Compression. In addition to the compression functionality available in earlier releases, Oracle Database 11g now offers a new Advanced Compression option that performs compression differently. Instead of compressing the row as it comes, it compresses the entire block when a certain threshold is reached in the block. This makes the process extremely efficient and offers significant space advantages.

  • Pivot (and, of course, Unpivot). Crosstab reports are fairly common in any reporting environment, especially since the report format is the easiest to read and data to be presented. In earlier releases the crosstab reports could be done by joining the table to itself as many times as there are columns. This results in less performant queries, particularly in data warehouses where the tables are typically large. In Oracle Database 11g, a new operator called PIVOT transforms the output to be presented in a crosstab format. UNPIVOT allows the data already in the format to be reset back to a relational format.

  • Partition Advisor. One of the key points to consider in partitioning is which column to partition on. Many people, even seasoned professionals, stumble here. Oracle Database 11g makes this process easier by providing a new tool called Partition Advisor, which analyzes the workload pattern and offers performance-based suggestions on the partition scheme. While it may not be 100-percent perfect, it's definitely a boon for those who are completely unfamiliar with the process. And it's at least a starting point for those who are proficient.

  • Data Caching. When data does not change often, why keep refreshing it from disk? In Oracle Database 11g, you can cache the result of a query (not a table) in a special memory pool called the Result Cache. When the table data changes, the cache is automatically updated, sort of like a self-refreshed materialized view in memory. It's also possible to cache the results of a PL/SQL function in this result cache. Since the results come from memory instead of from disk, the response times are infinitesimally fast. In large databases such as data warehouses, where tables under most of the lookup queries do not change much, you can cache the queries and the function calls to get a boost in performance.

  • Invisible Indexes. Indexes can be made invisible, which makes them not used in access paths unless a special session parameter is set. However, the index is maintained as a normal index, affecting the performance of DMLs. This is a perfect way to examine the impact of an index without committing it. If performance is not desired, the index can be dropped.

Back to Series TOC