Carl's Oracle

At the current project developers use to identify the C# objects with the almost unique GUID. No sequences are used at all. As we know objects cannot live a longer period without persistence - the objects are not only referenced by GUID in memory but also in the database. With GUID an object can be referenced unique even between different databases. What's  exact is a GUID?

Now imagine in our application we have a company table. This business staff wants to query a lot of columns - ordered if possible. The table has 300,000 rows but only 10,000 of the rows are important for daily working the so called preferred companies. The response time should be < 2 seconds. Accessing a table of 300,000 rows and sorting the result set with response time > 2 seconds seems to an unsolvable challenge.
I thought that’s something which could be solved with Materialized View technology. First create a materialized View log on company – then create a materialized view on the subset of the preferred companies to do all queries with preferred companies against a smaller table. The Query rewriting is done in ‘Background’ you would see it only with an execution plan output. So let’s go to action!

Preparing Query Rewrite

Using Query Rewrite you need some basic settings:

• Oracle Server Enterprise Edition

• init.ora parameter QUERY_REWRITE_ENABLED  should be set to TRUE

• init.ora parameter QUERY_REWRITE_INTEGRITY should be set to TRUSTED/STALE TOLERATED

• The user needs QUERY REWRITE System Privilege and if he is not the owner of the objects underlying the Materialized View he needs GLOBAL QEUERY REWRITE system privilege

• CREATE MATERIALIZED VIEW system privileges

• and - a lot of patience till it works ;-)

 

For demonstration i used the confidential data of the DBA_OBJECTS. First a table is created with GUID as Object ID. How is the in oracle GUID generated? A SYS_GUID() function is provide which generates GUID’s like SYSDATE generates date values.

SQL> CREATE TABLE cre.MY_OBJECTS_GUID
  2  AS
  3  SELECT Owner,
  4         Object_Name,
  5         Subobject_Name,
  6         Sys_Guid() AS Obj_Id,
  7         Data_Object_Id,
  8         Object_Type,
  9         Created,
 10         Last_Ddl_Time,
 11         TIMESTAMP,
 12         Status,
 13         Temporary,
 14         Generated,
 15         Secondary
 16    FROM Sys.Dba_Objects
 17  ;

Tabelle wurde erstellt.

What datatype oracle could have generated for a GUID? Let’s describe the table.

SQL> desc  MY_OBJECTS_GUID

 Name                                      Null?    Typ

 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJ_ID                                             RAW(16)
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL>

Oracle stores the GUID in a binary 16 - byte array. A little bit disappointing – I would like to have a native type like timestamp or like the date type. Later you will see that the implementation as raw data type will have a lot of influence on the QUERY REWRITE capability of the materialized views on this table.

The table needs to be actual on all time. The refresh on commit materialized view option satisfies this demand. To be able to replicate the table’s data it needs a primary key and a materialized view log. The materialized view log is the change log for all DML changing the table’s data. Internalized Triggers keep the content of the materialized view log consistent. Please keep in mind : Once a materialized view log is created on a table all DML is slower due to the internal trigger.

SQL> alter table MY_OBJECTS_GUID
  2    add constraint MY_OBJECTS_GUID_PK primary key (OBJ_ID);
Tabelle wurde geõndert.

 

SQL> CREATE MATERIALIZED VIEW LOG ON MY_OBJECTS_GUID WITH PRIMARY KEY;
Log von Materialized View wurde erstellt.
SQL>

 
As the company table which its preferred rows only a subset of database objects are in our focus – the indexes. A query running against our MY_OBJECTS_GUID with the object type index should use the materialized view we define now.

SQL> CREATE MATERIALIZED VIEW cre.MY_OBJECTS_GUID_INDEX_MV
  2    TABLESPACE USERS
  3    REFRESH FAST ON COMMIT
  4    ENABLE QUERY REWRITE
  5  AS
  6  SELECT Object_Name,
  7         Subobject_Name,
  8         Obj_Id,
  9         Data_Object_Id,
 10         Object_Type,
 11         Created,
 12         Last_Ddl_Time,
 13         TIMESTAMP,
 14         Status,
 15         Temporary,
 16         Generated,
 17         Secondary
 18    FROM cre.MY_OBJECTS_GUID
 19   WHERE MY_OBJECTS_GUID.Object_Type = 'INDEX'
 20  ;

Index wurde erstellt.

 

QUERY REWRITE OR NOT QUERY REWRITE that’s the question :

 

Let’s take SQL*PUS with autotrace option to see if the MVIEW MY_OBJECTS_GUID_INDEX_MV is used.

 

SQL> set linesize 120
SQL> set autotrace traceonly
SQL> SELECT *
  2    FROM Cre.My_Objects_Guid t
  3   WHERE t.Object_Type = 'INDEX'
  4     AND t.Object_Name = 'WRH$_SQLSTAT_PK'
  5  ;

 

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 2100302959 
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |     1 |    98 |    43   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS_GUID |     1 |    98 |    43   (3)| 00:00:01 |
------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):
--------------------------------------------------- 

   1 - filter("T"."OBJECT_NAME"='WRH$_SQLSTAT_PK' AND
              "T"."OBJECT_TYPE"='INDEX')

 

No – did not work! I would expect the optimizer to use the materialized view which handles only index objects and then use the index on it. After changing/rewriting  the Statement it did not work either.

What the documentation says

The Datawarhousing guide list some restriction on  materialized views for query rewrite.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref480

One of the restrictions is the reference of a RAW typed column. And that’s the problem with the GUID : It’s implemented as RAW(16) and thus not a candidate for query rewrite technology.

 

Nerver give up

The dictionary view DBMS_MVIEWS shows not only the definition of a Materialzed view but also the query rewrite capability:

As you can see our Materialized View is Rewrite enabled and the Rewrite_Capability is TEXTMATCH.

The Rewrite_Capability has different levels :

So i tried again – text match should be able to find a rewrite possibility it my defining Query of the MV is found in a query text. :
Remember the defining Query text  for the Mview:

Did not work; Now what did TEXTMATCH_ realy mean. The exact Query. No change in the Where-clause, exact use <Table_Name>.<Column_Name> no ‘*’ in attribute clause. This finally worked. :

But this makes no sense because if this query is textually changed then the Query Rewrite Path is not found.

Rewrite Query for Query Rewrite

With one trick (putting this Query in from clause as temporary table) the Query Rewrite could be used even with additional predicates:

Conclusion

• Query Rewrite is an impoartant feature helping to keep performance in spite of business demands.
  Even other RDBMS like DB2 UDB support this important feature too.
  
• Using GUID's in your data model decreases the capability of the Query Rewrite Feature
• Some workaorund my be found to 'enable' Qeury Rewrite
• A GUID native Oracle Datatype would help and help the Windows OO Community to design their data model.
  

Karl