I have a field that contains time of order creation(order_time). Naturally, the best data type for that field is TIMESTAMP, but I want to create index and I'm not sure that TIMESTAMP index would be better than any numerical index. What's the best practice here? I'm using oracle database

CodePudding user response：

Always use the most appropriate data-type for the data:

• If the data has date and time components and has a time-zone then use TIMESTAMP WITH TIME ZONE;
• If the data has date and time components with fractional seconds and no time-zone then use TIMESTAMP;
• If the data has date and time components with no fractional seconds and no time-zone then use DATE; and
• If your data is an instant measured, for example, as the number of milliseconds (or seconds) since 1970-01-01 00:00:00 UTC and you almost entirely use it in its numeric form (i.e. you never, or very rarely, convert it to a human readable format such as YYYY-MM-DD HH:MI:SS.FF) then you may want to store it as a number. However, if you want to format it so it is readable or compare it to dates then you should prefer the TIMESTAMP (or DATE) data type.

Never use an inappropriate data-type for your column. The index performance between the different data-types should be mostly irrelevant and the overheads of converting from an inappropriate data-type to an appropriate one are likely to be a much more significant cost.