Indexes on a Fragmented Table

Hi, If a table is fragmented by expression, should all of its indexes be fragmented (regardless of whether they're unique or non-unique)? At one time, I was told to keep any unique indexes non-fragmented in a separate dbspace. Platform: IDS 9.40.FC3 / AIX 5.1 Thank you.

I've create in the past a fragmented index by express, but the performance is strongly bad, the best way is create the table fragmented but the indexes not fragmented in a separated dbspaces. Celso Coimbra -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de Demeis, Tony Enviada em: sexta-feira, 12 de maio de 2006 11:54 Para: ids@iiug.org Assunto: Indexes on a Fragmented Table [6704] Hi, If a table is fragmented by expression, should all of its indexes be fragmented (regardless of whether they're unique or non-unique)? At one time, I was told to keep any unique indexes non-fragmented in a separate dbspace. Platform: IDS 9.40.FC3 / AIX 5.1 Thank you. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Demeis, Tony said: > > Hi, > > If a table is fragmented by expression, should all of its indexes be > fragmented (regardless of whether they're unique or non-unique)? > > At one time, I was told to keep any unique indexes non-fragmented in a > separate dbspace. > > Platform: IDS 9.40.FC3 / AIX 5.1 This is not true, you can fragment the index the same as the table, different from the table or not at all. It all depends... -- Bye now, Obnoxio Information within this post contains forward looking statements within the meaning of Section 27A of the Securities Act of 1933 and Section 21B of the S E C Act of 1934. Statements that involve discussions with respect to projections of future events are not statements of historical fact and may be forward looking statements. Don't rely on them to make a decision. The poster is not a reporting company registered under the Exchange Act of 1934. I have received a life peerage from Her Majesty, who is not an officer, minister or affiliate Labour party member. I intend to recover my loan now, which could cause the parliamentary majority to go down, resulting in losses for you. Today's Labour party has: an accumulated deficit and a reliance on loans from officers and affiliates to pay expenses. It is not an operating political party. The party is going to need financing to continue as a going concern. A failure to finance could cause the party to go out of business. This report shall not be construed as any kind of investment advice or solicitation. You can lose all your money by investing in this party.

You really raised an excellent question! Actually we desperately hope a index could be partitioned AUTOMATICALLY by Informix if we would declare the index being LOCAL(, and also we do NOT want the index columns must be a subset of fragmenting columns). I saw the local index from Oracle. Basically, a local index is associated with a partition(fragment) and is maintained by DBMS. whenever you add or drop a fragment, the associated local index is added or dropped FAST, automatically and independently (NO performance suffering and NO impact to other fragments or indexes if there is no global index associated with the table)! How NICE the feature would be! I did not see this feature in Informix. Thanks, Frank Demeis, Tony wrote: >Hi, > >If a table is fragmented by expression, should all of its indexes be >fragmented (regardless of whether they're unique or non-unique)? > >At one time, I was told to keep any unique indexes non-fragmented in a >separate dbspace. > >Platform: IDS 9.40.FC3 / AIX 5.1 > >Thank you. > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696

I have a large table with 2 indexes, a unique index and a non-unique index. Due to performance reasons, we fragment the non-unique index by expression into 14 different dbspaces. This fragmentation *forces* the optimizer to take a much better path allowing performance to be much better. We have recently fragmented the data by expression into different dbspaces as well and the performance is even better. All of the additional dbspaces are a bit of a pain but I'll take the management of that over the benefits of fragment elimination any day. I recommend you play with different table and index fragmentation schemes over as many dbspaces as can. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Celso Cabra.... Sent: Friday, May 12, 2006 10:24 AM To: ids@iiug.org Subject: RES: Indexes on a Fragmented Table [6705] I've create in the past a fragmented index by express, but the performance is strongly bad, the best way is create the table fragmented but the indexes not fragmented in a separated dbspaces. Celso Coimbra -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de Demeis, Tony Enviada em: sexta-feira, 12 de maio de 2006 11:54 Para: ids@iiug.org Assunto: Indexes on a Fragmented Table [6704] Hi, If a table is fragmented by expression, should all of its indexes be fragmented (regardless of whether they're unique or non-unique)? At one time, I was told to keep any unique indexes non-fragmented in a separate dbspace. Platform: IDS 9.40.FC3 / AIX 5.1 Thank you. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Actually, the default behavior of indexes on fragmented tables in Informix is completely equivalent to the behavior of local indexes on fragmented tables in Oracle: each fragment of index, created without 'IN dbspaceXXX', is created in the same dbspace, as table fragment. 'Detach fragment' in Informix executes momentarily when indexes are LOCAL (fragmented same way, as tables). The are still some disadvantages of using local indexes (or any type of fragmented index) over global non-fragmented index. Consider the following: CREATE TABLE transactions (tx_id INT, tx_date DATETIME YEAR TO SECOND, ...) FRAGMENT BY EXPRESSIONtx_date >= '2005-01-01 00:00:00' and tx_date < '2005-02-01 00:00:00' in dbs1, tx_date >= '2005-02-01 00:00:00' and tx_date < '2005-03-01 00:00:00' in dbs2; CREATE INDEX tx_idx1 on transactions(tx_date); This DDL creates local fragmented index, co-allocated with table data fragments. The biggest problem with that index is about running queries like: SELECT first 100 * FROM transactions WHERE tx_date >= '2005-01-29 00:00:00' and tx_date < '2005-02-0200:00:00' ORDER BY tx_date (Note, that date range spans across two table fragments) Informix (including Informix 10) is unable to use index on TX_DATE to do the sort!!!!!!!!! It creates a temporary table (may be, few million rows), sorts it, and then returns first 100 rows of the sorted result set. Optimizer directives (like --+FIRST_ROWS) do not help. If date range for SELECT goes to a single fragment, then Informix is able to use index fragment to do the sort the data for ORDER BY, and doesn't create the TEMP table. That is, the major benefit of global (non-fragmented) index is that it works much better for SELECT with ORDER BY The benefits of fragmented index are faster inserts, and dramatically faster 'ALTER FRAGMENT ON tab_name DETACH frag_name' -Alexey > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Yunyao > (Fra.... > Sent: Friday, May 12, 2006 11:45 AM > To: ids@iiug.org > Subject: Re: Indexes on a Fragmented Table [6708] > > > You really raised an excellent question! > > Actually we desperately hope a index could be partitioned AUTOMATICALLY > by Informix if we would declare the index being LOCAL(, and also we do > NOT want the index columns must be a subset of fragmenting columns). > > I saw the local index from Oracle. Basically, a local index is > associated with a partition(fragment) and is maintained by DBMS. > whenever you add or drop a fragment, the associated local index is > added or dropped FAST, automatically and independently (NO performance > suffering and NO impact to other fragments or indexes if there is no > global index associated with the table)! How NICE the feature would be! > > I did not see this feature in Informix. > > Thanks, > Frank > > Demeis, Tony wrote: > > >Hi, > > > >If a table is fragmented by expression, should all of its indexes be > >fragmented (regardless of whether they're unique or non-unique)? > > > >At one time, I was told to keep any unique indexes non-fragmented in a > >separate dbspace. > > > >Platform: IDS 9.40.FC3 / AIX 5.1 > > > >Thank you. > > > > > > >*********************************************************************** ****** > ** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > Yunyao "Frank" Qu > Computer Sciences Corporation(CSC) > NOAA/CLASS, (301)817-4696 > > > ************************************************************************ ****** > * > Forum Note: Use "Reply" to post a response in the discussion forum. >

The performance issue with a unique index is that if the index is fragmented, then it really should be fragmented by an expression and th= e fragment expression rules should include data which is within the index= . Unless it has recently changed, the index fragments follow the data fragments, which is terrible for unique indexes. Why -- Well if the un= ique index is following the data fragments, then each of the index fragments= must be examined to determine if a newly added column is in fact unique= . That can mean that if there are say 12 data fragments which are fragmen= ted by month, then unique indexes would require that each of the default 12= index fragment must be examined to determine if the index is unique. T= his can cause significant degradation. = "Obnoxio The...." = <obnoxio@serendip = ita.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: Indexes on a Fragmented Tabl= e 05/12/2006 11:33 [6707] = AM = = = Please respond to = ids@iiug.org = = = Demeis, Tony said: > > Hi, > > If a table is fragmented by expression, should all of its indexes be > fragmented (regardless of whether they're unique or non-unique)? > > At one time, I was told to keep any unique indexes non-fragmented in = a > separate dbspace. > > Platform: IDS 9.40.FC3 / AIX 5.1 This is not true, you can fragment the index the same as the table, different from the table or not at all. It all depends... -- Bye now, Obnoxio Information within this post contains forward looking statements within= the meaning of Section 27A of the Securities Act of 1933 and Section 21= B of the S E C Act of 1934. Statements that involve discussions with resp= ect to projections of future events are not statements of historical fact a= nd may be forward looking statements. Don't rely on them to make a decisio= n. The poster is not a reporting company registered under the Exchange Act= of 1934. I have received a life peerage from Her Majesty, who is not an officer,= minister or affiliate Labour party member. I intend to recover my loan now, which could cause the parliamentary majority to go down, resulting in losses for you. Today's Labour party has: an accumulated deficit and a reliance on loans from officers and affiliates to pay expenses. It is not an operating political party. The= party is going to need financing to continue as a going concern. A fail= ure to finance could cause the party to go out of business. This report sha= ll not be construed as any kind of investment advice or solicitation. You = can lose all your money by investing in this party. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

That is the default behavior of IDS fragmentation. See my other reply about the impact of unique indexes. Yes - we might want to use the default behavior in which each of the in= dex fragments follow the data fragments. It does make it quicker to attach= and detach a fragment. However, it also means that more work must be done = with normal DML operations if the table has a unique index because each of t= he fragments must be examined. = "Yunyao (Fra...." = <Yunyao.Qu@noaa.g = ov> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: Indexes on a Fragmented Tabl= e 05/12/2006 11:44 [6708] = AM = = = Please respond to = ids@iiug.org = = = You really raised an excellent question! Actually we desperately hope a index could be partitioned AUTOMATICALLY= by Informix if we would declare the index being LOCAL(, and also we do NOT want the index columns must be a subset of fragmenting columns). I saw the local index from Oracle. Basically, a local index is associated with a partition(fragment) and is maintained by DBMS. whenever you add or drop a fragment, the associated local index is added or dropped FAST, automatically and independently (NO performance suffering and NO impact to other fragments or indexes if there is no global index associated with the table)! How NICE the feature would be!= I did not see this feature in Informix. Thanks, Frank Demeis, Tony wrote: >Hi, > >If a table is fragmented by expression, should all of its indexes be >fragmented (regardless of whether they're unique or non-unique)? > >At one time, I was told to keep any unique indexes non-fragmented in a= >separate dbspace. > >Platform: IDS 9.40.FC3 / AIX 5.1 > >Thank you. > > >**********************************************************************= ********* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696 ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

<SNIP> > (Note, that date range spans across two table fragments) > Informix (including Informix 10) is unable to use index on TX_DATE > to do the sort!!!!!!!!! It creates a temporary table (may be, few This statement is misleading and incorrect. Alexey we discussed this in Tampa so I assume you replied to this before we spoke. It's not that IDS CANNOT use the index across fragments and so sorts the data, it's that IDS WILL NOT normally use a merge of sorted sinks across fragments and sort instead simply because in all of their testing it was FASTER! If you need the initial rows of data back sooner and don't care that the total query time will likely be a bit longer, then you can use the FIRST_ROWS optimization hint for such queries. With FIRST ROWS optimization set the optimizer WILL use a merge of the sorted data using the indexes on the separate fragments. This will ONLY be true if the index is fragmented on the same expression as the table and the fragmentation expression is the same as the ORDER BY clause. If not, then yes sorting will indeed occur because then the data coming from the fragments in NOT already sorted. > million rows), sorts it, and then returns first 100 rows > of the sorted result set. > Optimizer directives (like --+FIRST_ROWS) do not help. > If date range for SELECT goes to a single fragment, then Informix > is able to use index fragment to do the sort the data for ORDER BY, > and doesn't create the TEMP table. <SNIP> Art S. Kagel

HI, Art, I am curious about your statementt: ".....This will ONLY be true if the index is fragmented on the same expression as the table and the fragmentation expression is the same as the ORDER BY clause. If not, then yes sorting will indeed occur because then the data coming from the fragments in NOT already sorted. " Suppose I have a table, CREATE order ( order_date date, product char(80), price int, ....) FRAGMENT BY EXPRESSION order_date >= '2005-01-01 00:00:00' in dbs1, order_date < '2005-01-01 00:00:00' in dbs2; CREATE INDEX order_idx1 on order(product); Then SQL statements, SELECT FIRST 100 * from order where product like "TV100%" order by price IDS should (will?) search TWO first 100 rows from two fragments ( through local indexes) separately, then merge it, instead of sorting too many (with millions) rows. Thanks, Frank ART KAGEL, .... wrote: ><SNIP> > > >>(Note, that date range spans across two table fragments) >> >> > > > >>Informix (including Informix 10) is unable to use index on TX_DATE >>to do the sort!!!!!!!!! It creates a temporary table (may be, few >> >> > >This statement is misleading and incorrect. Alexey we discussed this in Tampa >so I assume you replied to this before we spoke. It's not that IDS CANNOT use >the index across fragments and so sorts the data, it's that IDS WILL NOT >normally use a merge of sorted sinks across fragments and sort instead simply >because in all of their testing it was FASTER! If you need the initial rows of >data back sooner and don't care that the total query time will likely be a bit >longer, then you can use the FIRST_ROWS optimization hint for such queries. >With FIRST ROWS optimization set the optimizer WILL use a merge of the sorted >data using the indexes on the separate fragments. This will ONLY be true if >the >index is fragmented on the same expression as the table and the fragmentation >expression is the same as the ORDER BY clause. If not, then yes sorting will >indeed occur because then the data coming from the fragments in NOT already >sorted. > > > >>million rows), sorts it, and then returns first 100 rows >>of the sorted result set. >>Optimizer directives (like --+FIRST_ROWS) do not help. >> >> > > > >>If date range for SELECT goes to a single fragment, then Informix >>is able to use index fragment to do the sort the data for ORDER BY, >>and doesn't create the TEMP table. >> >> ><SNIP> > >Art S. Kagel > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696

Actually, even if it sorts to merge the data from the two dbspaces, it will only be sorting the 100 rows that match the filter criteria not the millions of unfiltered rows in the fragments. Now, in your specific example, you are ordering on 'price' which is neither the FRAGMENT BY column (order_date) nor the indexed column in the index you presented (product). In this case the engine will have no choice but to sort the 100 or so matching data rows regardless of the optimization strategy. This is one of the examples I had in mind when I said that the optimizer may not be able to perform a simple merge in all cases. Art S. Kagel ----- Original Message ----- From: Yunyao (Fra.... <ids@iiug.org> At: 5/15 10:36:09 HI, Art, I am curious about your statementt: ".....This will ONLY be true if the index is fragmented on the same expression as the table and the fragmentation expression is the same as the ORDER BY clause. If not, then yes sorting will indeed occur because then the data coming from the fragments in NOT already sorted. " Suppose I have a table, CREATE order ( order_date date, product char(80), price int, ....) FRAGMENT BY EXPRESSION order_date >= '2005-01-01 00:00:00' in dbs1, order_date < '2005-01-01 00:00:00' in dbs2; CREATE INDEX order_idx1 on order(product); Then SQL statements, SELECT FIRST 100 * from order where product like "TV100%" order by price IDS should (will?) search TWO first 100 rows from two fragments ( through local indexes) separately, then merge it, instead of sorting too many (with millions) rows. Thanks, Frank ART KAGEL, .... wrote: ><SNIP> > > >>(Note, that date range spans across two table fragments) >> >> > > > >>Informix (including Informix 10) is unable to use index on TX_DATE >>to do the sort!!!!!!!!! It creates a temporary table (may be, few >> >> > >This statement is misleading and incorrect. Alexey we discussed this in Tampa >so I assume you replied to this before we spoke. It's not that IDS CANNOT use >the index across fragments and so sorts the data, it's that IDS WILL NOT >normally use a merge of sorted sinks across fragments and sort instead simply >because in all of their testing it was FASTER! If you need the initial rows of >data back sooner and don't care that the total query time will likely be a bit >longer, then you can use the FIRST_ROWS optimization hint for such queries. >With FIRST ROWS optimization set the optimizer WILL use a merge of the sorted >data using the indexes on the separate fragments. This will ONLY be true if >the >index is fragmented on the same expression as the table and the fragmentation >expression is the same as the ORDER BY clause. If not, then yes sorting will >indeed occur because then the data coming from the fragments in NOT already >sorted. > > > >>million rows), sorts it, and then returns first 100 rows >>of the sorted result set. >>Optimizer directives (like --+FIRST_ROWS) do not help. >> >> > > > >>If date range for SELECT goes to a single fragment, then Informix >>is able to use index fragment to do the sort the data for ORDER BY, >>and doesn't create the TEMP table. >> >> ><SNIP> > >Art S. Kagel > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- Yunyao "Frank" Qu Computer Sciences Corporation(CSC) NOAA/CLASS, (301)817-4696 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Art, Thanks a lot for your comment. I tested it once again, and it worked for me now. In the past, we did some testing with '--FIRST_ROWS', and it didn't work. The difference is that, previously, we passed data range dynamically, and the SQL statement was prepared with placeholders. No, I was passing all the parameters statically. The conclusion is that index sorting does work for fragmented indexes, but special considerations must be done: - query parameters, related to fragmentation column, must be passed statically; - --+FIRST_ROWS optimization should be used. I think, it was useful discussion anyway. -Alexey > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART > KAGEL, .... > Sent: Monday, May 15, 2006 9:06 AM > To: ids@iiug.org > Subject: RE: Indexes on a Fragmented Table [6718] > > > <SNIP> > > (Note, that date range spans across two table fragments) > > > Informix (including Informix 10) is unable to use index on TX_DATE > > to do the sort!!!!!!!!! It creates a temporary table (may be, few > > This statement is misleading and incorrect. Alexey we discussed this in Tampa > so I assume you replied to this before we spoke. It's not that IDS CANNOT use > the index across fragments and so sorts the data, it's that IDS WILL NOT > normally use a merge of sorted sinks across fragments and sort instead simply > because in all of their testing it was FASTER! If you need the initial rows of > data back sooner and don't care that the total query time will likely be a bit > longer, then you can use the FIRST_ROWS optimization hint for such queries. > With FIRST ROWS optimization set the optimizer WILL use a merge of the sorted > data using the indexes on the separate fragments. This will ONLY be true if > the > index is fragmented on the same expression as the table and the fragmentation > expression is the same as the ORDER BY clause. If not, then yes sorting will > indeed occur because then the data coming from the fragments in NOT already > sorted. > > > million rows), sorts it, and then returns first 100 rows > > of the sorted result set. > > Optimizer directives (like --+FIRST_ROWS) do not help. > > > If date range for SELECT goes to a single fragment, then Informix > > is able to use index fragment to do the sort the data for ORDER BY, > > and doesn't create the TEMP table. > <SNIP> > > Art S. Kagel > > > ************************************************************************ ****** > * > Forum Note: Use "Reply" to post a response in the discussion forum. >

Alexey: Likely the optimization is being done for parameterized values also, but that level of optimization is being delayed until the parameter values are known at OPEN time, so it does not show up in any EXPLAIN plan output. I worked with the R&D team several years ago to delay final optimizations like fragment elimination until OPEN time when the parameters were known. I just don't remember if that extended to the merge algorithm selection or not. One possibility is that while the parameter values are not know the optimizer may select an index that returns the data in an order other than the ORDER BY order, but that would show up in the explain plan output. Contact tech support to verify that. Art ----- Original Message ----- From: Alexey Sonkin <ids@iiug.org> At: 5/15 17:30:04 Art, Thanks a lot for your comment. I tested it once again, and it worked for me now. In the past, we did some testing with '--FIRST_ROWS', and it didn't work. The difference is that, previously, we passed data range dynamically, and the SQL statement was prepared with placeholders. No, I was passing all the parameters statically. The conclusion is that index sorting does work for fragmented indexes, but special considerations must be done: - query parameters, related to fragmentation column, must be passed statically; - --+FIRST_ROWS optimization should be used. I think, it was useful discussion anyway. -Alexey > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART > KAGEL, .... > Sent: Monday, May 15, 2006 9:06 AM > To: ids@iiug.org > Subject: RE: Indexes on a Fragmented Table [6718] > > > <SNIP> > > (Note, that date range spans across two table fragments) > > > Informix (including Informix 10) is unable to use index on TX_DATE > > to do the sort!!!!!!!!! It creates a temporary table (may be, few > > This statement is misleading and incorrect. Alexey we discussed this in Tampa > so I assume you replied to this before we spoke. It's not that IDS CANNOT use > the index across fragments and so sorts the data, it's that IDS WILL NOT > normally use a merge of sorted sinks across fragments and sort instead simply > because in all of their testing it was FASTER! If you need the initial rows of > data back sooner and don't care that the total query time will likely be a bit > longer, then you can use the FIRST_ROWS optimization hint for such queries. > With FIRST ROWS optimization set the optimizer WILL use a merge of the sorted > data using the indexes on the separate fragments. This will ONLY be true if > the > index is fragmented on the same expression as the table and the fragmentation > expression is the same as the ORDER BY clause. If not, then yes sorting will > indeed occur because then the data coming from the fragments in NOT already > sorted. > > > million rows), sorts it, and then returns first 100 rows > > of the sorted result set. > > Optimizer directives (like --+FIRST_ROWS) do not help. > > > If date range for SELECT goes to a single fragment, then Informix > > is able to use index fragment to do the sort the data for ORDER BY, > > and doesn't create the TEMP table. > <SNIP> > > Art S. Kagel > > > ************************************************************************ ****** > * > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.