How to Use rownum in Oracle

Rownum is a pseudo column that generates a serialized number based on the returned records. With rownum, we can produce some output results that were originally hard to implement, but because it is a special feature of pseudo columns, we also need to pay attention to some things during use and do not fall into the "trap ". The following describes how to use it and its precautions. 1. Special result output
With rownum, we can achieve some special output methods. 1.1 Top N result output
To retrieve the first few pieces of data in the output result, you can use rownum to easily implement: SQL> select * From t_test4
2 Where rownum <= 5; username user_id created
-------------------------------------------------
Wow 71 26-apr-07
CS2 70 15-Jan-07
3 69 01-nov-06
DMP 68 12--0-06
Profiler 67 05-sep-06 however, if you want to obtain top N data from a sorting result, there are some "traps" in using rownum. The following sections will introduce these "traps" and explain how to avoid them. 1.2 paging Query
Use rownum to paging the results. The following 6th to 10th records are returned: SQL> select * from
2 (
3 select a. *, rownum as rn from css_bl_view
4 where capture_phone_num = '(1) 925-4604800'
5) B
6 where B. Rn between 6 and 10; 6 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 2770 card = 2183 bytes = 7166789)
1 0 view (cost = 2770 card = 2183 bytes = 7166789)
2 1 count
3 2 Table Access (full) of 'css _ bl_view '(cost = 2770 card = 2183 bytes = 1305434)
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
29346 consistent gets
29190 physical reads
0 redo size
7328 bytes sent via SQL * Net to client
234 bytes encoded ed via SQL * Net From Client
4 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
5 rows processed another implementation method: SQL> select * From css_bl_view
2 Where capture_phone_num = '(1) 925-4604800'
3 and rownum <= 10
4 minus
5 select * From css_bl_view
6 where capture_phone_num = '(1) 925-4604800'
7 and rownum <= 5
8;
Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 5920 card = 10 bytes = 8970)
1 0 minus
2 1 sort (unique) (cost = 2960 card = 10 bytes = 5980)
3 2 count (stopkey)
4 3 Table Access (full) of 'css _ bl_view '(cost = 2770 card = 2183 bytes = 1305434)
5 1 sort (unique) (cost = 2960 card = 5 bytes = 2990)
6 5 count (stopkey)
7 6 Table Access (full) of 'css _ bl_view '(cost = 2770 card = 2183 bytes = 1305434)
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
62 consistent gets
50 physical reads
0 redo size
7232 bytes sent via SQL * Net to client
234 bytes encoded ed via SQL * Net From Client
4 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (Disk)
5 rows processed: SQL> select * from
2 (
3 select a. *, rownum as rn from css_bl_view
4 where capture_phone_num = '(1) 925-4604800'
5 and rownum <= 10
6) B
7 where B. Rn> 5;
Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 2770 card = 10 bytes = 32830)
1 0 view (cost = 2770 card = 10 bytes = 32830)
2 1 count (stopkey)
3 2 Table Access (full) of 'css _ bl_view '(cost = 2770 card = 2183 bytes = 1305434)
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
35 consistent gets
30 physical reads
0 redo size
7271 bytes sent via SQL * Net to client
234 bytes encoded ed via SQL * Net From Client
4 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
5 rows processed print the query plan and statistical data of the three implementation methods here. You can compare the performance of the three methods. 1.3 Use rownum for grouping and subsorting
For the following table t_test4: owner name
------------------------------------------------------
Strmadmin streams_queue
Aparkman job_queue
Sys AQ $ _ aq_srvntfn_table_e
Sys AQ $ _ kupc $ datapump_quetab_e
Aparkman AQ $ _ jms_text_e
Strmadmin AQ $ _ streams_queue_table_e
Sys AQ $ _ scheduler $ _ event_qtab_e
... If we want the result to be grouped by owner and then numbered by members in each group, the result is similar to the following: owner no name
------------------------------------------------------
Aparkman 1 job_queue
2 AQ $ _ jms_text_e
Strmadmin 1 streams_queue
2 AQ $ _ streams_queue_table_e
Sys 1 AQ $ _ aq_srvntfn_table_e
2 AQ $ _ kupc $ datapump_quetab_e
3 AQ $ _ scheduler $ _ event_qtab_e
... It would be complicated to implement this function without rownum, but with rownum we can easily implement: SQL> selectdecode (ROWNUM-min_sno, 0,. owner, null) Owner, decode (ROWNUM-min_sno, rownum + 1-min_sno) SnO,. name
2 from (select *
3 from t_test8
4 order by owner, name),
5 (select owner, min (rownum) min_sno
6 from (select *
7 from t_test8
8 order by owner, name)
9 group by owner) B
10 where a. Owner = B. owner; Owner SnO name
----------------------------------------------------------------------
Aparkman 1 job_queue
2 AQ $ _ jms_text_e
Strmadmin 1 streams_queue
2 AQ $ _ streams_queue_table_e
Sys 1 AQ $ _ aq_srvntfn_table_e
2 AQ $ _ kupc $ datapump_quetab_e
3 AQ $ _ scheduler $ _ event_qtab_e
4 AQ $ _ scheduler $ _ jobqtab_e
5 AQ $ _ streams_queue_table_e
6 AQ $ _ sys $ service_metrics_tab_e
7 AQ $ _ aq_event_table_e
8 AQ $ _ mem_mc_e
9 AQ $ _ alert_qt_e
10 alert_que
11 aq_event_table_q
12 sys $ service_metrics
13 streams_queue
14 srvqueue
15 scheduler $ _ jobq
16 schedue $ _ event_queue
17 aq_srvntfn_table_q
Sysman 1 AQ $ _ mgmt_policy_qtable_e
2 mgmt_policy_q
System 1 def $ _ aqerror
2 def $ _ aqcall
3 AQ $ _ def $ _ aqerror_e
4 AQ $ _ def $ _ aqcall_e
Wmsys 1 AQ $ _ WM $ event_queue_table_e
2 WM $ event_queue29 rows selected.
2. Performance
Many programmers like to add rownum = 1 when checking whether a table has the corresponding data. The idea is that as long as there is a piece of data, the corresponding data is displayed, and the query can be directly returned, this improves the performance. However, before 10 Gb, rownum = 1 cannot achieve the expected performance. Instead, you must use <2 or <= 1 as the filter condition to achieve the expected results. See the following query plan: SQL> select * From t_test1
2 Where object_id: <100
3 and rownum = 1;
Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 37 card = 1 bytes = 86)
1 0 count (stopkey)
2 1 Table Access (by index rowid) of 't_ test1' (cost = 37 card = 89 bytes = 7654)
3 2 Index (range scan) of 't_ test1_pk '(unique) (cost = 2 card = 89)
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
62 consistent gets
0 physical reads
0 redo size
654 bytes sent via SQL * Net to client
234 bytes encoded ed via SQL * Net From Client
4 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processedsql> select * From t_test1
2 Where object_id: <100
3 and rownum <= 1;
Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 37 card = 1 bytes = 86)
1 0 count (stopkey)
2 1 Table Access (by index rowid) of 't_ test1' (cost = 37 card = 89 bytes = 7654)
3 2 Index (range scan) of 't_ test1_pk '(unique) (cost = 2 card = 89) Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
3 consistent gets
0 physical reads
0 redo size
654 bytes sent via SQL * Net to client
234 bytes encoded ed via SQL * Net From Client
4 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processedsql>/
Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 37 card = 1 bytes = 86)
1 0 count (stopkey)
2 1 Table Access (by index rowid) of 't_ test1' (cost = 37 card = 89 bytes = 7654)
3 2 Index (range scan) of 't_ test1_pk '(unique) (cost = 2 card = 89)
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
3 consistent gets
0 physical reads
0 redo size
654 bytes sent via SQL * Net to client
234 bytes encoded ed via SQL * Net From Client
4 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed10g later, this problem was corrected: SQL> select * From t_test1
2 Where rownum = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 536364188 ------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 86 | 2 (0) | 00:00:01 |
| * 1 | count stopkey |
| 2 | table access full | t_test1 | 1 | 86 | 2 (0) | 00:00:01 |
------------------------------------------------------------------------------ Predicate information (identified by Operation ID ):
--------------------------------------------------- 1-filter (rownum = 1)
Statistics
----------------------------------------------------------
1 recursive cballs
0 dB block gets
4 consistent gets
1 physical reads
0 redo size
1201 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processedsql> select * From t_test1
2 Where rownum <= 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 536364188 ------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 86 | 2 (0) | 00:00:01 |
| * 1 | count stopkey |
| 2 | table access full | t_test1 | 1 | 86 | 2 (0) | 00:00:01 |
------------------------------------------------------------------------------ Predicate information (identified by Operation ID ):
--------------------------------------------------- 1-filter (rownum <= 1)
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
4 consistent gets
0 physical reads
0 redo size
1201 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed