Oninit cdi / Archive
Talk to an expert →

Re:

Posted in 2003

Topics: Performance & Tuning, Storage & Space Management, Security, Permissions & Auditing, Data Types & Schema Design

Madison Pruet — — source: IIUG Forums & Mailing Lists

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

↪ replying to Madison Pruet
David Williams — — source: IIUG Forums & Mailing Lists

----- 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 thetable. > > It is a bug Informix ? So yes, It's possible to circumvent it > > Thank you very match > > > > > > > > > > > > > >

↪ replying to David Williams
mohamed.afeilal@meditel.ma — — source: IIUG Forums & Mailing Lists

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 thetable. > > It is a bug Informix ? So yes, It's possible to circumvent it > > Thank you very match > > > > > > > > > > > > > >

↪ replying to David Williams
John Carlson — — source: IIUG Forums & Mailing Lists

Update stats would give the optimizer something to use, but there's no index on the column in question. That would affect dostats in that the column would not be included for HIGH. > -----Original Message----- > From: David Williams [mailto:djw@smooth1.fsnet.co.uk] > Sent: Wednesday, January 29, 2003 5:47 PM > To: ids@iiug.org > Subject: Re: [170] > > > > ----- 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

↪ replying to John Carlson
DarrenJacobs@carmax.com — — source: IIUG Forums & Mailing Lists

Try this directive if you want to avoid the scan. The saldo_pago is the second column in the index. But you are aware that the request is for all the data that is associated with the frag for yr 2002. select --+index (mimo_saldo idxmimosaldo3) * from mimo_saldo where year(saldo_pago) = 2002 ----- Forwarded by Darren Jacobs/7001/Carmax on 01/30/03 03:45 PM ----- "John Carlson " <John_Carlson@whsm To: ids@iiug.org ithusa.com> cc: Sent by: Subject: RE: [180] forum.subscriber@i iug.org 01/30/03 01:44 PM Update stats would give the optimizer something to use, but there's no index on the column in question. That would affect dostats in that the column would not be included for HIGH. > -----Original Message----- > From: David Williams [mailto:djw@smooth1.fsnet.co.uk] > Sent: Wednesday, January 29, 2003 5:47 PM > To: ids@iiug.org > Subject: Re: [170] > > > > ----- 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

Glossary