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¸ö×Ö½ÚÀ´ ......
¡¡
¡¡
DML Data manipulation language
SELECT
SELECT [DISTINCT] *|ÁÐxx [AS] "±ðÃûxx"[,ÁÐxx "±ðÃûxx"...]
×Ö·û´®Á¬½Ó·û ||, ×Ö·û»òÈÕÆÚÀàÐ͵Ä×Ö·û´®Óõ¥ÒýºÅ’’, ÁбðÃûÓÃË«ÒýºÅ“”¡£Èç¹û±ðÃûÖÐÓпոñ¡¢ÌØÊâ×Ö·û»òÕßÒªÇóÇø·Ö´óСд£¬±ØÐëÓÃË«ÒýºÅ¡£Ä¬ÈÏÇé¿öÏÂÁбêÌâΪ´óд£¬ ......
1£©ÊÂÎñÓëËø
µ±Ö´ÐÐÊÂÎñ²Ù×÷£¬±ÈÈç¶àÓû§Í¬Ê±½øÐвåÈë²Ù×÷ʱ£¬oracle»á¸øËùÓÐÕâЩ²åÈë²Ù×÷¼ÓÈë¶ÓÁУ¬ÏȽøÈë¶ÓÁеÄÏȽøÐвÙ×÷Ȩ£¬±ÈÈçÏÖÔÚAÓû§µÄ²Ù×÷ÊǵÚÒ»¸ö½øÈë¶ÓÁеģ¬ÄÇô´Ëʱ´Ë²Ù×÷¾Í»áÅжϲÙ×÷±íÉϵÄËøÊÇ·ñ´ò¿ª£¬Èç¹ûÊǹرյľÍ˵Ã÷ÓÐÆäËû²Ù×÷ÔÚÖ´ÐбØÐëµÈ´ýÆäÍê³É£¬Èç¹û´ò¿ªÁË£¬ÄǾͿÉÒÔÖ´Ðд˴ ......
oracle¶ÏµçºóÖØÆô³öÏÖµÄÎÊÌâÒѾ½â¾ö·½·¨
Ò»¡¢ORA-00132
ÎÊÌâÃèÊö £ºsyntax error or unresolved network name ''
µÚ1²½£º¸´ÖÆÒ»·Ýpfile²ÎÊýÎļþ£¨×¢Ò⣺oracleÖеÄpfileÖ¸µÄ¾ÍÊÇinit<sid>.oraÎļþ£©
$sqlplus '/as sysdba';
SQL> create pfile from spfile='/u01/oracle/product/10.2.0/db_1/dbs/spf ......
SET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 TRIMOUT ON TRIMSPOOL ON LINESIZE 2500 colsep | feedback off termout off pages 0
set colsep |
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set feedback on
declare cursor cur_no is
select beginno,endno from hm where 1=1;
b ......