ÓÃ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Ö´ÐеÄСÁ¿¸ßµÍ
Ïà¹ØÎĵµ£º
ºÜ¶àʱºòÎÒÃÇ¿ÉÄܶ¼ÐèÒªÕâôһ¸ö¼ò·±»¥Ïàת»»µÄSQLº¯Êý£¬½ñÌìÔÚÍøÉÏÕÒµ½µÄ£¬ÊÕ¼¯ÏÂÀ´¡£
ÒÔºóÓÐÁËËü¾ÍʡʶàÀ²¡£²»ÓÃÔÙд³ÌÐòÈ¡³öÀ´×ª»»ºóÔÙ¸üÐÂÊý¾Ý¿âÁË¡£
SQL¼òÌå·±Ìåת»»º¯Êý´úÂ룺
--Éú³ÉÂë±í
if exists (select * from dbo.sysobjects where id = object_id(N'[codetable]') and OBJECTPROPERTY(id, N'IsUserTable' ......
SQL¾ÛºÏº¯
±êÇ©£ºsql¾ÛºÏº¯Êý ÔÓ̸
¾ÛºÏº¯Êý£º
1.AVG ·µ»Ø×éÖÐµÄÆ½¾ùÖµ£¬¿ÕÖµ½«±»ºöÂÔ¡£
ÀýÈ磺use northwind // ²Ù×÷northwindÊý¾Ý¿â
Go
Select avg (unitprice) //´Ó±íÖÐÑ¡ÔñÇóunitpriceµÄƽ¾ùÖµ
& ......
CASE ¿ÉÄÜÊÇ SQL Öб»ÎóÓÃ×î¶àµÄ¹Ø¼ü×ÖÖ®Ò»¡£ËäÈ»Äã¿ÉÄÜÒÔǰÓùýÕâ¸ö¹Ø¼ü×ÖÀ´´´½¨×ֶΣ¬µ«ÊÇËü»¹¾ßÓиü¶àÓ÷¨¡£ÀýÈ磬Äã¿ÉÒÔÔÚ WHERE
×Ó¾äÖÐʹÓà CASE¡£
Ê×ÏÈÈÃÎÒÃÇ¿´Ò»Ï CASE µÄÓï·¨¡£ÔÚÒ»°ãµÄ SELECT ÖУ¬ÆäÓï·¨ÈçÏ£º
SELECT =
CASE
WHEN THEN
WHEN THEN
ELSE
......
ÔÎĵØÖ·£ºhttp://www.dingos.cn/index.php?topic=1688.0
OracleÓÐrownumÓÃÓÚ·ÃÎʱíÖÐÐкš£ÄÇôÔÚSQL ServerÖÐÊÇ·ñÓеÈЧµÄÄØ£¿»òÕßÔÚSQL ServerÖÐÈçºÎÊä³öÐкţ¿
-----------------------------------
ÔÚSQL ServerÖÐûÓÐÖ±½ÓµÈЧÓÚOracleµÄrownum
-----------------------------------
Ñϸñ˵À´£¬ÔÚ¹ØÏµÊý¾Ý¿âÖУ¬± ......
ÔÎijö´¦£ºhttp://www.dingos.cn/index.php?topic=1874.0
¶¨ÒåºÍÓ÷¨
CONVERT() º¯ÊýÊǰÑÈÕÆÚת»»ÎªÐÂÊý¾ÝÀàÐ͵ÄͨÓú¯Êý¡£
CONVERT() º¯Êý¿ÉÒÔÓò»Í¬µÄ¸ñʽÏÔʾÈÕÆÚ/ʱ¼äÊý¾Ý
Óï·¨
CONVERT(data_type(length),data_to_be_converted,style)
data_type(length) ¹æ¶¨Ä¿±êÊý¾ÝÀàÐÍ£¨´øÓпÉÑ¡µÄ³¤¶È£©¡£data_to_be_conve ......