RE: Fragmentation Elimination Problem
try changing your where clause to
timestamp = datetime(23) hour to hour
so your query will be,
select
timestamp,
current,
extend(current, hour to hour) - extend(timestamp, hour to hour)
from
session
where
timestamp = datetime(23) hour to hour
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of Curtis Crowson
Sent: Wednesday, February 15, 2006 8:00 AM
To: informix-list@iiug.org
Subject: Fragmentation Elimination Problem
I have the following fragmentation strategy that I am testing on a
table, but it doesn't do what I want. I want to have 24 fragments that
represent the hour of the timestamp. That way the query that clears
records away that are over some number of hours old will only have to
look in the relative fragments. The problem is that I don't get
fragment elimination. It seems like I am running a foul of the too
complicated to eliminate problem. I RTFM'd and was discouraged because
it looks like if you include a function (extend in my case) you may not
be able to eliminate fragments. I on the otherhand don't really think
of extend as a function as much as a way to pull out just the part of
the timestamp that I want. ;-)
Does anyone know of a way to do this? I have a work arround converting
the hour to a smallint and storing in a separate field and fragmenting
on this. I am prepared to use this with the appropriate trigger to
populate the field if no one can come up with a better idea.
Thanks for your help with this interesting problem.
Here is the explain plan that I get:
QUERY:
------
select
timestamp,
current,
extend(current, hour to hour) - extend(timestamp, hour to hour)
from
session
where
extend(timestamp, hour to hour) = "23"
Estimated Cost: 457
Estimated # of Rows Returned: 86
1) informix.session: SEQUENTIAL SCAN (Serial, fragments: ALL)
Filters: EXTEND (informix.session.timestamp ,hour to hour) =
datetime(23) hour to hour
Here is the better explain plan that I want with the work around. (The
sql for this is also below.)
QUERY:
------
select
timestamp,
current,
tsHour
from
session
where
tsHour = 18
Estimated Cost: 1
Estimated # of Rows Returned: 86
1) informix.session: SEQUENTIAL SCAN (Serial, fragments: 18)
Filters: informix.session.tshour = 18
Below I have my sample SQL to duplicate this problem if you want to try
it:
Sample Table:
create table session(
id serial,
timestamp datetime year to second
) ;
Sample Data:
4380|2006-01-16 21:00:00|
11221|2006-01-16 20:01:00|
11639|2006-01-16 21:59:00|
10624|2006-01-16 18:28:00|
10647|2006-01-16 18:42:00|
10094|2006-01-16 17:35:00|
11579|2006-01-16 21:44:00|
11233|2006-01-16 20:10:00|
10285|2006-01-16 17:52:00|
6747|2006-01-16 19:57:00|
10774|2006-01-16 19:02:00|
10414|2006-01-16 18:05:00|
10314|2006-01-16 17:55:00|
11630|2006-01-16 21:57:00|
10312|2006-01-16 17:55:00|
10797|2006-01-16 19:02:00|
10235|2006-01-16 17:46:00|
10319|2006-01-16 17:56:00|
8470|2006-01-16 20:31:00|
10196|2006-01-16 18:12:00|
10350|2006-01-16 20:19:00|
10934|2006-01-16 19:15:00|
10777|2006-01-16 18:51:00|
11635|2006-01-16 21:59:01|
715|2006-01-16 18:06:01|
10045|2006-01-16 18:08:01|
7983|2006-01-16 20:23:01|
2940|2006-01-16 18:33:01|
6742|2006-01-16 18:23:01|
10397|2006-01-16 18:07:01|
11386|2006-01-16 20:45:01|
10417|2006-01-16 18:05:01|
10717|2006-01-16 18:41:02|
11442|2006-01-16 21:00:02|
3906|2006-01-16 19:32:02|
10090|2006-01-16 17:35:02|
11112|2006-01-16 19:42:02|
10794|2006-01-16 18:51:02|
9727|2006-01-16 18:02:02|
11352|2006-01-16 21:30:02|
11360|2006-01-16 20:51:02|
11565|2006-01-16 21:38:02|
7972|2006-01-16 17:37:02|
11121|2006-01-16 21:58:03|
11087|2006-01-16 19:56:03|
10354|2006-01-16 18:01:03|
10590|2006-01-16 18:23:03|
11508|2006-01-16 21:20:03|
-- Original Fragmentation Strategy
alter fragment on table session init fragment by expression
partition hour0 extend(timestamp, hour to hour) = "00" in maindbs001,
partition hour1 extend(timestamp, hour to hour) = "01" in maindbs001,
partition hour2 extend(timestamp, hour to hour) = "02" in maindbs001,
partition hour3 extend(timestamp, hour to hour) = "03" in maindbs001,
partition hour4 extend(timestamp, hour to hour) = "04" in maindbs001,
partition hour5 extend(timestamp, hour to hour) = "05" in maindbs001,
partition hour6 extend(timestamp, hour to hour) = "06" in maindbs001,
partition hour7 extend(timestamp, hour to hour) = "07" in maindbs001,
partition hour8 extend(timestamp, hour to hour) = "08" in maindbs001,
partition hour9 extend(timestamp, hour to hour) = "09" in maindbs001,
partition hour10 extend(timestamp, hour to hour) = "10" in
maindbs001,
partition hour11 extend(timestamp, hour to hour) = "11" in
maindbs001,
partition hour12 extend(timestamp, hour to hour) = "12" in
maindbs001,
partition hour13 extend(timestamp, hour to hour) = "13" in
maindbs001,
partition hour14 extend(timestamp, hour to hour) = "14" in
maindbs001,
partition hour15 extend(timestamp, hour to hour) = "15" in
maindbs001,
partition hour16 extend(timestamp, hour to hour) = "16" in
maindbs001,
partition hour17 extend(timestamp, hour to hour) = "17" in
maindbs001,
partition hour18 extend(timestamp, hour to hour) = "18" in
maindbs001,
partition hour19 extend(timestamp, hour to hour) = "19" in
maindbs001,
partition hour20 extend(timestamp, hour to hour) = "20" in
maindbs001,
partition hour21 extend(timestamp, hour to hour) = "21" in
maindbs001,
partition hour22 extend(timestamp, hour to hour) = "22" in
maindbs001,
partition hour23 extend(timestamp, hour to hour) = "23" in maindbs001;
update statistics for table session;
update statistics high for table session;
set explain on;select
timestamp,
current,
extend(current, hour to hour) - extend(timestamp, hour to hour)
from
session
where
extend(timestamp, hour to hour) = "23"
;
-- For contrast if I use the work around I get the better explain plan:
create procedure hourToSmallint( inHour datetime hour to hour )
returning smallint ;
define i char(2);
let i = inHour ;
return i ;
end procedure ;
alter table session add(tsHour smallint) ;
update session set tsHour = hourToSmallint(timestamp) ;
alter table session modify(tsHour smallint not null) ;
alter fragment on table session init fragment by expression
partition hour0 tsHour = 00 in maindbs001,
partition hour1 tsHour = 01 in maindbs001,
partition hour2 tsHour = 02 in maindbs001,
partition hour3 tsHour = 03 in maindbs001,
par
>Savio Pinto Said >try changing your where clause to >timestamp = datetime(23) hour to hour Uh, this only seems to work if I don't have any minutes or seconds. So I wouldn't need to convert to smallint I could just have an extra field that was defined as tsHour datetime hour to hour I would then use your syntax. Thanks