ORACLEÎﻯÊÓͼ Query RewriteµÄÒ»°ãÀí½âÖ®Èý
ÔÚQuery RewriteÖдó¼Ò¿´µ½Õâ¸ö¼¼ÊõÀë²»¿ªÒ»Ñù¶«Î÷, ʵÌ廯ÊÓͼ, ¼ò³ÆMVIEW. ÕâÊÇOracleÔÚ8iÖÐÊ×ÏÈÍƳöµÄ¼¼Êõ, MVIEW³ýÁËÔÚQuery RewriteÖÐʹÓÃÍâ, »¹ÔÚMaster - Slave¸´ÖÆÖÐÓкÜÖØÒªµÄ×÷ÓÃ, ÔÚÕâ¶ùÎÒÃÇÖ÷Òª¹ØÐÄQuery RewriteÏà¹ØµÄµØ·½, OracleÔÚQuery Rewrite·½ÃæÔ½À´Ô½Ç¿ÁË, ÔÚOracle 8iÖлù±¾ÉÏÊÇText MatchµÄQuery Rewrite, ÔÚ9i/10gÖÐÓкܴóµÄ¸üÐÂÁË, »¹Ö§³ÖÒ»°ãµÄQuery Rewrite(Ö¸Text MatchÒÔÍâµÄ), Èç¿ÉÒÔÊÔÒ»ÏÂ×îºóÒ»¸öSQLÓï¾ä, ÔÚ8iÖв»ÄÜrewrite, ¶øÔÚ9iÖÐÈ´¿ÉÒÔ:
SQL> desc mvtest8
Name Null? Type
------------------------------ -------- -----------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SQL> create materialized view mv8_test
2 enable query rewrite
3 as
4 select owner,count(*) rowcnt from mvtest8
5 group by owner;
SQL> select count(*) from mvtest8 where owner='SYS';
ÔÚÓ¦ÓÃMVIEWʵÏÖQuery Rewriteʱ, ÐèÒª¶ÔMVIEWÓÐÒ»¸ö»ù±¾µÄÁ˽â, Ö÷ÒªÒªÀí½âÒÔÏÂÁ½µã:
1, MVIEWµÄÉè¼Æ, ¿ÉÒÔÂú×ã¶àÉÙSQLµÄRewrite.
2, MVIEWµÄË¢ÐÂ, MIVEWÊÇÒ»¸öÖмä±í, Êý¾Ý²»Ò»¶¨ÄÜ×îÐÂ, ¶ÔÓÚ´óÁ¿µÄÊý¾Ýˢз½·¨Ò²ÊǺÜÖØÒªµÄ.
ÆäʵÔÚÕâÁ½µãÖ®¼äÊÇÓÐЩ³åÍ»µÄ, ҪʵÏÖµÚÒ»µã, ¿ÉÄܸ´ÔÓµÄMVIEW¶¨ÒåÓÐÀûÓÚÌá¸ßQuery RewriteºóµÄÖ´ÐÐЧÂÊ, µ«¸´ÔÓµÄMVIEWÿ´Îˢж¼¿ÉÄÜÒª³ÃèÈ«²¿¼Ç¼, ¶ÔÓÚMVIEWË¢ÐÂ, ÔÚÓÐMVIEW LOGÓÐÇé¿öÏÂ, MVIEW¿ÉÒÔʵÏÖÔöÁ¿Ë¢ÐµÄ, Ò»°ãÀ´ËµÒªÊµÏÖÔöÁ¿Ë¢ÐÂÐèÒªÂú×ãÏÂÃæÕâÑùµÄÌõ¼þ:
1, MVIEWµÄSQLÖв»ÄÜ°üº¯·µ»ØÖµ²»È·¶¨µÄº¯Êý, Èçsysdate, rown
Ïà¹ØÎĵµ£º
Êý¾Ý·ÖÇøÊÇÕë¶Ô±íµÄ¡£
ΪʲôҪ½«±í·ÖÇø£¿
·ÖÇø±íͨ³£´æ´¢ÔÚ½ÏСµÄÎļþ£¨<2GB£©ÖУ¬Ò×ÓÚ±¸·Ý¡£
Ó²¼þ¹ÊÕÏʱ£¬Ö»ÓÐÊý¾Ý¿âµÄһС²¿·Ö»áÊܵ½Ó°Ïì¡£
Ò×ÓÚÊý¾Ý·ÖÎö¡£
¿ÉÒÔÔÚÆäËû·ÖÇø¼ÌÐøÌṩ·þÎñµÄͬʱά»¤ÐèÒª½øÐÐά»¤µÄ±íµÄ·ÖÇø¡£alter table sale drop partition q1_1999;
»ùÓÚÁ¿³ÌµÄ·ÖÇø
¹Ø¼üÊÇÑ¡Ôñ·ÖÇø¼ü£¬ËüÓ¦¸Ã¿ ......
µ±ÄãÔÚÊý¾Ý¿âÖд´½¨Êý¾Ý±íµÄʱºò£¬ÄãÐèÒª¶¨Òå±íÖÐËùÓÐ×ֶεÄÀàÐÍ¡£ORACLEÓÐÐí¶àÖÖÊý¾ÝÀàÐÍÒÔÂú×ãÄãµÄÐèÒª¡£Êý¾ÝÀàÐÍ´óÔ¼·ÖΪ£ºcharacter, number, date, LOB, ºÍRAWµÈÀàÐÍ¡£ËäÈ»ORACLE8iÒ²ÔÊÐíÄã×Ô¶¨ÒåÊý¾ÝÀàÐÍ£¬µ«ÊÇËüÃÇÊÇ×î»ù±¾µÄÊý¾ÝÀàÐÍ¡£ÔÚÏÂÃæµÄÎÄÕÂÖÐÄ㽫Á˽⵽ËûÃÇÔÚoracle ÖеÄÓ÷¨¡¢ÏÞÖÆÒÔ¼°ÔÊÐíÖµ¡£
¡¡¡¡
¡¡¡¡ ......
¡¡
¡¡
DML Data manipulation language
SELECT
SELECT [DISTINCT] *|ÁÐxx [AS] "±ðÃûxx"[,ÁÐxx "±ðÃûxx"...]
×Ö·û´®Á¬½Ó·û ||, ×Ö·û»òÈÕÆÚÀàÐ͵Ä×Ö·û´®Óõ¥ÒýºÅ’’, ÁбðÃûÓÃË«ÒýºÅ“”¡£Èç¹û±ðÃûÖÐÓпոñ¡¢ÌØÊâ×Ö·û»òÕßÒªÇóÇø·Ö´óСд£¬±ØÐëÓÃË«ÒýºÅ¡£Ä¬ÈÏÇé¿öÏÂÁбêÌâΪ´óд£¬ ......
http://space.itpub.net/4227/viewspace-68592
ÎﻯÊÓͼµÄ¿ìËÙË¢ÐÂÒªÇó»ù±¾±ØÐ뽨Á¢ÎﻯÊÓͼÈÕÖ¾£¬ÕâƪÎÄÕ¼òµ¥ÃèÊöÒ»ÏÂÎﻯÊÓͼÈÕÖ¾Öи÷¸ö×ֶεĺ¬ÒåºÍÓÃ;¡£
ÎﻯÊÓͼÈÕÖ¾µÄÃû³ÆΪMLOG$_ºóÃæ¸ú»ù±íµÄÃû³Æ£¬Èç¹û±íÃûµÄ³¤¶È³¬¹ý20룬Ôòֻȡǰ20룬µ±½Ø¶Ìºó³öÏÖÃû³ÆÖظ´Ê±£¬Oracle»á×Ô¶¯ÔÚÎﻯÊÓͼÈÕÖ¾Ãû³ÆºóÃæ¼ ......
oracleµÄÕýÔò±í´ïʽ(regular expression)¼òµ¥½éÉÜ
Ä¿Ç°£¬ÕýÔò±í´ïʽÒѾÔںܶàÈí¼þÖеõ½¹ã·ºµÄÓ¦Ó㬰üÀ¨*nix£¨Linux, UnixµÈ£©£¬HPµÈ²Ù×÷ϵͳ£¬PHP£¬C#£¬JavaµÈ¿ª·¢»·¾³¡£
Oracle 10gÕýÔò±í´ïʽÌá¸ßÁËSQLÁé»îÐÔ¡£ÓÐЧµÄ½â¾öÁËÊý¾ÝÓÐЧÐÔ£¬ Öظ´´ÊµÄ±æÈÏ, Î޹صĿհ׼ì²â£¬»òÕß·Ö½â¶à¸öÕýÔò×é³É
µÄ×Ö·û´®µÈÎÊÌâ¡£
Orac ......