Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use NESTED LOOP operation in Oracle". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use NESTED LOOP in Oracle.

An example is given to illustrate the performance problems of the NESTED LOOP operation used by the paging query when the paging query turns to the last few pages:

SQL > CREATE TABLE T AS SELECT * FROM DBA_USERS

The table has been created.

SQL > CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE

The table has been created.

SQL > ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME)

The table has changed.

SQL > ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER)

2 REFERENCES T (USERNAME)

The table has changed.

SQL > CREATE INDEX IND_T1_OWNER ON T1 (NAME)

The index has been created.

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (USER,'T')

The PL/SQL process completed successfully.

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T1')

The PL/SQL process completed successfully.

SQL > SELECT / * + FIRST_ROWS * / USER_ID, USERNAME, NAME

2 FROM

3 (

4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME

5 FROM

6 (

7 SELECT T.USER_ID, T.USERNAME, T1.NAME

8 FROM T, T1

9 WHERE T.USERNAME = T1.OWNER

10)

11 WHERE ROWNUM = 11

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20 Bytes=1200)

1 0 VIEW (Cost=97811 Card=20 Bytes=1200)

2 1 COUNT (STOPKEY)

3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)

4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)

5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)

6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics

0 recursive calls

0 db block gets

28 consistent gets

0 physical reads

0 redo size

574 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

SQL > SELECT USER_ID, USERNAME, NAME

2 FROM

3 (

4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME

5 FROM

6 (

7 SELECT T.USER_ID, T.USERNAME, T1.NAME

8 FROM T, T1

9 WHERE T.USERNAME = T1.OWNER

10)

11)

12 WHERE RN BETWEEN 11 AND 20

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)

10 VIEW (Cost=830 Card=96985 Bytes=5819100)

2 1 COUNT

3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)

4 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)

5 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)

Statistics

0 recursive calls

0 db block gets

8586 consistent gets

8052 physical reads

0 redo size

574 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

In the first few pages of a paged query, NESTED LOOP operations are much more efficient than HASH JOIN operations.

SQL > SET AUTOT OFF

SQL > SELECT COUNT (*) FROM T, T1 WHERE USERNAME = OWNER

COUNT (*)

-

96985

SQL > SET AUTOT TRACE

SQL > SELECT USER_ID, USERNAME, NAME

2 FROM

3 (

4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME

5 FROM

6 (

7 SELECT T.USER_ID, T.USERNAME, T1.NAME

8 FROM T, T1

9 WHERE T.USERNAME = T1.OWNER

10)

11)

12 WHERE RN BETWEEN 96971 AND 96980

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)

10 VIEW (Cost=830 Card=96985 Bytes=5819100)

2 1 COUNT

3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)

4 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)

5 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)

Statistics

0 recursive calls

0 db block gets

8586 consistent gets

8068 physical reads

0 redo size

571 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

For the last few pages, there is almost no change in execution efficiency with the HASH JOIN approach, while with the NESTED LOOP approach, the efficiency is significantly lower and much lower than the HASH JOIN approach.

SQL > SELECT / * + FIRST_ROWS * / USER_ID, USERNAME, NAME

2 FROM

3 (

4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME

5 FROM

6 (

7 SELECT T.USER_ID, T.USERNAME, T1.NAME

8 FROM T, T1

9 WHERE T.USERNAME = T1.OWNER

10)

11 WHERE ROWNUM = 96971

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96980 Bytes=5818800)

1 0 VIEW (Cost=97811 Card=96980 Bytes=5818800)

2 1 COUNT (STOPKEY)

3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)

4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)

5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)

6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics

0 recursive calls

0 db block gets

105566 consistent gets

8068 physical reads

0 redo size

571 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

In general, paging queries rarely turn to the last article. If you encounter this situation only occasionally, it will not have a great impact on system performance, but if you often encounter this situation, enough consideration should be given to the design of paging queries.

At this point, I believe you have a deeper understanding of "how to use NESTED LOOP operation in Oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.