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;
»ùÓÚÁ¿³ÌµÄ·ÖÇø
¹Ø¼üÊÇÑ¡Ôñ·ÖÇø¼ü£¬ËüÓ¦¸Ã¿ ......
µÚÒ»¿Î£º¿Í»§¶Ë
1. Sql Plus(¿Í»§¶Ë£©£¬ÃüÁîÐÐÖ±½ÓÊäÈ룺sqlplus£¬È»ºó°´ÌáʾÊäÈëÓû§Ãû£¬ÃÜÂë¡£
2. ´Ó¿ªÊ¼³ÌÐòÔËÐÐ:sqlplus£¬ÊÇͼÐΰæµÄsqlplus.
3. http://localhost:5560/isqlplus
Toad£º¹ÜÀí£¬ PlSql Developer:
µÚ¶þ¿Î£º¸ü¸ÄÓû§
1. sqlplus sys/bjsxt as sysdba
2. alter user scott account unlock;(½âËø)
......
1.»ù´¡ÈÏʶ
DBMS_RANDOM°üµÄÏêϸÐÅÏ¢£º
select text from all_source
where name = 'DBMS_RANDOM'
......
¡ö ¿ª·¢Ç°ÒªÇóÅäÖÃ
¡ö Select XML¸ñʽÊý¾Ý
¡ö Insert XML¸ñʽÊý¾Ý
¡ö Updata XML¸ñʽÊý¾Ý
¡ö Delete XML¸ñʽÊý¾Ý
¿ª·¢Ç°ÒªÇóÅäÖÃ
±ØÐë°²×°Oracle¿Í»§¶Ë
°ÑClasspathÖ¸Ïò
classes111.zip ......
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 ......