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 ÏòÒ»¸ö±íÖвåÈëÊý¾ÝµÄÁ½ÖÖ·½Ê½£º
Conventional Insert Operations£º´«Í³²åÈë»áÓÅÏÈʹÓøßˮλ֮Ï£¬»á±£Ö¤Êý¾ÝÓ¦ÓÃÍêÕûÐÔ£º¸ßˮλ֮ÏÂÊÇÖ¸£ºÉ¾³ýÖ®ºóµÄÊ£Óà¿Õ¼ä£¬¸ßˮλ֮ÉÏÊÇÖ¸£º´ÓÀ´Ã»ÓÐÓùýµÄ´¦Å®¿é¡£
Direct-path Insert Operatio ......
ʹÓÃscott/tigerÓû§ÏµÄemp±íºÍdept±íÍê³ÉÏÂÁÐÁ·Ï°£¬±íµÄ½á¹¹ËµÃ÷ÈçÏÂ
empÔ±¹¤±í(empnoÔ±¹¤ºÅ/enameÔ±¹¤ÐÕÃû/job¹¤×÷/mgrÉϼ¶±àºÅ/hiredateÊܹÍÈÕÆÚ/salн½ð/commÓ¶½ð/deptno²¿ÃűàºÅ)
dept²¿Ãűí(deptno²¿ÃűàºÅ/dname²¿ÃÅÃû³Æ/locµØµã)
¹¤×Ê £½ н½ð £« Ó¶½ð
1£®ÁгöÖÁÉÙÓÐÒ»¸öÔ±¹¤µÄËùÓв¿ÃÅ
2£®Áгöн½ð±È& ......
Oracle°²×°Íêºó£¬ÆäÖÐÓÐÒ»¸öȱʡµÄÊý¾Ý¿â£¬³ýÁËÕâ¸öȱʡµÄÊý¾Ý¿âÍ⣬ÎÒÃÇ»¹¿ÉÒÔ´´½¨×Ô¼ºµÄÊý¾Ý¿â¡£
¶ÔÓÚ³õѧÕßÀ´Ëµ£¬ÎªÁ˱ÜÃâÂé·³£¬¿ÉÒÔÓÃ'Database Configuration Assistant'Ïòµ¼À´´´½¨Êý¾Ý¿â¡£
´´½¨ÍêÊý¾Ý¿âºó£¬²¢²»ÄÜÁ¢¼´ÔÚÊý¾Ý¿âÖн¨±í£¬±ØÐëÏÈ´´½¨¸ÃÊý¾Ý¿âµÄ ......
http://www.51testing.com/?uid-16403-action-viewspace-itemid-101605
Ò»¡¢ORACLE10g×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅÏ¢--×Ô¶¯analyze
´ÓOracle Database 10g¿ªÊ¼£¬OracleÔÚ½¨¿âºó¾ÍĬÈÏ´´½¨ÁËÒ»¸öÃûΪGATHER_STATS_JOBµÄ¶¨Ê±ÈÎÎñ£¬ÓÃÓÚ×Ô¶¯ÊÕ¼¯CBOµÄͳ¼ÆÐÅÏ¢¡£
Õâ¸ö×Ô¶¯ÈÎÎñĬÈÏÇé¿öÏÂÔÚ¹¤×÷ÈÕÍíÉÏ10£º00-6£º00ºÍÖÜĩȫÌì¿ªÆ ......
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 ......