ORACLEÎﻯÊÓͼ Query RewriteµÄÒ»°ãÀí½âÖ®ËÄ
¿ÉÒÔ¿´µ½MVIEWÔÚQuery RewriteÖеÄÖØÒªÐÔ, ÒªÔÚʵ¼ÊÓ¦ÓÃÖÐʹÓÃ, ¾ÍµÃÖªµÀËüµÄºÜ¶à·½Ãæ, ÆäÖÐË¢ÐÂÊÇ×îÖ÷ÒªµÄ:
1, MVIEWÈÕÖ¾µÄ½¨Á¢
2, »ã×ÜÐ͵ÄMIVEWµÄË¢ÐÂ
3, JOINÀàÐ͵ÄMVIEWµÄË¢ÐÂ
4, ¸ü¸´ÔÓµÄMVIEWµÄË¢ÐÂ
5, ·ÖÇøÊ±µÄMVIEWµÄË¢ÐÂ
ÔÚÕâ¶ùÎÒÃÇÖ÷ÒªÌÖÂÛµÄÊÇÈçºÎʵÏÖFastË¢ÐÂ, ·ñÔòûÓжàÉÙÒâÒéµÄ. ÎÒÃÇÒ»µãÒ»µãÀ´¿´:
1, ҪʵÏÖÔöÁ¿Ë¢ÐÂ, ±ØÐëÔÚMVIEWÒýµÄµÄ±íÉÏ´´½¨MVIEW Log, ÎÒÃÇÖ÷ÒªÀ´ËµÒ»Ï¼¸¸öÑ¡Ïî
WITH ROWID/PRIMARY KEY : ÔÚMVIEW LogÖмǼROWID»òÖ÷¼üÒÔ·´Ó³¸Ä¸ü¹ýµÄ¼Ç¼, ¶ÔÓÚÒ»°ã±íÎÒÍÆ¼öÓÃWITH ROWID, ¶ÔÓÚIOT, ÔòÇëÓÃPRIMARY KEY.
(column list): ΪÁËÈÃMVIEW LOG±äµÃСһµãÄã¿ÉÒÔÖ»°üÀ¨½øÔÚMVIEWµÄSQLÖÐÒýÓõÄ×Ö¶Î, OracleÓñíÀ´ÊµÏÖMVIEW LOGÔÚ¸üÐÂÆµ·±µÄ±íÉÏ, MVIEW LOG¿ÉÄÜ»á±äµÃºÜ´ó. ÔÚÖ¸¶¨ÁËWITH PRIMARY KEYʱ, Ö÷¼üµÄÁÐÒѾ°üÀ¨ÁË, Òò´ËÔÚcolumn listÖоͲ»ÒªÔÙд½øÈ¥ÁË.
WITH SEQUENCE: ÓÃÓڼǼÐ޸ķ¢ÉúµÄ˳Ðò, Èç¹ûûÓжԻù±íµÄDELETE²Ù×÷Ôò¿ÉÒÔ²»ÓüÓÕâ¸öÑ¡Ïî.
INCLUDING NEW VALUES: Ö÷ÒªÓÃÓÚÔÚ»ã×ÜÐ͵ÄMVIEWʱ, ͬʱ¼Ç¼×ֶεľÉÖµºÍÐÂÖµÒÔʵÏÖ¿ìËÙË¢ÐÂ, ĬÈÏÊÇEXCLUDING NEW VALUES, ÕâʱÈçÐèÒªµ±Ç°Öµ, OracleÐèÒªµ½±íÖÐÈ¥²éѯ.
2, »ã×ÜÐ͵ÄMIVEWµÄË¢ÐÂ, OracleÖ§³Ö·Ö×麯Êý¼°Ò»²¿·ÝµÄ·ÖÎöº¯ÊýµÄÔöÁ¿Ë¢ÐÂ.
½«count(*)¼Óµ½MVIEWµÄSQL, Èç¹ûÓÐSUM(*)ºÍCOUNT(*)´æÔھͿÉÒÔʵÏÖÔöÁ¿Ë¢ÐÂ.
3, JOINÀàÐ͵ÄMVIEWµÄË¢ÐÂ
ÔÚJoinµÄMVIEWʱ½«»ù±íµÄROWID¶¼¼Óµ½MVIEWÖÐ, ÈçTA(A,B)ºÍTB(C,D), ÔòMVIEWʱ¿ÉÒÔдΪ, SELECT TA.ROWID TA_ROWID,TB.ROWID TB_ROWID, <<collist>> from TA,TB WHERE ...
4, ¸´ÔÓÀàÐ͵ÄMVIEWµÄË¢ÐÂ
¿ÉÒÔ¿¼ÂÇת»»³É¼¶ÁªµÄMVIEW, ÈçÏȽ¨JOINµÄMVIEW, ÔÙ½¨SUMMARYµÄMVIEW
5, ·ÖÇøÊ±µÄMVIEWµÄË¢ÐÂ
ÓÃDBMS_MVIEW.MARKER(±íÃû)À´»ñµÃ·ÖÇøID, ²¢¼Óµ½MIVEWµÄSELECTÁбíÖÐ, ÕâÑù¿ÉÒÔʵÏÖ·ÖÇø±íµÄÔöÁ¿Ë¢ÐÂ, Çë×ÔÒÑ×öʵÑéÀ´²âÊÔ.
¹ØÓÚÕⲿ·ÝµÄ½âÊÍ, ÔÚ<<Data Warehouse Guide>>ÕâÒ»¸öÎĵµÖÐÓкÜÏêϸµÄ½âÉÜ, ÎÒÒ²ÕýÔÚ¿´, ¿´Íêºó»á¸üеÄ.
Ïà¹ØÎĵµ£º
¹ØÓÚOracleÖÐ×Ö·û´®µÄ˵Ã÷
×Ö·û´®
OracleÖÐÓÐËÄÖÖ»ù±¾µÄ×Ö·û´®ÀàÐÍ,·Ö±ðÊÇchar¡¢varchar2¡¢ncharºÍnvarchar2¡£ÔÚOracleÖУ¬ËùÓд®¶¼ÒÔͬÑùµÄ¸ñʽ´æ´¢¡£ÔÚÊý¾Ý¿éÓÐÒ»¸ö1~3×ֽڵij¤¶È×ֶΣ¬Æäºó²ÅÊÇÊý¾Ý£¬Èç¹ûÊý¾ÝλNULL£¬³¤¶È×Ö¶ÎÔò±íʾΪһ¸öµ¥×Ö½ÚÖµ0xFF.
Èç¹û´®µÄ³¤¶ÈСÓÚ»òµÈÓÚ250£¨0x01~0xFA),Oracle»áʹÓÃ1¸ö×Ö½ÚÀ´ ......
ÎÒÃǾ³£Ì¸¼°OracleµÄȨÏÞ£¬±ÈÈçConnect£¬DBA£¬resource£¬ sysdba, select any
table,
select_catelog_roleÕâÑùһЩȨÏÞ£¬ÈÃÎÒÃÇÑÛ»¨çÔÂҵģ¬ÄÇôÈçºÎÀ´Çø·ÖÕâЩÖÖÀà·±¶àµÄprivilegeÖв»ÖÁÓÚÑÛÃÔÀëÄØ£¬Õâ¸öÎÄÕ½«
»á¶ÔÄãÔÚÕâ·½ÃæµÄÁ˽âÆðµ½°ïÖú¡£
ÒÔÉÏÎÒÃÇ¿´µ½µÄÕâЩȨÏÞ£¬¶¼ÊÇ¿ÉÒÔͨ¹ýg ......
http://space.itpub.net/4227/viewspace-68592
ÎﻯÊÓͼµÄ¿ìËÙË¢ÐÂÒªÇó»ù±¾±ØÐ뽨Á¢ÎﻯÊÓͼÈÕÖ¾£¬ÕâÆªÎÄÕ¼òµ¥ÃèÊöÒ»ÏÂÎﻯÊÓͼÈÕÖ¾Öи÷¸ö×ֶεĺ¬ÒåºÍÓÃ;¡£
ÎﻯÊÓͼÈÕÖ¾µÄÃû³ÆÎªMLOG$_ºóÃæ¸ú»ù±íµÄÃû³Æ£¬Èç¹û±íÃûµÄ³¤¶È³¬¹ý20룬Ôòֻȡǰ20룬µ±½Ø¶Ìºó³öÏÖÃû³ÆÖظ´Ê±£¬Oracle»á×Ô¶¯ÔÚÎﻯÊÓͼÈÕÖ¾Ãû³ÆºóÃæ¼ ......
itpubÂÛ̳ÖÐÔøÓÐÐÖµÜ˵¹ýmaterialized viewÊÇÒԿռ任ʱ¼ä£¬ÎÒÈÏΪ·Ç³£ÌùÇС£ÎﻯÊÓͼʵ¼Ê¾ÍÊÇ»ùÓÚ²éѯµÄÊý¾Ý¿½±´£¬ÆäÐÎʽ
¼òµ¥Àí½âµÄ»°¿ÉÒÔ¿´³ÉÊÇcreate table table_name as select * from table¡£
ºÇºÇ£¬ÈËÃÇ×ÜÊÇÏ£ÍûÊÂÇéÔ½¼òµ¥Ô½ºÃÂÓÚÊǵ±°³¿´µ½Õâ¸ö¶«Î÷Õâô¼òµ¥£¬Í·ÄÔÒ»·¢ÈÈÒ²ÆÈ²»¼°´ý³åÁ˹ýÔÆ£¬ÏÂÃæÎҾ;ßÌåÃè ......
http://www.anysql.net/oracle/query_rewrite_01.html
Query Rewrite ÔÚÊý¾Ý²Ö¿âÊÇÊÇÒ»¸ö·Ç³£ÓÐÓõļ¼Êõ, TomÔÚ<<Effective Oracle by Design>>Ò»ÊéÖн«ÊµÌ廯ÊÓͼ(MView)³ÆÎªÊÇÊý¾Ý²Ö¿âµÄË÷Òý, ÕâÊÇÔÙÌùÇв»¹ýµÄÁË, ÔÚOLTPÖе±SELECTÓï¾äµÄËùÓеÄ×ֶζ¼ÔÚË÷ÒýÖÐʱ, Oracle¿ÉÒÔ²»´Ó±í¶ÁÊý¾Ý, ¶øÖ± ......