Fragmentation Elimination Problem
The poster wanted a table fragmented into 24 expression-based fragments using EXTEND(timestamp, hour to hour) so a cleanup query would only touch relevant fragments, but the optimizer showed a sequential scan of ALL fragments — the function in the fragment expression apparently defeats fragment elimination. His own workaround, adding a smallint column holding the hour (populated via a procedure/trigger) and fragmenting on that, did give elimination. Replies mainly questioned whether fragment elimination was needed at all and criticised his 128-byte session_id index, suggesting a serial instead; no better solution to the EXTEND problem was offered, so no real resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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,
partition hour4 tsHour = 04 in maindbs001,
partition hour5 tsHour = 05 in maindbs001,
partition hour6 tsHour = 06 in maindbs001,
partition hour7 tsHour = 07 in maindbs001,
partition hour8 tsHour = 08 in maindbs001,
partition hour9 tsHour = 09 in maindbs001,
partition hour10 tsHour = 10 in maindbs001,
partition hour11 tsHour = 11 in maindbs001,
partition hour12 tsHour = 12 in maindbs001,
partition hour13 tsHour = 13 in maindbs001,
partition hour14 tsHour = 14 in maindbs001,
partition hour15 tsHour = 15 in maindbs001,
partition h
I am on version IBM Informix Dynamic Server Version 10.00.FC4.
Curtis Crowson said: > 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. Why do you want fragment elimination? -- Bye now, Obnoxio "Jesus you fucking people are hopeless." -- Double Anal "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
Obnoxio Said: >Why do you want fragment elimination? Why wouldn't I? I don't want to scan the whole table which can be quite large during peak usage and it is used frequently. I guess I could just put an index on the timestamp field and use an index scan to remove the unwanted records. I was just trying this solution to see if it might work well. And then I stumbled out of the starting gate.
Obnoxio Said: >Why do you want fragment elimination? Why wouldn't I? I don't want to scan the whole table which can be quite large during peak usage and it is used frequently. I guess I could just put an index on the timestamp field and use an index scan to remove the unwanted records. I was just trying this solution to see if it might work well. And then I stumbled out of the starting gate.
Curtis Crowson said: > Obnoxio Said: >>Why do you want fragment elimination? > > Why wouldn't I? I don't want to scan the whole table which can be quite > large during peak usage and it is used frequently. I guess I could just > put an index on the timestamp field and use an index scan to remove the > unwanted records. I was just trying this solution to see if it might > work well. And then I stumbled out of the starting gate. What kind of queries generally run against the table, though? -- Bye now, Obnoxio "Jesus you fucking people are hopeless." -- Double Anal "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
OTC Said >What kind of queries generally run against the table, though? All of the queries except for the session cleanup script use a session_id char(128) unique index to access the proper one session record.
Curtis Crowson said: > OTC Said >>What kind of queries generally run against the table, though? > > All of the queries except for the session cleanup script use a > session_id char(128) unique index to access the proper one session > record. A 128-byte index? Why bother? :o) -- Bye now, Obnoxio "Jesus you fucking people are hopeless." -- Double Anal "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
OTC Said: A 128-byte index? Why bother? :o) Locking issues on updates. Or It could be that I like indexes. And I am always looking for a place to add one. I have several tables with more indexes than columns. If I could just do away with the tables and data, leaving only the indexes I would be one happy DBA. ;-)
Curtis Crowson said: > OTC Said: > A 128-byte index? Why bother? :o) > > Locking issues on updates. > > Or It could be that I like indexes. And I am always looking for a place > to add one. I have several tables with more indexes than columns. If I > could just do away with the tables and data, leaving only the indexes I > would be one happy DBA. ;-) Perhaps you could achieve your goal with something like a serial -- smaller, faster and lighter. A 128-byte index isn't the most efficient option. -- Bye now, Obnoxio "Jesus you fucking people are hopeless." -- Double Anal "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com