ORACLEÈý²ãǶÌ×ʵÏÖ·ÖÒ³¹¦ÄܺÍROWNUMµÄʹÓÃ
(Ò»)ʵÏÖ·ÖÒ³¼°ÐÔÄÜ
OracleµÄ·ÖÒ³²éѯÓï¾ä»ù±¾ÉÏ¿ÉÒÔ°´ÕÕ±¾Îĸø³öµÄ¸ñʽÀ´½øÐÐÌ×Óá£
·ÖÒ³²éѯ¸ñʽ£º
SELECT * from
(
SELECT A.*, ROWNUM RN
from (SELECT * from TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
ÆäÖÐ×îÄÚ²ãµÄ²éѯSELECT * from TABLE_NAME±íʾ²»½øÐзҳµÄÔʼ²éѯÓï¾ä¡£ROWNUM <= 40ºÍRN >= 21¿ØÖÆ·ÖÒ³²éѯµÄÿҳµÄ·¶Î§¡£
ÉÏÃæ¸ø³öµÄÕâ¸ö·ÖÒ³²éѯÓï¾ä£¬ÔÚ´ó¶àÊýÇé¿öÓµÓнϸߵÄЧÂÊ¡£·ÖÒ³µÄÄ¿µÄ¾ÍÊÇ¿ØÖÆÊä³ö½á¹û¼¯´óС£¬½«½á¹û¾¡¿ìµÄ·µ»Ø¡£ÔÚÉÏÃæµÄ·ÖÒ³²éѯÓï¾äÖУ¬ÕâÖÖ¿¼ÂÇÖ÷ÒªÌåÏÖÔÚWHERE ROWNUM <= 40Õâ¾äÉÏ¡£
Ñ¡ÔñµÚ21µ½40Ìõ¼Ç¼´æÔÚÁ½ÖÖ·½·¨£¬Ò»ÖÖÊÇÉÏÃæÀý×ÓÖÐչʾµÄÔÚ²éѯµÄµÚ¶þ²ãͨ¹ýROWNUM <= 40À´¿ØÖÆ×î´óÖµ£¬ÔÚ²éѯµÄ×îÍâ²ã¿ØÖÆ×îСֵ¡£¶øÁíÒ»ÖÖ·½Ê½ÊÇÈ¥µô²éѯµÚ¶þ²ãµÄWHERE ROWNUM <= 40Óï¾ä£¬ÔÚ²éѯµÄ×îÍâ²ã¿ØÖÆ·ÖÒ³µÄ×îСֵºÍ×î´óÖµ¡£ÕâÊÇ£¬²éѯÓï¾äÈçÏ£º
SELECT * from
(
SELECT A.*, ROWNUM RN
from (SELECT * from TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
¶Ô±ÈÕâÁ½ÖÖд·¨£¬¾ø´ó¶àÊýµÄÇé¿öÏ£¬µÚÒ»¸ö²éѯµÄЧÂʱȵڶþ¸ö¸ßµÃ¶à¡£
ÕâÊÇÓÉÓÚCBOÓÅ»¯Ä£Ê½Ï£¬Oracle¿ÉÒÔ½«Íâ²ãµÄ²éѯÌõ¼þÍÆµ½ÄÚ²ã²éѯÖУ¬ÒÔÌá¸ßÄÚ²ã²éѯµÄÖ´ÐÐЧÂÊ¡£¶ÔÓÚµÚÒ»¸ö²éѯÓï¾ä£¬µÚ¶þ²ãµÄ²éѯÌõ¼þWHERE ROWNUM <= 40¾Í¿ÉÒÔ±»OracleÍÆÈëµ½ÄÚ²ã²éѯÖУ¬ÕâÑùOracle²éѯµÄ½á¹ûÒ»µ©³¬¹ýÁËROWNUMÏÞÖÆÌõ¼þ£¬¾ÍÖÕÖ¹²éѯ½«½á¹û·µ»ØÁË¡£
¶øµÚ¶þ¸ö²éѯÓï¾ä£¬ÓÉÓÚ²éѯÌõ¼þBETWEEN 21 AND 40ÊÇ´æÔÚÓÚ²éѯµÄµÚÈý²ã£¬¶øOracleÎÞ·¨½«µÚÈý²ãµÄ²éѯÌõ¼þÍÆµ½×îÄڲ㣨¼´Ê¹ÍƵ½×îÄÚ²ãҲûÓÐÒâÒ壬ÒòΪ×îÄÚ²ã²éѯ²»ÖªµÀRN´ú±íʲô£©¡£Òò´Ë£¬¶ÔÓÚµÚ¶þ¸ö²éѯÓï¾ä£¬Oracle×îÄڲ㷵»Ø¸øÖмä²ãµÄÊÇËùÓÐÂú×ãÌõ¼þµÄÊý¾Ý£¬¶øÖмä²ã·µ»Ø¸ø×îÍâ²ãµÄÒ²ÊÇËùÓÐÊý¾Ý¡£Êý¾ÝµÄ¹ýÂËÔÚ×îÍâ²ãÍê³É£¬ÏÔÈ»Õâ¸öЧÂÊÒª±ÈµÚÒ»¸ö²éѯµÍµÃ¶à¡£
ÉÏÃæ·ÖÎöµÄ²éѯ²»½ö½öÊÇÕë¶Ôµ¥±íµÄ¼òµ¥²éѯ£¬¶ÔÓÚ×îÄÚ²ã²éѯÊǸ´ÔӵĶà±íÁªºÏ²éѯ»ò×îÄÚ²ã²éѯ°üº¬ÅÅÐòµÄÇé¿öÒ»ÑùÓÐЧ¡£
ÏÂÃæ¼òµ¥ÌÖÂÛһ϶à±íÁªºÏµÄÇé¿ö¡£¶ÔÓÚ×î³£¼ûµÄµÈÖµ±íÁ¬½Ó²éѯ£¬CBOÒ»°ã¿ÉÄÜ»á²ÉÓÃÁ½ÖÖÁ¬½Ó·½Ê½NESTED LOOPºÍHASH JOIN£¨MERGE JOINЧÂʱÈHASH JOINЧÂʵͣ¬Ò»°ãCBO²»»á¿¼ÂÇ£©¡£ÔÚÕâÀÓÉÓÚʹÓÃÁË·ÖÒ³£¬Òò´ËÖ¸¶¨ÁËÒ»¸ö·µ»ØµÄ×î´ó¼Ç¼Êý£¬NESTED LOOPÔÚ·µ»Ø¼Ç¼Êý³¬¹ý×î´óֵʱ¿ÉÒÔÂíÉÏÍ£Ö¹²¢½«½á¹û·µ»Ø¸øÖмä²ã£¬¶øHASH JOIN±ØÐë´¦ÀíÍêËùÓнá¹û¼¯£¨MERGE JOI
Ïà¹ØÎĵµ£º
OracleÖÐstart with...connect by prior×Ó¾äÓ÷¨
connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from tablename start with Ìõ¼þ1
connect by Ìõ¼þ2
where Ìõ¼þ3;
Àý£º
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;
¼òµ ......
http://www.51testing.com/?uid-16403-action-viewspace-itemid-101605
Ò»¡¢ORACLE10g×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅÏ¢--×Ô¶¯analyze
´ÓOracle Database 10g¿ªÊ¼£¬OracleÔÚ½¨¿âºó¾ÍĬÈÏ´´½¨ÁËÒ»¸öÃûΪGATHER_STATS_JOBµÄ¶¨Ê±ÈÎÎñ£¬ÓÃÓÚ×Ô¶¯ÊÕ¼¯CBOµÄͳ¼ÆÐÅÏ¢¡£
Õâ¸ö×Ô¶¯ÈÎÎñĬÈÏÇé¿öÏÂÔÚ¹¤×÷ÈÕÍíÉÏ10£º00-6£º00ºÍÖÜĩȫÌì¿ªÆ ......
ÎÒÃǵijɹ¦×ÜÊÇÕ¾ÔÚ¾ÞÈ˵ļç°òÉÏÎÒ£¬ÏÈתÁ˱ðÈ˾Ñ飬×îºóÔÙ×ܽá×Ô¼ºµÄʵÑé½á¹û£¬Ñ§Ï°²ÅÄÜÓнø²½£¡
OracleµÄÊ±ÇøÎÊÌâ
Oracle 9i ¿ªÊ¼¶àÁË 3 ¸ö¹ØÓÚʱ¼äµÄÊý¾ÝÀàÐÍ£ºTIMESTAMP [(precision)] TIMESTAMP [(precision)] WITH TIME ZONE TIMESTAMP [(precision)] WITH LOCAL TIME ZONE£¬ÆäÖÐ TIMESTAMP [(precision)] ......
Redo Byte Address (RBA)
Recent entries in the redo thread of an Oracle instance are addressed using a 3-part redo byte address, or RBA. An RBA is comprised of
the log file sequence number (4 bytes)
the log file block number (4 bytes)
the byte offset into the block at which the redo record sta ......
DB2 תOracle
ijÌìÊÇÐÇÆÚ¼¸µÄº¯Êý
DB2:
select a.yyxe,a.zblb,a.ysdm,a.daynum,b.zjxm from zy_yspb a ,zy_zjlb b where a.ysdm=b.id and a.ksdm=10000 order by abs( a.daynum -DAYOFWEEK(current date)+1) desc,a.daynum,a.zblb
sql.executeQuery:ORA-00936: ȱʧ±í´ïʽ
Oracle :
......