Question regarding selecting from a fragmented table
Posted in 2004
Hi all
I have a table which is fragmented by date, year to day. When I query this
table using the fragment expression as the where clause of my select statement
Informix still searches all fragments. Should it not just access the one
fragment.
Informix version 9.21.UC6 running on Solaris 8. Any ideas?
QUERY:
------
select network_id, fragment_date
from gproc_statistics
where (extend (fragment_date, year to day) = datetime(2004-03-30) year to day)
Estimated Cost: 11783
Estimated # of Rows Returned: 25946
1) omcadmin.gproc_statistics: SEQUENTIAL SCAN (Serial, fragments: ALL)
Filters: EXTEND (omcadmin.gproc_statistics.fragment_date ,year to day) =
datetime(2004-03-30) year to day
Table definition:-
create table "omcadmin".gproc_statistics
(
network_id integer not null constraint "omcadmin".cnn_gpr_stats_054,
time_id integer not null constraint "omcadmin".cnn_gpr_stats_055,
fragment_date datetime year to minute not null ,
cpu_usage_mean float,
cpu_usage_min smallint,
cpu_usage_max smallint
)
fragment by expression
(EXTEND (fragment_date ,year to day) = datetime(2004-03-22)
year to day ) in omc_db_sp2 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-03-23)
year to day ) in omc_db_sp1 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-03-24)
year to day ) in omc_db_sp11 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-03-25)
year to day ) in omc_db_sp10 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-03-26)
year to day ) in omc_db_sp9 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-03-27)
year to day ) in omc_db_sp8 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-03-28)
year to day ) in omc_db_sp7 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-03-29)
year to day ) in omc_db_sp6 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-03-30)
year to day ) in omc_db_sp5 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-03-31)
year to day ) in omc_db_sp4 ,
(EXTEND (fragment_date ,year to day) = datetime(2004-04-01)
year to day ) in omc_db_sp3 ,
remainder in omc_db_sp12
extent size 64512 next size 13312 lock mode row;