{"id":38,"date":"2011-08-25T23:15:45","date_gmt":"2011-08-25T15:15:45","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=38"},"modified":"2011-09-22T18:38:12","modified_gmt":"2011-09-22T10:38:12","slug":"rownum%e5%9b%ba%e5%8c%96%e5%a4%96%e9%83%a8%e8%a1%a8%e7%bb%93%e6%9e%9c%e9%9b%86%e5%ad%98%e5%9c%a8%e9%97%ae%e9%a2%98","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=38","title":{"rendered":"ROWNUM\u56fa\u5316\u5916\u90e8\u8868\u7ed3\u679c\u96c6\u5b58\u5728\u95ee\u9898"},"content":{"rendered":"<p>\u5728\u5ba2\u6237\u768411.2.0.2\u73af\u5883\u4e2d\u78b0\u5230\u4e86\u8fd9\u4e2a\u95ee\u9898\uff0cOracle\u5728\u5904\u7406\u5305\u542bROWNUM\u56fa\u5316\u7684\u5916\u90e8\u8868\u52a0\u8f7d\u6570\u636e\u65f6\u8fd4\u56de\u9519\u8bef\u7684\u7ed3\u679c\u3002<br \/>\n\u5916\u90e8\u8868\u6784\u9020\u63cf\u8ff0\u53ef\u4ee5\u53c2\u8003\uff1a<a href=\"https:\/\/yangtingkun.net\/?p=35\">\u5229\u7528\u5916\u90e8\u8868\u8bfb\u53d6\u544a\u8b66\u65e5\u5fd7\u6587\u4ef6<\/a><br \/>\n\u5ba2\u6237\u73af\u5883\u4e2d\u521b\u5efa\u7684\u5916\u90e8\u8868\u548c\u4e0a\u9762\u94fe\u63a5\u4e2d\u7684\u4f8b\u5b50\u51e0\u4e4e\u5b8c\u5168\u4e00\u81f4\uff1a<\/p>\n<pre lang=\"SQL\">SQL&gt; CREATE TABLE T_ALERT\r\n  2  (TEXT VARCHAR2(4000)\r\n  3  )\r\n  4  ORGANIZATION EXTERNAL\r\n  5  (TYPE ORACLE_LOADER\r\n  6  DEFAULT DIRECTORY D_ALERT\r\n  7  ACCESS PARAMETERS\r\n  8  (RECORDS DELIMITED BY NEWLINE\r\n  9  FIELDS (TEXT (1:255) CHAR))\r\n 10  LOCATION ('alert_xshdb1.log'));\r\nTable created.\r\nSQL&gt; SELECT * FROM T_ALERT WHERE ROWNUM &lt; 10;\r\nTEXT\r\n------------------------------------------------------------------------------------------\r\nTue Nov 16 15:32:47 2010\r\nStarting ORACLE instance (normal)\r\nLICENSE_MAX_SESSION = 0\r\nLICENSE_SESSIONS_WARNING = 0\r\nPrivate Interface 'eth2:1' configured from GPnP for use as a private interconnect.\r\n  [name='eth2:1', type=1, ip=169.254.88.96, mac=00-15-17-f8-aa-a2, net=169.254.0.0\/16, mask=255.255.0.0, use=haip:cluster_interconnect\/62]\r\nPublic Interface 'eth0' configured from GPnP for use as a public interface.\r\n  [name='eth0', type=1, ip=10.0.10.53, mac=00-21-5e-11-7d-c0, net=10.0.10.0\/24, mask=255.255.255.0, use=public\/1]\r\nPublic Interface 'eth0:1' configured from GPnP for use as a public interface.\r\n9 rows selected.<\/pre>\n<p>\u5916\u90e8\u8868\u8bbf\u95ee\u6b63\u5e38\uff0c\u4f46\u662f\u4e0b\u9762\u8fd9\u4e24\u4e2a\u7b49\u4ef7\u7684\u5199\u6cd5\uff0c\u5f97\u5230\u7684\u7ed3\u679c\u5374\u5927\u76f8\u5f84\u5ead\uff1a<\/p>\n<pre lang=\"SQL\">SQL&gt; SELECT COUNT(*) FROM T_ALERT;\r\n  COUNT(*)\r\n----------\r\n    158299\r\nSQL&gt; WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)\r\n  2  SELECT * FROM A B\r\n  3  WHERE B.RN &gt;= (SELECT C.RN FROM A C WHERE TEXT = 'Mon Aug 22 02:00:00 2011');\r\n        RN TEXT\r\n---------- --------------------------------------------------------------------------------\r\n    158300 Starting ORACLE instance (normal)\r\n    158301 LICENSE_MAX_SESSION = 0\r\n    158302 LICENSE_SESSIONS_WARNING = 0\r\n    158303 Private Interface 'eth2:1' configured from GPnP for use as a private interconnect.\r\n    158304   [name='eth2:1', type=1, ip=169.254.88.96, mac=00-15-17-f8-aa-a2, net=169.254.0.0\/16, mask=255.255.0.0, use=haip:cluster_interconnect\/62]\r\n    158305 Public Interface 'eth0' configured from GPnP for use as a public interface.\r\n    158306   [name='eth0', type=1, ip=10.0.10.53, mac=00-21-5e-11-7d-c0, net=10.0.10.0\/24, mask=255.255.255.0, use=public\/1]\r\n    158307 Public Interface 'eth0:1' configured from GPnP for use as a public interface.\r\n    158308   [name='eth0:1', type=1, ip=10.0.10.55, mac=00-21-5e-11-7d-c0, net=10.0.10.0\/24, mask=255.255.255.0, use=public\/1]\r\n    158309 Public Interface 'eth0:2' configured from GPnP for use as a public interface.\r\n    158310   [name='eth0:2', type=1, ip=10.0.10.57, mac=00-21-5e-11-7d-c0, net=10.0.10.0\/24, mask=255.255.255.0, use=public\/1]\r\n    158311 Shared memory segment for instance monitoring created\r\n    158312 Picked latch-free SCN scheme 3\r\n.\r\n.\r\n.\r\n    316587 Sun Aug 21 06:00:00 2011\r\n    316588 VKRM started with pid=120, OS id=32339\r\n    316589 Mon Aug 22 02:00:00 2011\r\n    316590 Closing Resource Manager plan via scheduler window\r\n    316591 Clearing Resource Manager plan via parameter\r\n    316592 Mon Aug 22 02:00:35 2011\r\n    316593 Thread 1 advanced to log sequence 8130 (LGWR switch)\r\n    316594   Current log# 2 seq# 8130 mem# 0: +DATADG\/xshdb\/onlinelog\/group_2.258.735233587\r\n    316595   Current log# 2 seq# 8130 mem# 1: +FRADG\/xshdb\/onlinelog\/group_2.258.735233587\r\n    316596 Mon Aug 22 02:00:36 2011\r\n    316597 Archived Log entry 10625 added for thread 1 sequence 8129 ID 0x287369ad dest 1:\r\n158298 rows selected.\r\nSQL&gt; WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)\r\n  2  SELECT B.* FROM A B, A C\r\n  3  WHERE B.RN &gt;= C.RN\r\n  4  AND C.TEXT = 'Mon Aug 22 02:00:00 2011';\r\n        RN TEXT\r\n---------- -------------------------------------------------------------------------------\r\n    158299 Archived Log entry 10625 added for thread 1 sequence 8129 ID 0x287369ad dest 1:\r\n    158298 Mon Aug 22 02:00:36 2011\r\n    158297   Current log# 2 seq# 8130 mem# 1: +FRADG\/xshdb\/onlinelog\/group_2.258.735233587\r\n    158296   Current log# 2 seq# 8130 mem# 0: +DATADG\/xshdb\/onlinelog\/group_2.258.735233587\r\n    158295 Thread 1 advanced to log sequence 8130 (LGWR switch)\r\n    158294 Mon Aug 22 02:00:35 2011\r\n    158293 Clearing Resource Manager plan via parameter\r\n    158292 Closing Resource Manager plan via scheduler window\r\n    158291 Mon Aug 22 02:00:00 2011\u3001\r\n9 rows selected.<\/pre>\n<p>\u7b2c\u4e8c\u4e2aSQL\u8bed\u53e5\u7684\u7ed3\u679c\u662f\u6211\u4eec\u6240\u671f\u671b\u7684\uff0c\u800c\u7b2c\u4e00\u4e2aSQL\u7684\u7ed3\u679c\u660e\u663e\u662f\u9519\u8bef\u7684\uff0c\u5bf9\u6bd4\u8303\u56f4\u7ed3\u679c\u4e0d\u96be\u53d1\u73b0\uff0c\u9664\u4e86\u7b2c\u4e00\u6761\u8bb0\u5f55\u4e4b\u5916\uff0c\u8bed\u53e5\u8fd4\u56de\u4e86alert\u65e5\u5fd7\u6587\u4ef6\u4e2d\u5176\u4ed6\u6240\u6709\u7684\u6570\u636e\uff0c\u53ea\u4e0d\u8fc7ROWNUM\u7684\u7ed3\u679c\u5230\u662f\u7b26\u5408\u8981\u6c42\u3002<br \/>\n\u68c0\u67e5\u4e24\u4e2aSQL\u5206\u522b\u7684\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<pre lang=\"SQL\">SQL&gt; EXPLAIN PLAN FOR\r\n  2  WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)\r\n  3  SELECT * FROM A B\r\n  4  WHERE B.RN &gt;= (SELECT C.RN FROM A C WHERE TEXT = 'Mon Aug 22 02:00:00 2011');\r\nExplained.\r\nSQL&gt; SQL&gt; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------------\r\nPlan hash value: 2687076668\r\n-----------------------------------------------------------------------------------------\r\n| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-----------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT              |         |  8168 |    15M|    58   (0)| 00:00:01 |\r\n|*  1 |  VIEW                         |         |  8168 |    15M|    29   (0)| 00:00:01 |\r\n|   2 |   COUNT                       |         |       |       |            |          |\r\n|   3 |    EXTERNAL TABLE ACCESS FULL | T_ALERT |  8168 |    15M|    29   (0)| 00:00:01 |\r\n|*  4 |   VIEW                        |         |  8168 |    15M|    29   (0)| 00:00:01 |\r\n|   5 |    COUNT                      |         |       |       |            |          |\r\n|   6 |     EXTERNAL TABLE ACCESS FULL| T_ALERT |  8168 |    15M|    29   (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n   1 - filter(\"B\".\"RN\"&gt;= (SELECT \"C\".\"RN\" FROM  (SELECT ROWNUM \"RN\",\"TEXT\"\r\n              \"TEXT\" FROM \"T_ALERT\" \"T_ALERT\") \"C\" WHERE \"TEXT\"='Mon Aug 22 02:00:00 2011'))\r\n   4 - filter(\"TEXT\"='Mon Aug 22 02:00:00 2011')\r\n20 rows selected.\r\nSQL&gt; EXPLAIN PLAN FOR\r\n  2  WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)\r\n  3  SELECT B.* FROM A B, A C\r\n  4  WHERE B.RN &gt;= C.RN\r\n  5  AND C.TEXT = 'Mon Aug 22 02:00:00 2011';\r\nExplained.\r\nSQL&gt; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------------\r\nPlan hash value: 883860547\r\n-------------------------------------------------------------------------------------------\r\n| Id | Operation                      |Name   |Rows |Bytes|TempSpc| Cost (%CPU)| Time     |\r\n-------------------------------------------------------------------------------------------\r\n|  0 | SELECT STATEMENT               |       |3335K|  12G|       |  6960   (1)| 00:01:24 |\r\n|  1 |  MERGE JOIN                    |       |3335K|  12G|       |  6960   (1)| 00:01:24 |\r\n|  2 |   SORT JOIN                    |       |8168 |  15M|    31M|  3469   (1)| 00:00:42 |\r\n|  3 |    VIEW                        |       |8168 |  15M|       |    29   (0)| 00:00:01 |\r\n|  4 |     COUNT                      |       |     |     |       |            |          |\r\n|  5 |      EXTERNAL TABLE ACCESS FULL|T_ALERT|8168 |  15M|       |    29   (0)| 00:00:01 |\r\n|* 6 |   SORT JOIN                    |       |8168 |  15M|    31M|  3469   (1)| 00:00:42 |\r\n|* 7 |    VIEW                        |       |8168 |  15M|       |    29   (0)| 00:00:01 |\r\n|  8 |     COUNT                      |       |     |     |       |            |          |\r\n|  9 |      EXTERNAL TABLE ACCESS FULL|T_ALERT|8168 |  15M|       |    29   (0)| 00:00:01 |\r\n-------------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n   6 - access(INTERNAL_FUNCTION(\"B\".\"RN\")&gt;=INTERNAL_FUNCTION(\"C\".\"RN\"))\r\n       filter(INTERNAL_FUNCTION(\"B\".\"RN\")&gt;=INTERNAL_FUNCTION(\"C\".\"RN\"))\r\n   7 - filter(\"C\".\"TEXT\"='Mon Aug 22 02:00:00 2011')\r\n23 rows selected.<\/pre>\n<p>\u5bf9\u4e8e\u8fde\u63a5\u7684\u5199\u6cd5\uff0cORACLE\u91c7\u7528\u4e86MERGE JOIN\u3002\u800c\u7ed3\u679c\u9519\u8bef\u7684SQL\uff0cOracle\u76f4\u63a5\u901a\u8fc7\u5bf9\u4e24\u4e2aVIEW\u8fdb\u884c\u8fc7\u6ee4\u7684\u65b9\u5f0f\u83b7\u53d6\u8bb0\u5f55\uff0c\u8fd9\u65f6\u7b2c\u4e00\u4e2aVIEW\u4e2d\u7684ROWNUM\u56fa\u5316\u7ed3\u679c\u96c6\u5e76\u672a\u751f\u6548\uff0c\u6000\u7591\u662fOracle\u5728\u5904\u7406\u5916\u90e8\u8868\u65f6\u51fa\u73b0\u7684bug\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5728\u5ba2\u6237\u768411.2.0.2\u73af\u5883\u4e2d\u78b0\u5230\u4e86\u8fd9\u4e2a\u95ee\u9898\uff0cOracle\u5728\u5904\u7406\u5305\u542bROWNUM\u56fa\u5316\u7684\u5916\u90e8\u8868\u52a0\u8f7d\u6570\u636e\u65f6\u8fd4\u56de\u9519\u8bef\u7684\u7ed3\u679c\u3002 \u5916\u90e8\u8868\u6784\u9020\u63cf\u8ff0\u53ef\u4ee5\u53c2\u8003\uff1a\u5229\u7528\u5916\u90e8\u8868\u8bfb\u53d6\u544a\u8b66\u65e5\u5fd7\u6587\u4ef6 \u5ba2\u6237\u73af\u5883\u4e2d\u521b\u5efa\u7684\u5916\u90e8\u8868\u548c\u4e0a\u9762\u94fe\u63a5\u4e2d\u7684\u4f8b\u5b50\u51e0\u4e4e\u5b8c\u5168\u4e00\u81f4\uff1a SQL&gt; CREATE TABLE T_ALERT 2 (TEXT VARCHAR2(4000) 3 ) 4 ORGANIZATION EXTERNAL 5 (TYPE ORACLE_LOADER 6 DEFAULT DIRECTORY D_ALERT 7 ACCESS PARAMETERS 8 (RECORDS DELIMITED BY NEWLINE 9 FIELDS (TEXT (1:255) CHAR)) 10 LOCATION (&#8216;alert_xshdb1.log&#8217;)); Table created. &hellip; <a href=\"https:\/\/yangtingkun.net\/?p=38\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5],"tags":[27,28,76],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-C","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/38"}],"collection":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=38"}],"version-history":[{"count":8,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/38\/revisions"}],"predecessor-version":[{"id":63,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/38\/revisions\/63"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=38"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=38"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=38"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}