13 积分	0 好友	1 主题

发消息

About row_Number() - wrong result set

1^#

发表于 2012-3-26 15:18:13 | 查看: 6603| 回复: 8

问题描述：
用 row_Number() 排序后，在外层的SQL 根据排序定位结果集。（结果集错误）
请大家帮忙看看下例子的底层是如何执行的？（或者是BUG？METALINK后未果）

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create table t_rownum (id number,t_date date);

Table created
SQL> insert into t_rownum values (1,trunc(sysdate));

1 row inserted
SQL> insert into t_rownum values (2,trunc(sysdate));

1 row inserted
SQL> commit;

Commit complete

SQL> select id,row_number()over(order by t_date desc) r from t_rownum;

      ID       R
---------- ----------
      2       1
      1       2

SQL> select id,row_number()over(order by t_date desc) r from t_rownum;

2 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2982448889

-------------------------------------------------------------------------------
| Id  | Operation       | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |       |    2 | 44 |    4  (25)| 00:00:01 |
| 1 |  WINDOW SORT    |       |    2 | 44 |    4  (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T_ROWNUM |    2 | 44 |    3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

...

SQL> select * from (
  2  select id,row_number()over(order by t_date desc) r from t_rownum
  3  )
  4  where r = 2;

      ID       R
---------- ----------
      2       2

SQL> select * from (
  2  select id,row_number()over(order by t_date desc) r from t_rownum
  3  )
  4  where r = 2;

1 row selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1221292694

-------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |       |    2 | 52 |    4  (25)| 00:00:01 |
|*  1 |  VIEW                   |       |    2 | 52 |    4  (25)| 00:00:01 |
|*  2 | WINDOW SORT PUSHED RANK|       |    2 | 44 |    4  (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL    | T_ROWNUM |    2 | 44 |    3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("R"=2)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_DATE") DESC
            )<=2)

-- 后来通过添加ROWID解决了
SQL> select * from (
  2  select id,row_number()over(order by t_date desc,rowid desc) r from t_rownum
  3  )
  4  where r = 2;

      ID       R
---------- ----------
      1       2

SQL> select * from (
  2  select id,row_number()over(order by t_date desc,rowid desc) r from t_rownum
  3  )
  4  where r = 2;

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1221292694

-------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |       |    2 | 52 |    4  (25)| 00:00:01 |
|*  1 |  VIEW                   |       |    2 | 52 |    4  (25)| 00:00:01 |
|*  2 | WINDOW SORT PUSHED RANK|       |    2 | 68 |    4  (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL    | T_ROWNUM |    2 | 68 |    3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("R"=2)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_DATE") DESC
            ,INTERNAL_FUNCTION(ROWID) DESC )<=2)

谢谢！

分享0

收藏0 回复只看该作者道具举报

anbob

2^#

发表于 2012-3-26 15:41:08

SQL> select  * from (
  2  select  id,row_number()over(order by rownum desc) r from t_rownum) t
  3  where r=2;

      ID       R
---------- ----------
      1       2

回复只看该作者道具举报

kaertiger

3^#

发表于 2012-3-26 17:12:26

老兄ORACLE 什么版本？

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-3-30 20:00:23

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> drop table t_rownum ;

Table dropped.

SQL> create table t_rownum (id number,t_date date);

Table created.

SQL> insert into t_rownum values (1,trunc(sysdate));

1 row created.

SQL> insert into t_rownum values (2,trunc(sysdate));

1 row created.

SQL> commit;

Commit complete.

SQL> select id,row_number()over(order by t_date desc) r from t_rownum;

      ID       R
---------- ----------
      2       1
      1       2

SQL> select *
  from (select id, row_number() over(order by t_date desc) r from t_rownum)
  2 3 where r = 2;

      ID       R
---------- ----------
      2       2

SQL>
SQL>
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE

SQL>
SQL> explain plan for select *
  from (select id, row_number() over(order by t_date desc) r from t_rownum)
  2 3 where r = 2;

Explained.

SQL> set linesize 200 pagesize 1400
SQL> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2379977730

-------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |       |    2 | 52 |    3  (34)| 00:00:01 |
|*  1 |  VIEW                   |       |    2 | 52 |    3  (34)| 00:00:01 |
|*  2 | WINDOW SORT PUSHED RANK|       |    2 | 44 |    3  (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL    | T_ROWNUM |    2 | 44 |    2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("R"=2)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_DATE") DESC
            )<=2)

Note
-----
- dynamic sampling used for this statement

21 rows selected.

SQL> alter session set "_windowfunc_optimization_settings"=2;

Session altered.

SQL> select *
  2 from (select id, row_number() over(order by t_date desc) r from t_rownum)
  3 where r = 2;

      ID       R
---------- ----------
      1       2

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2012-3-30 20:05:32

10053 TRACE :

Without "_windowfunc_optimization_settings"

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10053 trace name context forever,level 1;
Statement processed.
SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> select *
  2 from (select id, row_number() over(order by t_date desc) r from t_rownum)
  3 where r = 2;

      ID       R
---------- ----------
      2       2

SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_3651.trc

******* UNPARSED QUERY IS *******
SELECT "T_ROWNUM"."ID" "ID",ROW_NUMBER() OVER ( ORDER BY "T_ROWNUM"."T_DATE" DESC ) "R" FROM "SYS"."T_ROWNUM" "T_ROWNUM"
kkoqbc-end
      : call(in-use=22328, alloc=32712), compile(in-use=46536, alloc=48920)
kkoqbc-start
         : call(in-use=22328, alloc=32712), compile(in-use=46624, alloc=48920)
****************
QUERY BLOCK TEXT
****************
select *
  from (select id, row_number() over(order by t_date desc) r from t_rownum)
where r = 2
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
ignature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=1 objn=0 hint_alias="from$_subquery$_001"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 714 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: from$_subquery$_001  Alias: from$_subquery$_001  (NOT ANALYZED)
#Rows: 0  #Blks:  0  AvgRowLen:  0.00
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]:  from$_subquery$_001[from$_subquery$_001]#0
***********************
Best so far: Table#: 0  cost: 3.0009  card: 2.0000  bytes: 52
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 3.0009  Degree: 1  Card: 2.0000  Bytes: 52
  Resc: 3.0009  Resc_io: 2.0000  Resc_cpu: 8575293
  Resp: 3.0009  Resp_io: 2.0000  Resc_cpu: 8575293
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******

SELECT "from$_subquery$_001"."ID" "ID","from$_subquery$_001"."R" "R" FROM  (SELECT "T_ROWNUM"."ID" "ID",ROW_NUMBER() OVER ( ORDER BY "T_ROWNUM"."T_DA
TE" DESC ) "R" FROM "SYS"."T_ROWNUM" "T_ROWNUM") "from$_subquery$_001" WHERE "from$_subquery$_001"."R"=2

kkoqbc-end
      : call(in-use=39064, alloc=49080), compile(in-use=47128, alloc=48920)
apadrv-end: call(in-use=39064, alloc=49080), compile(in-use=48336, alloc=48920)

sql_id=8xf8wzffynch2.
Current SQL statement for this session:
select *
  from (select id, row_number() over(order by t_date desc) r from t_rownum)
where r = 2

============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id  | Operation                | Name | Rows  | Bytes | Cost  | Time    |
--------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT       |       |    |    |    3 |          |
| 1 |  VIEW                   |       |    2 | 52 |    3 |  00:00:01 |
| 2 | WINDOW SORT PUSHED RANK |       |    2 | 44 |    3 |  00:00:01 |
| 3 | TABLE ACCESS FULL    | T_ROWNUM|    2 | 44 |    2 |  00:00:01 |
--------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("R"=2)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_DATE") DESC )<=2)

原SQL 变化为以下SQL:

SELECT "from$_subquery$_001"."ID" "ID","from$_subquery$_001"."R" "R" FROM  (SELECT "T_ROWNUM"."ID" "ID",ROW_NUMBER() OVER ( ORDER BY "T_ROWNUM"."T_DA
TE" DESC ) "R" FROM "SYS"."T_ROWNUM" "T_ROWNUM") "from$_subquery$_001" WHERE "from$_subquery$_001"."R"=2

WINDOW SORT PUSHED RANK

回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2012-3-30 20:09:17

With "_windowfunc_optimization_settings"=2;

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10053 trace name context forever,level 1;
Statement processed.
SQL> alter session set "_windowfunc_optimization_settings"=2;

Session altered.

SQL> select *
  2 from (select id, row_number() over(order by t_date desc) r from t_rownum)
  3 where r = 2;

      ID       R
---------- ----------
      1       2

SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_3665.trc

******* UNPARSED QUERY IS *******
SELECT "T_ROWNUM"."ID" "ID",ROW_NUMBER() OVER ( ORDER BY "T_ROWNUM"."T_DATE" DESC ) "R" FROM "SYS"."T_ROWNUM" "T_ROWNUM"
kkoqbc-end
      : call(in-use=22328, alloc=32712), compile(in-use=46536, alloc=50120)
kkoqbc-start
         : call(in-use=22328, alloc=32712), compile(in-use=46624, alloc=50120)
****************
QUERY BLOCK TEXT
****************
select *
  from (select id, row_number() over(order by t_date desc) r from t_rownum)
where r = 2
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=1 objn=0 hint_alias="from$_subquery$_001"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 714 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************

Table Stats::
  Table: from$_subquery$_001  Alias: from$_subquery$_001  (NOT ANALYZED)
#Rows: 0  #Blks:  0  AvgRowLen:  0.00
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]:  from$_subquery$_001[from$_subquery$_001]#0
***********************
Best so far: Table#: 0  cost: 3.0009  card: 2.0000  bytes: 52
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 3.0009  Degree: 1  Card: 2.0000  Bytes: 52
  Resc: 3.0009  Resc_io: 2.0000  Resc_cpu: 8575293
  Resp: 3.0009  Resp_io: 2.0000  Resc_cpu: 8575293

kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_001"."ID" "ID","from$_subquery$_001"."R" "R" FROM  (SELECT "T_ROWNUM"."ID" "ID",ROW_NUMBER() OVER ( ORDER BY "T_ROWNUM"."T_DA
TE" DESC ) "R" FROM "SYS"."T_ROWNUM" "T_ROWNUM") "from$_subquery$_001" WHERE "from$_subquery$_001"."R"=2
kkoqbc-end
      : call(in-use=39064, alloc=49080), compile(in-use=47128, alloc=50120)
apadrv-end: call(in-use=39064, alloc=49080), compile(in-use=48336, alloc=50120)

sql_id=8xf8wzffynch2.
Current SQL statement for this session:
select *
  from (select id, row_number() over(order by t_date desc) r from t_rownum)
where r = 2

============
Plan Table


---------------------------------------+-----------------------------------+
| Id  | Operation          | Name | Rows  | Bytes | Cost  | Time    |
---------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT    |       |    |    |    3 |          |
| 1 |  VIEW             |       |    2 | 52 |    3 |  00:00:01 |
| 2 | WINDOW SORT       |       |    2 | 44 |    3 |  00:00:01 |
| 3 | TABLE ACCESS FULL | T_ROWNUM|    2 | 44 |    2 |  00:00:01 |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("R"=2)

Content of other_xml column
===========================
  db_version    : 10.2.0.1
  parse_schema : SYS
  dynamic_sampling: yes
  plan_hash    : 3469184155
  Outline Data:
  /*+
BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$2")
   OUTLINE_LEAF(@"SEL$1")
   NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
   FULL(@"SEL$2" "T_ROWNUM"@"SEL$2")
END_OUTLINE_DATA
  */

使用 WINDOW SORT 而非WINDOW SORT PUSHED RANK，绕过了该Wrong Result 问题。

回复只看该作者道具举报

Maclean Liu(刘相兵

7^#

发表于 2012-3-30 20:10:45

这是 Oracle SQL optimizer Windows  Func的一个bug，相关BUG NOTE:

Bug 11671577 - wrong results with view pushed predicate and window functions

Bug 11671577  wrong results with view pushed predicate and window functions
This note gives a brief overview of bug 11671577.
The content was last updated on: 02-FEB-2011
Click here for details of each of the sections below.
Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected

      11.1.0.7

Platforms affected Generic (all / most platforms affected)

Fixed:

This issue is fixed in

      12.1 (Future Release)

Symptoms:

Related To:

Wrong Results

Optimizer
Analytic SQL (Windowing etc..)
_windowfunc_optimization_settings
Optimizer Join Push Predicate (JPPD)

Description

You may get incorrect result (less rows) from a query if the execution
plan shows that a join predicate has been pushed into a view (the plan
shows VIEW PUSHED PREDICATE) and that a rank function was pushed below
window sort operation (the plan shows WINDOW SORT PUSHED RANK).

-----------------------------------------------------
| Id  | Operation                   | Name    |
-----------------------------------------------------
| 0 | SELECT STATEMENT             |          |
| 1 |  NESTED LOOPS                |          |
...
|*  8 | VIEW PUSHED PREDICATE       |          |
|*  9 | WINDOW SORT PUSHED RANK    |          | <--- Should be only WINDOW SORT
|  10 |    TABLE ACCESS BY INDEX ROWID| TMTY002 |
|* 11 |    INDEX RANGE SCAN       | TMTY002_PK |
-----------------------------------------------------

Workaround:

alter session set "_windowfunc_optimization_settings"=2




Bug 5906937  Wrong result using ROW_NUMBER in SQL using a WITH query block
This note gives a brief overview of bug 5906937.
The content was last updated on: 20-JUN-2008
Click here for details of each of the sections below.
Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected

      10.2.0.3

Platforms affected Generic (all / most platforms affected)

Fixed:

This issue is fixed in

      10.2.0.4 (Server Patch Set)
      11.1.0.6 (Base Release)

Symptoms:

Related To:

Wrong Results

Optimizer (Subquery Factoring - WITH clause)
Analytic SQL (Windowing etc..)
_WINDOWFUNC_OPTIMIZATION_SETTINGS

Description

A  ROW_NUMBER predicate with a "WITH" clause query which gets
materialized  can produce wrong results  for  aggregates in
projection list.
The  explain plan output shows a row_number predicate pushdown
onto the temporary table for WITH clause to be materialized.

Workaround:
   1. use a "/*+ inline */" hint in the WITH clause to prevent it from
      being materialized (may affect query performance)
or
   2. use "_windowfunc_optimization_settings"=2
or
   3. rewrite the query with RANK instead of ROW_NUMBER

回复只看该作者道具举报

Maclean Liu(刘相兵

8^#

发表于 2012-3-30 20:12:24

_WINDOWFUNC_OPTIMIZATION_SETTINGS settings for window function optimizations

回复只看该作者道具举报

kaertiger

9^#

发表于 2012-3-31 10:30:31

谢了~！！！！ ML ～
膜拜～

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册