Table not using index

15 pts.
Tags:
Oracle
Oracle development
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
ASKED: November 13, 2006  4:29 AM
UPDATED: November 13, 2006  9:19 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ascdba
    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
    0 pointsBadges:
    report
  • Ws8oBPSQ
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/table-not-using-index/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following