OracleʹÓõÄhintsµ÷Õû»úÖÆ
10gÊý¾Ý¿â½éÉÜ£º¿ÉÒÔʹÓøü¶àеÄoptimizer hintsÀ´¿ØÖÆÓÅ»¯ÐÐΪ¡£ÏÖÔÚÈÃÎÒÃÇ¿ìËÙ½âÎöÒ»ÏÂÕâЩǿ´óµÄÐÂhints:
spread_min_analysis
ʹÓÃÕâÒ»hint£¬Äã¿ÉÒÔºöÂÔһЩ¹ØÓÚÈçÏêϸµÄ¹ØϵÒÀÀµÍ¼·ÖÎöµÈµç×Ó±í¸ñµÄ±àÒëʱ¼äÓÅ»¯¹æÔò¡£ÆäËûµÄһЩÓÅ»¯£¬Èç´´½¨¹ýÂËÒÔÓÐÑ¡ÔñÐԵĶ¨Î»µç×Ó±í¸ñ·ÃÎʽṹ²¢ÏÞÖÆÐÞ¶©¹æÔòµÈ£¬µÃµ½Á˼ÌÐøʹÓá£
ÓÉÓÚÔÚ¹æÔòÊý·Ç³£´óµÄÇé¿öÏ£¬µç×Ó±í¸ñ·ÖÎö»áºÜ³¤¡£ÕâÒ»Ìáʾ¿ÉÒÔ°ïÖúÎÒÃǼõÉÙÓɴ˲úÉúµÄÊýÒÔ°ÙСʱ¼ÆµÄ±àÒëʱ¼ä¡£
Àý:
SELECT /*+ SPREAD_MIN_ANALYSIS */ ...
spread_no_analysis
ͨ¹ýÕâÒ»hint£¬¿ÉÒÔʹÎÞµç×Ó±í¸ñ·ÖÎö³ÉΪ¿ÉÄÜ¡£Í¬Ñù£¬Ê¹ÓÃÕâÒ»hint¿ÉÒÔºöÂÔÐÞ¶©¹æÔòºÍ¹ýÂ˲úÉú¡£Èç¹û´æÔÚÒ»µç×Ó±í¸ñ·ÖÎö£¬±àÒëʱ¼ä¿ÉÒÔ±»¼õÉÙµ½×îµÍ³Ì¶È¡£
Àý:
SELECT /*+ SPREAD_NO_ANALYSIS */ ...
use_nl_with_index
ÕâÏîhintʹCBOͨ¹ýǶÌ×Ñ»·°ÑÌض¨µÄ±í¸ñ¼ÓÈëµ½ÁíÒ»ÔʼÐС£Ö»ÓÐÔÚÒÔÏÂÇé¿ö
ÖУ¬Ëü²ÅʹÓÃÌض¨±í¸ñ×÷ΪÄÚ²¿±í¸ñ:Èç¹ûûÓÐÖ¸¶¨±êÇ©£¬CBO±ØÐë¿ÉÒÔʹÓÃһЩ±êÇ©£¬ÇÒÕâЩ±êÇ©ÖÁÉÙÓÐÒ»¸ö×÷ΪË÷Òý¼üÖµ¼ÓÈëÅжÏ;·´Ö®£¬CBO±ØÐëÄܹ»Ê¹
ÓÃÖÁÉÙÓÐÒ»¸ö×÷ΪË÷Òý¼üÖµ¼ÓÈëÅжϵıêÇ©¡£
Àý:
SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...
CARDINALITY
´Ëhint¶¨ÒåÁ˶ÔÓɲéѯ»ò²éѯ²¿·Ö·µ»ØµÄ»ùÊýµÄÆÀ¼Û¡£×¢ÒâÈç¹ûûÓж¨Òå±í¸ñ£¬»ùÊýÊÇÓÉÕû¸ö²éѯËù·µ»ØµÄ×ÜÐÐÊý¡£
Àý:
SELECT /*+ CARDINALITY ( [tablespec] card ) */
SELECTIVITY
´Ëhint¶¨ÒåÁ˶Բéѯ»ò²éѯ²¿·ÖÑ¡ÔñÐÔµÄÆÀ¼Û¡£Èç¹ûÖ»¶¨ÒåÁËÒ»¸ö±í¸ñ£¬Ñ¡ÔñÐÔÊÇÔÚËù¶¨Òå±í¸ñÀïÂú×ãËùÓе¥Ò»±í¸ñÅжϵÄÐв¿·Ö¡£Èç¹û¶¨ÒåÁËһϵÁбí¸ñ£¬Ñ¡ÔñÐÔÊÇÖ¸Ôںϲ¢ÒÔÈκÎ˳ÐòÂú×ãËùÓпÉÓÃÅжϵÄÈ«²¿±í¸ñºó£¬ËùµÃ½á¹ûÖеÄÐв¿·Ö¡£
Àý:
SELECT /*+ SELECTIVITY ( [tablespec] sel ) */
È»¶ø£¬×¢ÒâÈç¹ûhints CARDINALITY ºÍ SELECTIVITY¶¼¶¨ÒåÔÚͬÑùµÄÒ»Åú±í¸ñ£¬¶þÕ߶¼»á±»ºöÂÔ¡£
no_use_nl
Hint no_use_nlʹCBOÖ´ÐÐÑ»·Ç¶Ì×£¬Í¨¹ý°ÑÖ¸¶¨±í¸ñ×÷ΪÄÚ²¿±í¸ñ£¬°Ñÿ¸öÖ¸¶¨±í¸ñÁ¬½Óµ½ÁíÒ»ÔʼÐС£Í¨¹ýÕâÒ»hint£¬Ö»ÓÐhash joinºÍsort-merge joins»áΪָ¶¨±í¸ñËù¿¼ÂÇ¡£
Àý:
SELECT /*+ NO_USE_NL ( employees ) */ ...
no_use_merge
´ËhintʹCBOͨ¹ý°ÑÖ¸¶¨±í¸ñ×÷ΪÄÚ²¿±í¸ñµÄ·½Ê½£¬¾Ü¾øsort-merge°Ñÿ¸öÖ¸¶¨±í¸ñ¼ÓÈëµ½ÁíÒ»ÔʼÐС£
Àý:
SELECT /*+ NO_USE_MERGE ( em
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
×î½üÓÐÒ»ÈÎÎñ£¬ÐèҪʹÓÃOracleÊý¾Ý¿â£¬ÒÔÇ°¶Ô´Ë²»Ì«ÊìϤ£¬¾Í´ÓÍøÉϲéÕÒ×ÊÁÏ£¬£¨ËµÊµÔÚµÄÍøÂçÕæÊÇÌ«ºÃÁË£¬Ò²¶à¿÷ÁËÐí¶àµÄÈÈÐĵļ¼ÊõÈËÔ±£¬Ô¸Òâ°Ñ×Ô¼ºµÄ¾ÑéºÍÐĵÃÓë´ó¼Ò·ÖÏí£©²¢×ªÌùÁËÒ»¸öÌû×Ӳο¼£¬²»¹ý´ËÌû×ÓÓÐЩµØ·½Ã»ÓÐ˵Çå³þ£¬È·ÊµÈÃÎÒ·ÑÁ˺ô󾢣¬²Å²âÊԳɹ¦¡£ÏÂÃæÎÒ°ÑÎҵIJ½Öè˵Ã÷һϣº£¨»·¾³£ºWindows XP sp2 + vs ......
·½·¨Ò»£¬Ê¹ÓÃSQL*Loader
Õâ¸öÊÇÓõĽ϶àµÄ·½·¨£¬Ç°Ìá±ØÐëoracleÊý¾ÝÖÐÄ¿µÄ±íÒѾ´æÔÚ¡£
´óÌå²½ÖèÈçÏ£º
1 ½«excleÎļþÁí´æΪһ¸öÐÂÎļþ±ÈÈçÎļþÃûΪtext.txt£¬ÎļþÀàÐÍÑ¡Îı¾Îļþ£¨ÖƱí·û·Ö¸ô£©£¬ÕâÀïÑ¡ÔñÀàÐÍΪcsv£¨¶ººÅ·Ö¸ô£©Ò²ÐУ¬µ«ÊÇÔÚдºóÃæµÄcontrol. ......
1.
´íÎóÌáʾ£ºORA-12545:ÒòÄ¿±êÖ÷»ú»ò¶ÔÏó²»´æÔÚ,Á¬½Óʧ°Ü.
´íÎóÖ¢×´£ºÐ½¨µÄÊý¾Ý¿âÔÚOEM¿ØÖÆ̨ÎÞ·¨ÏÔʾ£¨sysmanÓû§µÇ½£©£¬¶ÀÁ¢Æô¶¯Ä£Ê½¿ÉÒÔÕÒµ½£¬µ«ÎÞ·¨µÇ½£¬³öÏÖÈçÉÏ´íÎó¡£
½â¾ö°ì·¨£ºÐÞ¸Äoracle°²×°Ä¿Â¼Ï£¨oracle\ora90\network\ADMIN£©µÄtnsnames.oraÎļþ£¬½«µ±Ç°Êý¾Ý¿âÌí¼Óµ½¼àÌý¶ÔÏóÖС£ÐÞ¸ÄÖ®ºó£¬¿ÉÒÔÔÚ¶ÀÁ¢Ä ......