IDS Forum

Re: fragment elimination on query

Posted By: John Miller iii
Date: Friday, 7 July 2006, at 7:46 p.m.

In Response To: Re: fragment elimination on query (Quman)

You are correct, the "3D" should not be there.

John

ids-bounces@iiug.org wrote on 07/07/2006 10:59:08 AM:

>
> Thank you, John!
>
> The expression, c1[1,2] =3D'AA',
>
> "3D " should not be there?
>
> Quman
>
> On 7/6/06, John Miller iii <miller3@us.ibm.com> wrote:
> >
> >
> > Art:
> >
> > You do not need to have pdqpriority on in order to get fragmentation
> > elimination to
> > work. I believe the problem with matches/like is the complex expressio=

> > n
> > that one may
> > use in these functions.
> >
> > A way to over come this is to use column subscripting to match the
> > beginning of a
> > column. See example below:
> >
> > create table t2 ( c1 char(20) )
> > fragment by expression
> > partition p1 ( c1[1,2] =3D'AA') in rootdbs,
> > partition p2 ( c1[1,2] =3D'BB') in rootdbs,
> > partition rmd remainder in rootdbs;
> >
> > insert into t2 select"AA"||tabname from systables;
> > insert into t2 select"BB"||tabname from systables;
> >
> > set explain on;
> >
> > set pdqpriority 0;
> > select count(*) from t2 where c1[1,2] =3D 'AA';
> > select count(*) from t2 where c1 like 'AA%';
> >
> > QUERY:
> > ------
> > select count(*) from t2 where c1[1,2] =3D 'AA'
> >
> > Estimated Cost: 2
> > Estimated # of Rows Returned: 1
> >
> > 1) miller3.t2: SEQUENTIAL SCAN (Serial, fragments: 0, 2)
> >
> > Filters: miller3.t2.c1[1,2] =3D 'AA'
> >
> > QUERY:
> > ------
> > select count(*) from t2 where c1 like 'AA%'
> >
> > Estimated Cost: 3
> > Estimated # of Rows Returned: 1
> >
> > 1) miller3.t2: SEQUENTIAL SCAN (Serial, fragments: ALL)
> >
> > Filters: miller3.t2.c1 LIKE 'AA%'
> >
> > John Miller
> >
> > =
> >
> > "Art S. Kagel" =
> >
> > <kagel@bloomberg. =
> >
> > net> =
> > To
> >
> > Sent by: ids@iiug.org =
> >
> > ids-bounces@iiug. =
> > cc
> >
> > org =
> >
> > Subj=
> > ect
> >
> > Re: fragment elimination on quer=
> > y
> >
> > 07/06/2006 02:31 [7083] =
> >
> > PM =
> >
> > =
> >
> > =
> >
> > Please respond to =
> >
> > ids@iiug.org =
> >
> > =
> >
> > =
> >
> > Quman wrote:
> > >
> > > I have a table,
> > >
> > > create table "informix".ds_head
> > > (
> > <SNIP>> ) with crcols
> > > fragment by expression
> > > partition pt1 (datatype_name LIKE 'GVAR%' ) in dbdata00 ,
> > > partition pt2 (datatype_name LIKE 'GOES%' ) in dbdata01 ,
> > > partition pt3 (datatype_name LIKE 'CW%' ) in dbdata02 ,
> > > partition rmd remainder in dbdw00
> > > extent size 8192 next size 8192 lock mode row;
> > <SNIP>
> > > I made a query with a EXPLAIN ON,
> > >
> > >
> > > select * from ds_head
> > > where datatype_name like "AV%"
> > >
> > >
> > > Estimated Cost: 2797
> > > Estimated # of Rows Returned: 7947
> > >
> > > 1) informix.ds_head: INDEX PATH
> > >
> > > (1) Index Keys: datatype_name (Serial, fragments: ALL)
> > > Lower Index Filter: informix.ds_head.datatype_name LIKE 'AV%'
> > >
> > > Looks IDS does search all fragments, not just ONE partition rmd.
> > >
> > > Is this because "LIKE" is too confuse to be used by optimizer? If
so,=
> >
> > > what operator we should use?
> >
> > For fragment elimination to take place you have to run with
PDQPRIORITY=
> > >=3D
> > 1
> >
> > Art S. Kagel
> >
> >
***********************************************************************=
> > ********
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> > =
> >
> >
> >
> >
>
*******************************************************************************

> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
>
*******************************************************************************

> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• Re: fragment elimination on query
  Art S. Kagel -- Thursday, 6 July 2006, at 5:31 p.m.
  • Re: fragment elimination on query
    John Miller iii -- Thursday, 6 July 2006, at 6:44 p.m.
    • Re: fragment elimination on query
      Quman -- Friday, 7 July 2006, at 1:59 p.m.
      • Re: fragment elimination on query
        John Miller iii -- Friday, 7 July 2006, at 7:46 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.