Table not using index
Good day,
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
from cdrdata_new_temp
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.
Kind regards
Bonita Graupe
Software/Hardware used:
Software/Hardware used:
ASKED:
November 13, 2006 4:29 AM
UPDATED:
November 13, 2006 9:19 AM
Answer Wiki:
Hi,
You get no index usage because you indexed a column but you are querying it via an expression.
To enable the usage of an index you must not use the index expression (here: the column) in an expression.
From the way your query is written I assume the type of the new date_time column is VARCHAR2 (otherwise you wouldn't convert it, I guess).
The best solution would be to store the date_time column either as DATE or TIMESTAMP (not VARCHAR2). Then index the column and if you have all that, rewrite your query to use the column directly:
WHERE date_time = TO_TIMESTAMP('&from_date','dd-mon-rrrr hh24miss')
Regards,
Kojak
To see all answers submitted to the Answer Wiki: View Answer History.
Just to add to what was said — Oracle won’t use an index on an indexed column if you apply a function to that indexed column in an expression. However, if you must do this (use a function on an indexed field) you should look into Oracle’s function-based index — you create an index based on the use of a function.
hth,
Andy