1

I needed some help in knowing whether we can have an option of sorting the rows of the table in Oracle based on the time of insertion.

Like do we use any sorting of function based indexes.

I would like to perform this operation of auto sorting without having to declare any new column for recording time.

Does the oracle server keep track of that information which i can use for sorting.

Thanks in advance

Improve this question
3
  • Your best bet is to add a timestamp column to record time of insertion to reliably sort based on time of insertion. I wouldn't rely on pseudocolumns ROWID or ORA_ROWSCN. Commented Mar 25, 2014 at 6:28
  • Wont order by rownum works? Commented Mar 25, 2014 at 6:44
  • @vasin1987, ordering by rownum is only useful if the subquery you're sorting is sorted by something useful, e.g. a date/time column. Commented Mar 26, 2014 at 4:26

2 Answers 2

Reset to default
1

Oracle doesn't record the "time of insertion" for you, so you must add that column yourself, set it to the current time (e.g. SYSTIMESTAMP) on every insert, then ORDER BY that column when you query the table.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

The general answer is no.

However...

The ORA_ROWSCN pseudo column returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.