Thanks for the question, Otn Baba.

Asked: February 01, 2008 - 10:32 am UTC

Last updated: November 29, 2012 - 8:02 am UTC

Version: 10G

Viewed 10K+ times! This question is

You Asked

In your book you mention that you use materialize views to pre answer large queries, but you also did mention that they can also be used for effective replication.

1. Can you please explain how materialized views can be used for replication?

2. Please give example on those scenarios where 'refresh of commit' will not work?

3. Is the WITH clause in sql similar to a materialized view in concept and in how it works behind the scenes?

4. What does the hint 'materialize' accomplish? and do hints work in 10GR2 onwards at all?

Thanks

Otn

and Tom said...

1) it is not obvious (if you can use them to copy data - eg: replicate data - within a single database.... and they work with dblinks... then, you can use them to copy (replicate) data..)

you create a materialized view
selecting data from a remote database
and it synchronizes it upon each refresh
hence, you have replicated data - this goes back to version 7.0 of Oracle when they were originally called "snapshots"

http://docs.oracle.com/cd/B19306_01/server.102/b14226/repoverview.htm#sthref49

2) refresh on commit will NEVER work over a database link, that would be synchronous replication and synchronous replication just isn't anything anyone wants to even consider.

Refresh on commit is appropriate only in a single database.

3) no, a materialized view EXISTS. If you create on and 1,000 people execute a query that uses it - the materialized view is used 1,000 times. It is not created 1,000 times - it already exists.

A WITH subquery would be materialized for EACH QUERY execution. It would be materialized 1,000 times in the above scenario.

4) This undocumented hint causes Oracle to put the query block that was hinted as "materialize" into a temporary table for the duration of the query - and then that data is removed.

ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a90 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> with data
  2  as
  3  (select count(*) cnt from t )
  4  select t.*, data.cnt from t, data
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 658131203

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 52697 |  7256K|   445   (3)| 00:00:03 |
|   1 |  NESTED LOOPS        |      | 52697 |  7256K|   445   (3)| 00:00:03 |
|   2 |   VIEW               |      |     1 |    13 |   220   (2)| 00:00:02 |
|   3 |    SORT AGGREGATE    |      |     1 |       |            |          |
|   4 |     TABLE ACCESS FULL| T    | 52697 |       |   220   (2)| 00:00:02 |
|   5 |   TABLE ACCESS FULL  | T    | 52697 |  6587K|   224   (4)| 00:00:02 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA10GR2> with data
  2  as
  3  (select /*+ MATERIALIZE */ count(*) cnt from t )
  4  select t.*, data.cnt from t, data
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3271945946

------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%C
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            | 52697 |  7256K|   447
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |
|   2 |   LOAD AS SELECT           |                            |       |       |
|   3 |    SORT AGGREGATE          |                            |     1 |       |
|   4 |     TABLE ACCESS FULL      | T                          | 52697 |       |   220
|   5 |   MERGE JOIN CARTESIAN     |                            | 52697 |  7256K|   226
|   6 |    VIEW                    |                            |     1 |    13 |     2
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6603_323E82A |     1 |    13 |     2
|   8 |    BUFFER SORT             |                            | 52697 |  6587K|   226
|   9 |     TABLE ACCESS FULL      | T                          | 52697 |  6587K|   224
------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace off

see steps 1..4 - in the second plan - the loading of a temporary table with the data, instead of merging it all together into one big plan as the first one chose to.

Hints are a supported feature of the database, even in the current release 11g.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 08, 2010 - 12:48 pm UTC

Hi Tom,

In the first query above, will the WITH clause will evaluate for every row for t during join

OR

the with cluase is evaluate once and the result is join with table t

Thanks

February 15, 2010 - 11:02 am UTC

this plan:


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 52697 |  7256K|   445   (3)| 00:00:03 |
|   1 |  NESTED LOOPS        |      | 52697 |  7256K|   445   (3)| 00:00:03 |
|   2 |   VIEW               |      |     1 |    13 |   220   (2)| 00:00:02 |
|   3 |    SORT AGGREGATE    |      |     1 |       |            |          |
|   4 |     TABLE ACCESS FULL| T    | 52697 |       |   220   (2)| 00:00:02 |
|   5 |   TABLE ACCESS FULL  | T    | 52697 |  6587K|   224   (4)| 00:00:02 |
-----------------------------------------------------------------------------

is like this psuedo code:

for x in (select count(*) cnt from t )
loop
   for y in (select * from t )
   loop
      OUTPUT x.cnt, y.*;
   end loop;
end loop;

You can see that the select count(*) was evaluated once using TKPROF, I ran a count(*) just to see what it would take, then the query:

select count(*) from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0       1025          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03          0       1026          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1025 pr=0 pw=0 time=0 us)
  71630   TABLE ACCESS FULL T (cr=1025 pr=0 pw=0 time=69710 us cost=286 size=0 card=78267)
********************************************************************************
with data as (select count(*) cnt from t ) select t.*, data.cnt from t, data

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          0          0           0
Fetch     4777      0.37       0.37          0       6756          0       71630
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4779      0.37       0.37          0       6756          0       71630

Rows     Row Source Operation
-------  ---------------------------------------------------
  71630  NESTED LOOPS  (cr=6756 pr=0 pw=0 time=234620 us cost=572 size=13383657 card=78267)
      1   VIEW  (cr=1025 pr=0 pw=0 time=0 us cost=286 size=13 card=1)
<b>      1    SORT AGGREGATE (cr=1025 pr=0 pw=0 time=0 us)
  71630     TABLE ACCESS FULL T (cr=1025 pr=0 pw=0 time=58710 us cost=286 size=0 card=78267)</b>
  71630   TABLE ACCESS FULL T (cr=5731 pr=0 pw=0 time=127240 us cost=286 size=12366186 card=78267)

as you can see the IO's for that step in bold are the same for a single count(*) execution.

Repeating your example

Rajeshwaran, Jeyabal, August 23, 2011 - 10:36 am UTC

Tom:

I am repeating your example as such and I dont see that

select count(*) as cnt from t

available in TKPROF trace file.

drop table t purge;
create table t as select * from all_objects;
exec dbms_stats.gather_table_stats(user,'T');

alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

with datas as (select count(*) as cnt from t)
select t.*,cnt
from t, datas
/

select rtrim(c.value,'/') || '/' || d.instance_name ||
'_ora_' || ltrim(to_char(a.spid)) || '.trc'
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = sys_context( 'userenv', 'sessionid')
and c.name = 'user_dump_dest';

And the Tkprof shows me this.

C:\oracle\product\10.2.0\db_1\bin>tkprof C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORA10GR2\UDUMP/ora10gr2_ora_6464.trc c:\trash\result.txt sys=no

TKPROF: Release 10.2.0.3.0 - Production on Tue Aug 23 10:29:21 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

********************************************************************************

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.03       0.04          0          9          0           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.03       0.04          0          9          0           3

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3       21.17         29.13
********************************************************************************

with datas as (select count(*) as cnt from t)
select t.*,cnt
from t, datas

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      335      0.21       0.16          0       1713          0       50063
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      337      0.21       0.18          0       1713          0       50063

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
  50063  NESTED LOOPS  (cr=1713 pr=0 pw=0 time=306610 us)
      1   VIEW  (cr=693 pr=0 pw=0 time=6196 us)
      1    SORT AGGREGATE (cr=693 pr=0 pw=0 time=6182 us)
  50063     TABLE ACCESS FULL T (cr=693 pr=0 pw=0 time=100179 us)
  50063   TABLE ACCESS FULL T (cr=1020 pr=0 pw=0 time=100156 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     335        0.00          0.00
  SQL*Net message from client                   335        1.09        107.91
  SQL*Net more data to client                   910        0.00          0.00
********************************************************************************

Can you tell me what option you used to get this below high statement in your Tkprof results?

<quote - Your Tkprof result from the above post>

select count(*) from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0       1025          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03          0       1026          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1025 pr=0 pw=0 time=0 us)
  71630   TABLE ACCESS FULL T (cr=1025 pr=0 pw=0 time=69710 us cost=286 size=0 card=78267)
********************************************************************************

</quote>

August 30, 2011 - 3:19 pm UTC

re-read what I wrote.

I wrote:

You can see that the select count(*) was evaluated once using TKPROF, I ran a count(*) just to see what it would take, then the query:

I ran the count(*) myself - to see what work it would involve. Then I could compare that to the work done by the original query with the WITH clause.

Then I hilighted the stop in the plan (with the WITH clause) to show that based on the IO's we see - we could have only read the table ONCE.

The select count(*) from t was in my tkprof simply because I typed select count(*) from T in myself

Anil, May 31, 2012 - 12:25 am UTC

Tom, we have a similar query "with" clause which builds intermediate table. This query's 1st run always hangs, we usually terminate;surprisingly , rerun succeeds in few seconds.appreciate any pointers to determine the root cause pls

May 31, 2012 - 2:06 am UTC

trace it...

query the v$ views (v$session_wait/v$session_event)...

use ASH/AWR reports in OEM...

With Cluse in Materialized view

Sumi Menon, November 28, 2012 - 7:35 am UTC

Can i use With Clause in Materialized view?????
CREATE MATERILIZED view A....
AS
WITH DATA as ()
Selectt.*, DATA.b1 from t, Data ;

Can With caluse actually be used in a materialized view???

November 29, 2012 - 8:02 am UTC

ops$tkyte%ORA11GR2> create table t
  2  as
  3  select * from scott.emp;

Table created.

ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key (empno);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create materialized view mv
  2  as
  3  with data as (select deptno, count(*) cnt from t)
  4  select * from t;

Materialized view created.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Repeating your example

With Cluse in Materialized view

More to Explore

Hints

Materialized Views, Materialize optimizer hint

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Repeating your example

With Cluse in Materialized view

More to Explore

Hints