CODEWITHSUNDEEP
sqloracle-databasedatabase-partitioninganalytic-functions
user1017936
user1017936

Reputation: 153

Oracle query optimization written on table with partition

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 ?

Upvotes: 0

Views: 966

Answers (1)

Eggi
Eggi

Reputation: 1714

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

Upvotes: 0

Related Questions