Optimization of one SQL page

Today, we optimized a paging SQL statement. Although we have optimized thousands of SQL statements in the past, they are all OLAP statements. Although there are OLTP statements, we have never done paging optimization, so record it here.

The SQL and execution plan are as follows:

SQL> SELECT A.ROWNO,EMS_EVENT_VIEW.* FROM EMS_EVENT_VIEW,  2  (SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY first_occurrence_time DESC) AS ROWNO,EVENT_ID   3                    FROM EMS_EVENT_VIEW                 WHERE (first_occurrence_time>to_date('2012-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')   4    5                            and first_occurrence_time<to_date('2012-02-29 09:42:35','yyyy-mm-dd hh24:mi:ss'))                      or (last_occurrence_time>to_date('2012-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')                          and last_occurrence_time<to_date('2012-02-29 09:42:35','yyyy-mm-dd hh24:mi:ss')))  6    7    8           WHERE ROWNO>=0 AND ROWNO<=20) A  9  WHERE EMS_EVENT_VIEW.EVENT_ID=A.EVENT_ID;Plan hash value: 2052413575-------------------------------------------------------------------------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                          | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                   |                             |      1 |        |     20 |00:00:53.37 |     757K|       |       |          ||*  1 |  HASH JOIN                         |                             |      1 |     81G|     20 |00:00:53.37 |     757K|  1179K|  1179K| 6598K (0)||*  2 |   VIEW                             |                             |      1 |   2104K|     20 |00:00:30.83 |     101K|       |       |          ||*  3 |    WINDOW SORT PUSHED RANK         |                             |      1 |   2104K|     21 |00:00:30.83 |     101K|  4096 |  4096 | 4096  (0)||*  4 |     FILTER                         |                             |      1 |        |   1255K|00:00:30.10 |     101K|       |       |          ||   5 |      VIEW                          | EMS_EVENT_VIEW              |      1 |   2104K|   1255K|00:00:28.85 |     101K|       |       |          ||   6 |       UNION-ALL                    |                             |      1 |        |   1255K|00:00:28.85 |     101K|       |       |          ||   7 |        CONCATENATION               |                             |      1 |        |      0 |00:00:00.01 |     335 |       |       |          ||   8 |         TABLE ACCESS BY INDEX ROWID| EMS_EVENT                   |      1 |      1 |      0 |00:00:00.01 |      63 |       |       |          ||*  9 |          INDEX RANGE SCAN          | LAST_OCCURRENCE_TIME_INDEX  |      1 |      1 |      0 |00:00:00.01 |      63 |       |       |          ||* 10 |         TABLE ACCESS BY INDEX ROWID| EMS_EVENT                   |      1 |      1 |      0 |00:00:00.01 |     272 |       |       |          ||* 11 |          INDEX RANGE SCAN          | FIRST_OCCURRENCE_INDEX      |      1 |      1 |      0 |00:00:00.01 |     272 |       |       |          ||* 12 |        VIEW                        | index_join_006            |      1 |   2104K|   1255K|00:00:28.84 |     100K|       |       |          ||* 13 |         HASH JOIN                  |                             |      1 |        |   3863K|00:00:26.50 |     100K|   195M|     9M|  248M (0)||* 14 |          HASH JOIN                 |                             |      1 |        |   3863K|00:00:13.87 |   63020 |   160M|    10M|  214M (0)||  15 |           PARTITION RANGE ALL      |                             |      1 |   2104K|   3863K|00:00:00.01 |   31419 |       |       |          ||  16 |            INDEX FAST FULL SCAN    | IDX_FIRSTTIME_201202        |    338 |   2104K|   3863K|00:00:00.07 |   31419 |       |       |          ||  17 |           PARTITION RANGE ALL      |                             |      1 |   2104K|   3863K|00:00:00.01 |   31601 |       |       |          ||  18 |            INDEX FAST FULL SCAN    | IDX_LASTOCCURRENCE_201202   |    338 |   2104K|   3863K|00:00:00.06 |   31601 |       |       |          ||  19 |          INDEX FAST FULL SCAN      | PK_EMS_EVENT_HISTORY_201202 |      1 |   2104K|   3863K|00:00:00.01 |   37894 |       |       |          ||  20 |   VIEW                             | EMS_EVENT_VIEW              |      1 |   3864K|   3867K|00:00:19.34 |     656K|       |       |          ||  21 |    UNION-ALL                       |                             |      1 |        |   3867K|00:00:15.47 |     656K|       |       |          ||  22 |     TABLE ACCESS FULL              | EMS_EVENT                   |      1 |   3867 |   3950 |00:00:00.02 |    2046 |       |       |          ||  23 |     PARTITION RANGE ALL            |                             |      1 |   3860K|   3863K|00:00:07.73 |     654K|       |       |          ||  24 |      TABLE ACCESS FULL             | EMS_EVENT_HISTORY           |    338 |   3860K|   3863K|00:00:09.51 |     654K|       |       |          |-------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("EMS_EVENT_VIEW"."EVENT_ID"="from_subquery_003"."EVENT_ID")   2 - filter(("ROWNO">=:SYS_B_8 AND "ROWNO"<=:SYS_B_9))   3 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("FIRST_OCCURRENCE_TIME") DESC )<=:SYS_B_9)   4 - filter(:SYS_B_8<=:SYS_B_9)   9 - access("A"."LAST_OCCURRENCE_TIME">TO_DATE(:SYS_B_4,:SYS_B_5) AND "A"."LAST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_6,:SYS_B_7))  10 - filter((LNNVL("A"."LAST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_6,:SYS_B_7)) OR LNNVL("A"."LAST_OCCURRENCE_TIME">TO_DATE(:SYS_B_4,:SYS_B_5))))  11 - access("A"."FIRST_OCCURRENCE_TIME">TO_DATE(:SYS_B_0,:SYS_B_1) AND "A"."FIRST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_2,:SYS_B_3))  12 - filter((("B"."FIRST_OCCURRENCE_TIME">TO_DATE(:SYS_B_0,:SYS_B_1) AND "B"."FIRST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_2,:SYS_B_3)) OR              ("B"."LAST_OCCURRENCE_TIME">TO_DATE(:SYS_B_4,:SYS_B_5) AND "B"."LAST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_6,:SYS_B_7))))  13 - access(ROWID=ROWID)  14 - access(ROWID=ROWID)

If you cannot see the SQL clearly, I will post it here:

Select a. rowno, EMS _event_view .*
From EMS _event_view,
(Select *
From (select row_number () over (order by first_occurrence_time DESC) as rowno,
Event_id
From EMS _event_view
Where (first_occurrence_time>
To_date ('2017-02-22 00:00:00 ',
'Yyyy-mm-dd hh24: MI: ss') and
First_occurrence_time <
To_date ('2017-02-29 09:42:35 ',
'Yyyy-mm-dd hh24: MI: ss '))
))
Where rowno> = 0
And rowno <= 20)
Where EMS _event_view.event_id = A. event_id;

This SQL statement is actually a paging SQL statement that uses row_number over for paging. EMS _event_view is a view. This SQL statement is indeed very difficult to write. It needs to scan EMS _event_view twice. In fact, we can rewrite it and let it scan it once, instead of using event_id for self-connection.

The definition of EMS _event_view will not be posted, involving confidentiality. It probably means select * from a union all select * from B; there is no where filter condition.

Because a friend gave me this SQL statement and I couldn't connect to his dB, I only had to test it myself. The test code is as follows:

Create Table A as select * From dba_objects;
Create Table B as select * From dba_objects;

Create view test_view as select * from
Union all select * from B;

Create index idx_a on a (created, last_ddl_time );
Create index idx_ B on B (created, last_ddl_time );

Begin
Dbms_stats.gather_table_stats (ownname => 'Scott ',
Tabname => 'B ',
Estimate_percent = & gt; 100,
Method_opt => 'for all columns size auto ',
No_invalidate => false,
Degree => 4,
Cascade => true );
End;
/
Begin
Dbms_stats.gather_table_stats (ownname => 'Scott ',
Tabname => 'B ',
Estimate_percent = & gt; 100,
Method_opt => 'for all columns size auto ',
No_invalidate => false,
Degree => 4,
Cascade => true );
End;
/

The SQL statement to be optimized can be rewritten to the following code to access the view only once:

Select * from
(
Select T. *, rownum rn from
(Select/* + index (test_view.a idx_a) index (test_view. B idx_ B )*/*
From test_view
Where created> to_date ('2017-01-01 ', 'yyyy-mm-dd') or
Last_ddl_time <to_date ('1970-2007 ', 'yyyy-mm-dd ')
Order by created DESC
) T where rownum <= 20
) Where rn> = 0;

Now let's look at its execution plan.

SQL> select * from 2 (3 select T. *, rownum rn from 4 (select/* + index (test_view.a idx_a) index (test_view. B idx_ B) */* 5 from test_view 6 where created> to_date ('2017-01-01 ', 'yyyy-mm-dd') or 7 last_ddl_time <to_date ('2017-2007 ', 'yyyy-mm-dd') 8 order by created DESC 9) t where rownum <= 20 10) Where rn> = 0; 20 rows have been selected. Used time: 00: 00: 00.10 execution plan ------------------------------------------------------ plan hash value: 1808710389 bytes | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | minute | 0 | SELECT statement | 20 | 3800 | 1898 (2) | 00:00:23 | * 1 | View | 20 | 3800 | 1898 (2) | 00:00:23 | * 2 | count stopkey | 3 | View | 70304 | 11 M | 1898 (2) | 00:00:23 | * 4 | sort order by stopkey | 70304 | 6659k | 17m | 1898 (2) | 00:00:23 | 5 | View | test_view | 70304 | 6659k | 329 (5) | 00:00:04 | 6 | Union-all partition | 7 | table access by index rowid | A | 1650 | 156k | 238 (3) | 00:00:03 | * 8 | index full scan | idx_a | 1650 | 199 (4) | 00:00:03 | 9 | table access by index rowid | B | 1650 | 156k | 238 (3) | 00:00:03 | * 10 | index full scan | idx_ B | 1650 | 199 (4) | 00:00:03 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 1-filter ("RN"> = 0) 2-filter (rownum <= 20) 4-filter (rownum <= 20) 8-filter ("created"> to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ') or "last_ddl_time" <to_date ('1970-2007 ', 'yyyy-mm-dd ')) 10-filter ("created"> to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ') or "last_ddl_time" <to_date ('1970-2007 ', 'yyyy-mm-dd ')) statistical information listen 8 recursive cballs 0 dB block gets 566 consistent gets 9 physical reads 0 redo size 2621 bytes sent via SQL * Net to client 411 bytes received via SQL * Net from client 3 SQL * net roundtrips to/from client 1 sorts (memory) 0 sorts (Disk) 20 rows processed

Logic reads 566. Is the rewrite optimized? Obviously not, because the index idx_a and idx_ B are all full scan of the index and scan the entire index block. The original SQL index contains 3863 K pieces of data, and the performance must be very low. Therefore, the SQL statement is rewritten as follows:
Select * from
(
Select T. *, rownum rn from
(
Select * from
(Select * from
(
Select/* + index_desc ()*/*
From
Where created> to_date ('2017-01-01 ', 'yyyy-mm-dd') or
Last_ddl_time <to_date ('1970-2007 ', 'yyyy-mm-dd ')
Order by created DESC
) Where rownum <= 20
Union all
Select * from
(
Select/* + index_desc (B )*/*
From B
Where created> to_date ('2017-01-01 ', 'yyyy-mm-dd') or
Last_ddl_time <to_date ('1970-2007 ', 'yyyy-mm-dd ')
Order by created DESC
) Where rownum <= 20
) Order by created DESC
) T where rownum <= 20
) Where rn> = 0

The execution plan and logic read are as follows:

SQL> select * from 2 (3 select T. *, rownum rn from 4 (5 select * from 6 (select * from 7 (8 select/* + index_desc () */* 9 from a 10 where created> to_date ('1970-01-01 ', 'yyyy-mm-dd') or 11 last_ddl_time <to_date ('1970-2010 ', 'yyyy-mm-dd') 12 order by created DESC 13) Where rownum <= 20 14 union all 15 select * from 16 (17 select/* + index_desc (B) */* 18 from B 19 Where created> to_date ('100 0-01-01 ', 'yyyy-mm-dd') or 20 last_ddl_time <to_date ('2017-2007', 'yyyy-mm-dd') 21 order by created DESC 22) where rownum <= 20 23) order by created DESC 24) t where rownum <= 20 25) Where rn> = 0; 20 rows have been selected. Used time: 00: 00: 00.04 execution plan -------------------------------------------------------- plan hash value: 3460309830 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | minute | 0 | SELECT statement | 20 | 3800 | 244 (4) | 00:00:03 | * 1 | View | 20 | 3800 | 244 (4) | 00:00:03 | * 2 | count stopkey | 3 | View | 40 | 7080 | 244 (4) | 00:00:03 | * 4 | sort order by stopkey | 40 | 7080 | 244 (4) | 00:00:03 | 5 | View | 40 | 7080 | 243 (3) | 00:00:03 | 6 | Union-all | * 7 | count stopkey | 8 | View | 1650 | 285k | 238 (3) | 00:00:03 | 9 | table access by index rowid | A | 1650 | 156k | 238 (3) | 00:00:03 | * 10 | index full scan descending | idx_a | 1650 | 199 (4) | 00:00:03 | * 11 | count stopkey | 12 | View | 20 | 3540 | 5 (0) | 00:00:01 | 13 | table access by index rowid | B | 20 | 1940 | 5 (0) | 00:00:01 | * 14 | index full scan descending | idx_ B | 1650 | 4 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ----------------------------------------------- 1-filter ("RN"> = 0) 2-filter (rownum <= 20) 4-filter (rownum <= 20) 7-filter (rownum <= 20) 10-filter ("created"> to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ') or "last_ddl_time" <to_date ('1970-2007 ', 'yyyy-mm-dd') 11-filter (rownum <= 20) 14-filter ("created"> to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ') or "last_ddl_time" <to_date ('1970-2007 ', 'yyyy-mm-dd ')) statistics 1 recursive cballs 0 dB block gets 10 consistent gets 0 physical reads 0 redo size 2457 bytes sent via SQL * Net to client 411 bytes encoded ed via SQL * Net from client 3 SQL * net roundtrips to/from client 1 sorts (memory) 0 sorts (Disk) 20 rows processed

Logical reads are reduced by 60 times.

Now, based on this case, we will talk about the optimization of SQL paging. SQL paging is usually sorted, for example, select XXXX from t where condition order ......

Optimizing paging SQL focuses on the order by condition. When writing SQL statements, Oracle should perform an orderly scan of the index on the order by column, and then stop the query based on the stopkey, that is, do not scan all the index blocks. You should scan some blocks to stop.