Shulou(Shulou.com)06/02 Report--

The paging query statement of Oracle can basically be applied according to the format given in this article.

Oracle paging query statement (1): http://yangtingkun.itpub.net/post/468/100278

Oracle paging query statement (2): http://yangtingkun.itpub.net/post/468/101703

Move on to the second case of the query, including table joins:

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.

The T and T1 tables are created, and by default, HASH JOIN is much more efficient than NESTED LOOP:

SQL > SET AUTOT TRACE

SQL > SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER

96985 rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=844 Card=96985 Bytes=46164860)

1 0 HASH JOIN (Cost=844 Card=96985 Bytes=46164860)

2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=1044)

3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=37727165)

Statistics

39 recursive calls

0 db block gets

14475 consistent gets

7279 physical reads

0 redo size

37565579 bytes sent via SQL*Net to client

71618 bytes received via SQL*Net from client

6467 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

96985 rows processed

SQL > SELECT / * + FIRST_ROWS * / * FROM T, T1 WHERE T.USERNAME = T1.OWNER

96985 rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985 Bytes=46164860)

1 0 NESTED LOOPS (Cost=97811 Card=96985 Bytes=46164860)

2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=37727165)

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

4 3 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics

0 recursive calls

0 db block gets

117917 consistent gets

7268 physical reads

0 redo size

37565579 bytes sent via SQL*Net to client

71618 bytes received via SQL*Net from client

6467 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

96985 rows processed

But if the inner layer of the paging query is this kind of join query, you can get the first N records faster by using NESTED LOOP.

Let's take a look at the paging query in this case:

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 WHERE ROWNUM = 11

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=20 Bytes=1200)

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

2 1 COUNT (STOPKEY)

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

8 consistent gets

7 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 / * + 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

It seems that HASH JOIN is more efficient. Is that wrong?

In fact, this phenomenon is caused by the particularity of this example. The T table is created based on DBA_USERS, and this table is very small. The first step in HASH JOIN, that is, the full table scan of the first table, cannot apply STOPKEY, which is the advantage of NESTED LOOP over HASH JOIN mentioned above. However, in this example, the first table happens to be very small, and the cost of a full scan of this table is extremely low, thus making HASH JOIN more efficient. However, this has nothing in common, and if the size of the two tables is similar, or if Oracle mistakenly chooses to scan the large table first, the efficiency of using HASH JOIN will be much lower.

SQL > SELECT USER_ID, USERNAME, NAME

2 FROM

3 (

4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME

5 FROM

6 (

7 SELECT / * + ORDERED * / T.USER_ID, T.USERNAME, T1.NAME

8 FROM T1, T

9 WHERE T.USERNAME = T1.OWNER

10)

11 WHERE ROWNUM = 11

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=951 Card=20 Bytes=1200)

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

2 1 COUNT (STOPKEY)

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

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

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

Statistics

0 recursive calls

0 db block gets

8585 consistent gets

7310 physical reads

0 redo size

601 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

Through the HINT prompt, let Oracle scan the large table first, and this time the result is obvious. NESTED LOOP works much better than HASH JOIN.

Next, let's continue to compare the writing methods of the two paging operations. In order to make the results more representative, we use the FIRST_ROWS prompt to let Oracle join tables in the way of NESTED LOOP:

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 / * + 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)

12 WHERE RN BETWEEN 11 AND 20

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985 Bytes=5819100)

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

2 1 COUNT

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

105571 consistent gets

7299 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

There is a great difference in the efficiency of the two writing methods. The key is still whether STOPKEY can be applied to the innermost query.

For table joins, consider adding FIRST_ROWS hints when writing paged queries, which helps to return query results faster.

In fact, FIRST_ROWS prompts can be added not only to table joins, but to all paging queries. However, it should be noted that the goal of paging query is to return the first N records as soon as possible, so both ROWNUM and FIRST_ROWS mechanisms improve the query speed of the first few pages. For the last few pages of paging query, using these mechanisms not only can not improve the query speed, but will significantly reduce the query efficiency. Users should be aware of this.

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.