Sunday, May 13, 2007

ANSI & ORACLE MV - still a long way to go..

Anything new is always interesting and so is the my current project at a client place - which is sort of hybrid between Transactional and reporting system. Thanks to this project, I got a break from traditional TUNING tasks and am over-working on catching up with datawarehousing (DW) techniques & implementing them.

Here is something interesting what I figured last week (thanks to a question from client's dev team)..

-- Create 3 tables with 10 rows each
-- & add Primary keys

SQL> Create table test_tb1 as
2 select rownum c1, owner c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb1 add (
2 constraint test_tb1_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb2 as
2 select rownum c1, object_name c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb2 add (
2 constraint test_tb2_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb3 as
2 select rownum c1, object_type c2,created from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb3 add (
2 constraint test_tb3_pk primary key(c1)
3 )
4 /

Table altered.

-- this exercise is about MATERIALIZED VIEWS..so to have FAST REFRESHING (aka incremental refreshing) Materialized view implemented , we should first have MV LOG created on base tables.

--So here we create them


SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb1
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb2
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb3
2 with primary key,ROWID,sequence
3* (c2,created) including new values
SQL> /

Materialized view log created.

-- Now that we have base tables and MV logs ready, lets try creating a Materialized view..(using ANSI syntax)

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from test_tb1 t1
13 JOIN test_tb2 t2
14 ON t1.c1 = t2.c1
15 JOIN test_tb3 t3
16* on t1.c1 = t3.c1
SQL> /
JOIN test_tb3 t3
*
ERROR at line 15:
ORA-12015: cannot create a fast refresh materialized view from a complex query

-- that fails ..because optimizer thinks its a complex query (I am not good as ANSI..and not sure if there is any way to rewrite within ANSI standard itself )..but the point is , I didnt expect that to be considered as complex query


-- that was the problem reported from Dev team to me..and I just thought to test if it was ANSI SQL which was creating the problem. So here I rewrite the same SQL using traditional joins

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18* and t1.c1 = t3.c1
SQL> /

Materialized view created.

-- WORKS!! Thats surprising..


SQL> drop materialized view test_mv1;

Materialized view dropped.

-- Lets try the same with outer joins too

SQL> ed
Wrote file afiedt.buf

1* drop materialized view test_mv1
SQL> Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18 and t1.c1 = t3.c1(+)
19 /

Materialized view created.

-- Selecting from MV

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS

10 rows selected.

-- Testing MV functionality by adding more data to base tables and
-- seeing if MV works fine.

SQL> insert into test_tb1 select 10+rownum c1 , 'NEW DATA' c2 from test_tb1
2 /

10 rows created.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.

SQL> commit;

Commit complete.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> insert into test_tb2 select 10+rownum c1, 'New data' c2 from test_tb2
2 /

10 rows created.

SQL> select * from test_tb2;

C1 C2
---------- -------------------------
1 ICOL$
2 I_USER1
3 CON$
4 UNDO$
5 C_COBJ#
6 I_OBJ#
7 PROXY_ROLE_DATA$
8 I_IND1
9 I_CDEF2
10 I_PROXY_ROLE_DATA$_1
11 New data
12 New data
13 New data
14 New data
15 New data
16 New data
17 New data
18 New data
19 New data
20 New data

20 rows selected.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.


-- works fine!!!

Moral: Though ANSI standard has been introduced in Oracle since 9.1 version, its always better to check back to traditional join syntax when sometimes you get unexpected error with sql/optimizer.





6 comments:

Anonymous said...

This is my first post I'd love to congratulate you for such a terrific made forum!
I was sure this is a perfect way to make my first post!

Sincerely,
Monte Phil
if you're ever bored check out my site!
[url=http://www.partyopedia.com/articles/strawberry-shortcake-party-supplies.html]strawberry shortcake Party Supplies[/url].

Anonymous said...

[url=http://kfarbair.com][img]http://www.kfarbair.com/_images/_photos/photo_big7.jpg[/img][/url]

מלון [url=http://www.kfarbair.com]כפר בעיר[/url] - אינטימיות, [url=http://kfarbair.com/services.html]שקט[/url] . אנחנו מספקים שירותי אירוח מגוונים כמו כן ישנו במקום שירות חדרים הכולל [url=http://www.kfarbair.com/eng/index.html]אחרוחות רומנטיות[/url] במחירים מיוחדים אשר מוגשות ישירות לחדרכם!

לפרטים נוספים אנא לפנות לעמוד המלון - [url=http://kfarbair.com]כפר בעיר[/url] [url=http://www.kfarbair.com/contact.html][img]http://www.kfarbair.com/_images/apixel.gif[/img][/url]

Anonymous said...

Yes if the truth be known, in some moments I can say that I agree with you, but you may be inasmuch as other options.
to the article there is stationary a question as you did in the decrease publication of this solicitation www.google.com/ie?as_q=ftp clients aio ?
I noticed the catch-phrase you have in the offing not used. Or you functioning the black methods of helping of the resource. I take a week and do necheg

Anonymous said...

Hello. My wife and I bought our house about 6 months ago. It was a foreclosure and we were able to get a great deal on it. We also took advantage of the 8K tax credit so that definitely helped. We did an extensive remodeling job and now I want to refinance to cut the term to a 20 or 15 year loan. Does anyone know any good sites for mortgage information? Thanks!

Mike

Anonymous said...

רציתי לחלק אותכם בחוויה שעברתי אחרי הפיגוע בדולפינריום. במשך שנתיים לאחר הפיגוע, הרגשתי מדוכאת, חסרת שמחת חיים, עייפה ומדוכדכת. עם טיפולים רפואיים רגילים לא הצלחתי לעזו לעצמי ולכן פניתי ל- [b][url=http://www.maker.co.il/3_15707/%D7%9E%D7%90%D7%9E%D7%A8/%D7%9B%D7%9C-%D7%94%D7%A2%D7%95%D7%91%D7%93%D7%95%D7%AA-%D7%A2%D7%9C-%D7%A0%D7%A4%D7%A9-%D7%94%D7%90%D7%93%D7%9D-%D7%95%D7%A2%D7%99%D7%A1%D7%95%D7%99-%D7%A8%D7%A4%D7%95%D7%90%D7%99-(%D7%9E%D7%A1%D7%90%D7%92%60).html]עיסוי[/url][/b] רפואי אלטרנטיבי. המליצו לי על מעסה מקצועי מ-Spa-Vip.co.il - עיסוי עד הבית, אשר כולל מסאז איורוודה מצויין אשר גרם להתעוררות חושים תוך טיפול אחד בלבד. כזה שינוי בחיים לא חוויתי, אפילו אחרי טיפול עיסוי שוודי ראשון, הרגשתי נפלא, פיזרתי את השיער ויצאתי מה- [b][url=http://www.academics.co.il/Articles/Article12945.aspx]מסאג[/url][/b]' עם הרבה כוח, שמחה ומוכנה להמשיך הלאה.

עיסוי האיורוודה הוא עיסוי רפואי משולב עם מוזיקה נעימה, מוזיקה נעימה וריח של קטורת. בהתחלת העיסוי יש מגע מפנק מעל המגבת אחרי שחשפנו את הגוף מכף רגל ועד ראש. נמרח שמן חם מכף רגל ועד הראש, לאחר מכן תנועה מלטפת ועדינה מכף רגל ועד ראש ובחזרה. אחרי מספר תנועות מפנקות עוברים לצד השני של הגוף, לאחר אותם תנועות על הצד השני ישנו תהליך של לחיצות רפואיות כי הגוף התרגל למגע נעים ומרגיע ודבר זה גרם לשרירים להרפות במידה... כל זה בטיפול ספא מפנק של עיסוי עד בית הלקוח.

אישית לא האמנתי כי דברים כאלה יכולים להיות עד אשר חוויתי מסאז שוודי באיכות כזאת, המעסה המקצועי אליי הביתה עם מגע כלכך מפנק שלא האמנתי שיכול להיות. ממליצה לכל אחד ואחת אשר מעוניינים ב עיסוי, מסאג' או סתם פינוק מלטף באירוע אישי על Spa-VIP.co.il.
[img]http://spa-vip.co.il/_images/apixel.gif[/img]

Anonymous said...

top [url=http://www.c-online-casino.co.uk/]online casinos[/url] brake the latest [url=http://www.realcazinoz.com/]casino bonus[/url] manumitted no consign hand-out at the foremost [url=http://www.baywatchcasino.com/]liberated hand-out casino
[/url].