Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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 ²åÈë×ܽá

Oracle ÏòÒ»¸ö±íÖвåÈëÊý¾ÝµÄÁ½ÖÖ·½Ê½£º
       Conventional Insert Operations£º´«Í³²åÈë»áÓÅÏÈʹÓøßˮλ֮Ï£¬»á±£Ö¤Êý¾ÝÓ¦ÓÃÍêÕûÐÔ£º¸ßˮλ֮ÏÂÊÇÖ¸£ºÉ¾³ýÖ®ºóµÄÊ£Óà¿Õ¼ä£¬¸ßˮλ֮ÉÏÊÇÖ¸£º´ÓÀ´Ã»ÓÐÓùýµÄ´¦Å®¿é¡£
       Direct-path Insert Operatio ......

ÊìÁ·OracleµÄSqlÓï¾ä±Ø×ö18µÀ

 Ê¹ÓÃscott/tigerÓû§ÏµÄemp±íºÍdept±íÍê³ÉÏÂÁÐÁ·Ï°£¬±íµÄ½á¹¹ËµÃ÷ÈçÏÂ
empÔ±¹¤±í(empnoÔ±¹¤ºÅ/enameÔ±¹¤ÐÕÃû/job¹¤×÷/mgrÉϼ¶±àºÅ/hiredateÊܹÍÈÕÆÚ/salн½ð/commÓ¶½ð/deptno²¿ÃűàºÅ)
dept²¿Ãűí(deptno²¿ÃűàºÅ/dname²¿ÃÅÃû³Æ/locµØµã)
¹¤×Ê £½ н½ð £« Ó¶½ð
1£®ÁгöÖÁÉÙÓÐÒ»¸öÔ±¹¤µÄËùÓв¿ÃÅ
2£®Áгöн½ð±È& ......

¹ØÓÚoracleµÄ±í¿Õ¼ä£¬·ÖÇø±í£¬ÒÔ¼°Ë÷ÒýµÄ×ܽá

 http://episode.javaeye.com/blog/183016
±í¿Õ¼ä£º 
OracleµÄUNDOTBS01.DBFÎļþÌ«´óµÄ½â¾ö°ì·¨ 
1¡¢.½ûÖ¹undo tablespace×Ô¶¯Ôö³¤ 
alter   database   datafile   'full_path\undotbs01.dbf'   autoextend   off; 
2.-- ´´½¨Ò»¸öеÄС ......

ORACLE RBA(redo byte address)

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 ......

ORACLE ±í¿Õ¼ä²Ù×÷×ܽá

oracle±í¿Õ¼ä²Ù×÷Ïê½â
Ò»¡¢½¨Á¢±í¿Õ¼ä
CREATE TABLESPACE data01
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
UNIFORM SIZE 128k;
¶þ¡¢½¨Á¢UNDO±í¿Õ¼ä
CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M
#×¢Ò⣺ÔÚOPEN״̬ÏÂijЩʱ¿ÌÖ»ÄÜÓÃÒ»¸öUNDO±í¿Õ¼ä£¬Èç¹ûÒ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ