Sorry Snapshots are called MATERIALIZED VIEW nowadays:
-- From the Oracle Manual:
-- The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
The Performance Issue benefit of using MATERIALIZED VIEW is that you can SCHEDULE the refresh of your materialized VIEW.
IE you can run it at night.
Here's a simple complete example of using a MATERIALIZED VIEW:
First take care you have been granted to following system privilege:
SQL> CREATE ANY MATERIALIZED VIEW to <YOUR_USERNAME>;
Grant succeeded.
Now I create a demo table and I insert some test data:
(Make sure you have a primary key for the ORA-12014: table '' does not contain a primary key constraint)
[SYSTEM@DB1.BEN****:PCHQ-1093] CREATE TABLE DEMO(K NUMBER PRIMARY KEY);
Table created.
[SYSTEM@DB1.BEN****:PCHQ-1093] INSERT INTO DEMO VALUES(1);
1 row created.
[SYSTEM@DB1.BEN****:PCHQ-1093] INSERT INTO DEMO VALUES(2);
1 row created.
[SYSTEM@DB1.BEN****:PCHQ-1093] INSERT INTO DEMO VALUES(3);
1 row created.
[SYSTEM@DB1.BEN****:PCHQ-1093] INSERT INTO DEMO VALUES(4);
1 row created.
[SYSTEM@DB1.BEN****:PCHQ-1093] INSERT INTO DEMO VALUES(5);
1 row created.
[SYSTEM@DB1.BEN****:PCHQ-1093] COMMIT;
Commit complete.
Next I create the materialized view (my stored query), in my case it's a silly query but you can use your heavy query instead:
You need a materialized view log that Oracle uses to sync (refer the Oracle's Docs)
[SYSTEM@DB1.BEN****:PCHQ-1093] CREATE MATERIALIZED VIEW LOG ON DEMO WITH PRIMARY KEY;
Materialized view log created.
[SYSTEM@DB1.BEN****:PCHQ-1093] CREATE MATERIALIZED VIEW MV_DEMO
2 REFRESH FAST NEXT SYSDATE + 1
3 AS
4 SELECT
5 *
6 FROM
7 DEMO
8 WHERE
9 K > 4
10 ;
Materialized view created.
No I select from the MATERIALIZED VIEW :
[SYSTEM@DB1.BEN****:PCHQ-1093] SELECT * FROM MV_DEMO ;
K
----------
5
Below you can see the disadvatage, I insert into the master table demo and the materialized view doesn't display these new results until SYSDATE +1:
[SYSTEM@DB1.BEN****:PCHQ-1093] INSERT INTO DEMO VALUES(6);
1 row created.
[SYSTEM@DB1.BEN****:PCHQ-1093] COMMIT;
Commit complete.
[SYSTEM@DB1.BEN****:PCHQ-1093] SELECT * FROM MV_DEMO ;
K
----------
5
Hope this helps
Remi