[PHP]
[oracle@orcl3 ~]$ sqlplus icweb/icweb
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 4 16:28:51 2007
Copyright (c) 1982, 2005, Oracle.All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create materialized view test_mv2
2refresh force on demand
3enable query rewrite
4as select * from
5(select ns1.story_id,ns1.index_image,ns1.logic_source,ns1.title,ns1.title_eng,ns1.article,ns1.article_eng,ns1.link_url,
6ns1.story_memo,nst2.topic_id,row_number() over (partition by nst2.topic_id order by nst2.disorder,ns1.input_time desc) rn
7from news_story ns1,news_story_topic nst2
8where ns1.story_id=nst2.story_id
9and nst2.neodisorder != 99
10and ns1.story_status >= 0)
11where rn show parameter query
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled
stringTRUE
query_rewrite_integrity
stringenforced
SQL> alter session set query_rewrite_intergrity=stale_tolerated;
alter session set query_rewrite_intergrity=stale_tolerated
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
SQL> alter session set query_rewrite_integrity=stale_tolerated;
Session altered.
SQL> set autotrace on
SQL> select count(*) from test_mv2 where rn select count(*) from
2(select ns1.story_id,ns1.index_image,ns1.logic_source,ns1.title,ns1.title_eng,ns1.article,ns1.article_eng,ns1.link_url,ns1.story_memo,nst2.topic_id,row_number() over (partition by nst2.topic_id order by nst2.disorder,ns1.input_time desc) rn
3from news_story ns1,news_story_topic nst2
4where ns1.story_id=nst2.story_id
5and nst2.neodisorder != 99
6and ns1.story_status >= 0 ) where rn select count(*) from test_mv2 where rn select count(*) from
2(select ns1.story_id,ns1.index_image,ns1.logic_source,ns1.title,ns1.title_eng,ns1.article,ns1.article_eng,ns1.link_url,ns1.story_memo,nst2.topic_id,row_number() over (partition by nst2.topic_id order by nst2.disorder,ns1.input_time desc) rn
3from news_story ns1,news_story_topic nst2
4where ns1.story_id=nst2.story_id
5and nst2.neodisorder != 99
6and ns1.story_status >= 0 ) where rn desc news_story
Name
Null?Type
----------------------------------------- -------- ----------------------------
STORY_ID
NOT NULL VARCHAR2(50)
INDEX_IMAGE
VARCHAR2(50)
STORY_SOURCE
VARCHAR2(50)
INPUT_TIME
DATE
CATEGORY
NUMBER(38)
TITLE
VARCHAR2(400)
TITLE_ENG
VARCHAR2(400)
ARTICLE
VARCHAR2(4000)
ARTICLE_ENG
VARCHAR2(4000)
STORY_TIME
DATE
STORY_LOCATION
VARCHAR2(50)
EDITOR_ID
NUMBER(38)
STORY_KEYS
VARCHAR2(100)
LINK_URL
VARCHAR2(255)
STORY_STATUS
NUMBER(38)
EDITOR_COLOR
NUMBER(38)
LAST_MODIFY
DATE
STORY_MEMO
VARCHAR2(2000)
LOGIC_SOURCE
VARCHAR2(50)
SQL> desc news_story_topic
Name
Null?Type
----------------------------------------- -------- ----------------------------
RID
NOT NULL NUMBER(38)
STORY_ID
NOT NULL VARCHAR2(50)
TOPIC_ID
NUMBER(38)
DISORDER
NOT NULL NUMBER(38)
NEODISORDER
NUMBER(38)
DISPLAY
NUMBER
SQL> desc test_mv2
Name
Null?Type
----------------------------------------- -------- ----------------------------
STORY_ID
NOT NULL VARCHAR2(50)
INDEX_IMAGE
VARCHAR2(50)
LOGIC_SOURCE
VARCHAR2(50)
TITLE
VARCHAR2(400)
TITLE_ENG
VARCHAR2(400)
ARTICLE
VARCHAR2(4000)
ARTICLE_ENG
VARCHAR2(4000)
LINK_URL
VARCHAR2(255)
STORY_MEMO
VARCHAR2(2000)
TOPIC_ID
NUMBER(38)
RN
NUMBER
.
[/PHP]
|
