=?iso-8859-1?Q?RE=3A_R=E9f=2E_=3A_Re=3A__=5B171=5D_?=
I did a
little testing on this ... I think one of the reasons all fragments
are scanned is because the index is detached and not fragmented. If you
can, try creating your indexes so that they are fragmented with the table
(don't use the "in dbspace").
-----Original Message-----
From: mohamed.afe.... [mailto:mohamed.afeilal@meditel.ma]
Sent: Wednesday, January 29, 2003 6:27 PM
To: ids@iiug.org
Subject: Réf. : Re: [171]
Yes, and I still have the some problem
"David
Williams" Pour : ids@iiug.org
<djw@smooth1.fs cc :
net.co.uk> Objet : Re: [170]
Envoyé par :
forum.subscribe
r@iiug.org
29/01/2003
22:46
----- Original Message -----
From: "Madison Pruet " <mpruet@us.ibm.com>
To: <ids@iiug.org>
Sent: Wednesday, January 29, 2003 8:55 PM
Subject: Re: [166]
>
>
>
>
> Looks like it's time to contact tech support.
>
> On the surface I'd suppect a bug, but would need to fully review
> fragment elimination rules to see if it is a problem when one of the
> fragments
uses
> 'IN' while the others use equality.
>
>
Have you run update stats on the table? High/medium/low as recommended in
other posts here? (Use Art Kagel's dostats utility...)?
>
>
>
>
> mohamed.afeilal@m
> editel.ma To: Madison
Pruet/Dallas/IBM@IBMUS
> cc:
forum.subscriber@iiug.org, ids@iiug.org
> 01/29/2003 02:45 Subject: Réf. : Re:
Fragmentation on a column type datetime [163]
> PM
>
>
>
>
>
>
>
> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>