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
Ïà¹ØÎĵµ£º
1.
´íÎóÌáʾ£ºORA-12545:ÒòÄ¿±êÖ÷»ú»ò¶ÔÏó²»´æÔÚ,Á¬½Óʧ°Ü.
´íÎóÖ¢×´£ºÐ½¨µÄÊý¾Ý¿âÔÚOEM¿ØÖÆ̨ÎÞ·¨ÏÔʾ£¨sysmanÓû§µÇ½£©£¬¶ÀÁ¢Æô¶¯Ä£Ê½¿ÉÒÔÕÒµ½£¬µ«ÎÞ·¨µÇ½£¬³öÏÖÈçÉÏ´íÎó¡£
½â¾ö°ì·¨£ºÐÞ¸Äoracle°²×°Ä¿Â¼Ï£¨oracle\ora90\network\ADMIN£©µÄtnsnames.oraÎļþ£¬½«µ±Ç°Êý¾Ý¿âÌí¼Óµ½¼àÌý¶ÔÏóÖС£ÐÞ¸ÄÖ®ºó£¬¿ÉÒÔÔÚ¶ÀÁ¢Ä ......
Ò»¡¢ÔÚPLSQLÖд´½¨±í£º
create table HWQY.TEST
(
CARNO VARCHAR2(30),
CARINFOID NUMBER
)
¶þ¡¢ÔÚPLSQLÖд´½¨´æ´¢¹ý³Ì£º
create or replace procedure pro_test
AS
carinfo_id number;
BEGIN
select s_CarInfoID.nextval into carinfo_id
from dual;
insert into test(test ......
oracleµÄÌåϵºÜÅÓ´ó£¬ÒªÑ§Ï°Ëü£¬Ê×ÏÈÒªÁ˽âoracleµÄ¿ò¼Ü¡£ÔÚÕâÀ¼òÒªµÄ½²Ò»ÏÂoracleµÄ¼Ü¹¹£¬ÈóõѧÕ߶ÔoracleÓÐÒ»¸öÕûÌåµÄÈÏʶ¡£
¡¡¡¡1¡¢ÎïÀí½á¹¹£¨ÓÉ¿ØÖÆÎļþ¡¢Êý¾ÝÎļþ¡¢ÖØ×öÈÕÖ¾Îļþ¡¢²ÎÊýÎļþ¡¢¹éµµÎļþ¡¢ÃÜÂëÎļþ×é³É£©
¡¡¡¡¿ØÖÆÎļþ£º°üº¬Î¬»¤ºÍÑéÖ¤Êý¾Ý¿âÍêÕûÐԵıØÒªÐÅÏ¢¡¢ÀýÈ磬¿ØÖÆÎļþÓÃÓÚʶ±ðÊý¾ÝÎļ ......
¡¶1¡·DDLÓï¾ä(Êý¾Ý¶¨ÒåÓïÑÔ) Data Define Language
create
alter
drop
truncate ¿ªÍ·µÄÓï¾ä truncate table <±íÃû>
Ìصã:<1>½¨Á¢ºÍÐÞ¸ÄÊý¾Ý¶ÔÏó
&nb ......