Re: fragment elimination on query
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 >= 1
Art S. Kagel
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.
=
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.
>
>
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.
>