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

Oracle Óï¾äÓÅ»¯30¸ö¹æÔò(ÉÏ)

1. Ñ¡ÓÃÊʺϵÄOracleÓÅ»¯Æ÷
¡¡¡¡OracleµÄÓÅ»¯Æ÷¹²ÓÐ3ÖÖ£º
¡¡¡¡a. RULE (»ùÓÚ¹æÔò)
¡¡¡¡b. COST (»ùÓڳɱ¾)
¡¡¡¡c. CHOOSE (Ñ¡ÔñÐÔ)
¡¡¡¡ÉèÖÃȱʡµÄÓÅ»¯Æ÷£¬¿ÉÒÔͨ¹ý¶Ôinit.oraÎļþÖÐOPTIMIZER_MODE²ÎÊýµÄ¸÷ÖÖÉùÃ÷£¬ÈçRULE£¬COST£¬CHOOSE£¬ALL_ROWS£¬FIRST_ROWS . Ä㵱ȻҲÔÚSQL¾ä¼¶»òÊǻỰ(session)¼¶¶ÔÆä½øÐи²¸Ç¡£
¡¡¡¡ÎªÁËʹÓûùÓڳɱ¾µÄÓÅ»¯Æ÷(CBO£¬ Cost-Based Optimizer) £¬ Äã±ØÐë¾­³£ÔËÐÐanalyze ÃüÁÒÔÔö¼ÓÊý¾Ý¿âÖеĶÔÏóͳ¼ÆÐÅÏ¢(object statistics)µÄ׼ȷÐÔ¡£
¡¡¡¡Èç¹ûÊý¾Ý¿âµÄÓÅ»¯Æ÷ģʽÉèÖÃΪѡÔñÐÔ(CHOOSE)£¬ÄÇôʵ¼ÊµÄÓÅ»¯Æ÷ģʽ½«ºÍÊÇ·ñÔËÐйýanalyzeÃüÁîÓйء£ Èç¹ûtableÒѾ­±»analyze¹ý£¬ ÓÅ»¯Æ÷ģʽ½«×Ô¶¯³ÉΪCBO £¬ ·´Ö®£¬Êý¾Ý¿â½«²ÉÓÃRULEÐÎʽµÄÓÅ»¯Æ÷¡£
¡¡¡¡ÔÚȱʡÇé¿öÏ£¬ORACLE²ÉÓÃCHOOSEÓÅ»¯Æ÷£¬ÎªÁ˱ÜÃâÄÇЩ²»±ØÒªµÄÈ«±íɨÃè(full table scan) £¬ Äã±ØÐ뾡Á¿±ÜÃâʹÓÃCHOOSEÓÅ»¯Æ÷£¬¶øÖ±½Ó²ÉÓûùÓÚ¹æÔò»òÕß»ùÓڳɱ¾µÄÓÅ»¯Æ÷¡£
¡¡¡¡2. ·ÃÎÊTableµÄ·½Ê½Oracle ²ÉÓÃÁ½ÖÖ·ÃÎʱíÖмǼµÄ·½Ê½£º
¡¡¡¡a. È«±íɨÃè
¡¡¡¡È«±íɨÃè¾ÍÊÇ˳ÐòµØ·ÃÎʱíÖÐÿÌõ¼Ç¼¡£ ORACLE²ÉÓÃÒ»´Î¶ÁÈë¶à¸öÊý¾Ý¿é(database block)µÄ·½Ê½ÓÅ»¯È«±íɨÃè¡£
¡¡¡¡b. ͨ¹ýROWID·ÃÎʱí
¡¡¡¡Äã¿ÉÒÔ²ÉÓûùÓÚROWIDµÄ·ÃÎÊ·½Ê½Çé¿ö£¬Ìá¸ß·ÃÎʱíµÄЧÂÊ£¬ ROWID°üº¬Á˱íÖмǼµÄÎïÀíλÖÃÐÅÏ¢……ORACLE²ÉÓÃË÷Òý(INDEX)ʵÏÖÁËÊý¾ÝºÍ´æ·ÅÊý¾ÝµÄÎïÀíλÖÃ(ROWID)Ö®¼äµÄÁªÏµ¡£Í¨³£Ë÷ÒýÌṩÁË¿ìËÙ·ÃÎÊROWIDµÄ·½·¨£¬Òò´ËÄÇЩ»ùÓÚË÷ÒýÁеIJéѯ¾Í¿ÉÒԵõ½ÐÔÄÜÉϵÄÌá¸ß¡£
¡¡¡¡3. ¹²ÏíSQLÓï¾ä
¡¡¡¡ÎªÁ˲»Öظ´½âÎöÏàͬµÄSQLÓï¾ä£¬ÔÚµÚÒ»´Î½âÎöÖ®ºó£¬ ORACLE½«SQLÓï¾ä´æ·ÅÔÚÄÚ´æÖС£Õâ¿éλÓÚϵͳȫ¾ÖÇøÓòSGA(system global area)µÄ¹²Ïí³Ø(shared buffer pool)ÖеÄÄÚ´æ¿ÉÒÔ±»ËùÓеÄÊý¾Ý¿âÓû§¹²Ïí¡£ Òò´Ë£¬µ±ÄãÖ´ÐÐÒ»¸öSQLÓï¾ä(ÓÐʱ±»³ÆΪһ¸öÓαê)ʱ£¬Èç¹ûËüºÍ֮ǰµÄÖ´ÐйýµÄÓï¾äÍêÈ«Ïàͬ£¬ ORACLE¾ÍÄܺܿì»ñµÃÒѾ­±»½âÎöµÄÓï¾äÒÔ¼°×îºÃµÄÖ´Ðз¾¶¡£ ORACLEµÄÕâ¸ö¹¦ÄÜ´ó´óµØÌá¸ßÁËSQLµÄÖ´ÐÐÐÔÄܲ¢½ÚÊ¡ÁËÄÚ´æµÄʹÓá£
¡¡¡¡¿ÉϧµÄÊÇORACLEÖ»¶Ô¼òµ¥µÄ±íÌṩ¸ßËÙ»º³å(cache buffering) £¬Õâ¸ö¹¦Äܲ¢²»ÊÊÓÃÓÚ¶à±íÁ¬½Ó²éѯ¡£
¡¡¡¡Êý¾Ý¿â¹ÜÀíÔ±±ØÐëÔÚinit.oraÖÐΪÕâ¸öÇøÓòÉèÖúÏÊʵIJÎÊý£¬µ±Õâ¸öÄÚ´æÇøÓòÔ½´ó£¬¾Í¿ÉÒÔ±£Áô¸ü¶àµÄÓï¾ä£¬µ±È»±»¹²ÏíµÄ¿ÉÄÜÐÔÒ²¾ÍÔ½´óÁË¡£
¡¡¡¡µ±ÄãÏòORACLE Ìá½»Ò»¸öSQLÓï¾ä£¬ORACLE»áÊ×ÏÈÔÚÕâ¿éÄÚ´æÖвéÕÒÏàͬµÄÓï¾ä¡£
¡¡¡¡ÕâÀï


Ïà¹ØÎĵµ£º

С²¼ÀÏʦoracleÊÓƵ½²×ù±Ê¼Ç£¨¶þ£©

Oracle Process Structure
Oracle takes advantage of various types of processes:
¡¡—User process: Started at the time a database user requests connection to the Oracle server
  —Server process: Connects to the Oracle instance and is started when a user establishes a session
& ......

ORACLE Íâ¼üÔ¼ÊøÐÞ¸ÄÐÐΪ

OracleµÄÍâ¼üÓÃÀ´ÏÞÖÆ×Ó±íÖвο¼µÄ×ֶεÄÖµ£¬±ØÐëÔÚÖ÷±íÖдæÔÚ¡£¶øÇÒÔÚÖ÷±íµÄ¼Ç¼·¢Éú±ä»¯µ¼ÖÂÍâ¼ü²Î¿¼Î¨Ò»Ô¼ÊøÖµ·¢ÉúÁ˱仯ʱ£¬¶¨ÒåÁËһϵÁеĶ¯×÷¡£
ÔÚSQL92±ê×¼Öж¨ÒåÁ˼¸ÖÖÍâ¼ü¸Ä±äºó£¬ÈçºÎ´¦Àí×Ó±í¼Ç¼µÄ¶¯×÷£¬ÆäÖаüÀ¨£º
ÏÞÖÆRestrict£ºÕâÖÖ·½Ê½²»ÔÊÐí¶Ô±»²Î¿¼µÄ¼Ç¼µÄ¼üÖµÖ´Ðиüлòɾ³ýµÄ²Ù×÷£»
ÖÃΪ¿ÕSet to nu ......

oracleÎĵµµÚ¾ÅÕ´¥·¢Æ÷(1)

 µÚ¾ÅÕÂ½Ú :´¥·¢Æ÷±àÂë
    ´¥·¢Æ÷ÊÇ´æ´¢ÔÚÊý¾Ý¿â´æÖÐÒþʽµÄÔËÐлòÕß±»´¥·¢µÄÒ»¶Î³ÌÐò£¬µ±Ä³Ð©Ê¼þ·¢Éú¡£Í¨³£Çé¿öÏ£¬´¥·¢Æ÷ÖÐÖ§³Ö¿ÉÖ´ÐеÄÒ»¶Îpl/sql´úÂë¿é£¬µ±Ò»¸öinsert,update,»òÕßdeleteÃüÃû·¢ÉúÔÚÒ»¸ö±í»òÕßÊÓͼÉϵÄʱºò¡£ÆäÒ²Ö§³ÖϵͳºÍÊý¾Ý¿â»òÕß·½°¸Ê¼þ.oracleÊý¾Ý¿âÒ²Ö§³Ö´¥·¢Æ÷Öк¬Ó ......

ORACLE ORDER BYÓ÷¨×ܽá

½ñÌìÔÚ¹äÂÛ̳µÄʱºò¿´µ½shiyiwanͬѧдÁËÒ»¸öºÜ¼òµ¥µÄÓï¾ä£¬¿ÉÊÇorder byºóÃæµÄÐÎʽȴ±È½ÏÐÂÓ±£¨¶ÔÓÚÎÒÀ´ËµÅ¶£©£¬ÒÔÇ°´ÓÀ´Ã»¿´¹ýÕâÖÖÓ÷¨£¬¾ÍÏë¼ÇÏÂÀ´£¬ÕýºÃ×ܽáÒ»ÏÂORDER BYµÄ֪ʶ¡£
1¡¢ORDER BY ÖйØÓÚNULLµÄ´¦Àí
ȱʡ´¦Àí£¬OracleÔÚOrder by ʱÈÏΪnullÊÇ×î´óÖµ£¬ËùÒÔÈç¹ûÊÇASCÉýÐòÔòÅÅÔÚ×îºó£¬DESC½µÐòÔòÅÅÔÚ×îÇ°¡£
......

ORACLE³£ÓÃFAQ

 µÚÒ»²¿·Ö¡¢SQL&PL/SQL
[Q]ÔõôÑù²éѯÌØÊâ×Ö·û£¬ÈçͨÅä·û%Óë_
[A]select * from table where name like 'A_%' escape ''
[Q]ÈçºÎ²åÈëµ¥ÒýºÅµ½Êý¾Ý¿â±íÖÐ
[A]¿ÉÒÔÓÃASCIIÂë´¦Àí£¬ÆäËüÌØÊâ×Ö·ûÈç&Ò²Ò»Ñù£¬Èç
insert into t values('i'||chr(39)||'m'); -- chr(39)´ú±í×Ö·û'
»òÕßÓÃÁ½¸öµ¥ÒýºÅ±íʾһ¸ö
or ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ