Datetime column index access
Octavio Gomez found that a datetime year-to-second index was not used for extend(date_a, year to day) = today. June C. Hunt suggested an INDEX optimizer directive (IDS 7.3 or 9.2+) and checking explain plans, as a forced index raised estimated cost in her test. Art S. Kagel and Curtis Crowson suggested a BETWEEN range from midnight to end of day instead. No confirmation of the result.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi everybody,
I have a table with a datetime column (year to second) and I created a index
on that column.
When I try the next sentence:
Select * from table1 where extend(date_a, year to day) = today (or
any date, datetime(year to day) value)
The access is sequential.
How can I do to access that column in indexed path?
Of course, if I alter the column (year to day) the index will be used, but
in this case table structure cannot be modify
Thanks!
Octavio G'mez
sending to informix-list
Octavio G'mez wrote:
> I have a table with a datetime column (year to second) and I created a
index
> on that column.
>
> When I try the next sentence:
>
> Select * from table1 where extend(date_a, year to day) = today (or
> any date, datetime(year to day) value)>
> The access is sequential.
>
> How can I do to access that column in indexed path?
>
> Of course, if I alter the column (year to day) the index will be used, but
> in this case table structure cannot be modify
If you are using IDS 7.3, or IDS 9.2 or higher (I think I got those version
numbers right), you could specify an optimizer directive with your SELECT
statement. For example:
SELECT {+INDEX (TABLE1 <index_name>)}
* FROM TABLE1
WHERE EXTEND(date_a, YEAR TO DAY) = TODAY
The Guide to SQL: Syntax manuals describe the optimizer directives.
Just a side note - look at the explain plans. In a very small test that I
just ran, the estimated cost was higher when I forced the use of the index.
The test was too small to make a difference in the actual run time, but...
it might be worth keeping track.
--
June Hunt
On Wed, 21 Jan 2004 14:21:27 -0500, Octavio G'''mez wrote:
try:
Select * from table1
where
date_a between extend(today, year to second)
and extend((today+1), year to second);
Art S. Kagel
> Hi everybody,
>
> I have a table with a datetime column (year to second) and I created a index
> on that column.
>
> When I try the next sentence:
>
> Select * from table1 where extend(date_a, year to day) = today (or
> any date, datetime(year to day) value)>
> The access is sequential.
>
> How can I do to access that column in indexed path?
>
> Of course, if I alter the column (year to day) the index will be used, but
> in this case table structure cannot be modify
>
> Thanks!
>
> Octavio G'''mez
> sending to informix-list
Octavio G'mez <sogomez@hotmail.com> wrote in message news:<bumkj2$kaj$1@terabinaries.xmission.com>...
> Hi everybody,
>
> I have a table with a datetime column (year to second) and I created a index
> on that column.
>
> When I try the next sentence:
>
> Select * from table1 where extend(date_a, year to day) = today (or
> any date, datetime(year to day) value)>
> The access is sequential.
>
> How can I do to access that column in indexed path?
>
> Of course, if I alter the column (year to day) the index will be used, but
> in this case table structure cannot be modify
>
> Thanks!
>
> Octavio G'mez
> sending to informix-list
You could use between:
select * from table1 where date_a between datetime(midnight of day)
datetime(11:59:59 pm of same day)