Sorting on NUMBER or DATE

[Tomislav Petrovic]

Which is more expensive (slower), sorting data using NUMBER field or
using DATE field....
And how much more expensive?

Tomy.

[Ed prochak]

"Tomislav Petrovic" <t.********@inet.hr> wrote in message news:<ca**********@garrison.globalnet.hr>...

Which is more expensive (slower), sorting data using NUMBER field or
using DATE field....
And how much more expensive?

Tomy.

Considering that dates are internally stored as numbers, it would be
difficuly to tell the difference.

IF your concern really is the performance, then make a benchmark test
on Your hardware with Your data. Because in performance issues there
are only guidelines, no hard rules. And keep in mind that your test
data better be very close to real data or your production performance
could differ from your test performance. And usually not on the better
side of the line 8^)

hth,
Ed

[Mark D Powell]

"Tomislav Petrovic" <t.********@inet.hr> wrote in message news:<ca**********@garrison.globalnet.hr>...

Which is more expensive (slower), sorting data using NUMBER field or
using DATE field....
And how much more expensive?

Tomy.

Tomy, I believe that in the majority of cases the difference is
insignificant.

I used dba_ojects to populate a test table with 3 columns: the object
name for 30 char, the sysdate minus the rownum, and the object_id.
Then I selected * from my test table to populate the buffer.

Next I "set timing on" in sqlplus and did a select * from my_table
order by the_date and the_number one after the other. For 39,357 row
the run time differences were around 5/100 of a second with number
winning by just a tad.

You should be able to run some more tests, perhaps with a larger
sample size, and see.

HTH -- Mark D Powell --

[Mark C. Stock]

"Mark D Powell" <Ma*********@eds.com> wrote in message
news:26**************************@posting.google.c om...
| "Tomislav Petrovic" <t.********@inet.hr> wrote in message
news:<ca**********@garrison.globalnet.hr>...
| > Which is more expensive (slower), sorting data using NUMBER field or
| > using DATE field....
| > And how much more expensive?
| >
| > Tomy.
|
| Tomy, I believe that in the majority of cases the difference is
| insignificant.
|
| I used dba_ojects to populate a test table with 3 columns: the object
| name for 30 char, the sysdate minus the rownum, and the object_id.
| Then I selected * from my test table to populate the buffer.
|
| Next I "set timing on" in sqlplus and did a select * from my_table
| order by the_date and the_number one after the other. For 39,357 row
| the run time differences were around 5/100 of a second with number
| winning by just a tad.
|
| You should be able to run some more tests, perhaps with a larger
| sample size, and see.
|
| HTH -- Mark D Powell --

Tomy

Good answers, but actually, you should spend your tuning time on things that
have a lot more impact -- like making sure indexes are used where
appropriate rather than full table scans, and there are no unnecessary
PL/SQL loop iterations, your SGA is properly sized, your sorts are tuned,
etc. etc.

++ mcs