OracleµÄº£Á¿´æ´¢¼¼Êõ
ÏÂÎçÈ¥²Î¼ÓÒ»¸öOracleÓйغ£Á¿Êý¾Ý´æ´¢¼¼ÊõµÄÅàѵ½²×ùÁË¡£
µØÖ·ÔÚ¹ãÖÝÊÐÁÖºÍÎ÷·101ºÅÌìºÓÇø¼Æ¾´óÂ¥Î÷²àÈýÂ¥¡£
Åàѵ·¢Æð»ú¹¹Îª£º¹ãÖÝÖÐî£ÐÅÏ¢¼¼ÊõÓÐÏÞ¹«Ë¾¡£
ÏÂÃæ¾Í¼òÒª×ܽáÒ»ÏÂËùÌýµ½µÄһЩ¶«Î÷£¬Ò²ËãÊÇѧµ½µÄÕâЩ¼¼Êõ¡£²»¹ýÓеĶ«Î÷²»ÖªµÀ×ܽáµÄ¶Ô²»¶Ô£¬ÔÝÇÒàñàðÍÌÔæ°É¡£
OracleµÄ´æ´¢¼¼Êõ
´óÌåÉÏ·ÖΪÁ½ÖÖ£¬Ò»ÖÖÊǺϵļ¼Êõ£¬Ò»ÖÖÊǷֵļ¼Êõ¡£ºÏµÄ¼¼Êõ¼´½«¸÷¸ö·Ö²ã´æ´¢µÄÊý¾ÝÕûºÏÔÚÒ»Æð¡£·ÖµÄ¼¼Êõ¼´½«Êý¾Ý·Ö²¼Ê½´æ´¢£¬Ò²¼´µ½´¦´æ´¢¡£
¶øOracleÖвÉÓÃÁ˸߿ÉÓÃÐÔ¼¼Êõ£¬ÔÚÊý¾Ý¿âµÄ´æ´¢ºÍÓÅ»¯·½ÃæÉæ¼°µ½ÈçϵÄһЩ¼¼ÊõºÍ¼¼ÇÉ£º
1£© ·Ö²¼Ê½¼¼Êõ
2£© Cache¼¼Êõ
3£© Search¼¼Êõ
4£© Êý¾Ý¿âµÄ·¶Ê½Éè¼Æ
5£© Êý¾Ý¿âµÄOLTPÓëOLAP¼¼Êõ
6£© Êý¾Ý¿âSQL²¢ÐÐÖ´ÐÐ
ÓйØÊý¾ÝµÄ´æ´¢ÎÊÌ⣬ÓÐÈçÏÂÁ½¸ö°¸Àý£º
eBayÊÇÊÀ½çÁìÏȵĵç×ÓÉÌÎñ·þÎñÉÌ£¬ËüµÄº£Á¿Êý¾Ý´æ´¢·½°¸Æäʵ²ÉÓõÄÊǶÁд°þÀë¼¼ÇÉ¡£
ÈçÏÂͼËùʾ£º
ÈçÉÏͼËùʾ£¬Êý¾Ý¿âд²Ù×÷ºÍ¶Á²Ù×÷ÎïÀíÉÏ·ÖÀ뿪À´£¬µ±Ð´²Ù×÷Ö´Ðкó£¬Êý¾Ý¿âShare Plex¾Í¶àÁËһЩд²Ù×÷µÄ¼Ç¼£¬È»ºó½«¸üеÄÊý¾Ý֪ͨ´æ´¢PC»ú¡£¶ø¶Á²Ù×÷ʱ£¬»ùÓÚº£Á¿Êý¾ÝµÄ¸ºÔؾùºâÉ豸¾Í֪ͨÄÇЩ´æ´¢»ú£¬¶ÁÊý¾Ý¡£
¶øGoogleµÄ½â¾ö·½°¸ÊÇ£ºËü²ÉÓÃ×Ô¼ºµÄGoogle File SystemÀ´´æ´¢Îļþ£¬ÓÉBigTableÀ´ÊµÏÖ¸ßËÙ´æ´¢ÓÚ²éÕÒ£¬´´½¨ÔÚGFSÉÏ£¬»¯ÕûΪÁ㣬·Ö²¼ÔÚ¶ą̀PCÉÏ£¬¶øÕâЩPC»úÊÇÈÝÒ׸ü»»µÄ¡£
OracleµÄÊý¾Ý¿âÓ¦Ó÷ÖÀàÖУ¬Ó¦ÓòÙ×÷·ÖÁ½À࣬һÀàÊÇ»ùÓÚÏìӦʱ¼ä£¬²¢·¢ÐÔ²Ù×÷´¦ÀíµÈ¿¼ÂǵÄOLTP£¬ÁíÒ»ÀàÊÇ»ùÓÚÍÌÍÂÁ¿£¬¹ÜÀí·ÖÎöµÈ¿¼ÂǵĵÄOLAP¡£
º£Á¿Êý¾ÝµÄ´¦Àí˼·£ºº£Á¿Êý¾ÝµÄµ¼Èëµ¼³öµÄ×î¿ì˼ÏëÊ·Êý¾Ý¿âÊý¾Ýµ¼³ö³ÉFileÐÎʽ£¬È»ºóÓÉFileµ¼Èëµ½ÁíÍâÒ»¸öÊý¾Ý¿âÖУ¬³£Óõļ¼ÊõΪLoad¼¼Êõ¡£ÈçOracleµÄSQL Loader¹¤¾ß£¬Õâ¿î¹¤¾ß¿ÉÒÔ½«ÆäËûÓ¦ÓóÌÐòµ¼Èëµ½OracleÊý¾Ý¿âÖУ¬²¢²»Ò»¶¨ÊÇ´ÓOracleµ¼Èëµ½OracleµÄÊý¾Ý¿âÖС£
ÔÚOracleµÄÓ¦Óýâ¾ö·½°¸ÖУ¬Ëü³£ÓõĴóÊý¾ÝÁ¿´¦Àí¼¼ÊõÓУºSQL Loader¹¤¾ß£¬Íⲿ±í£¬OCIºÍÖ±½Ó·¾¶API£¬µ¼Èë/µ¼³ö£¬¶à±í²åÈëÓëºÏ²¢£¬±íº¯Êý£¬±í¿Õ¼ä´«Êä¼¼Êõ¡£ÈçÓÐexp/imp¡¢Êý¾Ý±Ã¼¼Êõ¡£ÏÂÃæÏêϸ½éÉÜÒ»ÏÂÕâЩ¼¼Êõ¡£
SQL LoaderÊǽ«ÍⲿÊý¾Ý¼ÓÔØµ½OracleÊý¾Ý¿âÖУ¬Ëü°üº¬Ò»¸ö¹¦ÄÜÊ®·ÖÇ¿´óµÄÊý¾Ý·ÖÎöÒýÇæ£¬¸ÃÒýÇæ¶ÔÊý¾ÝÎļþÖÐÊý¾ÝµÄ¸ñʽ¼¸ºõûÓÐÏÞÖÆ¡£
Ö±½Ó×°ÔØ·¨ÊÇÖ±½Ó¼ÓÔØ²åÈë
Ïà¹ØÎĵµ£º
¡¡¡¡decode()º¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLʵÏÖ»¹Ã»Óд˹¦ÄÜ¡£
DECODEº¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLʵÏÖ»¹Ã»Óд˹¦ÄÜ¡£DECODEÓÐÊ²Ã´Ó ......
½éÉÜÒ»ÏÂÄÚÁª¡¢×óÁª¡¢ÓÒÁª
Ò».ÏÈ¿´Ò»Ð©×î¼òµ¥µÄÀý×Ó
Àý×Ó
Table A
aid adate
1 a1
2 a2
3 a3
TableB
bid bdate
1 b1
2 b2
4 b4
Á½¸ö±ía,bÏàÁ¬½Ó,Ҫȡ³öidÏàͬµÄ×Ö¶Î
select * from a inner join b on a.aid = b.bidÕâÊǽöÈ¡³öÆ¥ÅäµÄÊý¾Ý.
´ËʱµÄÈ¡³öµÄÊÇ:
1 a1 b1
2 a2 b2
ÄÇôleft join Ö¸:
select * ......
¿ÉÒÔʹÓÃGROUPING_IDº¯Êý½èÖúHAVING×Ó¾ä¶Ô¼Ç¼½øÐйýÂË£¬½«²»°üº¬Ð¡¼Æ»òÕß×ܼƵļǼ³ýÈ¥¡£GROUPING_ID()º¯Êý¿ÉÒÔ½ÓÊÜÒ»Áлò¶àÁУ¬·µ»ØGROUPINGλÏòÁ¿µÄÊ®½øÖÆÖµ¡£GROUPINGλÏòÁ¿µÄ¼ÆËã·½·¨Êǽ«°´ÕÕ˳Ðò¶ÔÿһÁе÷ÓÃGROUPINGº¯ÊýµÄ½á¹û×éºÏÆðÀ´¡£
¹ØÓÚGROUPINGº¯ÊýµÄʹÓ÷½·¨¿ÉÒԲμûÎÒÇ°ÃæÐ´µÄһƪÎÄÕÂ
http://blog.csdn ......
1£®Êýѧº¯Êý
¢Ù¾ø¶ÔÖµ
l S£ºselect abs(-1) value
l O£ºselect abs(-1) value from dual
¢ÚÈ¡Õû(´ó)
l S£ºselect ceiling(-001) value
l O£ºselect ceil(-001) value from dual
¢ÛÈ¡Õû£¨Ð¡£©
l S£ºselect floor(-001) value  ......
ÎÊ£ºÊ²Ã´ÊÇNULL£¿
´ð£ºÔÚÎÒÃDz»ÖªµÀ¾ßÌåÓÐʲôÊý¾ÝµÄʱºò£¬Ò²¼´Î´Öª£¬¿ÉÒÔÓÃNULL£¬ÎÒÃdzÆËüΪ¿Õ£¬ORACLEÖУ¬º¬ÓпÕÖµµÄ±íÁ㤶ÈΪÁã¡£
ORACLEÔÊÐíÈκÎÒ»ÖÖÊý¾ÝÀàÐ͵Ä×Ö¶ÎΪ¿Õ£¬³ýÁËÒÔÏÂÁ½ÖÖÇé¿ö£º
1¡¢Ö÷¼ü×ֶΣ¨primary key£©£¬
2¡¢¶¨ÒåʱÒѾ¼ÓÁËNOT NULLÏÞÖÆÌõ¼þµÄ×Ö¶Î
˵Ã÷£º
1¡¢µÈ¼ÛÓÚûÓÐÈκÎÖµ¡¢ÊÇδ֪Êý¡£
2 ......