I have a table containing 4 billion records and adding 7 million records per day, working with a date and timestamp. Currently the table is created with a date (charging_date) and a timestamp (named timestamp but with format varchar2(6) - i.e. '065000' for 06H50 AM). I created a new column DATE_TIME where I concatenated the charging_date and timestamp values into a DATE_TIME value with format 'dd-mon-rrrr hh24miss'. However the values stored in this column are store as "1/1/2005 12:06:50 AM" for example. I did create an index on this value and analyzed the table afterwards. When I do a select with a parameter value like the following:
select charging_date, timestamp, date_time
where to_timestamp(date_time,'dd-mon-rrrr hh24miss') = to_timestamp('&from_date','dd-mon-rrrr hh24miss')
it is doing a full table scan and don't return any records. It takes about 15 minutes to return after the select. How can I force the select to use the index and what format must the user enter to pick up the value in the DATE_TIME column.
The exercise is needed in order to improve the poor performance of the queries on the table. All the queries work on a selection of charging_date and timestamp values. Currently I must concatenate the charging_date and timestamp values in order the get the data in the correct start and end date time combination. This takes forever to return my results.