IDS Forum

Re:

Posted By: Madison Pruet
Date: Wednesday, 29 January 2003, at 3:55 p.m.

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.






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

Messages In This Thread

• Re:
  Madison Pruet -- Wednesday, 29 January 2003, at 3:55 p.m.
  • Re:
    David Williams -- Wednesday, 29 January 2003, at 5:46 p.m.
    • =?iso-8859-1?q?R=E9f=2E_=3A_Re=3A_?=
      mohamed.afeilal@meditel.ma -- Wednesday, 29 January 2003, at 7:27 p.m.
    • RE:
      John Carlson -- Thursday, 30 January 2003, at 1:44 p.m.
      • RE:
        DarrenJacobs@carmax.com -- Thursday, 30 January 2003, at 3:53 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.