Quman wrote:
> 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?
>
Your fragment elimination strategy should work because Informix says so
;-)

http://publib.boulder.ibm.com/infoce...oc/perf265.htm
> Thanks,
>
> Quman

Why would you want fragment elimination on the table when you are using
an Index? I think you are confusing yourself here. If the query uses an
index it doesn't care how the table is fragmented because it is busy
traversing the B-Tree which points to the individual records. If you
want to reduce the work the index does then fragment the index so that
the engine can eliminate fragments from the index. (This was correctly
suggested by someone else in this thread.) If you were scanning the
table then fragment elimination in the table would help you.



>From another page in the manual:

<<BEGIN MANUAL>>
Improving Performance for Individual Queries

If the primary goal of fragmentation is improved performance for
individual queries, try to distribute all the rows of the table evenly
over the different disks. Overall query-completion time is reduced when
the database server does not have to wait for data retrieval from a
table fragment that has more rows than other fragments.
<<< BEGIN IMPORTANT>>
If queries access data by performing sequential scans against
significant portions of tables, fragment the table rows only. Do not
fragment the index. If an index is fragmented and a query has to cross
a fragment boundary to access the data, the performance of the query
can be worse than if you do not fragment.

If queries access data by performing an index read, you can improve
performance by using the same distribution scheme for the index and the
table.
<<<END IMPORTANT>>
If you use round-robin fragmentation, do not fragment your index.
Consider placing that index in a separate dbspace from other table
fragments.
<<END MANUAL>>
My emphasis added.