Friday, December 30, 2011

Check Constraint & NOT NULL Constraint

Learnt something newly in Oracle database, its about Check constraint and NOT NULL constraints.


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
  2  (
  3     x number not null,
  4     y number check (y is not null),
  5     z number,
  6     constraint t_chk check(z is not null)
  7  )
  8  nologging;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert /*+ append */ into h
  2  select level,level,level
  3  from dual
  4  connect by level <=1000000;

1000000 rows created.

Elapsed: 00:00:01.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.04
rajesh@ORA11GR2>

Now, we have defined NOT NULL constraint on column 'x' and column level check constraint in 'y' and table level check constraint on column 'z'.


rajesh@ORA11GR2>
rajesh@ORA11GR2> select constraint_type,
  2  search_condition
  3  from user_constraints
  4  where table_name ='T'
  5  /

CONST SEARCH_CONDITION
----- -------------------------
C     "X" IS NOT NULL
C     y is not null
C     z is not null

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2>

The NOT NULL constraint is Technically implemented in the database as CHECK constraint.  But the real difference comes into picture how the optimizer understand NOTNULL constraint and CHECK constraint.


rajesh@ORA11GR2> create index t_ind on t(x) nologging;

Index created.

Elapsed: 00:00:00.64
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */ count(*)
  2  from t
  3  /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 646498162

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |  2239   (1)| 00:00:27 |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T_IND |  1000K|  2239   (1)| 00:00:27 |
------------------------------------------------------------------

rajesh@ORA11GR2>

With the index on column X, the optimizer knew that X was not null and the index on X therefore pointed to every row in the table, so its safe to count the rows via the Index.

If we drop and recreate the index on column Y, the optimizer cannot use this new index.


rajesh@ORA11GR2> drop index t_ind;

Index dropped.

Elapsed: 00:00:00.06
rajesh@ORA11GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.68
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */ count(*)
  2  from t
  3  /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   771   (2)| 00:00:10 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000K|   771   (2)| 00:00:10 |
-------------------------------------------------------------------

rajesh@ORA11GR2>

In this case the optimizer is not aware of that Y was NOT NULL, because this check constraint doesn't provide enough information for the optimizer & entirely null entries will not be preserved in B*Tree index. and because Y was assumed by the optimizer to allow for NULL values, So the optimizer could not use this index.

The same applies to column 'Z'.


rajesh@ORA11GR2> drop index t_ind;

Index dropped.

Elapsed: 00:00:00.04
rajesh@ORA11GR2> create index t_ind on t(z) nologging;

Index created.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */ count(*)
  2  from t
  3  /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   771   (2)| 00:00:10 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000K|   771   (2)| 00:00:10 |
-------------------------------------------------------------------

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>





Posted by at 2 comments:

Tuesday, December 6, 2011

Partitioned Mview Refresh

Materialized views are partitioned for

1) PCT Refresh, there by improving refresh performance.

2) Partition pruning: only relevant aggregated groups are accessed, there by reducing query processing cost.


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table emp
  2  partition by list(deptno)
  3  (
  4     partition p1 values (1),
  5     partition p2 values (2),
  6     partition p3 values (3),
  7     partition p4 values (4),
  8     partition p5 values (5),
  9     partition pmax values (default)
 10  )
 11  nologging
 12  as
 13  select level as empno,
 14     dbms_random.string('A',30) as ename,
 15     dbms_random.string('B',7) as job,
 16     sysdate - mod(level,100) as hire_date,
 17     dbms_random.value(1,1000) as comm,
 18     dbms_random.value(1,10000) as salary,
 19     mod(level,5)+1 as deptno
 20  from dual
 21  connect by level <= 1000000;

Table created.

Elapsed: 00:01:29.65
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
  2  add constraint emp_pk primary key(empno);

Table altered.

Elapsed: 00:00:14.29
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'EMP',
  5     estimate_percent=>dbms_stats.auto_sample_size,
  6     cascade=>false,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.15
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table dept
  2  as
  3  select level as deptno,
  4     dbms_random.string('C',10) as dname,
  5     dbms_random.string('E',7) as loc
  6  from dual
  7  connect by level <= 5;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
  2  add constraint dept_pk primary key(deptno);

Table altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'DEPT',
  5     estimate_percent=>dbms_stats.auto_sample_size,
  6     cascade=>false,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA11GR2> alter table emp add
  2  constraint emp_fk
  3  foreign key(deptno) references dept;

Table altered.

Elapsed: 00:00:07.79
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;

Table altered.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>

rajesh@ORA11GR2> create materialized view emp_dept_mv
  2  partition by list(deptno)
  3  (
  4     partition p1 values (1),
  5     partition p2 values (2),
  6     partition p3 values (3),
  7     partition p4 values (4),
  8     partition p5 values (5),
  9     partition pmax values (default)
 10  )
 11  build immediate
 12  refresh on demand
 13  enable query rewrite
 14  as
 15  select e.deptno,count(*)
 16  from dept d,
 17      emp e
 18  where e.deptno = d.deptno
 19  group by e.deptno;

Materialized view created.

Elapsed: 00:00:07.56
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'EMP_DEPT_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*)
  2  from emp
  3  where deptno =1;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1215826357

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     1 |     6 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                |             |     1 |     6 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE        |             |     1 |     6 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT_MV |     1 |     6 |     3   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;

The query rewrite happens as expected. Now we will make the partition p1 to Stale by deleting some data's from the detailed table.

rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 FRESH
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                              6 FRESH

6 rows selected.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>

rajesh@ORA11GR2> delete from emp
  2  where deptno  = 1
  3  and rownum = 1;

1 row deleted.

Elapsed: 00:00:00.03
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.09
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 STALE
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>

Refreshing the (partitioned) materialized view, will make its stale segments to Fresh.

rajesh@ORA11GR2> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> alter session set events '10046
  2  trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set echo off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_DEPT_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.57
rajesh@ORA11GR2>
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 FRESH
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

But what happened behind the scene is, Oracle did a delete on Mview and re-inserted the new data's from detail table. 

So what happens if the Stale partition in Mview has many records? then we will spend more time in deleting the data from stale partition.

SQL ID: 88sv43hhjtc0y
Plan Hash: 1443979106
DELETE FROM "RAJESH"."EMP_DEPT_MV" 
WHERE
  ( ( ( ("DEPTNO" IN (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          3          4           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          4           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  EMP_DEPT_MV (cr=3 pr=0 pw=0 time=0 us)
      1   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=0 us cost=3 size=3 card=1)
      1    MAT_VIEW ACCESS FULL EMP_DEPT_MV PARTITION: 1 1 (cr=3 pr=0 pw=0 time=0 us cost=3 size=3 card=1)

--------------------------------------------------------------------------------

SQL ID: 2hk25tz0vayzs
Plan Hash: 3660430689
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "RAJESH"."EMP_DEPT_MV"SELECT  /*+ 
  X_DYN_PRUNE */ "E"."DEPTNO" , COUNT(*)  FROM "EMP" "E","DEPT" "D" WHERE 
  ("E"."DEPTNO"="D"."DEPTNO") AND  ( ( ( ("E"."DEPTNO" IN (1)) ) )  )GROUP BY 
  "E"."DEPTNO"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.10       1.25       2894       2906          5           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.10       1.25       2894       2906          5           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=2906 pr=2894 pw=0 time=0 us)
      1   SORT GROUP BY NOSORT (cr=2905 pr=2894 pw=0 time=0 us cost=806 size=26000 card=2000)
 199999    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2905 pr=2894 pw=0 time=3421974 us cost=806 size=26000 card=2000)
 199999     TABLE ACCESS FULL EMP PARTITION: 1 1 (cr=2905 pr=2894 pw=0 time=2845781 us cost=806 size=26000 card=2000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                         82        0.03          1.15
--------------------------------------------------------------------------------

So the right way to refresh (partitioned) Mview is to go nonatomic (atomic=>false), So when you do this Oracle will Truncate the partitions rather than deleting it.

rajesh@ORA11GR2> delete from emp
  2  where deptno  = 1
  3  and rownum = 1;

1 row deleted.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 STALE
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> @trace.sql;
rajesh@ORA11GR2> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> alter session set events '10046
  2  trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.04
rajesh@ORA11GR2> set echo off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_DEPT_MV',atomic_refresh=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.85
rajesh@ORA11GR2>

Tkprof shows me this.

SQL ID: a7rykky0gvrq9
Plan Hash: 0
LOCK TABLE "RAJESH"."EMP_DEPT_MV" PARTITION ("P1")  IN EXCLUSIVE MODE  NOWAIT 
  


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        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 2)
--------------------------------------------------------------------------------

SQL ID: c3yz7q6n5d2h0
Plan Hash: 1999525523
ALTER TABLE "RAJESH"."EMP_DEPT_MV" TRUNCATE  PARTITION P1 UPDATE GLOBAL 
  INDEXES 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          3          1           0
Execute      1      0.04       0.21          4          0         38           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.04       0.24          4          3         39           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.03          0.05
  reliable message                                2        0.00          0.00
  enq: RO - fast object reuse                     2        0.00          0.00
  db file sequential read                         4        0.02          0.02
  local write wait                                3        0.00          0.00
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

SQL ID: 80rm69rmu73s5
Plan Hash: 1312300534
insert /*+ RELATIONAL("EMP_DEPT_MV") PARALLEL("EMP_DEPT_MV",1) APPEND 
  NESTED_TABLE_SET_SETID NO_REF_CASCADE */ into "RAJESH"."EMP_DEPT_MV" 
  partition ("P1") (select /*+ RELATIONAL("EMP_DEPT_MV") 
  PARALLEL("EMP_DEPT_MV",1) */  *  from "RAJESH"."EMP_DEPT_MV" partition 
  ("P1") ) delete global indexes


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

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD AS SELECT  (cr=3 pr=0 pw=0 time=0 us)
      1   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
      1    MAT_VIEW ACCESS FULL EMP_DEPT_MV PARTITION: 1 1 (cr=3 pr=0 pw=0 time=0 us cost=3 size=10 card=1)

--------------------------------------------------------------------------------




Posted by at No comments:

Monday, December 5, 2011

Partition Change Tracking in Mview

Partition change Tracking is the ability to identify which rows in materialized view's are affected by certain fact table partition changes. When a partition is modified in fact table those rows become stale and all others remain fresh, rather than considering entire materialized view as stale this allows query rewrite to use those rows that are fresh while the query_rewrite_integrity is set to ENFORCED or TRUSTED.

A new data dictionary dba_mview_detail_partition was introduced in Oracle 11g will show which partitions are are stale or fresh.

Here is the snipped of code to demonstrate Partition change Tracking


rajesh@ORA11GR2> create table emp
  2  partition by list(deptno)
  3  (
  4     partition p1 values (1),
  5     partition p2 values (2),
  6     partition p3 values (3),
  7     partition p4 values (4),
  8     partition p5 values (5),
  9     partition pmax values (default)
 10  )
 11  nologging
 12  as
 13  select level as empno,
 14     dbms_random.string('A',30) as ename,
 15     dbms_random.string('B',7) as job,
 16     sysdate - mod(level,100) as hire_date,
 17     dbms_random.value(1,1000) as comm,
 18     dbms_random.value(1,10000) as salary,
 19     mod(level,5)+1 as deptno
 20  from dual
 21  connect by level <= 1000000;

Table created.

Elapsed: 00:01:21.53
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
  2  add constraint emp_pk primary key(empno);

Table altered.

Elapsed: 00:00:11.25
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'EMP',
  5     estimate_percent=>dbms_stats.auto_sample_size,
  6     cascade=>false,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.25
rajesh@ORA11GR2> create table dept
  2  as
  3  select level as deptno,
  4     dbms_random.string('C',10) as dname,
  5     dbms_random.string('E',7) as loc
  6  from dual
  7  connect by level <= 5;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
  2  add constraint dept_pk primary key(deptno);

Table altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'DEPT',
  5     estimate_percent=>dbms_stats.auto_sample_size,
  6     cascade=>false,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA11GR2> alter table emp add
  2  constraint emp_fk
  3  foreign key(deptno) references dept;

Table altered.

Elapsed: 00:00:05.96
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;

Table altered.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>

The fact table is partition by list one for each department.



rajesh@ORA11GR2> create materialized view emp_dept_mv
  2  build immediate
  3  refresh on demand
  4  enable query rewrite
  5  as
  6  select e.deptno,count(*)
  7  from dept d,
  8      emp e
  9  where e.deptno = d.deptno
 10  group by e.deptno;

Materialized view created.

Elapsed: 00:00:06.34
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'EMP_DEPT_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 FRESH
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>

rajesh@ORA11GR2> show parameter query_rewrite;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced
rajesh@ORA11GR2>
rajesh@ORA11GR2>


rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*)
  2  from emp
  3  where deptno =1;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2723375566

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |             |     1 |     6 |            |          |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT_MV |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMP_DEPT_MV"."DEPTNO"=1)

rajesh@ORA11GR2> set autotrace off;

Now the query got rewritten to use materialized view to produce it's results.

Marking any changes to fact table, those rows become stale in Materialized views.

rajesh@ORA11GR2> delete from emp
  2  where deptno  = 1
  3  and rownum = 1;

1 row deleted.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 STALE
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.04

Now the query rewrite is not possible for partition p1 since it became STALE. Accessing data from Mview will produce incorrect results, so the optimizer went scanning the base tables to answer this query.

rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*)
  2  from emp
  3  where deptno =1;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2365616264

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    13 |   806   (1)| 00:00:10 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |  2000 | 26000 |   806   (1)| 00:00:10 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | EMP  |  2000 | 26000 |   806   (1)| 00:00:10 |     1 |     1 |
-----------------------------------------------------------------------------------------------

But still query rewrite is possible for FRESH partitions.


rajesh@ORA11GR2> select count(*)
  2  from emp
  3  where deptno =2;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2723375566

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |             |     1 |     6 |            |          |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT_MV |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMP_DEPT_MV"."DEPTNO"=2)

What happens when we refresh Mview? The affected rows in Materialized view are deleted and re-inserted.

rajesh@ORA11GR2>
rajesh@ORA11GR2> @trace.sql
rajesh@ORA11GR2> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> alter session set events '10046
  2  trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2> set echo off;
rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_DEPT_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.59
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 FRESH
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>

Tkprof shows me this:

--------------------------------------------------------------------------------

SQL ID: 88sv43hhjtc0y
Plan Hash: 3770894312
DELETE FROM "RAJESH"."EMP_DEPT_MV" 
WHERE
  ( ( ( ("DEPTNO" IN (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          3          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          3           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  EMP_DEPT_MV (cr=3 pr=0 pw=0 time=0 us)
      1   MAT_VIEW ACCESS FULL EMP_DEPT_MV (cr=3 pr=0 pw=0 time=0 us cost=3 size=3 card=1)

--------------------------------------------------------------------------------

SQL ID: 2hk25tz0vayzs
Plan Hash: 3660430689
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "RAJESH"."EMP_DEPT_MV"SELECT  /*+ 
  X_DYN_PRUNE */ "E"."DEPTNO" , COUNT(*)  FROM "EMP" "E","DEPT" "D" WHERE 
  ("E"."DEPTNO"="D"."DEPTNO") AND  ( ( ( ("E"."DEPTNO" IN (1)) ) )  )GROUP BY 
  "E"."DEPTNO"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.18       1.37       2894       2910         24           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.18       1.37       2894       2910         24           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=2910 pr=2894 pw=0 time=0 us)
      1   SORT GROUP BY NOSORT (cr=2905 pr=2893 pw=0 time=0 us cost=806 size=26000 card=2000)
 199999    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2905 pr=2893 pw=0 time=2304007 us cost=806 size=26000 card=2000)
 199999     TABLE ACCESS FULL EMP PARTITION: 1 1 (cr=2905 pr=2893 pw=0 time=1748159 us cost=806 size=26000 card=2000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                         81        0.03          1.25
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         2        0.00          0.00
--------------------------------------------------------------------------------




Posted by at 4 comments:
Subscribe to: Posts (Atom)