Unix Technical Forum - View Single Post - fragment elimination on query

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>  &nb sp;
> (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>  &nb sp; (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
>

Thread: fragment elimination on query View Single Post

Thread: fragment elimination on query
View Single Post