ORACLEÎﻯÊÓͼ Query RewriteµÄÒ»°ãÀí½âÖ®Îå
¶ÔÓÚÒ»¸ö¸ø¶¨µÄSQL, ºÍÏÖÓеÄMVIEW, Õâ¸öSQL¿ÉÄܱ»Rewrite, Ò²¿ÉÄܲ»Äܱ»Rewrite, ÈçºÎÕÒ³öÕâÆäÖеÄÔÒòÄØ, ¿¿¾Ñé¿ÉÒÔ½â¾öһЩÎÊÌâ, ²»¹ýËù»¨µÄʱ¼ä¾ÍÒª³¤Ò»µãÁË. ÆäʵÔÚOracleÖÐÌṩÁËÒ»¸öº¯Êý¿ÉÒÔÓÃÓÚ½âÊÍΪʲôij¸öSQL²»Äܱ»ÖØд, Õâ¸ö¹ý³ÌλÓÚdbms_mviewÕâ¸ö°üÖÐ, ¹ý³ÌÃûΪexplain_rewrite, ÓÐÁËÕâ¸ö¹¤¾ß, ¿ÉÒÔ¿ìËÙµØÕÒ³öΪʲô²»Äܱ»ÖØд, ҪʹÓÃÕâ¸ö¹ý³Ì, ÄãÐèÒªÊÂÏÈ´´½¨Ò»¸ö±íREWRITE_TABLE, ¿ÉÒÔͨ¹ý@?/rdbms/admin/utlxrw.sqlÀ´´´½¨.
CREATE TABLE REWRITE_TABLE
(
statement_id VARCHAR2(30), -- id for the query
mv_owner VARCHAR2(30), -- owner of the MV
mv_name VARCHAR2(30), -- name of the MV
sequence INTEGER, -- sequence no of the error msg
query VARCHAR2(2000),-- user query
message VARCHAR2(512), -- EXPLAIN_REWRITE error msg
pass VARCHAR2(3), -- rewrite pass no
flags INTEGER, -- associated flags
reserved1 INTEGER, -- currently not used
reserved2 VARCHAR2(10) -- currently not used
)
ÓÐÁËÕâ¸
Ïà¹ØÎĵµ£º
¹ØÓÚOracleÖÐ×Ö·û´®µÄ˵Ã÷
×Ö·û´®
OracleÖÐÓÐËÄÖÖ»ù±¾µÄ×Ö·û´®ÀàÐÍ,·Ö±ðÊÇchar¡¢varchar2¡¢ncharºÍnvarchar2¡£ÔÚOracleÖУ¬ËùÓ䮶¼ÒÔͬÑùµÄ¸ñʽ´æ´¢¡£ÔÚÊý¾Ý¿éÓÐÒ»¸ö1~3×ֽڵij¤¶È×ֶΣ¬Æäºó²ÅÊÇÊý¾Ý£¬Èç¹ûÊý¾ÝλNULL£¬³¤¶È×Ö¶ÎÔò±íʾΪһ¸öµ¥×Ö½ÚÖµ0xFF.
Èç¹û´®µÄ³¤¶ÈСÓÚ»òµÈÓÚ250£¨0x01~0xFA),Oracle»áʹÓÃ1¸ö×Ö½ÚÀ´ ......
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: ......
¡¡
¡¡
DML Data manipulation language
SELECT
SELECT [DISTINCT] *|ÁÐxx [AS] "±ðÃûxx"[,ÁÐxx "±ðÃûxx"...]
×Ö·û´®Á¬½Ó·û ||, ×Ö·û»òÈÕÆÚÀàÐ͵Ä×Ö·û´®Óõ¥ÒýºÅ’’, ÁбðÃûÓÃË«ÒýºÅ“”¡£Èç¹û±ðÃûÖÐÓпոñ¡¢ÌØÊâ×Ö·û»òÕßÒªÇóÇø·Ö´óСд£¬±ØÐëÓÃË«ÒýºÅ¡£Ä¬ÈÏÇé¿öÏÂÁбêÌâΪ´óд£¬ ......
itpubÂÛ̳ÖÐÔøÓÐÐÖµÜ˵¹ýmaterialized viewÊÇÒԿռ任ʱ¼ä£¬ÎÒÈÏΪ·Ç³£ÌùÇС£ÎﻯÊÓͼʵ¼Ê¾ÍÊÇ»ùÓÚ²éѯµÄÊý¾Ý¿½±´£¬ÆäÐÎʽ
¼òµ¥Àí½âµÄ»°¿ÉÒÔ¿´³ÉÊÇcreate table table_name as select * from table¡£
ºÇºÇ£¬ÈËÃÇ×ÜÊÇÏ£ÍûÊÂÇéÔ½¼òµ¥Ô½ºÃÂÓÚÊǵ±°³¿´µ½Õâ¸ö¶«Î÷Õâô¼òµ¥£¬Í·ÄÔÒ»·¢ÈÈÒ²ÆȲ»¼°´ý³åÁ˹ýÔÆ£¬ÏÂÃæÎҾ;ßÌåÃè ......
ÔÚQuery RewriteÖдó¼Ò¿´µ½Õâ¸ö¼¼ÊõÀë²»¿ªÒ»Ñù¶«Î÷, ʵÌ廯ÊÓͼ, ¼ò³ÆMVIEW. ÕâÊÇOracleÔÚ8iÖÐÊ×ÏÈÍƳöµÄ¼¼Êõ, MVIEW³ýÁËÔÚQuery RewriteÖÐʹÓÃÍâ, »¹ÔÚMaster - Slave¸´ÖÆÖÐÓкÜÖØÒªµÄ×÷ÓÃ, ÔÚÕâ¶ùÎÒÃÇÖ÷Òª¹ØÐÄQuery RewriteÏà¹ØµÄµØ·½, OracleÔÚQuery Rewrite·½ÃæÔ½À´Ô½Ç¿ÁË, ÔÚOracle 8iÖлù±¾ÉÏÊÇText Ma ......