ÓÃEXPLAIN PLAN ·ÖÎöSQLÓï¾ä
ÈçºÎÉú³Éexplain plan?
¡¡¡¡½â´ð:ÔËÐÐutlxplan.sql. ½¨Á¢plan ±í
¡¡¡¡Õë¶ÔÌØ¶¨SQLÓï¾ä£¬Ê¹Óà explain plan set statement_id = 'tst1' into plan_table
¡¡¡¡ÔËÐÐutlxplp.sql »ò utlxpls.sql²ì¿´explain plan
EXPLAIN PLAN ÊÇÒ»¸öºÜºÃµÄ·ÖÎöSQLÓï¾äµÄ¹¤¾ß,ËüÉõÖÁ¿ÉÒÔÔÚ²»Ö´ÐÐSQLµÄÇé¿öÏ·ÖÎöÓï¾ä. ͨ¹ý·ÖÎö,ÎÒÃǾͿÉÒÔÖªµÀORACLEÊÇÔõôÑùÁ¬½Ó±í,ʹÓÃʲô·½Ê½É¨Ãè±í(Ë÷ÒýɨÃè»òÈ«±íɨÃè)ÒÔ¼°Ê¹Óõ½µÄË÷ÒýÃû³Æ.
ÄãÐèÒª°´ÕÕ´ÓÀïµ½Íâ,´ÓÉϵ½ÏµĴÎÐò½â¶Á·ÖÎöµÄ½á¹û. EXPLAIN PLAN·ÖÎöµÄ½á¹ûÊÇÓÃËõ½øµÄ¸ñʽÅÅÁеÄ, ×îÄÚ²¿µÄ²Ù×÷½«±»×îÏȽâ¶Á, Èç¹ûÁ½¸ö²Ù×÷´¦ÓÚͬһ²ãÖÐ,´øÓÐ×îС²Ù×÷ºÅµÄ½«±»Ê×ÏÈÖ´ÐÐ.
NESTED LOOPÊÇÉÙÊý²»°´ÕÕÉÏÊö¹æÔò´¦ÀíµÄ²Ù×÷, ÕýÈ·µÄÖ´Ðз¾¶ÊǼì²é¶ÔNESTED LOOPÌṩÊý¾ÝµÄ²Ù×÷,ÆäÖвÙ×÷ºÅ×îСµÄ½«±»×îÏÈ´¦Àí.
ÒëÕß°´:
ͨ¹ýʵ¼ù, ¸Ðµ½»¹ÊÇÓÃSQLPLUSÖеÄSET TRACE ¹¦ÄܱȽϷ½±ã.
¾ÙÀý:
SQL> list
1 SELECT *
2 from dept, emp
3* WHERE emp.deptno = dept.deptno
SQL> set autotrace traceonly /*traceonly ¿ÉÒÔ²»ÏÔʾִÐнá¹û*/
SQL> /
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
30 consistent gets
0 physical reads
0 redo size
2598 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
ͨ¹ýÒÔÉÏ·ÖÎö,¿ÉÒԵóöʵ¼ÊµÄÖ´Ðв½ÖèÊÇ:
1. TABLE ACCESS (FULL) OF 'EMP'
2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4. NESTED LOOPS (JOINING 1 AND 3)
×¢: ĿǰÐí¶àµÚÈý·½µÄ¹¤¾ßÈçTOADºÍORACLE±¾ÉíÌṩµÄ¹¤¾ßÈçOMSµÄSQL Analyze¶¼ÌṩÁ˼«Æä·½±ãµÄEXPLAIN PLAN¹¤¾ß.Ò²Ðíϲ»¶Í¼Ðλ¯½çÃæµÄÅóÓÑÃÇ¿ÉÒÔÑ¡ÓÃËüÃÇ.
----------------------------------------------------------------------------
¶ÔÓÚsqlÖ´ÐеÄСÁ¿¸ßµÍ
Ïà¹ØÎĵµ£º
×î½ü¸Õѧ»áÔÚVB2008 ÖÐʹÓòÎÊý»¯SQLÓï¾ä£¬ÓÚÊÇÂíÉÏÓõ½´úÂëÖУ¬È´Åöµ½²é²»µ½ÈκÎÊý¾ÝµÄÇé¿ö£¬¾À½áÁ˺ü¸Ì죬»¹ÊÇûÓиãÃ÷°×£¬²îµãÍÂѪ¡£²»µÃÒÑ»¹ÊÇÏÈÔÚ´úÂëÖÐʹÓÃ×Ö·û´®Æ´½ÓµÄSQLÓï¾ä¡£
°üº¬²ÎÊý»¯SQLÓï¾äµÄ´úÂëÈçÏ£º
Dim cmSl As N ......
SqlServer µÄÐÔÄÜ±È Access Ìá¸ßµÄ²»Ö¹Ò»µãÁË¡£
²»¹ý£¬¿ÆÑ´CMSµÄÃâ·Ñ°æ´øµÄÊÇ Access Êý¾Ý¿â¡£
ÓÐûÓа취ת³É mssql µÄÄØ£¿µ±È»¿ÉÒÔ
1, mssqlÖÐн¨Ò»ÕÅ±í£¬È»ºóµ¼Èë access ÀïÃæµÄ±íºÍÊý¾Ý.
Õâ¸öÍøÉÏÓз½·¨£¬²»¶à˵ÁË¡£
2, ÐÞ¸Ä conn.asp µ½ sql server °æ±¾£¬Õâ¸öÒ²¼òµ¥¡£
3, ä¯ÀÀÊý¾Ý£¬Ö±½Ó¾Í¿ÉÒÔ¿´ ......
ºÜ¶àʱºòÎÒÃÇ¿ÉÄܶ¼ÐèÒªÕâôһ¸ö¼ò·±»¥Ïàת»»µÄSQLº¯Êý£¬½ñÌìÔÚÍøÉÏÕÒµ½µÄ£¬ÊÕ¼¯ÏÂÀ´¡£
ÒÔºóÓÐÁËËü¾ÍʡʶàÀ²¡£²»ÓÃÔÙд³ÌÐòÈ¡³öÀ´×ª»»ºóÔÙ¸üÐÂÊý¾Ý¿âÁË¡£
SQL¼òÌå·±Ìåת»»º¯Êý´úÂ룺
--Éú³ÉÂë±í
if exists (select * from dbo.sysobjects where id = object_id(N'[codetable]') and OBJECTPROPERTY(id, N'IsUserTable' ......
¹«Ë¾×éÖ¯SQLˮƽ¿¼ÊÔ£¬¿´ÁËд½Ì²Ä£¬Ð´Á˵ã×ܽᷢÉÏÀ´¸ú´ó¼Ò·ÖÏí¡£
ÎÒ¹«Ë¾Ê¹ÓõÄÊÇSybase ASE12.5£¬ËùÒÔÏÂÃæµÄÒ»Ð©ÌØÐÔÊÇÕë¶ÔSybase ASEµÄ¡£
Ò»£ºSQL Bisic
1£ºSQL(Structured Quary Language)ÌØÐÔ£º
a£º±ê×¼»¯
b£º·Ç¹ý³Ì»¯µÄ
c£º¿ÉÓÅ»¯µÄ
d£ºÃæÏò¼¯ºÏ²Ù×÷µÄ
2£ºASEÖеÄÊý¾ÝÀàÐÍ
a£ºNumberic
b£ºCharacter
c£ºDat ......
ÔÎĵØÖ·£ºhttp://www.dingos.cn/index.php?topic=1688.0
OracleÓÐrownumÓÃÓÚ·ÃÎʱíÖÐÐкš£ÄÇôÔÚSQL ServerÖÐÊÇ·ñÓеÈЧµÄÄØ£¿»òÕßÔÚSQL ServerÖÐÈçºÎÊä³öÐкţ¿
-----------------------------------
ÔÚSQL ServerÖÐûÓÐÖ±½ÓµÈЧÓÚOracleµÄrownum
-----------------------------------
Ñϸñ˵À´£¬ÔÚ¹ØÏµÊý¾Ý¿âÖУ¬± ......