11 July, 2012

ON COMMIT Refresh without a Primary Key


Today's question : How can you build an ON COMMIT Refresh Materialized View for a table that has no Primary Key ?
.
.
.

09 July, 2012

Materialized View Refresh ON COMMIT

A quick demonstration of a Materialized View that is refreshed ON COMMIT.

NOTE : This blog post has been updated after Yuri pointed out an error.

SQL> create table source_table
  2  as select rownum as source_pk, dbms_random.string('X',15) as col_2, sysdate-1000+rownum as col_3
  3  from dual connect by level < 1001
  4  /

Table created.

SQL> 
SQL> alter table source_table add constraint source_pk primary key (source_pk);

Table altered.

SQL> 
SQL> create materialized view log on source_table ;

Materialized view log created.

SQL> 
SQL> create materialized view my_mv refresh on commit
  2  as select  source_pk as key_col, col_3 as data_col from source_table
  3  /

Materialized view created.

SQL> 
SQL> select count(*) from source_table;

  COUNT(*)
----------
      1000

SQL> select count(*) from my_mv;

  COUNT(*)
----------
      1000

SQL> 
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> insert into source_table values (5001,'XXXXX',sysdate+50);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from my_mv where key_col=5001;

   KEY_COL DATA_COL
---------- ---------
      5001 03-OCT-12

SQL> 
.
The trace file shows, among other SQLs :

INSERT /*+ IDX(0) */ INTO "HEMANT"."MLOG$_SOURCE_TABLE" (dmltype$$,old_new$$,
  snaptime$$,change_vector$$,xid$$,"SOURCE_PK") 
VALUES
 (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          1          2         29           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          1          2         29           1

DELETE FROM "HEMANT"."MY_MV" SNAP$
WHERE
 "KEY_COL" IN (SELECT DISTINCT LOG$."KEY_COL" FROM (SELECT MLOG$."SOURCE_PK"
  "KEY_COL" FROM "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "XID$$" = :1
  AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$."KEY_COL") NOT IN (SELECT
  MAS_TAB$."SOURCE_PK" "KEY_COL" FROM "SOURCE_TABLE" "MAS_TAB$" WHERE
  LOG$."KEY_COL" = MAS_TAB$."SOURCE_PK"))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0         10          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0         10          0           0

MERGE INTO "HEMANT"."MY_MV" "SNA$" USING (SELECT CURRENT$."KEY_COL",
  CURRENT$."DATA_COL" FROM (SELECT "SOURCE_TABLE"."SOURCE_PK" "KEY_COL",
  "SOURCE_TABLE"."COL_3" "DATA_COL" FROM "SOURCE_TABLE" "SOURCE_TABLE")
  CURRENT$, (SELECT DISTINCT MLOG$."SOURCE_PK" "KEY_COL" FROM
  "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "XID$$" = :1      AND
  ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."KEY_COL" = LOG$."KEY_COL")"AV$"
  ON ("SNA$"."KEY_COL" = "AV$"."KEY_COL") WHEN MATCHED THEN UPDATE  SET
  "SNA$"."KEY_COL" = "AV$"."KEY_COL","SNA$"."DATA_COL" = "AV$"."DATA_COL"
  WHEN NOT MATCHED THEN INSERT  (SNA$."KEY_COL",SNA$."DATA_COL") VALUES
  (AV$."KEY_COL",AV$."DATA_COL")


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          1         33         26           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1         33         26           1

delete from "HEMANT"."MLOG$_SOURCE_TABLE"
where
 xid$$ = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          7          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          7          2           1

.

05 July, 2012

An Oracle Installer that automatically switches to Console mode

The WebLogic  installer can switch automatically to console mode.

UPDATE :  However, quickstart.sh still requires an X-display as I discovered after the install completed.

-sh-3.2$ echo $HOME
/newfs/wlogic
-sh-3.2$ ./wls1211_linux32.bin
Extracting 0%....................................................................................................100%
Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified

Unable to instantiate GUI, defaulting to console mode.





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Welcome:
--------

This installer will guide you through the installation of WebLogic 12.1.1.0. Type "Next" or enter 
to proceed to the next prompt.  If you want to change data entered previously, type "Previous".  
You may quit the installer at any time by typing "Exit".




Enter [Exit][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Choose Middleware Home Directory:
---------------------------------

    "Middleware Home" = [Enter new value or use default "/newfs/wlogic/Oracle/Middleware"]




Enter new Middleware Home OR [Exit][Previous][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

   1|Email:[]
   2|Support Password:[]
   3|Receive Security Update:[Yes]



Enter index number to select OR [Exit][Previous][Next]> 3





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

    "Receive Security Update:" = [Enter new value or use default "Yes"]



Enter [Yes][No]? No





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

    "Receive Security Update:" = [Enter new value or use default "Yes"]


    ** Do you wish to bypass initiation of the configuration manager and
    **  remain uninformed of critical security issues in your configuration?


Enter [Yes][No]? Yes





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

   1|Email:[]
   2|Support Password:[]
   3|Receive Security Update:[No]



Enter index number to select OR [Exit][Previous][Next]> 




<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

   1|Email:[]
   2|Support Password:[]
   3|Receive Security Update:[No]



Enter index number to select OR [Exit][Previous][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Choose Install Type:
--------------------

Select the type of installation you wish to perform. 

 ->1|Typical
    |  Install the following product(s) and component(s):
    | - WebLogic Server
    | - Oracle Coherence

   2|Custom
    |  Choose software products and components to install and perform optional configuration.





Enter index number to select OR [Exit][Previous][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Choose Product Installation Directories:
----------------------------------------

Middleware Home Directory: [/newfs/wlogic/Oracle/Middleware]

Product Installation Directories:


   1|WebLogic Server: [/newfs/wlogic/Oracle/Middleware/wlserver_12.1]
   2|Oracle Coherence: [/newfs/wlogic/Oracle/Middleware/coherence_3.7]




Enter index number to select OR [Exit][Previous][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

The following Products and JDKs will be installed:
--------------------------------------------------

    WebLogic Platform 12.1.1.0
    |_____WebLogic Server
    |    |_____Core Application Server
    |    |_____Administration Console
    |    |_____Configuration Wizard and Upgrade Framework
    |    |_____Web 2.0 HTTP Pub-Sub Server
    |    |_____WebLogic SCA
    |    |_____WebLogic JDBC Drivers
    |    |_____Third Party JDBC Drivers
    |    |_____WebLogic Server Clients
    |    |_____Xquery Support
    |    |_____Evaluation Database
    |_____Oracle Coherence
    |    |_____Coherence Product Files
    |_____JDKs
         |_____SUN SDK 1.6.0_29
         |_____Oracle JRockit 1.6.0_29 SDK

    *Estimated size of installation: 1,178.0 MB




Enter [Exit][Previous][Next]> 
Jul 5, 2012 3:23:23 AM java.util.prefs.FileSystemPreferences$2 run
INFO: Created user preferences directory.





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Installing files..

0%          25%          50%          75%          100%
[------------|------------|------------|------------]
[******************


Cool. Isn't it ?