您的FIRST_NAME列很有可能为空。
SQL> create table customer (first_name varchar2(20), last_name varchar2(20));Table created.SQL> insert into customer select dbms_random.string('U', 20), dbms_random.string('U', 20) from dual connect by level <= 100000;100000 rows created.SQL> create index c on customer(first_name);Index created.SQL> explain plan for select * from ( 2 select FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN 3 from CUSTOMER C 4 ) 5 where RN between 1 and 20 6 order by RN;Explained.SQL> @explain ""Plan hash value: 1474094583----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | SELECt STATEMENT | | 117K| 2856K| | 1592 (1)| 00:00:20 || 1 | SORT ORDER BY | | 117K| 2856K| 4152K| 1592 (1)| 00:00:20 ||* 2 | VIEW | | 117K| 2856K| | 744 (2)| 00:00:09 ||* 3 | WINDOW SORT PUSHED RANK| | 117K| 1371K| 2304K| 744 (2)| 00:00:09 || 4 | TABLE ACCESS FULL | CUSTOMER | 117K| 1371K| | 205 (1)| 00:00:03 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("RN">=1 AND "RN"<=20) 3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20)Note----- - dynamic sampling used for this statement (level=2)21 rows selected.SQL> alter table customer modify first_name not null;Table altered.SQL> explain plan for select * from ( 2 select FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN 3 from CUSTOMER C 4 ) 5 where RN between 1 and 20 6 order by RN;Explained.SQL> @explain ""Plan hash value: 1725028138----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECt STATEMENT | | 117K| 2856K| | 850 (1)| 00:00:11 || 1 | SORT ORDER BY | | 117K| 2856K| 4152K| 850 (1)| 00:00:11 ||* 2 | VIEW | | 117K| 2856K| | 2 (0)| 00:00:01 ||* 3 | WINDOW NOSORT STOPKEY| | 117K| 1371K| | 2 (0)| 00:00:01 || 4 | INDEX FULL SCAN | C | 117K| 1371K| | 2 (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("RN">=1 AND "RN"<=20) 3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20)Note----- - dynamic sampling used for this statement (level=2)21 rows selected.SQL>在其中添加一个NOT NULL来解决它。
SQL> explain plan for select * from ( 2 select FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN 3 from CUSTOMER C 4 where first_name is not null 5 ) 6 where RN between 1 and 20 7 order by RN;Explained.SQL> @explain ""Plan hash value: 1725028138----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 117K| 2856K| | 850 (1)| 00:00:11 || 1 | SORT ORDER BY | | 117K| 2856K| 4152K| 850 (1)| 00:00:11 ||* 2 | VIEW | | 117K| 2856K| | 2 (0)| 00:00:01 ||* 3 | WINDOW NOSORT STOPKEY| | 117K| 1371K| | 2 (0)| 00:00:01 ||* 4 | INDEX FULL SCAN | C | 117K| 1371K| | 2 (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("RN">=1 AND "RN"<=20) 3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20) 4 - filter("FIRST_NAME" IS NOT NULL)Note----- - dynamic sampling used for this statement (level=2)22 rows selected.SQL>
