Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Analytic Function Issue
Oracle Analytic Function Issue [message #286896] Mon, 10 December 2007 06:41 Go to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
I'm running the query below on Oracle 10g, Release 2 and I'm getting some very strange results. Has anyone seen this behaviour before and if you have can you try and explain to me why it is occurring - it's driving me nuts!

I ran the following query:
SELECT recdate, COUNT(1) OVER ( PARTITION BY sender ) AS num_rec
FROM   my_table
WHERE  sender   = '44123465798'
AND    recdate >= TO_DATE('DD-NOV-2007','DD-MON-YYYY')
ORDER BY recdate ASC

This gives me the following record-set NOT ordered by recdate

RECDATE NUM_REC
-------------------- -------
03-NOV-2007 18:00:38 9
04-NOV-2007 11:48:00 9
04-NOV-2007 13:09:25 9
11-NOV-2007 00:35:59 9
30-NOV-2007 23:14:39 9
19-NOV-2007 11:07:13 9
27-NOV-2007 22:06:20 9
30-NOV-2007 23:14:37 9
18-NOV-2007 20:04:28 9


If I then run the query again (but ORDER BY recdate DESC), it returns the records in descending order (as I would expect). If I change the query into an inline view as follows:

SELECT *
FROM ( SELECT recdate, COUNT(1) OVER ( PARTITION BY sender ) AS num_rec
       FROM   tmp_tpg_63336
       WHERE  sender   = '44123465798'
       AND    recdate >= TO_DATE('DD-NOV-2007','DD-MON-YYYY')
       AND    matched  = 'N' )
ORDER BY recdate ASC

The same problem occurs and the records are returned as shown above (not in Ascending order). The only way I've managed to get round this is to ORDER BY DESC in the inline view and then ORDER BY recdate ASC in the outermost query:

SELECT *
FROM ( SELECT recdate, COUNT(1) OVER ( PARTITION BY sender ) AS num_rec
       FROM   tmp_tpg_63336
       WHERE  sender   = '44123465798'
       AND    recdate >= TO_DATE('DD-NOV-2007','DD-MON-YYYY')
       AND    matched  = 'N'
       ORDER BY recdate DESC )
ORDER BY recdate ASC

Which gives:

RECDATE NUM_REC
-------------------- -------
03-NOV-2007 18:00:38 9
04-NOV-2007 11:48:00 9
04-NOV-2007 13:09:25 9
11-NOV-2007 00:35:59 9
18-NOV-2007 20:04:28 9
19-NOV-2007 11:07:13 9
27-NOV-2007 22:06:20 9
30-NOV-2007 23:14:37 9
30-NOV-2007 23:14:39 9


Any ideas - i'm pulling out what little hair I have left!

Cheers
Re: Oracle Analytic Function Issue [message #286903 is a reply to message #286896] Mon, 10 December 2007 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are bugs when "order by" and analytical functions are used at the same time.
In short, if order is ascendant and refer a column in the select clause (including the analytical function "over" part), then "order by" is ignored.
You can see this in the execution plan, optimizer simplified too much the plan and removed the "sort/order by" step because it assumes there is one in the analytical function and it is the same.

In your case, you can workaround adding the order by clause inside the analytical function, then you'll get the correct order.

Regards
Michel
Re: Oracle Analytic Function Issue [message #286905 is a reply to message #286896] Mon, 10 December 2007 07:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can you show us real copy-pastes?
('DD-NOV-2007' hardly looks like a valid date to me)
Plus create table & inserts?


Edit: Never mind, as Michel already addressed it.

[Updated on: Mon, 10 December 2007 07:01]

Report message to a moderator

Re: Oracle Analytic Function Issue [message #286906 is a reply to message #286903] Mon, 10 December 2007 07:02 Go to previous message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
Thanks for you prompt response Michel
Previous Topic: retrieve data without symbol '(single quote)
Next Topic: dbms_scheduler and program_argument_type
Goto Forum:
  


Current Time: Tue Dec 06 12:26:29 CST 2016

Total time taken to generate the page: 0.10606 seconds