Creating a sorted table

[weberm]

I am writing a program which loads a transaction file that is sorted by transaction ID to a database table so that it's batched by payment type with sequential non-repeating numbers. I thought I could do it by using a table to sort the transactions by payment type and then releasing the records and incrementing the batch number whenever the payment type changes when it loads to the database table, but I see no way to sort the table. Am I barking up the wrong tree or missing something completely?

 

[carp]

A sorted table has no inherent meaning in Oracle. Even if you insert records in some sequential order (based on data values), Oracle can (and often will) store them in non-contiguous locations. This is why you would create an index on the column(s) that you wish to sort on - it will store your values in a sorted list (actually a B* tree) and this, in turn, will speed up your queries.

I think the closest you are going to get to a "sorted table" in Oracle is an index-organized table (IOT), but I would recommend you study these before deciding whether to use a "normal" table with indexes or an IOT.

 

[Thargy]

Carp,

all I have to say is "Amen".

Regards

T

 

[LKBrwnDBA]

Being aware of the issue posted by carp in reference to the non-sorted order of the rows stored in a table:

If your purpose is just to generate sequential numbers 1 to N for each source payment type *AND* you already have the source data in some kind of "staging" or "external" table, you could try something like this:

INSERT INTO myTable ( Payment_type, Payment_Seq, Other_Cols)
SELECT Payment_type
     , ROW_NUMBER() OVER (PARTITION BY Payment_type
                    ORDER BY Trans_Date) Payment_Seq
     , Other_Cols
  FROM StageTable;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[weberm]

Sorry for the late reply, but upon further investigation, I realized that I could give them what they wanted by loading them normally and changing the load reports to display the data as they want and sorting by payment type when I assign receipt numbers to the transactions.