ORACLEÖÐin ºÍ existsÇø±ð
INºÍEXISTSÇø±ð
in ÊÇ°ÑÍâ±íºÍÄÚ±í×÷hash join£¬¶øexistsÊǶÔÍâ±í×÷loop£¬Ã¿´ÎloopÔÙ¶ÔÄÚ±í½øÐвéѯ¡£
Ò»Ö±ÒÔÀ´ÈÏΪexists±ÈinЧÂʸߵÄ˵·¨ÊDz»×¼È·µÄ¡£
Èç¹û²éѯµÄÁ½¸ö±í´óСÏ൱£¬ÄÇôÓÃinºÍexists²î±ð²»´ó¡£
Èç¹ûÁ½¸ö±íÖÐÒ»¸ö½ÏС£¬Ò»¸öÊÇ´ó±í£¬Ôò×Ó²éѯ±í´óµÄÓÃexists£¬×Ó²éѯ±íСµÄÓÃin£º
ÀýÈ磺±íA£¨Ð¡±í£©£¬±íB£¨´ó±í£©
1£º
select * from A where cc in (select cc from B)
ЧÂʵͣ¬Óõ½ÁËA±íÉÏccÁеÄË÷Òý£»
select * from A where exists(select cc from B where cc=A.cc)
ЧÂʸߣ¬Óõ½ÁËB±íÉÏccÁеÄË÷Òý¡£
Ïà·´µÄ
2£º
select * from B where cc in (select cc from A)
ЧÂʸߣ¬Óõ½ÁËB±íÉÏccÁеÄË÷Òý£»
select * from B where exists(select cc from A where cc=B.cc)
ЧÂʵͣ¬Óõ½ÁËA±íÉÏccÁеÄË÷Òý¡£
´øinµÄ¹ØÁª×Ó²éѯÊǶàÓàµÄ£¬ÒòΪin×Ó¾äºÍ×Ó²éѯÖÐÏà¹ØµÄ²Ù×÷µÄ¹¦ÄÜÊÇÒ»ÑùµÄ¡£È磺
select staff_name from staff_member where staff_id in
(select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);
Ϊ·Ç¹ØÁª×Ó²éѯָ¶¨exists×Ó¾äÊDz»Êʵ±µÄ£¬ÒòΪÕâÑù»á²úÉúµÑ¿¨³Ë»ý¡£È磺
select staff_name from staff_member where staff_id
exists (select staff_id from staff_func);
not in ºÍnot exists
Èç¹û²éѯÓï¾äʹÓÃÁËnot in ÄÇôÄÚÍâ±í¶¼½øÐÐÈ«±íɨÃ裬ûÓÐÓõ½Ë÷Òý£»
¶ønot extsts µÄ×Ó²éѯÒÀÈ»ÄÜÓõ½±íÉϵÄË÷Òý¡£
ËùÒÔÎÞÂÛÄĸö±í´ó£¬ÓÃnot exists¶¼±Ènot inÒª¿ì¡£
¾¡Á¿²»ÒªÊ¹ÓÃnot in×Ӿ䡣ʹÓÃminus ×Ӿ䶼±Ènot in ×Ó¾ä¿ì£¬ËäȻʹÓÃminus×Ó¾äÒª½øÐÐÁ½´Î²éѯ£º
select staff_name from staff_member where staff_id in (select staff_id from staff_member minus select staff_id from staff_func where func_id like '81%');
in Óë "=" µÄÇø±ð
select name from student where name in ('zhang','wang','li','zhao');
Óë
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
µÄ½á¹ûÊÇÏàͬµÄ¡£
Ïà¹ØÎĵµ£º
¾ÍÊÇÓÉÆÕͨ×Ö·û£¨ÀýÈç×Ö·ûaµ½z£©ÒÔ¼°ÌØÊâ×Ö·û£¨³ÆΪԪ×Ö·û£©×é³ÉµÄÎÄ×Öģʽ¡£¸ÃģʽÃèÊöÔÚ²éÕÒÎÄ×ÖÖ÷Ìåʱ´ýÆ¥ÅäµÄÒ»¸ö»ò¶à¸ö×Ö·û´®¡£ÕýÔò±í´ïʽ×÷Ϊһ¸öÄ£°å£¬½«Ä³¸ö×Ö·ûģʽÓëËùËÑË÷µÄ×Ö·û´®½øÐÐÆ¥Åä¡£
±¾ÎÄÏêϸµØÁгöÁËÄÜÔÚÕýÔò±í´ïʽÖÐʹÓã¬ÒÔÆ¥ÅäÎı¾µÄ¸÷ÖÖ×Ö·û¡£µ±ÄãÐèÒª½âÊÍÒ»¸öÏÖÓеÄÕýÔò±í´ïʽʱ£¬¿ÉÒÔ×÷Ϊһ¸ö¿ ......
À´Ô´£º²»Ïê ×÷ÕߣºØýÃû ʱ¼ä£º2009-9-6 17:14:04 Tags£º Orac ʹÓ÷½·¨
ÐèÇó£ºÒµÎñÂß¼ÔÚ´¦ÀíÊý¾Ýʱ£¬ÐèÒª·µ»ØMessage²¢×èÖ¹³ÌÐòµÄ¼ÌÐøÔËÐС£µ«ÊÇ£¬ÔÚ´æ´¢¹ý³ÌʹÓÃOracleÊý¾Ý¿âµÄRaise¸ù±¾ÎÞ·¨Âú×ãÏÖÔÚµÄÒªÇó¡£
½â¾ö·½·¨£º
ʹÓÃRAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR ( erro ......
×Ô´ÓѧORACLEÒÔÀ´¾Í´ÓÀ´Ã»Óз¢Éú¹ýʲô¹ÊÕÏ£¬±È½ÏÐÒÔË¡£ÆäʵҲ²»ÄÜËãÐÒÔË£¬ÒòΪÕâÑù¾ÍÉÙÁ˶ÍÁ¶µÄ»ú»áÁË£¬ºÇºÇ£¬Ôõô¾õµÃ×Ô¼ºÓеãÇóÅ°µÄÇãÏò……
¼´±ãÊÇÔÚѧϰbackup and recovery basicsµÄʱºò£¬Ò²Ö»ÊÇ×ö×ö±¸·ÝµÄʵÑ飬û×ö¹ý»Ö¸´£¬ÒòΪÉí±ßûÓÐÈËÖ¸µ¼£¬Ò²Ã»ÆäËûͬѧÔÚѧORACLE£¬ËùÒÔÅÂÒ»³öÎÊÌâ¿Ï¶¨ºÜÂé·³£¬½â ......
select 'create sequence '||sequence_name||
' minvalue '||min_value||
' maxvalue '||max_value||
' start with '||last_number||
&n ......
Ò»£º
ʹÓÃProfile¶ÔÓû§Session»á»°½øÐÐ×ÊÔ´ÏÞÖÆ
--µ«ÊÇÈç¹ûʹÓÃÁËÁ¬½Ó³ØÖ®ÀàµÄ¶«¶«£¬¾Í»á³öЩÎÊÌ⣬±ÈÈçǰ̨Á¬½Ó²»ÉÏÖ®ÀàµÄ
--http://www.eygle.com/archives/2008/10/profile_session_limit.html
sqlplus "/ as sysdba"
SQL> show parameter resource
SQL> alter system set resource_limit= ......