Re: a little help with query.

Ian Michael Gumby schrieb: > Silly question... What's the data type of the profile_token? > Is it really a Serial column? > > > Date: Mon, 6 Jul 2009 09:32:25 -0500 > Subject: a little help with query. > From: floyd@fwellers.com > To: informix-list@iiug.org > > The below query takes a long time the first time running because it must go to disk. But it takes like 50 seconds !! > the table has 94 million very wide rows. It is fragmented by a char column ( but not the column in the query ). > > Given the below sqexplain output, is there anything obvious that can be done to speed it up ? Does the slowness have to do with the 'not null" clause ? > > Thanks, > Floyd > > =============SQEXPLAIN OUTPUT HERE ========================= > select count (distinct varchar_column ) from table where varchar_column is not null and profile_token = 1234> > > > Estimated Cost: 4104 > Estimated # of Rows Returned: 1 > > 1) owner.table: INDEX PATH > > Filters: owner.table.varchar_column IS NOT NULL > > (1) Index Keys: profile_token varchar_column (Serial, fragments: ALL) > Lower Index Filter: owner.table > .profile_token = 1234 > > ====================END SQEXPLAIN OUTPUT=================== > > _________________________________________________________________ > Windows Live': Keep your life in sync. > http://windowslive.com/explore?ocid=TXT_TAGLM_WL_BR_life_in_synch_062009 Floyd, I sorta cannot see your original posting. 3 questions: Is the index used here a composite index, and in what page size dbspace and what is the size of the index in pages? (oncheck -pT) Is the query plan I can see here complete? No single line missing? I wonder that there is no sort to get unique count only .... Is there an update statistics high for column profile_token? And what type of update statitics is run for column varchar_column and what is the actual varchar definition, especially max size? dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Floyd in addition, Does it make sense to maybe change the fill factor of the index? Again, its been a while but what happens when you have a lot of rows that do not have a really unique index? > Date: Wed, 8 Jul 2009 00:59:34 +0200 > From: richard.kofler@chello.at > Subject: Re: a little help with query. > To: informix-list@iiug.org > > Ian Michael Gumby schrieb: > > Silly question... What's the data type of the profile_token? > > Is it really a Serial column? > > > > > > Date: Mon, 6 Jul 2009 09:32:25 -0500 > > Subject: a little help with query. > > From: floyd@fwellers.com > > To: informix-list@iiug.org > > > > The below query takes a long time the first time running because it must go to disk. But it takes like 50 seconds !! > > the table has 94 million very wide rows. It is fragmented by a char column ( but not the column in the query ). > > > > Given the below sqexplain output, is there anything obvious that can be done to speed it up ? Does the slowness have to do with the 'not null" clause ? > > > > Thanks, > > Floyd > > > > =============SQEXPLAIN OUTPUT HERE ========================= > > select count (distinct varchar_column ) from table where varchar_column is not null and profile_token = 1234> > > > > > > > Estimated Cost: 4104 > > Estimated # of Rows Returned: 1 > > > > 1) owner.table: INDEX PATH > > > > Filters: owner.table.varchar_column IS NOT NULL > > > > (1) Index Keys: profile_token varchar_column (Serial, fragments: ALL) > > Lower Index Filter: owner.table > > .profile_token = 1234 > > > > ====================END SQEXPLAIN OUTPUT=================== > > > > _________________________________________________________________ > > Windows Live™: Keep your life in sync. > > http://windowslive.com/explore?ocid=TXT_TAGLM_WL_BR_life_in_synch_062009 > > Floyd, I sorta cannot see your original posting. > > 3 questions: > > Is the index used here a composite index, and in what page size dbspace > and what is the size of the index in pages? (oncheck -pT) > > Is the query plan I can see here complete? No single line missing? > I wonder that there is no sort to get unique count only .... > > Is there an update statistics high for column profile_token? > And what type of update statitics is run for column varchar_column > and what is the actual varchar definition, especially max size? > > dic_k > > -- > Richard Kofler > SOLID STATE EDV > Dienstleistungen GmbH > Vienna/Austria/Europe > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Insert movie times and more without leaving Hotmail®. http://windowslive.com/Tutorial/Hotmail/QuickAdd?ocid=TXT_TAGLM_WL_HM_Tutorial_QuickAdd_062009

Richard Kofler wrote: > Ian Michael Gumby schrieb: >> Silly question... What's the data type of the profile_token? Is it >> really a Serial column? >> >> >> Date: Mon, 6 Jul 2009 09:32:25 -0500 >> Subject: a little help with query. >> From: floyd@fwellers.com >> To: informix-list@iiug.org >> >> The below query takes a long time the first time running because it >> must go to disk. But it takes like 50 seconds !! >> the table has 94 million very wide rows. It is fragmented by a char >> column ( but not the column in the query ). >> >> Given the below sqexplain output, is there anything obvious that can >> be done to speed it up ? Does the slowness have to do with the 'not >> null" clause ? >> >> Thanks, >> Floyd >> >> =============SQEXPLAIN OUTPUT HERE ========================= >> select count (distinct varchar_column ) from table where>> varchar_column is not null and profile_token = 1234 >> >> >> >> Estimated Cost: 4104 >> Estimated # of Rows Returned: 1 >> >> 1) owner.table: INDEX PATH >> >> Filters: owner.table.varchar_column IS NOT NULL >> >> (1) Index Keys: profile_token varchar_column (Serial, fragments: >> ALL) >> Lower Index Filter: owner.table >> .profile_token = 1234 >> >> ====================END SQEXPLAIN OUTPUT=================== >> >> _________________________________________________________________ >> Windows Live': Keep your life in sync. >> http://windowslive.com/explore?ocid=TXT_TAGLM_WL_BR_life_in_synch_062009 > > Floyd, I sorta cannot see your original posting. > > 3 questions: > > Is the index used here a composite index, and in what page size dbspace > and what is the size of the index in pages? (oncheck -pT) > > Is the query plan I can see here complete? No single line missing? > I wonder that there is no sort to get unique count only .... You don't need to sort since the access and every data needed is in the index (already sorted). Regards.

On Jul 8, 4:15 pm, Fernando Nunes <domusonl...@gmail.com> wrote: s no sort to get unique count only .... > > You don't need to sort since the access and every data needed is in the index > (already sorted). > > Regards. Are you sure? What happens when you have an index where the key is not unique and you have a lot of rows with the same key value but you want to sort on a secondary element? Sure the key value will be sorted, but the secondary value? Not so much, so he may need the sort to guarantee order. Look at it this way you have the tupples : {(4,'a'),(5,'a'),(5.'x'), (5,'b'), (3,'c')} If the index is on column 1, then if you select on column 1 you'll get the tupples in the order of 3,4,5 however how will you see the tupples with a key value of 5? Will you see them in the insert order of a,x,b? So if Floyd wanted to see them in a,b,x he'll have to sort on them in ascending value.

grendal wrote: > On Jul 8, 4:15 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > s no sort to get unique count only .... >> You don't need to sort since the access and every data needed is in the index >> (already sorted). >> >> Regards. > > Are you sure? > > What happens when you have an index where the key is not unique and > you have a lot of rows with the same key value but you want to sort on > a secondary element? > Sure the key value will be sorted, but the secondary value? Not so > much, so he may need the sort to guarantee order. > > Look at it this way you have the tupples : {(4,'a'),(5,'a'),(5.'x'), > (5,'b'), (3,'c')} > > If the index is on column 1, then if you select on column 1 you'll get > the tupples in the order of 3,4,5 however how will you see the tupples > with a key value of 5? Will you see them in the insert order of > a,x,b? > > So if Floyd wanted to see them in a,b,x he'll have to sort on them in > ascending value. I wasn't making a generic statement. I was answering to the specific situation presented by the OP. As for the example you gave, the columns not leading the index are also sorted, but of course, they are sorted "inside" each value in the prior columns. In the OP situation, the leading column was profile_token and the second column was varchar_column (strange name for a column). If you look at the query he is looking for the number of distinct values of this column excluding NULLs. So after locating the first "1234" it just has to scan the index, checking for each different value in the second column. Since it has a condition "varchar_column" not null it could even work in Oracle :) The SQEXPLAIN output shows no evidence of "temporary file needed for sort", so I assume it does as explained abode. Other versions/conditions could have different behaviors. If there was no condition on the leading column it could still use the index but it would depend very much on the statistics and size of the table. Later (> 10.00.FC5?) versions would choose this way more often because of the introduction of the INDEX SELF JOIN feature. Regards.

Hi, When you put the filter on varchar_column column the engine works on data pages to process your count, because IS NOT NULL or != filter is not applied to index, for test purposes try to put varchar_column=some value, the access plan will be "Key Only" Without the filter probably your access plan is "Key Only", in this access plan your count(distinct .. ) works only on the index pages. Because this your query is faster without the filter, to ceck my teory verify your access plan without the filter. Regards Vagner