0

We have an application that needs precise order of items in a table based on datetime item (actually, autoincrement Id primary key) was created. It has to be without gaps and once created, never changed. Order number is later used as a legal identifier (its just a requirement). There could be thousands of items per partition.

Question is what is the better practice:

1) Calculate DENSE_RANK() on each retrieval of item from DB (which means calculating it over thousands of items)

OR

2) Calculate it on item creation and save in a dedicated column?

Additionally, if 2) is the better option, is there a way to optimize calculating it so that it starts from the previous one that has order number assigned or is it safer to always run DENSE_RANK() function on full partition?

dee zg
  • 13,793
  • 10
  • 42
  • 82
  • you might like to read this as well meanwhile we post solution : https://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle – Prabhat G Jul 03 '17 at 09:10
  • i am aware of differences between rank, dense_rank and row_number but its a very good read, thank you! – dee zg Jul 03 '17 at 09:13
  • 3
    If Order number is a legal requirement maybe you should dedicate a column to it and populate it during the creation. – Sergio Prats Jul 03 '17 at 09:23

0 Answers0