Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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>>ÕâÒ»¸öÎĵµÖÐÓкÜÏêϸµÄ½âÉÜ, ÎÒÒ²ÕýÔÚ¿´, ¿´Íêºó»á¸üеÄ.


Ïà¹ØÎĵµ£º

1 oracle ʵÀý

1 oracle ʵÀý
°²×°--È«¾ÖÊý¾Ý¿âÃû£º¿ÉÒÔ¼ÓÀ©Õ¹Ãû£º±ÈÈçtest.com.cn£¨¶øÊý¾Ý¿âʵÀýÃûΪtest£©
Êý¾Ý¿â¿ÚÁî:ΪÊý¾Ý¿âϵͳÕÊ»§£ºsys,system,sysman,dbsnmpÌṩÃÜÂë
¸ß¼¶°²×°£ºÎªÃ¿¸öÓû§Ìṩ²»Í¬µÄÃÜÂë
sys: change_on_install
system:manager
sysman:oem_temp
dbsnmp:dbsnmp
internal: orcale
scott:tiger
demo: ......

oracle¼òµ¥Óï·¨±Ê¼Ç


 
¡¡
¡¡
DML Data manipulation language
SELECT 
SELECT [DISTINCT] *|ÁÐxx [AS] "±ðÃûxx"[,ÁÐxx "±ðÃûxx"...]
×Ö·û´®Á¬½Ó·û ||, ×Ö·û»òÈÕÆÚÀàÐ͵Ä×Ö·û´®Óõ¥ÒýºÅ’’, ÁбðÃûÓÃË«ÒýºÅ“”¡£Èç¹û±ðÃûÖÐÓпոñ¡¢ÌØÊâ×Ö·û»òÕßÒªÇóÇø·Ö´óСд£¬±ØÐëÓÃË«ÒýºÅ¡£Ä¬ÈÏÇé¿öÏÂÁбêÌâΪ´óд£¬ ......

ORACLEÎﻯÊÓͼ ÎﻯÊÓͼÈÕÖ¾½á¹¹

http://space.itpub.net/4227/viewspace-68592
ÎﻯÊÓͼµÄ¿ìËÙË¢ÐÂÒªÇó»ù±¾±ØÐ뽨Á¢ÎﻯÊÓͼÈÕÖ¾£¬ÕâÆªÎÄÕ¼òµ¥ÃèÊöÒ»ÏÂÎﻯÊÓͼÈÕÖ¾Öи÷¸ö×ֶεĺ¬ÒåºÍÓÃ;¡£
 
ÎﻯÊÓͼÈÕÖ¾µÄÃû³ÆÎªMLOG$_ºóÃæ¸ú»ù±íµÄÃû³Æ£¬Èç¹û±íÃûµÄ³¤¶È³¬¹ý20룬Ôòֻȡǰ20룬µ±½Ø¶Ìºó³öÏÖÃû³ÆÖظ´Ê±£¬Oracle»á×Ô¶¯ÔÚÎﻯÊÓͼÈÕÖ¾Ãû³ÆºóÃæ¼ ......

oracle´¥·¢redoдµÄËĸöÌõ¼þ

µ±LGWR´¦ÓÚ¿ÕÏÐ״̬ʱ£¬ËüÒÀÀµÓÚrdbms rpc messageµÈ´ýʼþ£¬´¦ÓÚÐÝÃß״̬£¬Ö±µ½ÈýÃëʱ¼äµ½£¬Èç¹ûÕâ¸öʱºòLGWR·¢ÏÖÓÐRedoÐÅÏ¢ÐèҪд³öµÄ»°£¬ÄÇôLGWR½«Ö´ÐÐдµÄ²Ù×÷£¬log file parallel writeµÈ´ýʼþ½«»á³öÏÖ¡£
¡¡¡¡×ܽáÒ»ÏÂRedoдµÄ´¥·¢Ìõ¼þ£º
¡¡¡¡1£¬Ã¿ÈýÃëÖÓ³¬Ê±(timeout)£º
µ±LGWR´¦ÓÚ¿ÕÏÐ״̬ʱ£¬ËüÒÀÀµÓÚrdbms r ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ