Re: fragment elimination on query
Hello Quman
it is using the index to fetch the data;
> select * from ds_head
> where datatype_name like "AV%">
>
> Estimated Cost: 2797
> Estimated # of Rows Returned: 7947
>
> 1) informix.ds_head: INDEX PATH
try giving it an hint not to use the index or drop the index; then
you'll probably see frag elimantion.
OR better fragment your index too...
this one:
> create index "informix".ds_head_idx2 on "informix".ds_head (datatype_name,
> inventory_id) using btree ;
Superboer.
Quman schreef:
> I have a table,
>
> create table "informix".ds_head
> (
> inventory_id serial not null ,
> dataset_name varchar(255,44) not null ,
> dataset_size_bytes integer,
> datatype_name char(10) not null ,
> datatype_version char(10),
> ingest_status char(10),
> ingest_dt datetime year to second not null ,
> orig_data_filenm varchar(255,44) not null ,
> distribution_site char(1) not null ,
> data_source char(10),
> has_visual_file char(1),
> restriction_level smallint
> ) 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;
>
> reate unique index "informix".ds_head_idx0 on "informix".ds_head
> (dataset_name,datatype_name) using btree ;
> create index "informix".ds_head_idx2 on "informix".ds_head (datatype_name,
> inventory_id) using btree ;
>
> create unique index "informix".ds_head_ipk on "informix".ds_head
> (inventory_id) using btree in dbdw01 ;
>
> alter table "informix".ds_head add constraint primary key (inventory_id)
> constraint "informix".ds_head_pk ;
>
>
> 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?
>
> Thanks,
>
> Quman
>
> ------=_Part_72260_7493042.1152212713507
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2706
>
> <div> </div>
> <div>I have a table,</div>
> <div> </div>
> <div>create table "informix".ds_head<br> (<br> inventory_id serial not null ,<br> dataset_name varchar(255,44) not null ,<br> dataset_size_bytes integer,<br> datatype_name char(10) not null ,<br> datatype_version char(10),
> <br> ingest_status char(10),<br> ingest_dt datetime year to second not null ,<br> orig_data_filenm varchar(255,44) not null ,<br> distribution_site char(1) not null ,<br> data_source char(10),<br> has_visual_file char(1),
> <br> restriction_level smallint<br> ) with crcols<br> fragment by expression<br> partition pt1 (datatype_name LIKE 'GVAR%' ) in dbdata00 ,<br> partition pt2 (datatype_name LIKE 'GOES%' ) in dbdata01 ,<br> partition pt3 (datatype_name LIKE 'CW%' ) in dbdata02 ,
> <br> partition rmd remainder in dbdw00<br> extent size 8192 next size 8192 lock mode row;<br> </div>
> <div>reate unique index "informix".ds_head_idx0 on "informix".ds_head<br> (dataset_name,datatype_name) using btree ;<br>create index "informix".ds_head_idx2 on "informix".ds_head (datatype_name,
> <br> inventory_id) using btree ;</div>
> <div> </div>
> <div>create unique index "informix".ds_head_ipk on "informix".ds_head<br> (inventory_id) using btree in dbdw01 ;<br> </div>
> <div>alter table "informix".ds_head add constraint primary key (inventory_id)<br> constraint "informix".ds_head_pk ;<br> </div>
> <div> </div>
> <div>I made a query with a EXPLAIN ON,</div>
> <div> </div>
> <div>
> <p>select * from ds_head<br>where datatype_name like "AV%"</p>
> <p><br>Estimated Cost: 2797<br>Estimated # of Rows Returned: 7947</p>
> <p> 1) informix.ds_head: INDEX PATH</p>
> <p> (1) Index Keys: datatype_name (Serial, fragments: ALL)<br> Lower Index Filter: informix.ds_head.datatype_name LIKE 'AV%'<br></p>
> <p>Looks IDS does search all fragments, not just ONE partition rmd.</p>
> <p>Is this because "LIKE" is too confuse to be used by optimizer? If so, what operator we should use?</p>
> <p>Thanks,</p>
> <p>Quman</p>
> <p> </p>
> <p> </p></div>
> <div> </div>
> <div><br> </div>
> <div> </div>
>
> ------=_Part_72260_7493042.1152212713507--
HI, Superboer!
You raised a excellent question.
The table is not new and the primary key can not be redefined(referenced by
other tables already).
datatype_name is the only possible column to be used as fragmenting
key(column), so a global unique index on "inventory_id" can not be
fragmented by informix.
So, my question is,
Is it possible or reasonable to have a fragmented unique index( index keys
do not include fragmentation keys) in future Informix?
Thanks,
Quman
On 6 Jul 2006 23:26:15 -0700, Superboer <superboer7@t-online.de> wrote:
>
> Hello Quman
>
> it is using the index to fetch the data;
>
> > select * from ds_head
> > where datatype_name like "AV%"> >
> >
> > Estimated Cost: 2797
> > Estimated # of Rows Returned: 7947
> >
> > 1) informix.ds_head: INDEX PATH
>
> try giving it an hint not to use the index or drop the index; then
> you'll probably see frag elimantion.
>
> OR better fragment your index too...
> this one:
>
> > create index "informix".ds_head_idx2 on "informix".ds_head
> (datatype_name,
> > inventory_id) using btree ;
>
>
> Superboer.
>
>
>
> Quman schreef:
>
> > I have a table,
> >
> > create table "informix".ds_head
> > (
> > inventory_id serial not null ,
> > dataset_name varchar(255,44) not null ,
> > dataset_size_bytes integer,
> > datatype_name char(10) not null ,
> > datatype_version char(10),
> > ingest_status char(10),
> > ingest_dt datetime year to second not null ,
> > orig_data_filenm varchar(255,44) not null ,
> > distribution_site char(1) not null ,
> > data_source char(10),
> > has_visual_file char(1),
> > restriction_level smallint
> > ) 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;
> >
> > reate unique index "informix".ds_head_idx0 on "informix".ds_head
> > (dataset_name,datatype_name) using btree ;
> > create index "informix".ds_head_idx2 on "informix".ds_head
> (datatype_name,
> > inventory_id) using btree ;
> >
> > create unique index "informix".ds_head_ipk on "informix".ds_head
> > (inventory_id) using btree in dbdw01 ;
> >
> > alter table "informix".ds_head add constraint primary key (inventory_id)
> > constraint "informix".ds_head_pk ;
> >
> >
> > 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?
> >
> > Thanks,
> >
> > Quman
> >
> > ------=_Part_72260_7493042.1152212713507
> > Content-Type: text/html; charset=ISO-8859-1
> > X-Google-AttachSize: 2706
> >
> > <div> </div>
> > <div>I have a table,</div>
> > <div> </div>
> > <div>create table "informix".ds_head<br>
> (<br> inventory_id serial not null ,<br>
> dataset_name varchar(255,44) not null ,<br>
> dataset_size_bytes integer,<br> datatype_name char(10) not
> null ,<br> datatype_version char(10),
> > <br> ingest_status char(10),<br>
> ingest_dt datetime year to second not null ,<br>
> orig_data_filenm varchar(255,44) not null ,<br>
> distribution_site char(1) not null ,<br> data_source
> char(10),<br> has_visual_file char(1),
> > <br> restriction_level smallint<br> ) with
> crcols<br> fragment by expression<br> partition pt1
> (datatype_name LIKE 'GVAR%' ) in dbdata00 ,<br> partition
> pt2 (datatype_name LIKE 'GOES%' ) in dbdata01 ,<br>
> partition pt3 (datatype_name LIKE 'CW%' ) in dbdata02 ,
> > <br> partition rmd remainder in dbdw00<br>
> extent size 8192 next size 8192 lock mode row;<br> </div>
> > <div>reate unique index "informix".ds_head_idx0 on
> "informix".ds_head<br>
> (dataset_name,datatype_name) using btree ;<br>create index
> "informix".ds_head_idx2 on "informix".ds_head
> (datatype_name,
> > <br> inventory_id) using btree ;</div>
> > <div> </div>
> > <div>create unique index "informix".ds_head_ipk on
> "informix".ds_head<br> (inventory_id) using
> btree in dbdw01 ;<br> </div>
> > <div>alter table "informix".ds_head add constraint primary key
> (inventory_id)<br> constraint
> "informix".ds_head_pk ;<br> </div>
> > <div> </div>
> > <div>I made a query with a EXPLAIN ON,</div>
> > <div> </div>
> > <div>
> > <p>select * from ds_head<br>where datatype_name like "AV%"</p>
> > <p><br>Estimated Cost: 2797<br>Estimated # of Rows Returned: 7947</p>
> > <p> 1) informix.ds_head: INDEX PATH</p>
> > <p> (1) Index Keys: datatype_name (Serial,
> fragments: ALL)<br> Lower Index
> Filter: informix.ds_head.datatype_name LIKE 'AV%'<br></p>
> > <p>Looks IDS does search all fragments, not just ONE
> partition rmd.</p>
> > <p>Is this because "LIKE" is too confuse to
> be used by optimizer? If so, what operator we should use?</p>
> > <p>Thanks,</p>
> > <p>Quman</p>
> > <p> </p>
> > <p> </p></div>
> > <div> </div>
> > <div><br> </div>
> > <div> </div>
> >
> > ------=_Part_72260_7493042.1152212713507--
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Quman wrote: > The table is not new and the primary key can not be > redefined(referenced by other tables already). You would need to drop the foreign keys that reference it and rebuild the primary key or its underlying unique index with a fragmentation strategy and then put everything else back. This may be impractical on your system. > datatype_name is the only possible column to be used as fragmenting > key(column), so a global unique index on "inventory_id" can not be > fragmented by informix. > So, my question is, > Is it possible or reasonable to have a fragmented unique index( index > keys do not include fragmentation keys) in future Informix? What version of Informix are you running? I think you're using v10 as you're using the partition syntax. From v9 onwards indices and unique indices can be fragmented in a way which is independent of the data which they point to as data and indices are stored separately. You could fragment inventory_id using a schema like: create unique index "informix".ds_head_ipk on "informix".ds_head (inventory_id) using btree fragment by expression partition no1 (mod (inventory_id, 4) = 0) in part1, partition no2 (mod (inventory_id, 4) = 1) in part2, partition no3 (mod (inventory_id, 4) = 2) in part3, partition no4 (mod (inventory_id, 4) = 3) in part4; Of course you can have as many fragments/partitions as you want by changing the 4 passed to the mod function and adding more partitions. (Mod divides by the number given and gives the remainder.) Ben.
On 7/7/06, Ben Thompson <ben@nomonitorsoftspam.com> wrote: > > Quman wrote: > > > The table is not new and the primary key can not be > > redefined(referenced by other tables already). > > You would need to drop the foreign keys that reference it and rebuild > the primary key or its underlying unique index with a fragmentation > strategy and then put everything else back. This may be impractical on > your system. This is not possible for us now. > datatype_name is the only possible column to be used as fragmenting > > key(column), so a global unique index on "inventory_id" can not be > > fragmented by informix. > > So, my question is, > > Is it possible or reasonable to have a fragmented unique index( index > > keys do not include fragmentation keys) in future Informix? > > What version of Informix are you running? I think you're using v10 as > you're using the partition syntax. From v9 onwards indices and unique > indices can be fragmented in a way which is independent of the data > which they point to as data and indices are stored separately. You could > fragment inventory_id using a schema like: > > create unique index "informix".ds_head_ipk on "informix".ds_head > (inventory_id) using btree fragment by expression > partition no1 (mod (inventory_id, 4) = 0) in part1, > partition no2 (mod (inventory_id, 4) = 1) in part2, > partition no3 (mod (inventory_id, 4) = 2) in part3, > partition no4 (mod (inventory_id, 4) = 3) in part4; > > Of course you can have as many fragments/partitions as you want by > changing the 4 passed to the mod function and adding more partitions. > (Mod divides by the number given and gives the remainder.) Fragmenting a index independantly looks not making much sense to us, since every time you maintain (drop, recreate, ...) some piece of data, the corresponding index must also be maintanined. you can not do the data and its index independantly, right? For example, if I detach or attach a fragment, the corresponding index must also be deatched or attached. I mean if the index is fragmented with its data, the maintenance could be faster, since you need not touch other fragments or index( every thing could be done locally!). Thanks, Frank Ben. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
Quman wrote:
> On 7/7/06, Ben Thompson <ben@nomonitorsoftspam.com> wrote:
> >
> > Quman wrote:
> >
> > > The table is not new and the primary key can not be
> > > redefined(referenced by other tables already).
> >
> > You would need to drop the foreign keys that reference it and rebuild
> > the primary key or its underlying unique index with a fragmentation
> > strategy and then put everything else back. This may be impractical on
> > your system.
>
>
> This is not possible for us now.
> Fragmenting a index independantly looks not making much sense to us, since
> every time you maintain (drop, recreate, ...) some piece of data, the
> corresponding index must also be maintanined. you can not do the data and
> its index independantly, right?
No, this isn't correct. The index isn't attached to the table it is
just fragmented with the same scheme.
create index "informix".ds_head_idx2 on "informix".ds_head
(datatype_name,
inventory_id) using btree ;
To fragment the above index with the same scheme as the table, which
looks like what you are trying to accomplish.
drop index ds_head_idx2 ;
create index ds_head_idx2 on ds_head(datatype_name, inventory_id)
fragment by expression
partition pt1 (datatype_name LIKE 'GVAR%' ) in idx01,
partition pt2 (datatype_name LIKE 'GOES%' ) in idx01,
partition pt3 (datatype_name LIKE 'CW%' ) in idx01,
partition rmd remainder in idx01;
So, when you run your query part of the index will be eliminated from
consideration.
Related threads
- Conversion to differeent characters sets
- Problem in changing locale via dbexport/dbimport
- RE: openlink error "Unable to load locale categories"