\u8fd9\u7bc7\u6587\u7ae0\u901a\u8fc7\u4f8b\u5b50\u8bf4\u660e\u5206\u9875\u67e5\u8be2\u4f7f\u7528\u7684NESTED LOOP\u64cd\u4f5c\uff0c\u5728\u5206\u9875\u67e5\u8be2\u7ffb\u5230\u6700\u540e\u51e0\u9875\u65f6\u7684\u6027\u80fd\u95ee\u9898\uff1a
\nSQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;
\n\u8868\u5df2\u521b\u5efa\u3002
\nSQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;
\n\u8868\u5df2\u521b\u5efa\u3002
\nSQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);
\n\u8868\u5df2\u66f4\u6539\u3002
\nSQL> ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER)
\n2 REFERENCES T(USERNAME);
\n\u8868\u5df2\u66f4\u6539\u3002
\nSQL> CREATE INDEX IND_T1_OWNER ON T1(NAME);
\n\u7d22\u5f15\u5df2\u521b\u5efa\u3002
\nSQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’)
\nPL\/SQL \u8fc7\u7a0b\u5df2\u6210\u529f\u5b8c\u6210\u3002
\nSQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T1’)
\nPL\/SQL \u8fc7\u7a0b\u5df2\u6210\u529f\u5b8c\u6210\u3002
\nSQL> SET AUTOT TRACE
\nSQL> SELECT \/*+ FIRST_ROWS *\/ USER_ID, USERNAME, NAME
\n2 FROM
\n3 (
\n4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
\n5 FROM
\n6 (
\n7 SELECT T.USER_ID, T.USERNAME, T1.NAME
\n8 FROM T, T1
\n9 WHERE T.USERNAME = T1.OWNER
\n10 )
\n11 WHERE ROWNUM <= 20
\n12 )
\n13 WHERE RN >= 11;
\n\u5df2\u9009\u62e910\u884c\u3002
\nExecution Plan
\n———————————————————-
\n0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20 Bytes=1200)
\n1 0 VIEW (Cost=97811 Card=20 Bytes=1200)
\n2 1 COUNT (STOPKEY)
\n3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
\n4 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)
\n5 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=1 Card=1 Bytes=11)
\n6 5 INDEX (UNIQUE SCAN) OF ‘PK_T’ (UNIQUE)<\/p>\n
Statistics
\n———————————————————-
\n0 recursive calls
\n0 db block gets
\n28 consistent gets
\n0 physical reads
\n0 redo size
\n574 bytes sent via SQL*Net to client
\n503 bytes received via SQL*Net from client
\n2 SQL*Net roundtrips to\/from client
\n0 sorts (memory)
\n0 sorts (disk)
\n10 rows processed
\nSQL> SELECT USER_ID, USERNAME, NAME
\n2 FROM
\n3 (
\n4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
\n5 FROM
\n6 (
\n7 SELECT T.USER_ID, T.USERNAME, T1.NAME
\n8 FROM T, T1
\n9 WHERE T.USERNAME = T1.OWNER
\n10 )
\n11 )
\n12 WHERE RN BETWEEN 11 AND 20;
\n\u5df2\u9009\u62e910\u884c\u3002
\nExecution Plan
\n———————————————————-
\n0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)
\n1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)
\n2 1 COUNT
\n3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
\n4 3 TABLE ACCESS (FULL) OF ‘T’ (Cost=2 Card=12 Bytes=132)
\n5 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)<\/p>\n
Statistics
\n———————————————————-
\n0 recursive calls
\n0 db block gets
\n8586 consistent gets
\n8052 physical reads
\n0 redo size
\n574 bytes sent via SQL*Net to client
\n503 bytes received via SQL*Net from client
\n2 SQL*Net roundtrips to\/from client
\n0 sorts (memory)
\n0 sorts (disk)
\n10 rows processed
\n\u5728\u5206\u9875\u67e5\u8be2\u7684\u524d\u51e0\u9875\uff0cNESTED LOOP\u64cd\u4f5c\u6bd4HASH JOIN\u64cd\u4f5c\u6548\u7387\u9ad8\u5f97\u591a\u3002
\nSQL> SET AUTOT OFF
\nSQL> SELECT COUNT(*) FROM T, T1 WHERE USERNAME = OWNER;
\nCOUNT(*)
\n———-
\n96985
\nSQL> SELECT USER_ID, USERNAME, NAME
\n2 FROM
\n3 (
\n4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
\n5 FROM
\n6 (
\n7 SELECT T.USER_ID, T.USERNAME, T1.NAME
\n8 FROM T, T1
\n9 WHERE T.USERNAME = T1.OWNER
\n10 )
\n11 )
\n12 WHERE RN BETWEEN 96971 AND 96980;
\n\u5df2\u9009\u62e910\u884c\u3002
\nExecution Plan
\n———————————————————-
\n0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)
\n1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)
\n2 1 COUNT
\n3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
\n4 3 TABLE ACCESS (FULL) OF ‘T’ (Cost=2 Card=12 Bytes=132)
\n5 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)<\/p>\n
Statistics
\n———————————————————-
\n0 recursive calls
\n0 db block gets
\n8586 consistent gets
\n8068 physical reads
\n0 redo size
\n571 bytes sent via SQL*Net to client
\n503 bytes received via SQL*Net from client
\n2 SQL*Net roundtrips to\/from client
\n0 sorts (memory)
\n0 sorts (disk)
\n10 rows processed
\n\u5bf9\u4e8e\u6700\u540e\u51e0\u9875\uff0c\u91c7\u7528HASH JOIN\u7684\u65b9\u5f0f\uff0c\u6267\u884c\u6548\u7387\u51e0\u4e4e\u6ca1\u6709\u4efb\u4f55\u6539\u53d8\uff0c\u800c\u91c7\u7528NESTED LOOP\u65b9\u5f0f\uff0c\u5219\u6548\u7387\u4e25\u91cd\u4e0b\u964d\uff0c\u800c\u4e14\u8fdc\u8fdc\u4f4e\u4e8eHASH JOIN\u7684\u65b9\u5f0f\u3002
\nSQL> SELECT \/*+ FIRST_ROWS *\/ USER_ID, USERNAME, NAME
\n2 FROM
\n3 (
\n4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
\n5 FROM
\n6 (
\n7 SELECT T.USER_ID, T.USERNAME, T1.NAME
\n8 FROM T, T1
\n9 WHERE T.USERNAME = T1.OWNER
\n10 )
\n11 WHERE ROWNUM <= 96980
\n12 )
\n13 WHERE RN >= 96971;
\n\u5df2\u9009\u62e910\u884c\u3002
\nExecution Plan
\n———————————————————-
\n0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96980 Bytes=5818800)
\n1 0 VIEW (Cost=97811 Card=96980 Bytes=5818800)
\n2 1 COUNT (STOPKEY)
\n3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
\n4 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)
\n5 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=1 Card=1 Bytes=11)
\n6 5 INDEX (UNIQUE SCAN) OF ‘PK_T’ (UNIQUE)<\/p>\n
Statistics
\n———————————————————-
\n0 recursive calls
\n0 db block gets
\n105566 consistent gets
\n8068 physical reads
\n0 redo size
\n571 bytes sent via SQL*Net to client
\n503 bytes received via SQL*Net from client
\n2 SQL*Net roundtrips to\/from client
\n0 sorts (memory)
\n0 sorts (disk)
\n10 rows processed
\n\u5206\u9875\u67e5\u8be2\u4e00\u822c\u60c5\u51b5\u4e0b\uff0c\u5f88\u5c11\u4f1a\u7ffb\u5230\u6700\u540e\u4e00\u7bc7\uff0c\u5982\u679c\u53ea\u662f\u5076\u5c14\u78b0\u5230\u8fd9\u79cd\u60c5\u51b5\uff0c\u5bf9\u7cfb\u7edf\u6027\u80fd\u4e0d\u4f1a\u6709\u5f88\u5927\u7684\u5f71\u54cd\uff0c\u4f46\u662f\u5982\u679c\u7ecf\u5e38\u78b0\u5230\u8fd9\u79cd\u60c5\u51b5\uff0c\u5728\u8bbe\u8ba1\u5206\u9875\u67e5\u8be2\u65f6\u5e94\u8be5\u7ed9\u4e88\u8db3\u591f\u7684\u8003\u8651\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u8fd9\u7bc7\u6587\u7ae0\u901a\u8fc7\u4f8b\u5b50\u8bf4\u660e\u5206\u9875\u67e5\u8be2\u4f7f\u7528\u7684NESTED LOOP\u64cd\u4f5c\uff0c\u5728\u5206\u9875\u67e5\u8be2\u7ffb\u5230\u6700\u540e\u51e0\u9875\u65f6\u7684\u6027\u80fd\u95ee\u9898\uff1a SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS; \u8868\u5df2\u521b\u5efa\u3002 SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE; \u8868\u5df2\u521b\u5efa\u3002 SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME); \u8868\u5df2\u66f4\u6539\u3002 SQL> ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER) 2 REFERENCES T(USERNAME); \u8868\u5df2\u66f4\u6539\u3002 SQL> CREATE INDEX IND_T1_OWNER ON T1(NAME); \u7d22\u5f15\u5df2\u521b\u5efa\u3002 SQL> … Continue reading Oracle\u5206\u9875\u67e5\u8be2\u8bed\u53e5\u6bd4\u8f83 rownum \u4e0e between<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[200],"class_list":["post-596","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle"],"_links":{"self":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/596","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=596"}],"version-history":[{"count":1,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/596\/revisions"}],"predecessor-version":[{"id":597,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/596\/revisions\/597"}],"wp:attachment":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}