Materialized views are a data warehousing/decision support system tool that can increase by many orders of magnitude the speed of queries that access a large number (maybe many hundreds of thousands or millions) of records. In basic terms, they allow a user to query potentially terabytes of detail data in seconds (or less). They accomplish this by transparently using pre-computed summarizations and joins of
data. These pre-computed summaries would typically be very small compared to the original source data.
Say, for example, your company has a sales database loaded with the details of a million orders, and you want to get a breakdown of sales by region (a common enough query). Each and every record would be scanned, the data aggregated to the region level, and the calculation performed. Using a materialized view, we can store a summary of the sales data by region, and have the system maintain these summaries
for us. If you have ten sales regions, this summary will have ten records, so instead of sifting through a million detail records, we’ll query only ten. Furthermore, if someone asks a slightly different question, say for the sales in a specific region, then that query can also obtain the answer from the materialized
view.
What you’ll need to run the Examples
In order to run the examples in this chapter you’ll need access to a Personal or Enterprise Edition of Oracle 8.1.5, or higher. This functionality is not provided in the Standard release. You will need a user account with the following privileges (at least):
❑ GRANT CREATE SESSION
❑ GRANT CREATE TABLE
❑ GRANT CREATE MATERIALIZED VIEW
❑ GRANT QUERY REWRITE
The first three privileges above may be granted to a role that you have been granted. The QUERY REWRITE privilege must be granted directly to you.
Additionally, you’ll need access to a tablespace with about 30 — 50MB of free space.
Finally, you must be using the Cost-Based Optimizer (CBO) in order to make use of query rewrite. If you do not use the CBO, query rewrite will not take place. In these examples, our optimizer goal will be left at the default of CHOOSE; simply analyzing the tables will ensure we can take advantage of query rewrite.
An Example
A quick example will demonstrate what a materialized view entails. The concept demonstrated below is that of reducing the execution time of a long running query transparently, by summarizing data in the database. A query against a large table will be transparently rewritten into a query against a very small table, without any loss of accuracy in the answer. We’ll start with a large table that contains a list of owners of objects, and the objects they own. This table is based on the ALL_OBJECTS data dictionary view:
SQL> create table my_all_objects
nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects
/
Table created.
SQL> insert /*+ APPEND */ into my_all_objects
select * from my_all_objects;
65742 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into my_all_objects
select * from my_all_objects;
131484 rows created.
SQL> commit;
Commit complete.
SQL> analyze table my_all_objects compute statistics;
Table analyzed
SQL> set timing on
SQL> select owner, count(*) from my_all_objects group by owner;
OWNER COUNT(*)
------------------------------ ----------
A 36
B 24
CTXSYS 2220
DBSNMP 48
DEMO 60
DEMO11 36
DEMO_DDL 108
MDSYS 2112
MV_USER 60
ORDPLUGINS 312
ORDSYS 2472
OUR_TYPES 12
OUTLN 60
PERFSTAT 636
PUBLIC 117972
SCHEDULER 36
SCOTT 84
SEAPARK 36
SYS 135648
SYSTEM 624
TESTING 276
TKYTE 12
TTS_USER 48
TYPES 36
24 rows selected.
Elapsed: 00:00:03.35
SQL> set timing off
SQL> set autotrace traceonly
SQL> select owner, count(*) from my_all_objects group by owner;
24 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2525 Card=24 Bytes=120)
1 0 SORT (GROUP BY) (Cost=2525 Card=24 Bytes=120)
2 1 TABLE ACCESS (FULL) OF ‘MY_ALL_OBJECTS’ (Cost=547 Card=262968
Statistics
----------------------------------------------------------
0 recursive calls
27 db block gets
3608 consistent gets
3516 physical reads
0 redo size
1483 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed
SQL> grant query rewrite to tkyte;
Grant succeeded.
SQL> alter session set query_rewrite_enabled=true;
Session altered.
SQL> alter session set query_rewrite_integrity=enforced;
Session altered.
SQL> create materialized view my_all_objects_aggs
build immediate
refresh complete on commit
enable query rewrite
as
select owner, count(*)
from my_all_objects
group by owner
/
Materialized view created.
sql> analyze table my_all_objects_aggs compute statistics;
Table analyzed.
Basically, what we’ve done is pre-calculate the object count, and define this summary information as a materialized view. We have asked that the view be immediately built and populated with data. You’ll notice that we have also specified REFRESH ON COMMIT and ENABLE QUERY REWRITE, but more on these in a moment. Also notice that we may have created a materialized view, but when we ANALYZE,
we are analyzing a table. A materialized view creates a real table, and this table may be indexed, analyzed, and so on.
First, let’s see the view in action by issuing the same query again (the query that we used to define the view itself):
SQL> set timing on
SQL> select owner, count(*)
from my_all_objects
group by owner;
OWNER COUNT(*)
------------------------------ ----------
A 36
B 24
...
TYPES 36
24 rows selected.
Elapsed: 00:00:00.10
sql> set timing off
sql> set autotrace traceonly
sql> select owner, count(*)
from my_all_objects
group by owner;
rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=24 Bytes=216)
1 0 TABLE ACCESS (FULL) OF ‘MY_ALL_OBJECTS_AGGS’ (Cost=1 Card=Valve)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
7 consistent gets
0 physical reads
0 redo size
1483 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
sql> set autotrace off
From over 3,600 consistent gets (logical I/Os), to just 12. No physical I/O this time around as the data was found in the cache. Our buffer cache will be much more efficient now as it has less to cache. I could not even begin to cache the previous query’s working set, but now I can. Notice how our query plan shows we are now doing a full scan of the MY_ALL_OBJECTS_AGGS table, even though we queried the detail table MY_ALL_OBJECTS. When the SELECT OWNER, COUNT(*)... query is issued, the database automatically directs it to our materialized view.
Let’s take this a step further by adding a new row to the MY_ALL_OBJECTS table, and committing the change:
SQL> insert into my_all_objects
( owner, object_name, object_type, object_id )
values
( ‘New Owner’, ‘New Name’, ‘New Type’, 1111111 );
1 row created.
SQL> commit;
Commit complete.
Now, we issue effectively the same query again, but this time we’re just looking at our newly inserted row:
SQL> set timing on
SQL> select owner, count(*)
from my_all_objects
where owner = ‘New Owner’
group by owner;
OWNER COUNT(*)
------------------------------ ----------
New Owner 1
Elapsed: 00:00:00.01
sql> set timing off
sql> set autotrace traceonly
sql> select owner, count(*)
from my_all_objects
where owner = ‘New Owner’
group by owner;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
1 0 TABLE ACCESS (FULL) OF ‘MY_ALL_OBJECTS_AGGS’ (Cost=1 Card=Valve)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
6 consistent gets
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
The analysis shows that we scanned the materialized view and found the new row. By specifying REFRESH ON COMMIT in our original definition of the view, we requested that Oracle maintain synchronization between the view and the details – when we update the details, the summary will be maintained as well. It cannot maintain synchronization in every case of an arbitrary materialized view, but in the case of a single table summary (as we have) or joins with no aggregation, it can.
Now, one last query:
sql> set timing on
sql> select count(*)
2 from my_all_objects
3 where owner = ‘New Owner’;
COUNT(*)
----------
1
Elapsed: 00:00:00.00
sql> set timing off
sql> set autotrace traceonly
sql> select count(*)
from my_all_objects
where owner = ‘New Owner’;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF ‘MY_ALL_OBJECTS_AGGS’ (Cost=1 Card=Valve)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
5 consistent gets
0 physical reads
0 redo size
367 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sql> set autotrace off
We can see that Oracle is smart enough to use the view even when the query appears to be slightly different. There was no GROUP BY clause here, yet the database recognized that the materialized view could still be used. This is what makes materialized views ‘magical’. The end users do not have to be aware of these summary tables. The database will realize for us that the answer already exists and, as
long as we enable query rewrite (which we did), will automatically rewrite the query to use them. This feature allows you to immediately impact existing applications, without changing a single query.
Uses of Materialized Views
This is relatively straightforward and is answered in a single word – performance. By calculating the answers to the really hard questions up front (and once only), we will greatly reduce the load on our machine. We will experience:
❑ Less physical reads — There is less data to scan through.
❑ Less writes — We will not be sorting/aggregating as frequently.
❑ Decreased CPU consumption — We will not be calculating aggregates and functions on the data, as we will have already done that.
❑ Markedly faster response times — Our queries will return incredibly quickly when a summary is used, as opposed to the details. This will be a function of the amount of work we can avoid by using the materialized view, but many orders of magnitude is
not out of the question.
Setting Up
There is one mandatory INIT.ORA parameter necessary for materialized views to function, and this is the COMPATIBLE parameter. The value of COMPATIBLE should be set to 8.1.0, or above, in order for query rewrites to be functional. If this value is not set appropriately, query rewrite will not be invoked. There are two other relevant parameters that may be set at either the system-level (via the INIT.ORA
file), or the session-level (via the ALTER SESSION command). They are:
❑ QUERY_REWRITE_ENABLED – Unless the value of this parameter is set to TRUE, query rewrites will not take place. The default value is FALSE.
❑ QUERY_REWRITE_INTEGRITY – This parameter controls how Oracle rewrites queries and may be set to one of three values:
ENFORCED – Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle. There are mechanisms by which we can tell Oracle about other inferred relationships, and this would allow for more queries to be rewritten, but since Oracle does not enforce those relationships, it would not make use of these facts at this level.
TRUSTED – Queries will be rewritten using the constraints that are enforced by Oracle, as well as any relationships existing in the data that we have told Oracle about, but are not enforced by the database. For example, in our initial example we could have created the physical table MY_ALL_OBJECTS_AGGS manually using a parallel, no-logging CREATE TABLE AS SELECT (to speed up the building of the summary table). We could have then created the materialized view, instructing it to use this pre-built table instead of creating the summary table itself. If we wish Oracle to use this pre-built table during a subsequent query rewrite, we must specify a
value of TRUSTED. This is because we want Oracle to ‘trust’ that we have supplied the correct data in the pre-built table – Oracle does not enforce that the data in this table is correct.
STALE_TOLERATED – Queries will be rewritten to use materialized views even if Oracle knows the data contained in the materialized view is ‘stale’ (out-of-sync with the details). This might be useful in an environment where the summary tables are refreshed on a recurring basis, not on commit, and a slightly out-of-sync answer is acceptable.
Query Rewrite
When query rewrite is enabled, Oracle will use the following steps to try and rewrite a query with a materialized view.
Full Exact Text Match
In this method, Oracle considers possible exact string matches in the set of available materialized views found in the data dictionary. In the example above, this is the method Oracle would have used for the very first query that used the materialized view. The algorithm used is ‘friendlier’ (more flexible) than a
shared pool comparison (which demands an exact byte-for-byte match) as it ignores whitespace, case of characters, and other formatting.
Partial Text Match
Starting with the FROM clause, the optimizer compares the remaining text of the materialized view’s defining query. This allows for mismatches between items in the SELECT list. If the data you need can be generated from the materialized view (if your SELECT list can be satisfied) Oracle will rewrite the query using the materialized view. The query SELECT LOWER(OWNER) FROM MY_ALL_OBJECTS GROUP
BY OWNER; would be an example of a partial text match.
General Query Rewrite Methods
These enable the use of a materialized view even if it contains only part of the data, more data than requested, or data that can be converted. The optimizer tests the materialized view’s definition against the individual components of the query (SELECT, FROM, WHERE, GROUP BY) to find a match. The checks that Oracle performs against these components are:
❑ Data sufficiency – Can the required data be obtained from a given materialized view? If you ask for column X, and column X is not in the materialized view and, furthermore, it is notretrievable via some join with the materialized view, then Oracle will not rewrite the query to use that view. For example, the query SELECT DISTINCT OWNER FROM MY_ALL_OBJECTS, using our previous example, can be rewritten using our materialized view – the OWNER column is available. The query SELECT DISTINCT OBJECT_TYPE FROM MY_ALL_OBJECTS cannot be satisfied using the materialized view, as the view does not have sufficient data.
❑ Join compatibility – Ensures that any JOIN required by the submitted query can be satisfied by the materialized view.
Estimating Size
The ESTIMATE_SUMMARY_SIZE routine will report the estimated number of rows and bytes of storage that the materialized view will consume. Since hindsight is 20/20, we can ask DBMS_OLAP to estimate a figure and then compare that figure to what we get in reality.
In order to run this procedure, you will need to ensure that you have a PLAN_TABLE installed in your schema. You will find the CREATE TABLE statement in the [ORACLE_HOME]/rdbms/admin directory on your database server, in a file named utlxplan.sql. If you execute this script, it will create the
PLAN_TABLE for you. This table is used by the EXPLAIN PLAN facility that in turn is used by DBMS_OLAP to estimate the size of the materialized view. With this table in place, we can use the built-in ESTIMATE_SUMMARY_SIZE routine to get an estimate of the number of rows/bytes a materialized view would need if we were to build it. I start with a DELETE STATISTICS on our SALES_MV materialized view. DBMS_OLAP would not normally have access to a materialized view to actually see what the sizes
are, so we have to hide it (otherwise DBMS_OLAP will get the exact answer from the data dictionary):
sql> analyze table sales_mv DELETE statistics;
Table analyzed.
sql> declare
num_rows number;
num_bytes number;
begin
dbms_olap.estimate_summary_size
( ‘SALES_MV_ESTIMATE’,
‘select customer_hierarchy.zip_code,
time_hierarchy.mmyyyy,
sum(sales.sales_amount) sales_amount
from sales, time_hierarchy, customer_hierarchy
where sales.trans_date = time_hierarchy.day
and sales.cust_id = customer_hierarchy.cust_id
group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy’,
num_rows,
num_bytes );
dbms_output.put_line( num_rows || ‘ rows’ );
dbms_output.put_line( num_bytes || ‘ bytes’ );
end;
/
409 rows
36401 bytes
PL/SQL procedure successfully completed.
Materialized Views are Not Designed for OLTP Systems
As mentioned above, materialized views typically add overhead to individual transactions and, if created with REFRESH ON COMMIT, will introduce contention. The overhead arises from the need to track the changes made by a transaction – these changes will either be maintained in the session state or in log tables. In a high-end OLTP system, this overhead is not desirable. The concurrency issue comes into
play with a REFRESH ON COMMIT materialized view, due to the fact that many rows in the detail fact table point to a single row in a summary table. An update to any one of perhaps thousands of records, will need to modify a single row in the summary. This will naturally inhibit concurrency in a high update situation.
This does not preclude the use of materialized views with OLTP, in particular materialized views that are REFRESHed ON DEMAND, with a full refresh. A full refresh does not add the overhead of tracking transaction-level changes. Rather, at some point in time, the defining query for the materialized view is executed, and the results simply replace the existing materialized view. Since this is done on demand (or on a timed basis), the refresh may be scheduled for a time when the load is light. The resulting materialized view is especially relevant for reporting purposes – your OLTP data can be transformed using SQL into something that is easy and fast to query, every night. The next day, your online reports of yesterday’s activities run as fast as possible, and easily co-exist with your OLTP system
Query Rewrite Integrity
As we discussed above – this has three modes:
❑ ENFORCED – Will only use a materialized view if there is no chance of getting incorrect or stale data.
❑ TRUSTED – Oracle will use a materialized view even if some of the constraints it is relying on are something Oracle did not validate or enforce. This is typical in a data warehouse environment, where many constraints may be present but have not been enforced by Oracle.
❑ STALE_TOLERATED – Oracle will use a materialized view even if it knows the data it is derived from has changed. This is typical in a reporting environment such as described in the preceding caveat.
Below are some examples:-
SQL> create table emp (empno number primary key, ename varchar2(30), salary number);
Table created.
SQL> show parameter query;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
ENAME VARCHAR2(30)
SALARY NUMBER
SQL> create materialized view mv_emp_full
2 build immediate
3 refresh complete on commit
4 enable query rewrite as
5 select * from emp where salary between 20000 and 50000;
Materialized view created.
SQL> select * from mv_emp_full;
no rows selected
SQL> insert into emp values (1,'Manzoor',38000);
1 row created.
SQL> select * from mv_emp_full;
no rows selected
SQL> commit;
Commit complete.
SQL> select * from mv_emp_full;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
SQL> create materialized view mv_emp_full_demand
2 build immediate
3 refresh complete on demand
4 enable query rewrite as
5 select * from emp where salary between 20000 and 50000;
Materialized view created.
SQL> select * from mv_emp_full_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
SQL> insert into emp values (2,'Jameel',30000);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mv_emp_full_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
SQL> begin
2 dbms_mview.refresh('MV_EMP_FULL_DEMAND','C');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from mv_emp_full_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
SQL> create materialized view log on emp;
Materialized view log created.
SQL> create materialized view mv_emp_fast_commit
2 build immediate
3 refresh fast on commit
4 enable query rewrite
5 as
6 select * from emp where salary between 20000 and 50000;
Materialized view created.
SQL> select * from mv_emp_fast_commit;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
SQL> insert into emp values (3,'Ramiz',35000);
1 row created.
SQL> select * from mv_emp_fast_commit;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
SQL> commit;
Commit complete.
SQL> select * from mv_emp_fast_commit;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
SQL> create materialized view mv_emp_fast_demand
2 build immediate
3 refresh fast on demand
4 enable query rewrite
5 as
6 select * from emp where salary between 20000 and 50000;
Materialized view created.
SQL> select * from mv_emp_fast_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
SQL> insert into emp values (4,'Anees',45000);
1 row created.
SQL> select * from mv_emp_fast_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
SQL> commit;
Commit complete.
SQL> select * from mv_emp_fast_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
SQL> begin
2 dbms_mview.refresh('MV_EMP_FAST_DEMAND','F');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from mv_emp_fast_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
4 Anees 45000
SQL> spool off;
SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_EMP_FAST_DEMAND','MANZOOR') FROM DUAL;
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_EMP_FAST_DEMAND','MANZOOR')
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "MANZOOR"."MV_EMP_FAST_DEMAND"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MANZOOR"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_EMP_FAST_DEMAND','MANZOOR')
--------------------------------------------------------------------------------
TABLESPACE "MANZOOR"
REFRESH FAST ON DEMAND
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
ENABLE QUERY REWRITE
AS SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."SALARY" "SALARY"
FROM "EMP" "EMP" WHERE "EMP"."SALARY">=20000 AND "EMP"."SALARY"<=50000
How to schedule the Materialized views to Refresh
begin
dbms_scheduler.create_job
(
job_name => 'REFRESH_MVIEW',
job_type => 'PLSQL_BLOCK',
job_action => 'Begin dbms_mview.refresh(''EMP_COMPLETE_DEMAND'',''C''); end;',
number_of_arguments => 0,
start_date => systimestamp,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Job to Refresh emp_complete_demand mview'
);
end;
/
SQL> select job_name, state, last_run_duration, next_run_date from dba_scheduler_jobs where job_name = 'REFRESH_MVIEW';
JOB_NAME STATE LAST_RUN_DURATION
------------------------------ --------------- ---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
REFRESH_MVIEW SCHEDULED
21-OCT-10 02.00.41.500000 PM +01:00
SQL> select * from emp;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
3 Manzoor 30000
1 Kannan 43000
4 Kannan 30000
2 Kannan 40000
5 Prasana 50000
6 Karthik 45000
SQL> select * from emp_complete_Demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
3 Manzoor 30000
4 Kannan 30000
2 Kannan 40000
SQL> update emp set salary = 35000;
6 rows updated.
SQL> commit;
Commit complete.
SQL> select * from emp_complete_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
3 Manzoor 30000
4 Kannan 30000
2 Kannan 40000
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
21-OCT-10 01.05.44.137000 PM +01:00
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
21-OCT-10 02.31.47.116000 PM +01:00
SQL> select * from emp_complete_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
3 Manzoor 35000
1 Kannan 35000
4 Kannan 35000
2 Kannan 35000
5 Prasana 35000
6 Karthik 35000
6 rows selected.
data. These pre-computed summaries would typically be very small compared to the original source data.
Say, for example, your company has a sales database loaded with the details of a million orders, and you want to get a breakdown of sales by region (a common enough query). Each and every record would be scanned, the data aggregated to the region level, and the calculation performed. Using a materialized view, we can store a summary of the sales data by region, and have the system maintain these summaries
for us. If you have ten sales regions, this summary will have ten records, so instead of sifting through a million detail records, we’ll query only ten. Furthermore, if someone asks a slightly different question, say for the sales in a specific region, then that query can also obtain the answer from the materialized
view.
What you’ll need to run the Examples
In order to run the examples in this chapter you’ll need access to a Personal or Enterprise Edition of Oracle 8.1.5, or higher. This functionality is not provided in the Standard release. You will need a user account with the following privileges (at least):
❑ GRANT CREATE SESSION
❑ GRANT CREATE TABLE
❑ GRANT CREATE MATERIALIZED VIEW
❑ GRANT QUERY REWRITE
The first three privileges above may be granted to a role that you have been granted. The QUERY REWRITE privilege must be granted directly to you.
Additionally, you’ll need access to a tablespace with about 30 — 50MB of free space.
Finally, you must be using the Cost-Based Optimizer (CBO) in order to make use of query rewrite. If you do not use the CBO, query rewrite will not take place. In these examples, our optimizer goal will be left at the default of CHOOSE; simply analyzing the tables will ensure we can take advantage of query rewrite.
An Example
A quick example will demonstrate what a materialized view entails. The concept demonstrated below is that of reducing the execution time of a long running query transparently, by summarizing data in the database. A query against a large table will be transparently rewritten into a query against a very small table, without any loss of accuracy in the answer. We’ll start with a large table that contains a list of owners of objects, and the objects they own. This table is based on the ALL_OBJECTS data dictionary view:
SQL> create table my_all_objects
nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects
/
Table created.
SQL> insert /*+ APPEND */ into my_all_objects
select * from my_all_objects;
65742 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into my_all_objects
select * from my_all_objects;
131484 rows created.
SQL> commit;
Commit complete.
SQL> analyze table my_all_objects compute statistics;
Table analyzed
SQL> set timing on
SQL> select owner, count(*) from my_all_objects group by owner;
OWNER COUNT(*)
------------------------------ ----------
A 36
B 24
CTXSYS 2220
DBSNMP 48
DEMO 60
DEMO11 36
DEMO_DDL 108
MDSYS 2112
MV_USER 60
ORDPLUGINS 312
ORDSYS 2472
OUR_TYPES 12
OUTLN 60
PERFSTAT 636
PUBLIC 117972
SCHEDULER 36
SCOTT 84
SEAPARK 36
SYS 135648
SYSTEM 624
TESTING 276
TKYTE 12
TTS_USER 48
TYPES 36
24 rows selected.
Elapsed: 00:00:03.35
SQL> set timing off
SQL> set autotrace traceonly
SQL> select owner, count(*) from my_all_objects group by owner;
24 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2525 Card=24 Bytes=120)
1 0 SORT (GROUP BY) (Cost=2525 Card=24 Bytes=120)
2 1 TABLE ACCESS (FULL) OF ‘MY_ALL_OBJECTS’ (Cost=547 Card=262968
Statistics
----------------------------------------------------------
0 recursive calls
27 db block gets
3608 consistent gets
3516 physical reads
0 redo size
1483 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed
SQL> grant query rewrite to tkyte;
Grant succeeded.
SQL> alter session set query_rewrite_enabled=true;
Session altered.
SQL> alter session set query_rewrite_integrity=enforced;
Session altered.
SQL> create materialized view my_all_objects_aggs
build immediate
refresh complete on commit
enable query rewrite
as
select owner, count(*)
from my_all_objects
group by owner
/
Materialized view created.
sql> analyze table my_all_objects_aggs compute statistics;
Table analyzed.
Basically, what we’ve done is pre-calculate the object count, and define this summary information as a materialized view. We have asked that the view be immediately built and populated with data. You’ll notice that we have also specified REFRESH ON COMMIT and ENABLE QUERY REWRITE, but more on these in a moment. Also notice that we may have created a materialized view, but when we ANALYZE,
we are analyzing a table. A materialized view creates a real table, and this table may be indexed, analyzed, and so on.
First, let’s see the view in action by issuing the same query again (the query that we used to define the view itself):
SQL> set timing on
SQL> select owner, count(*)
from my_all_objects
group by owner;
OWNER COUNT(*)
------------------------------ ----------
A 36
B 24
...
TYPES 36
24 rows selected.
Elapsed: 00:00:00.10
sql> set timing off
sql> set autotrace traceonly
sql> select owner, count(*)
from my_all_objects
group by owner;
rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=24 Bytes=216)
1 0 TABLE ACCESS (FULL) OF ‘MY_ALL_OBJECTS_AGGS’ (Cost=1 Card=Valve)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
7 consistent gets
0 physical reads
0 redo size
1483 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
sql> set autotrace off
From over 3,600 consistent gets (logical I/Os), to just 12. No physical I/O this time around as the data was found in the cache. Our buffer cache will be much more efficient now as it has less to cache. I could not even begin to cache the previous query’s working set, but now I can. Notice how our query plan shows we are now doing a full scan of the MY_ALL_OBJECTS_AGGS table, even though we queried the detail table MY_ALL_OBJECTS. When the SELECT OWNER, COUNT(*)... query is issued, the database automatically directs it to our materialized view.
Let’s take this a step further by adding a new row to the MY_ALL_OBJECTS table, and committing the change:
SQL> insert into my_all_objects
( owner, object_name, object_type, object_id )
values
( ‘New Owner’, ‘New Name’, ‘New Type’, 1111111 );
1 row created.
SQL> commit;
Commit complete.
Now, we issue effectively the same query again, but this time we’re just looking at our newly inserted row:
SQL> set timing on
SQL> select owner, count(*)
from my_all_objects
where owner = ‘New Owner’
group by owner;
OWNER COUNT(*)
------------------------------ ----------
New Owner 1
Elapsed: 00:00:00.01
sql> set timing off
sql> set autotrace traceonly
sql> select owner, count(*)
from my_all_objects
where owner = ‘New Owner’
group by owner;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
1 0 TABLE ACCESS (FULL) OF ‘MY_ALL_OBJECTS_AGGS’ (Cost=1 Card=Valve)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
6 consistent gets
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
The analysis shows that we scanned the materialized view and found the new row. By specifying REFRESH ON COMMIT in our original definition of the view, we requested that Oracle maintain synchronization between the view and the details – when we update the details, the summary will be maintained as well. It cannot maintain synchronization in every case of an arbitrary materialized view, but in the case of a single table summary (as we have) or joins with no aggregation, it can.
Now, one last query:
sql> set timing on
sql> select count(*)
2 from my_all_objects
3 where owner = ‘New Owner’;
COUNT(*)
----------
1
Elapsed: 00:00:00.00
sql> set timing off
sql> set autotrace traceonly
sql> select count(*)
from my_all_objects
where owner = ‘New Owner’;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF ‘MY_ALL_OBJECTS_AGGS’ (Cost=1 Card=Valve)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
5 consistent gets
0 physical reads
0 redo size
367 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sql> set autotrace off
We can see that Oracle is smart enough to use the view even when the query appears to be slightly different. There was no GROUP BY clause here, yet the database recognized that the materialized view could still be used. This is what makes materialized views ‘magical’. The end users do not have to be aware of these summary tables. The database will realize for us that the answer already exists and, as
long as we enable query rewrite (which we did), will automatically rewrite the query to use them. This feature allows you to immediately impact existing applications, without changing a single query.
Uses of Materialized Views
This is relatively straightforward and is answered in a single word – performance. By calculating the answers to the really hard questions up front (and once only), we will greatly reduce the load on our machine. We will experience:
❑ Less physical reads — There is less data to scan through.
❑ Less writes — We will not be sorting/aggregating as frequently.
❑ Decreased CPU consumption — We will not be calculating aggregates and functions on the data, as we will have already done that.
❑ Markedly faster response times — Our queries will return incredibly quickly when a summary is used, as opposed to the details. This will be a function of the amount of work we can avoid by using the materialized view, but many orders of magnitude is
not out of the question.
Setting Up
There is one mandatory INIT.ORA parameter necessary for materialized views to function, and this is the COMPATIBLE parameter. The value of COMPATIBLE should be set to 8.1.0, or above, in order for query rewrites to be functional. If this value is not set appropriately, query rewrite will not be invoked. There are two other relevant parameters that may be set at either the system-level (via the INIT.ORA
file), or the session-level (via the ALTER SESSION command). They are:
❑ QUERY_REWRITE_ENABLED – Unless the value of this parameter is set to TRUE, query rewrites will not take place. The default value is FALSE.
❑ QUERY_REWRITE_INTEGRITY – This parameter controls how Oracle rewrites queries and may be set to one of three values:
ENFORCED – Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle. There are mechanisms by which we can tell Oracle about other inferred relationships, and this would allow for more queries to be rewritten, but since Oracle does not enforce those relationships, it would not make use of these facts at this level.
TRUSTED – Queries will be rewritten using the constraints that are enforced by Oracle, as well as any relationships existing in the data that we have told Oracle about, but are not enforced by the database. For example, in our initial example we could have created the physical table MY_ALL_OBJECTS_AGGS manually using a parallel, no-logging CREATE TABLE AS SELECT (to speed up the building of the summary table). We could have then created the materialized view, instructing it to use this pre-built table instead of creating the summary table itself. If we wish Oracle to use this pre-built table during a subsequent query rewrite, we must specify a
value of TRUSTED. This is because we want Oracle to ‘trust’ that we have supplied the correct data in the pre-built table – Oracle does not enforce that the data in this table is correct.
STALE_TOLERATED – Queries will be rewritten to use materialized views even if Oracle knows the data contained in the materialized view is ‘stale’ (out-of-sync with the details). This might be useful in an environment where the summary tables are refreshed on a recurring basis, not on commit, and a slightly out-of-sync answer is acceptable.
Query Rewrite
When query rewrite is enabled, Oracle will use the following steps to try and rewrite a query with a materialized view.
Full Exact Text Match
In this method, Oracle considers possible exact string matches in the set of available materialized views found in the data dictionary. In the example above, this is the method Oracle would have used for the very first query that used the materialized view. The algorithm used is ‘friendlier’ (more flexible) than a
shared pool comparison (which demands an exact byte-for-byte match) as it ignores whitespace, case of characters, and other formatting.
Partial Text Match
Starting with the FROM clause, the optimizer compares the remaining text of the materialized view’s defining query. This allows for mismatches between items in the SELECT list. If the data you need can be generated from the materialized view (if your SELECT list can be satisfied) Oracle will rewrite the query using the materialized view. The query SELECT LOWER(OWNER) FROM MY_ALL_OBJECTS GROUP
BY OWNER; would be an example of a partial text match.
General Query Rewrite Methods
These enable the use of a materialized view even if it contains only part of the data, more data than requested, or data that can be converted. The optimizer tests the materialized view’s definition against the individual components of the query (SELECT, FROM, WHERE, GROUP BY) to find a match. The checks that Oracle performs against these components are:
❑ Data sufficiency – Can the required data be obtained from a given materialized view? If you ask for column X, and column X is not in the materialized view and, furthermore, it is notretrievable via some join with the materialized view, then Oracle will not rewrite the query to use that view. For example, the query SELECT DISTINCT OWNER FROM MY_ALL_OBJECTS, using our previous example, can be rewritten using our materialized view – the OWNER column is available. The query SELECT DISTINCT OBJECT_TYPE FROM MY_ALL_OBJECTS cannot be satisfied using the materialized view, as the view does not have sufficient data.
❑ Join compatibility – Ensures that any JOIN required by the submitted query can be satisfied by the materialized view.
Estimating Size
The ESTIMATE_SUMMARY_SIZE routine will report the estimated number of rows and bytes of storage that the materialized view will consume. Since hindsight is 20/20, we can ask DBMS_OLAP to estimate a figure and then compare that figure to what we get in reality.
In order to run this procedure, you will need to ensure that you have a PLAN_TABLE installed in your schema. You will find the CREATE TABLE statement in the [ORACLE_HOME]/rdbms/admin directory on your database server, in a file named utlxplan.sql. If you execute this script, it will create the
PLAN_TABLE for you. This table is used by the EXPLAIN PLAN facility that in turn is used by DBMS_OLAP to estimate the size of the materialized view. With this table in place, we can use the built-in ESTIMATE_SUMMARY_SIZE routine to get an estimate of the number of rows/bytes a materialized view would need if we were to build it. I start with a DELETE STATISTICS on our SALES_MV materialized view. DBMS_OLAP would not normally have access to a materialized view to actually see what the sizes
are, so we have to hide it (otherwise DBMS_OLAP will get the exact answer from the data dictionary):
sql> analyze table sales_mv DELETE statistics;
Table analyzed.
sql> declare
num_rows number;
num_bytes number;
begin
dbms_olap.estimate_summary_size
( ‘SALES_MV_ESTIMATE’,
‘select customer_hierarchy.zip_code,
time_hierarchy.mmyyyy,
sum(sales.sales_amount) sales_amount
from sales, time_hierarchy, customer_hierarchy
where sales.trans_date = time_hierarchy.day
and sales.cust_id = customer_hierarchy.cust_id
group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy’,
num_rows,
num_bytes );
dbms_output.put_line( num_rows || ‘ rows’ );
dbms_output.put_line( num_bytes || ‘ bytes’ );
end;
/
409 rows
36401 bytes
PL/SQL procedure successfully completed.
Materialized Views are Not Designed for OLTP Systems
As mentioned above, materialized views typically add overhead to individual transactions and, if created with REFRESH ON COMMIT, will introduce contention. The overhead arises from the need to track the changes made by a transaction – these changes will either be maintained in the session state or in log tables. In a high-end OLTP system, this overhead is not desirable. The concurrency issue comes into
play with a REFRESH ON COMMIT materialized view, due to the fact that many rows in the detail fact table point to a single row in a summary table. An update to any one of perhaps thousands of records, will need to modify a single row in the summary. This will naturally inhibit concurrency in a high update situation.
This does not preclude the use of materialized views with OLTP, in particular materialized views that are REFRESHed ON DEMAND, with a full refresh. A full refresh does not add the overhead of tracking transaction-level changes. Rather, at some point in time, the defining query for the materialized view is executed, and the results simply replace the existing materialized view. Since this is done on demand (or on a timed basis), the refresh may be scheduled for a time when the load is light. The resulting materialized view is especially relevant for reporting purposes – your OLTP data can be transformed using SQL into something that is easy and fast to query, every night. The next day, your online reports of yesterday’s activities run as fast as possible, and easily co-exist with your OLTP system
Query Rewrite Integrity
As we discussed above – this has three modes:
❑ ENFORCED – Will only use a materialized view if there is no chance of getting incorrect or stale data.
❑ TRUSTED – Oracle will use a materialized view even if some of the constraints it is relying on are something Oracle did not validate or enforce. This is typical in a data warehouse environment, where many constraints may be present but have not been enforced by Oracle.
❑ STALE_TOLERATED – Oracle will use a materialized view even if it knows the data it is derived from has changed. This is typical in a reporting environment such as described in the preceding caveat.
Below are some examples:-
SQL> create table emp (empno number primary key, ename varchar2(30), salary number);
Table created.
SQL> show parameter query;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
ENAME VARCHAR2(30)
SALARY NUMBER
SQL> create materialized view mv_emp_full
2 build immediate
3 refresh complete on commit
4 enable query rewrite as
5 select * from emp where salary between 20000 and 50000;
Materialized view created.
SQL> select * from mv_emp_full;
no rows selected
SQL> insert into emp values (1,'Manzoor',38000);
1 row created.
SQL> select * from mv_emp_full;
no rows selected
SQL> commit;
Commit complete.
SQL> select * from mv_emp_full;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
SQL> create materialized view mv_emp_full_demand
2 build immediate
3 refresh complete on demand
4 enable query rewrite as
5 select * from emp where salary between 20000 and 50000;
Materialized view created.
SQL> select * from mv_emp_full_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
SQL> insert into emp values (2,'Jameel',30000);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mv_emp_full_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
SQL> begin
2 dbms_mview.refresh('MV_EMP_FULL_DEMAND','C');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from mv_emp_full_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
SQL> create materialized view log on emp;
Materialized view log created.
SQL> create materialized view mv_emp_fast_commit
2 build immediate
3 refresh fast on commit
4 enable query rewrite
5 as
6 select * from emp where salary between 20000 and 50000;
Materialized view created.
SQL> select * from mv_emp_fast_commit;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
SQL> insert into emp values (3,'Ramiz',35000);
1 row created.
SQL> select * from mv_emp_fast_commit;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
SQL> commit;
Commit complete.
SQL> select * from mv_emp_fast_commit;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
SQL> create materialized view mv_emp_fast_demand
2 build immediate
3 refresh fast on demand
4 enable query rewrite
5 as
6 select * from emp where salary between 20000 and 50000;
Materialized view created.
SQL> select * from mv_emp_fast_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
SQL> insert into emp values (4,'Anees',45000);
1 row created.
SQL> select * from mv_emp_fast_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
SQL> commit;
Commit complete.
SQL> select * from mv_emp_fast_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
SQL> begin
2 dbms_mview.refresh('MV_EMP_FAST_DEMAND','F');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from mv_emp_fast_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
1 Manzoor 38000
2 Jameel 30000
3 Ramiz 35000
4 Anees 45000
SQL> spool off;
SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_EMP_FAST_DEMAND','MANZOOR') FROM DUAL;
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_EMP_FAST_DEMAND','MANZOOR')
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "MANZOOR"."MV_EMP_FAST_DEMAND"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MANZOOR"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_EMP_FAST_DEMAND','MANZOOR')
--------------------------------------------------------------------------------
TABLESPACE "MANZOOR"
REFRESH FAST ON DEMAND
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
ENABLE QUERY REWRITE
AS SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."SALARY" "SALARY"
FROM "EMP" "EMP" WHERE "EMP"."SALARY">=20000 AND "EMP"."SALARY"<=50000
How to schedule the Materialized views to Refresh
begin
dbms_scheduler.create_job
(
job_name => 'REFRESH_MVIEW',
job_type => 'PLSQL_BLOCK',
job_action => 'Begin dbms_mview.refresh(''EMP_COMPLETE_DEMAND'',''C''); end;',
number_of_arguments => 0,
start_date => systimestamp,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Job to Refresh emp_complete_demand mview'
);
end;
/
SQL> select job_name, state, last_run_duration, next_run_date from dba_scheduler_jobs where job_name = 'REFRESH_MVIEW';
JOB_NAME STATE LAST_RUN_DURATION
------------------------------ --------------- ---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
REFRESH_MVIEW SCHEDULED
21-OCT-10 02.00.41.500000 PM +01:00
SQL> select * from emp;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
3 Manzoor 30000
1 Kannan 43000
4 Kannan 30000
2 Kannan 40000
5 Prasana 50000
6 Karthik 45000
SQL> select * from emp_complete_Demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
3 Manzoor 30000
4 Kannan 30000
2 Kannan 40000
SQL> update emp set salary = 35000;
6 rows updated.
SQL> commit;
Commit complete.
SQL> select * from emp_complete_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
3 Manzoor 30000
4 Kannan 30000
2 Kannan 40000
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
21-OCT-10 01.05.44.137000 PM +01:00
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
21-OCT-10 02.31.47.116000 PM +01:00
SQL> select * from emp_complete_demand;
EMPNO ENAME SALARY
---------- ------------------------------ ----------
3 Manzoor 35000
1 Kannan 35000
4 Kannan 35000
2 Kannan 35000
5 Prasana 35000
6 Karthik 35000
6 rows selected.
No comments:
Post a Comment