Re: =?ISO-8859-1?Q?R=E9f=2E_=3A_Re=3A_Fragmentation_on_?=
Hi
Avoid using remainder clause in the fragmentation expression and it will do
sequential scan in the proper fragment only elimination by expression).
Uri
mohamed.afe.... wrote:
>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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
--
Uri Haham
Professional Services Manager
ComSoft Technologies Ltd.
P.O.B 2016
Herzliya 46120 ISRAEL
Main switchboard: +972-9-9598999
Main fax no: +972-9-9598980
Extension: +972-9-9598627
E-mail uri@comsoft.co.il
URL www.comsoft.co.il