ͨ¹ý·ÖÎöSQLÓï¾äµÄÖ´Ðмƻ®ÓÅ»¯SQL(Èý)
ËÄ£ºORACLEµÄÓÅ»¯Æ÷
ÓÅ»¯Æ÷ÓÐʱҲ±»³ÆΪ²éѯÓÅ»¯Æ÷£¬ÕâÊÇÒòΪ²éѯÊÇÓ°ÏìÊý¾Ý¿âÐÔÄÜ×îÖ÷ÒªµÄ²¿·Ö£¬²»ÒªÒÔΪֻÓÐSELECTÓï¾äÊDzéѯ¡£Êµ¼ÊÉÏ£¬´øÓÐÈκÎWHEREÌõ¼þµÄDML(INSERT¡¢UPDATE¡¢DELETE)Óï¾äÖж¼°üº¬²éѯҪÇó£¬ÔÚºóÃæµÄÎÄÕÂÖУ¬µ±Ëµµ½²éѯʱ£¬²»Ò»¶¨Ö»ÊÇÖ¸SELECTÓï¾ä£¬Ò²ÓпÉÄÜÖ¸DMLÓï¾äÖеIJéѯ²¿·Ö¡£ÓÅ»¯Æ÷ÊÇËùÓйØϵÊý¾Ý¿âÒýÇæÖеÄ×îÉñÃØ¡¢×ÌôÕ½ÐԵIJ¿¼þÖ®Ò»£¬´ÓÐÔÄܵĽǶȿ´Ò²ÊÇ×îÖØÒªµÄ²¿·Ö£¬ËüÐÔÄܵĸߵÍÖ±½Ó¹Øϵµ½Êý¾Ý¿âÐÔÄܵĺûµ¡£
ÎÒÃÇÖªµÀ£¬SQLÓï¾äͬÆäËüÓïÑÔ(ÈçCÓïÑÔ)µÄÓï¾ä²»Ò»Ñù£¬ËüÊǷǹý³Ì»¯(non-procedural)µÄÓï¾ä£¬¼´µ±ÄãҪȡÊý¾Ýʱ£¬²»ÐèÒª¸æËßÊý¾Ý¿âͨ¹ýºÎÖÖ;¾¶È¥È¡Êý¾Ý£¬Èçµ½µ×ÊÇͨ¹ýË÷ÒýÈ¡Êý¾Ý£¬»¹ÊÇÓ¦¸Ã½«±íÖеÄÿÐÐÊý¾Ý¶¼È¡³öÀ´£¬È»ºóÔÙͨ¹ýÒ»Ò»±È½ÏµÄ·½Ê½È¡Êý¾Ý(¼´È«±íɨÃè)£¬ÕâÊÇÓÉÊý¾Ý¿âµÄÓÅ»¯Æ÷¾ö¶¨µÄ£¬Õâ¾ÍÊǷǹý³Ì»¯µÄº¬Ò壬Ҳ¾ÍÊÇ˵£¬ÈçºÎÈ¡Êý¾ÝÊÇÓÉÓÅ»¯Æ÷¾ö¶¨£¬¶ø²»ÊÇÓ¦Óÿª·¢Õßͨ¹ý±à³Ì¾ö¶¨¡£ÔÚ´¦ÀíSQLµÄSELECT¡¢UPDATE¡¢INSERT»òDELETEÓï¾äʱ£¬Oracle ±ØÐë·ÃÎÊÓï¾äËùÉæ¼°µÄÊý¾Ý£¬OracleµÄÓÅ»¯Æ÷²¿·ÖÓÃÀ´¾ö¶¨·ÃÎÊÊý¾ÝµÄÓÐЧ·¾¶£¬Ê¹µÃÓï¾äÖ´ÐÐËùÐèµÄI/OºÍ´¦Àíʱ¼ä×îС¡£
ΪÁËʵÏÖÒ»¸ö²éѯ£¬Äں˱ØÐëΪÿ¸ö²éѯ¶¨ÖÆÒ»¸ö²éѯ²ßÂÔ£¬»òΪȡ³ö·ûºÏÌõ¼þµÄÊý¾ÝÉú³ÉÒ»¸öÖ´Ðмƻ®(execution plan)¡£µäÐ͵ģ¬¶ÔÓÚͬһ¸ö²éѯ£¬¿ÉÄÜÓм¸¸öÖ´Ðмƻ®¶¼·ûºÏÒªÇ󣬶¼Äܵõ½·ûºÏÌõ¼þµÄÊý¾Ý¡£ÀýÈ磬²ÎÓëÁ¬½ÓµÄ±í¿ÉÒÔÓжàÖÖ²»Í¬µÄÁ¬½Ó·½·¨£¬ÕâÈ¡¾öÓÚÁ¬½ÓÌõ¼þºÍÓÅ»¯Æ÷²ÉÓõÄÁ¬½Ó·½·¨¡£ÎªÁËÔÚ¶à¸öÖ´Ðмƻ®ÖÐÑ¡Ôñ×îÓŵÄÖ´Ðмƻ®£¬ÓÅ»¯Æ÷±ØÐëʹÓÃһЩʵ¼ÊµÄÖ¸±êÀ´ºâÁ¿Ã¿¸öÖ´Ðмƻ®Ê¹ÓõÄ×ÊÔ´(I/0´ÎÊý¡¢CPUµÈ)£¬ÕâЩ×ÊÔ´Ò²¾ÍÊÇÎÒÃÇËù˵µÄ´ú¼Û(cost)¡£Èç¹ûÒ»¸öÖ´Ðмƻ®Ê¹ÓõÄ×ÊÔ´¶à£¬ÎÒÃǾÍ˵ʹÓÃÖ´Ðмƻ®µÄ´ú¼Û´ó¡£ÒÔÖ´Ðмƻ®µÄ´ú¼Û´óС×÷ΪºâÁ¿±ê×¼£¬ÓÅ»¯Æ÷Ñ¡Ôñ´ú¼Û×îСµÄÖ´Ðмƻ®×÷ΪÕæÕýÖ´ÐиòéѯµÄÖ´Ðмƻ®£¬²¢Å×ÆúÆäËüµÄÖ´Ðмƻ®¡£
ÔÚORACLEµÄ·¢Õ¹¹ý³ÌÖУ¬Ò»¹²¿ª·¢¹ý2ÖÖÀàÐ͵ÄÓÅ»¯Æ÷£º»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ºÍ»ùÓÚ´ú¼ÛµÄÓÅ»¯Æ÷¡£Õâ2ÖÖÓÅ»¯Æ÷µÄ²»Í¬Ö®´¦¹Ø¼üÔÚÓÚ£ºÈ¡µÃ´ú¼ÛµÄ·½·¨ÓëºâÁ¿´ú¼ÛµÄ´óС²»Í¬¡£ÏÖ¶ÔÿÖÖÓÅ»¯Æ÷×öһϼòµ¥µÄ½éÉÜ£º
»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ -- Rule Based (Heuristic) Optimization(¼ò³ÆRBO)£º
ÔÚORACLE7֮ǰ£¬Ö÷ÒªÊÇʹÓûùÓÚ¹æÔòµÄÓÅ»¯Æ÷¡£ORACLEÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖвÉÓÃÆô·¢Ê½µÄ·½·¨(Heuristic Approach)»ò¹æÔò(Rules)À´Éú³ÉÖ´Ðмƻ®¡£ÀýÈ磬Èç¹ûÒ»¸ö²éѯµÄwhereÌõ¼þ(where clause)°üº¬Ò»¸öν´Ê(predicat
Ïà¹ØÎĵµ£º
ϵͳҪÇó½øÐÐSQLÓÅ»¯£¬¶ÔЧÂʱȽϵ͵ÄSQL½øÐÐÓÅ»¯£¬Ê¹ÆäÔËÐÐЧÂʸü¸ß£¬ÆäÖÐÒªÇó¶ÔSQLÖеIJ¿·Öin/not inÐÞ¸ÄΪexists/not exists
Ð޸ķ½·¨ÈçÏ£º
inµÄSQLÓï¾ä
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
from tab_oa_pub WHERE is_check=1 and
category_id in (sel ......
»¹ÊǼ¸Î»¶ÁÎÒµÄÊé (¡¶Oracle´óÐÍÊý¾Ý¿âϵͳÔÚAIX UNIXÉϵÄʵսÏê½â¡·) µÄ¶ÁÕßÀ´Óʼþ×ÉѯÎÊÌ⣬ºÜ±§Ç¸ÎÒ²»ÄÜÖðλ»Ø¸´Óʼþ£¬µÚÒ»ÎÒÕæµÄͦ棬µÚ¶þÕâЩÎÊÌâÆÄÓй²ÐÔ¡£ÎÒÀ´½èÖúÕâ¸öµØ·½£¬Í³Ò»µØºÍÖîλ̽ÌÖ£¬ºÜ¶àÎÊÌâÎÒÒ²ÊÇÔÚÃþË÷ÖУ¬²»ÄÜÖ±½Ó¸ø´ó¼Ò´ð°¸£¬ºÇºÇ£¬´ó¼Ò¼ûÁ°¡!
˵˵ËøÎÊÌâ¡£ MS SQLÖ§³ÖµÄËøÀàÐͺÍOracle²îÒìÊǺ ......
ÔÚSQL ServerÀィMysqlºÍOracleÊý¾Ý¿âÁ´½Ó»¹ÊDZȽÏÈÝÒ׵ģºÎÒÃÇÏÈÔÚ·þÎñÆ÷ÉÏ°²×°MysqlºÍOracleÊý¾Ý¿âÏà¹ØµÄODBCÇý¶¯³ÌÐò£¬ÔÚ[¹ÜÀí¹¤¾ß]->[Êý¾ÝÔ´(ODBC)] ÉèÖÃϵͳDSN£¬ÈçÏÂͼ:
ÉèÖúÃÁËÒÔºó£¬ÎÒÃÇ¿ÉÒÔÓÃSQL Server×Ô´øµÄµ¼³öºÍµ¼È빤¾ßÀ´Êµ ......