SQLÓÅ»¯¹ý³ÌÖг£¼ûOracle HINTµÄÓ÷¨
ÔÚ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(TA
Ïà¹ØÎĵµ£º
±¾½ÚÖ÷Òª½éÉÜʹÓÃCLR´´½¨±êÁ¿º¯Êý£¬±íÖµº¯ÊýºÍ¾ÛºÏº¯Êý¡£
Ëùν±êÁ¿º¯ÊýÖ¸µÄ¾ÍÊǴ˺¯ÊýÖ»·µ»ØÒ»¸öÖµ¡£±íÖµº¯Êý·µ»ØÖµÊÇÒ»¸ö±í¡£¾ÛºÏº¯ÊýÊÇÔÚselectÓï¾äÖÐʹÓõģ¬ÓÃÀ´¾ÛºÏÒ»¸ö½á¹û¼¯£¬ÀàËÆÓÚSum()»òÊÇCount()µÈÄÚÖõĺ¯Êý£¬¶øÇÒÕæÕýµÄ×Ô¶¨Òå¾ÛºÏº¯ÊýÄ¿Ç°Ö»ÄÜÓÃCLRÀ´ÊµÏÖ¡£
ÏÂÃæµÄÀý×ÓʹÓÃÁËSQLServer×Ô´øµÄpubsÊý¾Ý¿â¡£
1 ......
SQL×Ö·û´®´¦Àíº¯Êý´óÈ«(ת)2008-04-01 17:21SQL×Ö·û´®´¦Àíº¯Êý´óÈ«(ת)select ×Ö¶Î1 from ±í1 where ×Ö¶Î1.IndexOf("ÔÆ")=1;
ÕâÌõÓï¾ä²»¶ÔµÄÔÒòÊÇindexof£¨£©º¯Êý²»ÊÇsqlº¯Êý£¬¸Ä³Ésql¶ÔÓ¦µÄº¯Êý¾Í¿ÉÒÔÁË¡£
left£¨£©ÊÇsqlº¯Êý¡£
select ×Ö¶Î1 from ±í1 where charindex£¨'ÔÆ',×Ö¶Î1£©=1; ×Ö·û´®º¯Êý¶Ô¶þ½øÖÆÊý¾Ý¡¢×Ö·û´ ......
SQL code
¶¯Ì¬sqlÓï¾ä»ù±¾Óï·¨
1 :ÆÕͨSQLÓï¾ä¿ÉÒÔÓÃExecÖ´ÐÐ
eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- Çë×¢Òâ×Ö·û´®Ç°Ò»¶¨Òª¼ÓN
2:×Ö¶ÎÃû£¬±íÃû£¬Êý¾Ý¿âÃûÖ®Àà×÷Ϊ±äÁ¿Ê±£¬±ØÐëÓö¯Ì¬SQL
eg:
declare @ ......
ÆäʵÔÚÐÞ¸ÄÊý¾Ý¿âÃû³Æ֮ǰ£¬Èç¹ûÓÐÓû§Á¬½Óµ½Êý¾Ý¿âµÄ»°»áÔì³ÉÊý¾Ý¿âÖØÃüÃûʧ°Ü£¬¿ÉÒÔÏÈÖ´ÐÐ
select spid
from master.dbo.sysprocesses
where dbid=db_id('OldDbName')
½á¹û¼¯ÖÐÏÔʾµÄÊǵ±Ç°Á¬½Óµ½Êý¾Ý¿âOldDbNameµÄÁ¬½Ó
±ÈÈç½á¹ûÊÇ
79
81
µ±È»£¬Êµ¼ÊÖµÓ¦¸Ã²»ÊÇÕâÁ½¸ö
È»ºóÖ´ÐÐ
kill 79
kill 81
¹Ø±ÕÒѽ¨Á¢ ......
´ÓAccessÊý¾Ý¿âµ¼Èëµ½SQL
·½·¨Ò»:
ÔÚMSsqlÖÐÒª¼òÀúODBC..·½·¨±È½Ï±¿µã..
·½·¨¶þ:
Ö±½Ó´ÓAccessÉýǨµ½MSsql..
ÒòΪAccessÊý¾Ý¿âÓÐÉèÖÃÃÜÂë..»¹Õæ²»ºÃ¸ã..¸ãÁ˺ü¸´Î¶¼Ê§°ÜÁË..
ÀûÓ÷½·¨¶þ..Ò»´Î³É¹¦..
ÓÃÊý¾Ý¿âÉýǨ...¼òµ¥...
½¨ÒéʹÓõڶþÖÖ·½·¨µ¼ÈëACCESSÊý¾Ý¿âµ½MSSQLÊý¾Ý¿âÖÐ..Ê¡ÊÂ!!! ......