=?iso-8859-1?q?R=E9f=2E_=3A_Re=3A_Fragmentation_on_a_column_type_datetim
This is the schema of the table :
create table "pps".mimo_saldo
(
mimo_codigo integer,
saldo_tipo integer,
saldo_caixa char(3),
saldo_valor decimal(10,2),
saldo_pago datetime year to second,
saldo_pago_cli datetime year to second,
factura_codigo varchar(12)
) with rowids
fragment by expression
(YEAR (saldo_pago ) IN (2000 ,2001 )) in dbs_saldo0 ,
(YEAR (saldo_pago ) = 2002 ) in dbs_saldo1 ,
(YEAR (saldo_pago ) = 2003 ) in dbs_saldo2 ,
remainder in dbs_saldo_remain
extent size 2000000 next size 500000 lock mode row;
revoke all on "pps".mimo_saldo from "public";
create index "pps".idxmimosaldo1 on "pps".mimo_saldo (mimo_codigo)
in dbs_saldo_idx ;
create index "pps".idxmimosaldo2 on "pps".mimo_saldo (saldo_tipo,
saldo_pago_cli) in dbs_saldo_idx ;
create index "pps".idxmimosaldo3 on "pps".mimo_saldo (saldo_tipo,
saldo_pago) in dbs_saldo_idx ;
create index "pps".idxmimosaldo4 on "pps".mimo_saldo (saldo_caixa)
in dbs_saldo_idx ;
create index "pps".idxmimosaldo5 on "pps".mimo_saldo (mimo_codigo,
saldo_tipo,saldo_pago_cli) in dbs_saldo_idx ;
create index "pps".idxmimosaldo6 on "pps".mimo_saldo (mimo_codigo,
saldo_tipo,saldo_pago) in dbs_saldo_idx ;
And these is the requete
select * from mimo_saldo where year(saldo_pago) = 2002
Estimated Cost: 3373859
Estimated # of Rows Returned: 6520898
1) pps.mimo_saldo: SEQUENTIAL SCAN (Serial, fragments: ALL)
Filters: YEAR (pps.mimo_saldo.saldo_pago ) = 2002
Madison Pruet
<mpruet@us.ib Pour : "mohamed.afe...." <mohamed.afeilal@meditel.ma>
m.com> cc : forum.subscriber@iiug.org, ids@iiug.org
Objet : Re: Fragmentation on a column type datetime [163]
29/01/2003
20:28
Please include the where clause of the fragmentation and the where clause
of the query.
"mohamed.afe...."
<mohamed.afeilal@ To: ids@iiug.org
meditel.ma> cc:
Sent by: Subject: Fragmentation on a
column type datetime [163]
forum.subscriber@
iiug.org
01/29/2003 02:16
PM
We fragment a table by expression on a column of datetime type. By using
set explain on, I saw that for a transaction witch makes reference only
with the column of fragmentation, the access is sequentel of all the table.
It is a bug Informix ? So yes, It's possible to circumvent it
Thank you very match