1

I have the following in my SQL where clause. This is running against an Oracle database. The timestamp field is defined in the database as a timestimp field. my select query is :

create index ind_timestamp on sms_in(to_char(timestamp,'YYYY-MM-DD'));

select * from sms_in where to_char(timestamp,'YYYY-MM-DD')in '2018-08-01';

but when I'm tring to execut the query the database acces is full. That means the indexing doesn't work.

Dominik
  • 1,016
  • 11
  • 30
Dach Ch
  • 23
  • 1
  • 8
  • 1
    Wondering why an `IN` clause and not `=` in the query – Andrew Aug 07 '18 at 12:30
  • How does the plan change if you change `in` to `=`? Also, how many rows are in your table, and how many of those rows have `TO_CHAR(TIMESTAMP, 'YYYY-MM-DD') ` equal to `2018-08-01`? Also, when was the last time you gathered statistics on this table? Use `SELECT LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME = 'SMS_IN'` to find out. – Bob Jarvis - Слава Україні Aug 07 '18 at 12:30
  • also, it's better to run dbms_stats.gather_table_stats() on sms_in table to update optimizer calculations. – Dima Yankin Aug 07 '18 at 12:32
  • 2
    why are you using function based index, add an index on timestamp column and convert your string to timestamp in the where clause – hotfix Aug 07 '18 at 12:32
  • The query result is correct the issue is with the index it doesn't work. – Dach Ch Aug 07 '18 at 12:36
  • also I'm using the indexing because the table contains more than 100K records. While fetching the records, the cpu usage goes to 100%. – Dach Ch Aug 07 '18 at 12:37
  • i don't say you shouldn't use an index. you shoul use a normal index on column and not an function based index – hotfix Aug 07 '18 at 12:39
  • @hotfix - that mechanism won't quite work without some more effort on the query, the to_char is truncating the timestamp, removing the time element. If he converts a date to timestamp, it will be midnight by default, and not match without the extra logic / effort (this assumes his timestamps are not being set as pure dates) – Andrew Aug 07 '18 at 12:40
  • @Andrew he could use `between 00:00:00 and 23:59:59` and that should use an index – hotfix Aug 07 '18 at 12:44
  • @Andrew that's true I'm using the timestamp with diffrent format. – Dach Ch Aug 07 '18 at 12:49
  • @hotfix - agreed, that is the extra query work which I think needs to be spelt out if you write up your comment in an answer. – Andrew Aug 07 '18 at 12:50
  • @hotfix that mechanism doesn't work. – Dach Ch Aug 07 '18 at 12:50
  • 3
    How many different dates are in the table? (`select count(distinct trunc(timestamp)) from sms_in`) The DBMS will only use the index when it expects only a small part of the table to match the criteria. And an `IN` clause makes it much less likely that an index be used. This is no problem; a full table scan, even for millions of rows, can still be faster than an index access. And that 100% CPU usage is also not bad per se. – Thorsten Kettner Aug 07 '18 at 12:50
  • @ThorstenKettner the table have 215 different dates. – Dach Ch Aug 07 '18 at 12:55
  • @DachCh i don't see a reason why? you should tell me why it doesn't work. – hotfix Aug 07 '18 at 12:59
  • @hotfix the table acces is full it cost 4014 like I'm not using an index. – Dach Ch Aug 07 '18 at 13:08
  • @DachCh add your execution plan – hotfix Aug 07 '18 at 13:10
  • I would rather create an index on `TRUNC(timestamp)` – Wernfried Domscheit Aug 07 '18 at 13:14

1 Answers1

3

If you are always looking for the data for a particular day, then my suggestion would be as follow

you add an in normal index on timestamp column

create index ind_timestamp on sms_in(timestamp);

your select could Looks like

select * 
  from sms_in 
 where timestamp between to_date('2018-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
                     and to_date('2018-08-01 23:59:59','YYYY-MM-DD HH24:MI:SS');

or you can do it like

select * 
  from sms_in 
 where timestamp >= to_date('2018-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  and timestamp  <= to_date('2018-08-01 23:59:59','YYYY-MM-DD HH24:MI:SS');

EDIT:

This answer is basically correct, but the logic should be:

select * 
 from sms_in 
 where timestamp >= date '2018-08-01' and
       timestamp < date '2018-08-02 ;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
hotfix
  • 3,376
  • 20
  • 36
  • 1
    This will not get any timestamps between `23:59:59.999000001` and `23:59:59.999999999` you would be better using `WHERE timestamp >= TIMESTAMP '2018-01-01 00:00:00' AND timestamp < TIMESTAMP '2018-01-02 00:00:00'. – MT0 Aug 07 '18 at 12:57
  • you are right, but first we should know how the data in his table looks like. maybe he just save FF3 – hotfix Aug 07 '18 at 13:01
  • The timestamp column type is date,it look like 'YYYY-MM-DD'. But I'm using other query where I want only 'YYYY-MM' or 'YYYY' or 'MM' etc. It depends of what I'm fetching. – Dach Ch Aug 07 '18 at 13:03
  • we don't know about another cases you do, if you Need it too, you have to edit your question and add all cases you want to be handled – hotfix Aug 07 '18 at 13:08
  • This answer is correct but I don't know how to obtain the next day ('2018-08-02') . – Dach Ch Aug 07 '18 at 13:30
  • `date '2018-08-01' +1` – hotfix Aug 07 '18 at 13:32