create table master_table
(
userid_a varchar2(20),
userid_b varchar2(20),
name varchar2(50),
details varchar2(1000),
constraint pk_master_table_id_a primary key (userid_a)
) Table created.
insert into master_table values ('A001','B010','Max','Some interesting details')1 row(s) inserted.
insert into master_table values ('A002','B020','Tom','More interesting details')1 row(s) inserted.
insert into master_table values ('A003','B030','Mary','Much more interesting details')1 row(s) inserted.
CREATE materialized VIEW mv_personaldata
ORGANIZATION INDEX
REFRESH complete ON demand
--START WITH SYSDATE
--NEXT Trunc(SYSDATE) + interval '1 5' DAY TO hour
AS
SELECT *
from master_table Statement processed.
select * from mv_personaldata where userid_b = 'B010'| USERID_A | USERID_B | NAME | DETAILS | A001 | B010 | Max | Some interesting details |
|---|
explain plan for select * from mv_personaldata where userid_b = 'B010'Statement processed.
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY)| PLAN_TABLE_OUTPUT | Plan hash value: 907604132 | -------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | 553 | 2 (0)| 00:00:01 | | |* 1 | INDEX FAST FULL SCAN| SYS_IOT_TOP_3291221 | 1 | 553 | 2 (0)| 00:00:01 | | -------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("USERID_B"='B010') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
create index idx_userid_b on mv_personaldata (userid_b)Index created.
explain plan for select * from mv_personaldata where userid_b = 'B010'Statement processed.
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY)| PLAN_TABLE_OUTPUT | Plan hash value: 3629046319 | ----------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ----------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | 553 | 1 (0)| 00:00:01 | | |* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_3291221 | 1 | 553 | 1 (0)| 00:00:01 | | |* 2 | INDEX RANGE SCAN| IDX_USERID_B | 1 | | 1 (0)| 00:00:01 | | ----------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - access("USERID_B"='B010') | 2 - access("USERID_B"='B010') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|