I'm using Oracle 9, but the system should work also for Oracle 8 and 10.
The problem is: date fields cannot store anything beyond seconds, but I'm storing a series of events that occur with millisecond precision. The obvious solution is creating a new column to store the milliseconds. But is this the better solution?
It does not seem very clever to me, because doing that imposes that all my queries (there are a plenty of them) will need a change in the ORDER BY clause.
You can use the timestamp type. Here is a good explanation of it:
Although it is not supported in Oracle 8 very well.
In Oracle 8 you had to do a work around (varchar field or two fields, a datetime and 2nd to hold the milliseconds)
If you can somehow avoid the requirement to do this in Oracle 8, consider the TIMESTAMP datatype.
I would suggest you choose between supporting Oracle 8 (which is soon to go out of service, if it is not already out of service) and millisecond support. Of the two, I'd recommend ditching Oracle 8 and only supporting Oracle 9 or later.
[Fair disclosure: I work for a competitor - I'd even recommend switching to us. If my info on Oracle 8 going out of support is wrong, sorry. But the advice is as unbiassed as I can make it.]
As already mention, timestamp is the datatype in oracle 9 and later that supports the millisecond precision you are looking for. If you want to support oracle 8 as well then you will have to use a varchar2(25) to store the value. In oracle8 you will have to use a java function or external program to get the millisecond precision to store in the table's varchar2 column.