oracle numberover,row_number()over不使用索引问题.

直接查下面语句你就明白为什么不走索引了.

select

row_number()over(partition by f.XXFLRECID order by f.ISSUE_TIME desc) hr

from  test f

你都全表数据进行扫描.数据库为什么要选择索引呢?

select

row_number()over(partition by f.XXFLRECID order by f.ISSUE_TIME desc) hr

from  test f

where f.ISSUE_TIME ='你的时间'.

查询一下看看.结果又如何.

不是用row_number()over数据库就不用索引了.

测试如下:

已用时间:  00: 00: 00.76

SQL> analyze table rowtest compute statistics for all indexes for all columns;

表已分析。

已用时间:  00: 00: 04.04

SQL> select

2  row_number() over(partition by idgp order by id desc) hr from rowtest where

idgp=2;

已选择10000行。

已用时间:  00: 00: 00.18

执行计划

----------------------------------------------------------

Plan hash value: 327554111

--------------------------------------------------------------------------------

| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         | 10000 | 60000 |       |   282   (6)| 00:0

|   1 |  WINDOW SORT       |         | 10000 | 60000 |   328K|   282   (6)| 00:0

|*  2 |   TABLE ACCESS FULL| ROWTEST | 10000 | 60000 |       |   247   (6)| 00:0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("IDGP"=2)

统计信息

----------------------------------------------------------

1  recursive calls

0  db block gets

1067  consistent gets

0  physical reads

0  redo size

136870  bytes sent via SQL*Net to client

7711  bytes received via SQL*Net from client

668  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

10000  rows processed

SQL> row_number() over(partition by idgp order by id desc) hr from rowtest where

id=2;

SP2-0734: 未知的命令开头 "row_number..." - 忽略了剩余的行。

SQL> select

2  row_number() over(partition by idgp order by id desc) hr from rowtest where

id=2;

已用时间:  00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 1875287493

--------------------------------------------------------------------------------

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |             |     1 |     6 |     2  (50)

|   1 |  WINDOW SORT                 |             |     1 |     6 |     2  (50)

|   2 |   TABLE ACCESS BY INDEX ROWID| ROWTEST     |     1 |     6 |     1   (0)

|*  3 |    INDEX RANGE SCAN          | ROWTEST_IDX |     1 |       |     1   (0)

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("ID"=2)

统计信息

----------------------------------------------------------

1  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

402  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

[本帖最后由 tanfufa 于 2008-2-2 12:35 编辑]