Oracle SQLµÄÓ²½âÎöºÍÈí½âÎö
˵µ½Èí½âÎö£¨soft prase£©ºÍÓ²½âÎö£¨hard prase£©£¬¾Í²»Äܲ»ËµÒ»ÏÂOracle¶ÔsqlµÄ´¦Àí¹ý³Ì¡£µ±Äã·¢³öÒ»ÌõsqlÓï¾ä½»¸¶Oracle£¬ÔÚÖ´ÐкͻñÈ¡½á¹ûǰ£¬Oracle¶Ô´Ësql½«½øÐм¸¸ö²½ÖèµÄ´¦Àí¹ý³Ì£º
1¡¢Óï·¨¼ì²é£¨syntax check£©
¼ì²é´ËsqlµÄƴдÊÇ·ñÓï·¨¡£
2¡¢ÓïÒå¼ì²é£¨semantic check£©
ÖîÈç¼ì²ésqlÓï¾äÖеķÃÎʶÔÏóÊÇ·ñ´æÔÚ¼°¸ÃÓû§ÊÇ·ñ¾ß±¸ÏàÓ¦µÄȨÏÞ¡£
3¡¢¶ÔsqlÓï¾ä½øÐнâÎö£¨prase£©
ÀûÓÃÄÚ²¿Ëã·¨¶Ôsql½øÐнâÎö£¬Éú³É½âÎöÊ÷£¨parse tree£©¼°Ö´Ðмƻ®£¨execution plan£©¡£
4¡¢Ö´ÐÐsql£¬·µ»Ø½á¹û£¨execute and return£©
ÆäÖУ¬Èí¡¢Ó²½âÎö¾Í·¢ÉúÔÚµÚÈý¸ö¹ý³ÌÀï¡£
OracleÀûÓÃÄÚ²¿µÄhashËã·¨À´È¡µÃ¸ÃsqlµÄhashÖµ£¬È»ºóÔÚlibrary cacheÀï²éÕÒÊÇ·ñ´æÔÚ¸ÃhashÖµ£»
¼ÙÉè´æÔÚ£¬Ôò½«´ËsqlÓëcacheÖеĽøÐбȽϣ»
¼ÙÉè“Ïàͬ”£¬¾Í½«ÀûÓÃÒÑÓеĽâÎöÊ÷ÓëÖ´Ðмƻ®£¬¶øÊ¡ÂÔÁËÓÅ»¯Æ÷µÄÏà¹Ø¹¤×÷¡£ÕâÒ²¾ÍÊÇÈí½âÎöµÄ¹ý³Ì¡£
³ÏÈ»£¬Èç¹ûÉÏÃæµÄ2¸ö¼ÙÉèÖÐÈÎÓÐÒ»¸ö²»³ÉÁ¢£¬ÄÇôÓÅ»¯Æ÷¶¼½«½øÐд´½¨½âÎöÊ÷¡¢Éú³ÉÖ´Ðмƻ®µÄ¶¯×÷¡£Õâ¸ö¹ý³Ì¾Í½ÐÓ²½âÎö¡£
´´½¨½âÎöÊ÷¡¢Éú³ÉÖ´Ðмƻ®¶ÔÓÚsqlµÄÖ´ÐÐÀ´ËµÊÇ¿ªÏú°º¹óµÄ¶¯×÷£¬ËùÒÔ£¬Ó¦µ±¼«Á¦±ÜÃâÓ²½âÎö£¬¾¡Á¿Ê¹ÓÃÈí½âÎö¡£
Õâ¾ÍÊÇÔںܶàÏîÄ¿ÖУ¬³«µ¼¿ª·¢Éè¼ÆÈËÔ±¶Ô¹¦ÄÜÏàͬµÄ´úÂëҪŬÁ¦±£³Ö´úÂëµÄÒ»ÖÂÐÔ£¬ÒÔ¼°ÒªÔÚ³ÌÐòÖжàʹÓð󶨱äÁ¿µÄÔÒò¡£
/****************************************************/
´ó¼Ò¶¼ÔÚ˵ÔÚSqlÖÐʹÓÃÁËBind Var£¨°ó¶¨±äÁ¿£©»áÌá¸ß²»ÉÙÐÔÄÜ£¬ÄÇËûµ½µ×ÊÇÈçºÎÌá¸ßÐÔÄܵÄÄØ£¿
ʹÓÃÁËBind VarÄÜÌá¸ßÐÔÄÜÖ÷ÒªÊÇÒòΪÕâÑù×ö¿ÉÒÔ¾¡Á¿±ÜÃâ²»±ØÒªµÄÓ²·ÖÎö£¨Hard Parse£©¶ø½ÚÔ¼ÁËʱ¼ä£¬Í¬Ê±½ÚÔ¼ÁË´óÁ¿µÄCPU×ÊÔ´¡£
µ±Ò»¸öClientÌá½»Ò»ÌõSql¸øOracleºó£¬Oracle Ê×ÏÈ»á¶ÔÆä½øÐнâÎö£¨Parse£©£¬È»ºó½«½âÎö½á¹ûÌá½»¸øÓÅ»¯Æ÷£¨Optimiser£©À´½øÐÐÓÅ»¯¶øÈ¡µÃOracleÈÏΪµÄ×î
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
ת×ÔijµØ,¶Ô×÷ÕߺÜÀ¢¾Î- -!²»ÏþµÃµØÖ·ÁË..
ORACLE¶à±í²éѯÓÅ»¯
ÕâÀïÌṩµÄÊÇÖ´ÐÐÐÔÄܵÄÓÅ»¯,¶ø²»ÊǺǫ́Êý¾Ý¿âÓÅ»¯Æ÷×ÊÁÏ:
²Î¿¼Êý¾Ý¿â¿ª·¢ÐÔÄÜ·½ÃæµÄ¸÷ÖÖÎÊÌâ,ÊÕ¼¯ÁËһЩÓÅ»¯·½°¸Í³¼ÆÈçÏÂ(µ±È»,ÏóË÷ÒýµÈÓÅ»¯·½°¸Ì«¹ý¼òµ¥¾Í²»ÁÐÈëÁË,ºÙºÙ):
Ö´Ðз¾¶:ORACLEµÄÕâ¸ö¹¦ÄÜ´ó´óµØÌá¸ßÁËSQLµÄÖ´ÐÐÐÔÄܲ¢½ÚÊ¡ÁËÄÚ´æµÄʹÓÃ: ......
1.¸ü¸Ä¹éµµÂ·¾¶
ÔÚORACLE10GÖУ¬Ä¬ÈϵĹ鵵·¾¶Îª$ORACLE_BASE/flash_recovery_area¡£¶ÔÓÚÕâ¸ö·¾¶£¬
ORACLEÓÐÒ»¸öÏÞÖÆ£¬¾ÍÊÇĬÈÏÖ»ÄÜÓÐ2GµÄ¿Õ¼ä¸ø¹éµµÈÕ־ʹÓ㬿ÉÒÔʹÓÃÏÂÃæÁ½¸öSQLÓï¾äÈ¥²é¿´ËüµÄÏÞÖÆ
1. select * from v$recovery_file_dest;
sql >show parameter db_recovery_file_dest(Õâ¸ö¸üÓѺÃÖ±¹ÛһЩ)
µ± ......
1.½¨±í
create table temp(rq varchar(10),shengfu nchar(1))
2.²åÈëÊý¾Ý
insert into temp values('2005-05-09','ʤ')
insert into temp values('2005-05-09','ʤ')
insert into temp values('2005-05-09','¸º')
insert into temp values('2005-05-09','¸º')
insert into temp values('2005-05-10','ʤ')
insert i ......
º¯Êý£º
XMLPARSE£º½âÎö×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý£¬²úÉúXML
XMLSERIALIZE£º½«XMLֵת»»Îª×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý
XMLVALIDATE£º¸ù¾ÝXML schemaУÑéXMLÖµµÄÓÐЧÐÔ
XMLEXISTS£º¼ì²âXQueryÊÇ·ñ·µ»Ø½á¹û£¨ÈçÓÉÒ»¸ö»ò¶à¸öÏîËù×é³ÉµÄÐòÁУ©
XMLQUERY£ºÖ´ÐÐÒ»¸öXQuery²¢ÒÔÒ»¸öÐòÁзµ»Ø½á¹û
XMLTABLE£ºÖ´ÐÐÒ»¸öXQuery£¬ÒÔ¹ØÏ ......