0

I have one table in Oracle consists of around 55 million records with partition on date column.

This table stores around 600,000 records for each day based on some position.

Now, some analytical functions are used in one select query in procedure e.g. lead, lag, row_number() over(partition by col1, date order by col1, date) which is taking too much time due to 'partition by' and 'order by' clause on date column.

Is there any other alternative to optimize the query ?

Chris Gerken
  • 16,221
  • 6
  • 44
  • 59
user1017936
  • 143
  • 1
  • 5
  • 14
  • 2
    You are making an assumption as to why it's taking too long. Have you captured an extended SQL trace using `DBMS_MONITOR.SESSION_TRACE_ENABLE`? What's the execution plan? – Adam Musch Mar 06 '12 at 06:02
  • If I comment the column using analytical function in select query then it runs very fast – user1017936 Mar 06 '12 at 07:43
  • 2
    It runs very fast, or it starts returning data very fast? There's a difference. Adding an analytic function requires Oracle to partition the data and sort it all within those before it can start assigning the row_numbers() - and returning data to you. What's the ENTIRE query? – Adam Musch Mar 06 '12 at 07:47
  • Hi Adam, can you give ur email id – user1017936 Mar 06 '12 at 08:36
  • 1
    @user1017936, you can edit your question to add the query. Just use the `edit` buttom at the bottom of the question. – Ben Mar 06 '12 at 08:54
  • @user1017936: No, I'm not providing my email address. Not without a consulting contract. – Adam Musch Mar 06 '12 at 14:19
  • This may be neither here nor there, but if your analytic function already partitions by `COL1` and `DATE`, then `ORDER`ing `BY` `COL1` and `DATE` makes no sense: there'll be no sensible differentiation within the analytic-function partition to order by. You might as well `PARTITION BY col1, date ORDER BY NULL`. – Tebbe Mar 06 '12 at 17:31

1 Answers1

0

Have you considered using a materialized view where you store the results of your analytical functions?

More information about MVs

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm

Eggi
  • 1,684
  • 4
  • 20
  • 31