fragment elimination on query
This is a discussion on fragment elimination on query within the Informix forums, part of the Database Server Software category; --> I have a table, create table "informix".ds_head ( inventory_id serial not null , dataset_name varchar(255,44) not null , dataset_size_bytes ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-20-2008, 12:30 PM
| ||||
| ||||
 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      |
|
04-20-2008, 12:30 PM
| |||
| |||
| 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 |
|
04-20-2008, 12:30 PM
| |||
| |||
| 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 |
|
04-20-2008, 12:30 PM
| |||
| |||
| 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> &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-- |
|
04-20-2008, 12:30 PM
| |||
| |||
| Re: fragment elimination on query Art S. Kagel wrote: > For fragment elimination to take place you have to run with PDQPRIORITY > >= 1 Are you sure this is correct Art? Here is a query I just ran to check with PDQPRIORITY set to zero: QUERY: ------ select * from customer where id=3 Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.customer: INDEX PATH (1) Index Keys: serialno (Serial, fragments: 3) Lower Index Filter: informix.customer.id = 3 Ben. |
|
04-20-2008, 12:31 PM
| |||
| |||
| Re: fragment elimination on query 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. |
|
04-20-2008, 12:31 PM
| |||
| |||
| Re: fragment elimination on query Ben Thompson wrote: > Art S. Kagel wrote: > >> For fragment elimination to take place you have to run with >> PDQPRIORITY >= 1 > > > Are you sure this is correct Art? Here is a query I just ran to check > with PDQPRIORITY set to zero: Apparently not as sure as I once was. John Miller agrees with you, and obviously it worked below. IB I was thinking of parallel fragment scan which is invoked when PDQPRIORITY is set to 1 or LOW or greater. <SIGH!> Only 51 and my mind is already going! Very sad... ;-{ Art S. Kagel > QUERY: > ------ > select * from customer where id=3 > > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > > 1) informix.customer: INDEX PATH > > (1) Index Keys: serialno (Serial, fragments: 3) > Lower Index Filter: informix.customer.id = 3 > > Ben. |
|
04-20-2008, 12:31 PM
| |||
| |||
| Re: 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 > |
|
04-20-2008, 12:31 PM
| |||
| |||
| Fwd: fragment elimination on query ---------- Forwarded message ---------- From: Quman <yquman@gmail.com> Date: Jul 7, 2006 11:55 AM Subject: Re: fragment elimination on query To: Superboer <superboer7@t-online.de> Cc: informix-list@iiug.org 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 > |
|
04-20-2008, 12:31 PM
| ||||
| ||||
| Re: fragment elimination on query 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. |
Linear Mode
