Oracle PL/SQL Tutorial/View/Materialized View

Материал из SQL эксперт
Версия от 10:08, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

alter MATERIALIZED VIEW dept20_mv enable query rewrite

SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason",  "N",  "TRAINER", 2,   date "1965-12-18",  800 , NULL,  10);
1 row created.
SQL> insert into employees values(2,"Jerry",  "J",  "SALESREP",3,   date "1966-11-19",  1600, 300,   10);
1 row created.
SQL> insert into employees values(3,"Jord",   "T" , "SALESREP",4,   date "1967-10-21",  1700, 500,   20);
1 row created.
SQL> insert into employees values(4,"Mary",   "J",  "MANAGER", 5,   date "1968-09-22",  1800, NULL,  20);
1 row created.
SQL> insert into employees values(5,"Joe",    "P",  "SALESREP",6,   date "1969-08-23",  1900, 1400,  30);
1 row created.
SQL> insert into employees values(6,"Black",  "R",  "MANAGER", 7,   date "1970-07-24",  2000, NULL,  30);
1 row created.
SQL> insert into employees values(7,"Red",    "A",  "MANAGER", 8,   date "1971-06-25",  2100, NULL,  40);
1 row created.
SQL> insert into employees values(8,"White",  "S",  "TRAINER", 9,   date "1972-05-26",  2200, NULL,  40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C",  "DIRECTOR",10,  date "1973-04-27",  2300, NULL,  20);
1 row created.
SQL> insert into employees values(10,"Pink",  "J",  "SALESREP",null,date "1974-03-28",  2400, 0,     30);
1 row created.
SQL>
SQL> create MATERIALIZED VIEW dept20_mv as
  2  select * from employees where deptno = 20;
select * from employees where deptno = 20
                                       *
ERROR at line 2:
ORA-12014: table "EMPLOYEES" does not contain a primary key constraint

SQL>
SQL> alter MATERIALIZED VIEW dept20_mv enable query rewrite;
alter MATERIALIZED VIEW dept20_mv enable query rewrite
*
ERROR at line 1:
ORA-12003: materialized view "sqle"."DEPT20_MV" does not exist

SQL>
SQL> set autotrace on explain
SQL>
SQL> select *
  2  from   employees
  3  where  deptno = 20
  4  and    job = "TRAINER";
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    90 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    90 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO"=20 AND "JOB"="TRAINER")
Note
-----
   - dynamic sampling used for this statement
SQL>
SQL> explain plan for
  2  select * from employees
  3  where deptno = 20 and job = "TRAINER";
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    90 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    90 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO"=20 AND "JOB"="TRAINER")
Note
-----
   - dynamic sampling used for this statement
17 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089
--------------------------------------------------------------------------------
-------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU
)| Time     |
--------------------------------------------------------------------------------
-------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0
)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |
 |          |
--------------------------------------------------------------------------------
-------------

SQL>
SQL> set autotrace off
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL>


Create materialized view

SQL>
SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason",  "N",  "TRAINER", 2,   date "1965-12-18",  800 , NULL,  10);
1 row created.
SQL> insert into employees values(2,"Jerry",  "J",  "SALESREP",3,   date "1966-11-19",  1600, 300,   10);
1 row created.
SQL> insert into employees values(3,"Jord",   "T" , "SALESREP",4,   date "1967-10-21",  1700, 500,   20);
1 row created.
SQL> insert into employees values(4,"Mary",   "J",  "MANAGER", 5,   date "1968-09-22",  1800, NULL,  20);
1 row created.
SQL> insert into employees values(5,"Joe",    "P",  "SALESREP",6,   date "1969-08-23",  1900, 1400,  30);
1 row created.
SQL> insert into employees values(6,"Black",  "R",  "MANAGER", 7,   date "1970-07-24",  2000, NULL,  30);
1 row created.
SQL> insert into employees values(7,"Red",    "A",  "MANAGER", 8,   date "1971-06-25",  2100, NULL,  40);
1 row created.
SQL> insert into employees values(8,"White",  "S",  "TRAINER", 9,   date "1972-05-26",  2200, NULL,  40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C",  "DIRECTOR",10,  date "1973-04-27",  2300, NULL,  20);
1 row created.
SQL> insert into employees values(10,"Pink",  "J",  "SALESREP",null,date "1974-03-28",  2400, 0,     30);
1 row created.
SQL>
SQL>
SQL> create or replace VIEW dept20_v as
  2  select * from employees where deptno = 20;
View created.
SQL>
SQL> create TABLE dept20_t as
  2  select * from employees where deptno = 20;
Table created.
SQL>
SQL> create MATERIALIZED VIEW dept20_mv as
  2  select * from employees where deptno = 20;
select * from employees where deptno = 20
                                       *
ERROR at line 2:
ORA-12014: table "EMPLOYEES" does not contain a primary key constraint

SQL>
SQL>
SQL> drop table dept20_t;
Table dropped.
SQL> drop materialized view dept20_mv;
drop materialized view dept20_mv
*
ERROR at line 1:
ORA-12003: materialized view "sqle"."DEPT20_MV" does not exist

SQL>
SQL> drop table employees;
Table dropped.
SQL>


drop materialized view

SQL>
SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason",  "N",  "TRAINER", 2,   date "1965-12-18",  800 , NULL,  10);
1 row created.
SQL> insert into employees values(2,"Jerry",  "J",  "SALESREP",3,   date "1966-11-19",  1600, 300,   10);
1 row created.
SQL> insert into employees values(3,"Jord",   "T" , "SALESREP",4,   date "1967-10-21",  1700, 500,   20);
1 row created.
SQL> insert into employees values(4,"Mary",   "J",  "MANAGER", 5,   date "1968-09-22",  1800, NULL,  20);
1 row created.
SQL> insert into employees values(5,"Joe",    "P",  "SALESREP",6,   date "1969-08-23",  1900, 1400,  30);
1 row created.
SQL> insert into employees values(6,"Black",  "R",  "MANAGER", 7,   date "1970-07-24",  2000, NULL,  30);
1 row created.
SQL> insert into employees values(7,"Red",    "A",  "MANAGER", 8,   date "1971-06-25",  2100, NULL,  40);
1 row created.
SQL> insert into employees values(8,"White",  "S",  "TRAINER", 9,   date "1972-05-26",  2200, NULL,  40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C",  "DIRECTOR",10,  date "1973-04-27",  2300, NULL,  20);
1 row created.
SQL> insert into employees values(10,"Pink",  "J",  "SALESREP",null,date "1974-03-28",  2400, 0,     30);
1 row created.
SQL>
SQL>
SQL> create or replace VIEW dept20_v as
  2  select * from employees where deptno = 20;
View created.
SQL>
SQL> create TABLE dept20_t as
  2  select * from employees where deptno = 20;
Table created.
SQL>
SQL> create MATERIALIZED VIEW dept20_mv as
  2  select * from employees where deptno = 20;
select * from employees where deptno = 20
                                       *
ERROR at line 2:
ORA-12014: table "EMPLOYEES" does not contain a primary key constraint

SQL>
SQL>
SQL> drop table dept20_t;
Table dropped.
SQL> drop materialized view dept20_mv;
drop materialized view dept20_mv
*
ERROR at line 1:
ORA-12003: materialized view "sqle"."DEPT20_MV" does not exist

SQL>
SQL> drop table employees;
Table dropped.
SQL>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/View/Materialized_View&oldid=4126»