Oracle paging query statement (III)

Continue to look at the second case of the query, including the table connection:

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

SQL> ALTER TABLE T1 add constraint fk_t1_owner foreign key (owner)
2 references T (username );

The table has been 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 is successfully completed.

SQL> exec dbms_stats.gather_table_stats (user, 't1 ')

The PL/SQL process is successfully completed.

Table T and table T1 are created. 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;

Row 96985 has 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 cballs
0 dB block gets
14475 consistent gets
7279 physical reads
0 redo size
37565579 bytes sent via SQL * Net to client
71618 bytes encoded ed 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;

Row 96985 has 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 cballs
0 dB block gets
117917 consistent gets
7268 physical reads
0 redo size
37565579 bytes sent via SQL * Net to client
71618 bytes encoded ed via SQL * Net From Client
6467 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
96985 rows processed

However, if the inner layer of the paging query is such a connection query, you can use the nested loop to get the first N records faster.

Next, 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 <= 20
12)
13 where rn> = 11;

10 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 cballs
0 dB block gets
8 consistent gets
7 physical reads
0 redo size
574 bytes sent via SQL * Net to client
503 bytes encoded ed 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 <= 20
12)
13 where rn> = 11;

10 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 cballs
0 dB block gets
28 consistent gets
0 physical reads
0 redo size
574 bytes sent via SQL * Net to client
503 bytes encoded ed 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 it wrong.

In fact, this phenomenon is caused by the particularity of this example. The T table is created based on dba_users. This table is small. In hash join, the first step is that the full table scan of the first table cannot apply the stopkey, which is the advantage of the nested loop mentioned above over hash join. However, in this example, the first table is very small, and the full scan of this table is very low. Therefore, it seems that hash join is more efficient. However, this is not common. If the size of the two tables is similar, or Oracle chooses to scan the large table first, the efficiency of using hash join is 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 <= 20
12)
13 where rn> = 11;

10 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 cballs
0 dB block gets
8585 consistent gets
7310 physical reads
0 redo size
601 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
10 rows processed

With the hint prompt, let oracle first scan the large table, and the result is very obvious this time. The effect of nested loop is much better than that of hash join.

Next, we will continue to compare the two paging operations. In order to make the results more representative, the first_rows prompt is used here to let Oracle use the nested loop method for table join:

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 <= 20
12)
13 where rn> = 11;

10 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 cballs
0 dB block gets
28 consistent gets
0 physical reads
0 redo size
574 bytes sent via SQL * Net to client
503 bytes encoded ed 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;

10 rows have been selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = hint: first_rows (cost = 97811 card = 96985 bytes = 5819100)
1 0 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 cballs
0 dB block gets
105571 consistent gets
7299 physical reads
0 redo size
574 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
10 rows processed

The efficiency of the two statements varies greatly. The key is whether the stopkey can be applied to the innermost query.

For Table join, you can add the first_rows prompt when writing paging queries, which helps to return query results faster.

In fact, it is not just table join. You can add the first_rows prompt to all paging queries. However, note that the purpose of paging query is to return the first N records as soon as possible. Therefore, the rownum and first_rows mechanisms increase the query speed of the previous pages, for the last few pages of the paging query, using these mechanisms will not improve the query speed, but will significantly reduce the query efficiency, users should be aware of this point.