SQLÓï¾äÓÅ»¯·½·¨30Àý
ÔÚSQLÓï¾äÓÅ»¯¹ý³ÌÖУ¬ÎÒÃǾ³£»áÓõ½hint,ÏÖ×ܽáÒ»ÏÂÔÚSQLÓÅ»¯¹ý³ÌÖг£¼ûOracle
HINTµÄÓ÷¨£º
1. /*+ALL_ROWS*/
±íÃ÷¶ÔÓï¾ä¿éÑ¡Ôñ»ùÓÚ¿ªÏúµÄÓÅ»¯·½·¨,²¢»ñµÃ×î¼ÑÍÌÍÂÁ¿,ʹ×ÊÔ´ÏûºÄ×îС»¯.
ÀýÈç:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN from BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/
±íÃ÷¶ÔÓï¾ä¿éÑ¡Ôñ»ùÓÚ¿ªÏúµÄÓÅ»¯·½·¨,²¢»ñµÃ×î¼ÑÏìӦʱ¼ä,ʹ×ÊÔ´ÏûºÄ×îС»¯.
ÀýÈç:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN from BSEMPMS WHERE EMP_NO='SCOTT';
3. /*+CHOOSE*/
±íÃ÷Èç¹ûÊý¾Ý×ÖµäÖÐÓзÃÎʱíµÄͳ¼ÆÐÅÏ¢,½«»ùÓÚ¿ªÏúµÄÓÅ»¯·½·¨,²¢»ñµÃ×î¼ÑµÄÍÌÍÂ
Á¿;
±íÃ÷Èç¹ûÊý¾Ý×ÖµäÖÐûÓзÃÎʱíµÄͳ¼ÆÐÅÏ¢,½«»ùÓÚ¹æÔò¿ªÏúµÄÓÅ»¯·½·¨;
ÀýÈç:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN from BSEMPMS WHERE EMP_NO='SCOTT';
4. /*+RULE*/
±íÃ÷¶ÔÓï¾ä¿éÑ¡Ôñ»ùÓÚ¹æÔòµÄÓÅ»¯·½·¨.
ÀýÈç:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN from BSEMPMS WHERE EMP_NO='SCOTT';
5. /*+FULL(TABLE)*/
±íÃ÷¶Ô±íÑ¡ÔñÈ«¾ÖɨÃèµÄ·½·¨.
ÀýÈç:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM from BSEMPMS A WHERE EMP_NO='SCOTT';
6. /*+ROWID(TABLE)*/
ÌáʾÃ÷È·±íÃ÷¶ÔÖ¸¶¨±í¸ù¾ÝROWID½øÐзÃÎÊ.
ÀýÈç:
SELECT /*+ROWID(BSEMPMS)*/ * from BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';
7. /*+CLUSTER(TABLE)*/
ÌáʾÃ÷È·±íÃ÷¶ÔÖ¸¶¨±íÑ¡Ôñ´ØÉ¨ÃèµÄ·ÃÎÊ·½·¨,ËüÖ»¶Ô´Ø¶ÔÏóÓÐЧ.
ÀýÈç:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO from BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /*+INDEX(TABLE INDEX_NAME)*/
±íÃ÷¶Ô±íÑ¡ÔñË÷ÒýµÄɨÃè·½·¨.
ÀýÈç:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ from BSEMPMS WHERE SEX='M';
9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
±íÃ÷¶Ô±íÑ¡ÔñË÷ÒýÉýÐòµÄɨÃè·½·¨.
ÀýÈç:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ from BSEMPMS WHERE DPT_NO='SCOTT';
10. /*+INDEX_COMBINE*/
Ϊָ¶¨±íÑ¡Ôñλͼ·ÃÎÊ·¾,Èç¹ûINDEX_COMBINEÖÐûÓÐÌṩ×÷Ϊ²ÎÊýµÄË÷Òý,½«Ñ¡Ôñ³ö
λͼË÷ÒýµÄ²¼¶û×éºÏ·½Ê½.
ÀýÈç:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * from BSEMPMS WHERE SAL <5000000 AND HIREDATE <SYSDATE;
11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
Ïà¹ØÎĵµ£º
1.²éѯµÄÄ£ºýÆ¥Åä
¾¡Á¿±ÜÃâÔÚÒ»¸ö¸´ÔÓ²éѯÀïÃæÊ¹Óà LIKE '%parm1%'—— ºìÉ«±êʶλÖõİٷֺŻᵼÖÂÏà¹ØÁеÄË÷ÒýÎÞ·¨Ê¹Óã¬×îºÃ²»ÒªÓÃ.
½â¾ö°ì·¨:
ÆäʵֻÐèÒª¶Ô¸Ã½Å±¾ÂÔ×ö¸Ä½ø£¬²éѯËٶȱã»áÌá¸ß½ü°Ù±¶¡£¸Ä½ø·½·¨ÈçÏ£º
a¡¢ÐÞ¸Äǰ̨³ÌÐò——°Ñ²éѯÌõ¼þµÄ¹©Ó¦ÉÌÃû³ÆÒ»À¸ÓÉÔÀ´µÄÎı¾ÊäÈë¸ÄΪÏÂÀÁб ......
Ò»¡¢SQLƴд½¨Òé 1¡¢²éѯʱ²»·µ»Ø²»ÐèÒªµÄÐС¢ÁÐ ÒµÎñ´úÂëÒª¸ù¾Ýʵ¼ÊÇé¿ö¾¡Á¿¼õÉÙ¶Ô±íµÄ·ÃÎÊÐÐÊý£¬×îС»¯½á¹û¼¯£¬ÔÚ²éѯʱ£¬²»Òª¹ý¶àµØÊ¹ÓÃͨÅä·ûÈ磺select * from table1Óï¾ä£¬ÒªÓõ½¼¸ÁоÍÑ¡Ôñ¼¸ÁУ¬È磺select col1,col2 from table1;ÔÚ¿ÉÄܵÄÇé¿öϾ¡Á¿ÏÞÖÆ½á¹û¼¯ÐÐÊýÈ磺se ......
×î½üÔÚÕÒÒ»´Îsql²éѯµÄÎÞÏÞ·ÖÀà²éѯµÄÉè¼Æ£¬ÍøÉÏÕÒÁËÒ»ÏÂÕâ¸öÊý¾Ý±íµÄÉè¼ÆºÜÓÐÌØÉ«£¬
²»Óõݹ飬ÒÀ¿¿¸ö¼òµ¥SQLÓï¾ä¾ÍÄÜÁгö²Ëµ¥£¬¿´¿´Õâ¸öÊý¾Ý±íÔõôÉè¼ÆµÄ£¬²¢¶ÔÏÂÃæµÄÊý¾Ý±í½á¹¹µÄ²éѯ½øÐзÖÎö.
Êý¾Ý¿â×ֶδó¸ÅÈçÏ£º
-----------------------------------------------------------------------------------
id ......
1. Ö´ÐÐÒ»¸öSQL½Å±¾Îļþ
SQL>start file_name
SQL>@ file_name
2. ¶Ôµ±Ç°µÄÊäÈë½øÐбà¼
SQL>edit
3. ÖØÐÂÔËÐÐÉÏÒ»´ÎÔËÐеÄsqlÓï¾ä
SQL>/
4. ½«ÏÔʾµÄÄÚÈÝÊä³öµ½Ö¸¶¨Îļþ
SQL> SPOOL file_name
ÔÚÆÁÄ»ÉϵÄËùÓÐÄÚÈݶ¼°üº¬ÔÚ¸ÃÎļþÖУ¬°üÀ¨ÄãÊäÈëµÄsqlÓï¾ä¡£
5. ¹Ø±Õspool ......
°´Ö¸¶¨´ÎÊýÖØ¸´×Ö·û±í´ïʽ¡£
Óï·¨
REPLICATE ( character_expression, integer_expression)
²ÎÊý
character_expression
×Ö·ûÊý¾ÝÐ͵Ä×ÖĸÊý×Ö±í´ïʽ£¬»òÕß¿ÉÒÔÒþʽת»»Îª nvarchar »ò ntext µÄÆäËûÊý¾ÝÀàÐ͵Ä×ÖĸÊý×Ö±í´ïʽ¡£
integer_expression
¿ÉÒÔÒþʽת»»Îª int µÄ±í´ïʽ¡£Èç¹û integer_expression Ϊ ......