ÓÃ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Ö´ÐеÄСÁ¿¸ßµÍ
Ïà¹ØÎĵµ£º
ÎÒ˵µÄ²»¶¨Ìõ¼þÊÇÖ¸²éѯÌõ¼þµÄ¸öÊý²»¶¨¡£ÓÐʱһ¸ö£¬ÓÐʱÁ½¸ö£¬ÓÐʱºÃ¼¸¸ö¡£
Ê×ÏÈÎÒ·¢ÏÖ
select * from A where a='kkk' Óë
select * from A where a like 'kkk'
ÆäʵЧ¹ûÊÇÒ»Ñù£¬Ö»Òª like ºóÃæµÄ×Ö·û´®²»°üº¬Í¨Åä·û¡£ÕâÑùÒ»À´¾ÍºÜ·½±ãÁË¡£Æ©ÈçÓÐ
select * from A where a='KKK' and b='LLL'
ÓÐÁ½¸ö²éѯÌõ¼þ£¬ ......
sql º¯Êý×ܽá
³£ÓõÄ×Ö·û´®º¯ÊýÓУº
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ......
MYSQL¿ÉÒÔÔÚ¿ØÖÆÌ¨Ï½øÐвÙ×÷£¬ÀûÓÃÃüÁî
SQL´úÂë
mysql -h localhost -u root -p123456
¾Í¿ÉÒԵǽMYSQLµÄ¿ØÖÆÌ¨¡£
sql serverÒ²ÌṩÁË¿ØÖÆÌ¨ÏµIJÙ×÷¹¦ÄÜ¡£
ÔÚSQL SERVERµÄ binnĿ¼Ï£¬ÌṩÁËosql.exeÓëisql.exeÕâÁ½¸öÎļþ¡£
ÕâÁ½¸ö³ÌÐò¶¼ÌṩÁË·ÃÎÊSQL SERVERµÄ¹¦ÄÜ£¬ÕâÁ ......
ÎÒ¾³£ÎʵÄÒ»¸öÎÊÌâÊÇ£ºÄãÊÇÈçºÎÀ´À©Õ¹SQL DatabaseµÄ£¿ÓкܶàµÄ·½·¨¿ÉÒÔ×öµ½£¬±ÈÈçʹÓûº´æ£¬¹²Ïí»òÕßÓÃÆäËûµÄ¼¼Êõ½«Ñ¹Á¦´ÓÊý¾Ý¿âÊÍ·Å£¬µ«Êµ¼ÊÉÏÎÒÃÇûÓС£SQL Database´Ó¸ù±¾ÉÏÀ´ËµÊDz»¾ßÓÐÀ©Õ¹ÐԵģ¬ÎÒÃÇҲûÓÐħ·¨Ê¹ËüºöÈ»¾ßÓÐÀ©Õ¹ÐÔ¡£
ʲôÊÇÀ©Õ¹ÐÔ£¿
±ØÐë¾ßÓм¸Ï¼¸µã£º
ºáÏòÀ©Õ¹£º¸ü¶àµÄserver¿ÉÒÔ´øÀ´¸ü¸ßµÄÐÔÄÜ ......