fragment elimination on query
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
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
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