Online index usage in one table query

How Online decides which index it uses in ONE table queries? Should it use the the most unique index first? It seems not to use the best available index vr_varaus in following example. Manual measurements and response times also points out that the query is quite/very inefficient. Environment HP-UX B.10.20 U Informix Dynamic Server Version 7.30.UC6 Table Name vrivi Row Size 244 Number of Rows 696873 Number of Columns 41 vrivi ( vr_serial serial not null, vr_varaus integer, vr_alkupvm date, vr_loppupvm date, vr_nro char(7), vr_firma char(5), vr_orgnro char(7), .. ); create index vr_varaus on vrivi (vr_varaus); create index vr_serial on vrivi (vr_serial); create index vr_alkupvm on vrivi (vr_alkupvm); create index vr_haku on vrivi (vr_nro,vr_firma); create index vr_haku1 on vrivi (vr_firma,vr_nro); create index vr_haku2 on vrivi (vr_firma,vr_orgnro, vr_alkupvm); Index uniqueness select tabname,idxname,nrows/nunique duplicates from systables,sysindexes where systables.tabid=sysindexes.tabid and systables.tabid > 99 and nunique > 0 order by duplicates DESC tabname idxname duplicates -- vrivi vr_alkupvm 1878.43636363636 vrivi vr_haku1 790.668367346939 vrivi vr_haku2 790.668367346939 vrivi vr_haku 427.211578221916 vrivi vr_varaus 3.64772827577279 vrivi vr_serial 1.00000000000000 -- SQL-query set explain on select count(*) from vrivi where vr_varaus = 1077295 andvr_firma = "NYVI" and vr_nro = "BQ" and vr_alkupvm = "04.07.99" and vr_kello = "23:30" and vr_kirjstat = "-" set explain off sqexplain.out QUERY: ------ select count ( *) from vrivi where vr_varaus = 1077295 and vr_firma = "NYVI" and vr_nro = "BQ" and vr_alkupvm = "04.07.99" and vr_kello = "23:30" and vr_kirjstat = "-" Estimated Cost: 3 Estimated # of Rows Returned: 1 1) vrivi: INDEX PATH Filters: (vrivi.vr_varaus = 1077295 AND (vrivi.vr_alkupvm = 04.07.99 AND (vrivi.vr_kello = '23:30' AND vrivi.vr_kirjstat = '-' ) ) ) (1) Index Keys: vr_firma vr_nro Lower Index Filter: (vrivi.vr_firma = 'NYVI' AND vrivi.vr_nro = 'BQ' )

In article <7lstec$on0$1@tron.sci.fi>, "Pentti Tiet'v'inen" <pat@sci.fi> wrote: > How Online decides which index it uses in ONE table queries? > Should it use the the most unique index first? The query path is selected according to the optimiser statistics. So, probably, all you need to do is to UPDATE STATISTICS, or UPDATE STATISTICS HIGH . Play with dustributions - this has great impact on the query path. You are using 7.30 - so, you can point optimiser to the desired index with directives. > It seems not to use the best available index vr_varaus in following example. > Manual measurements and response times also points out that the query is > quite/very inefficient. > > Environment > HP-UX B.10.20 U > Informix Dynamic Server Version 7.30.UC6 > > Table Name vrivi > Row Size 244 > Number of Rows 696873 > Number of Columns 41 > > vrivi > ( > vr_serial serial not null, > vr_varaus integer, > vr_alkupvm date, > vr_loppupvm date, > vr_nro char(7), > vr_firma char(5), > vr_orgnro char(7), > .. > ); > create index vr_varaus on vrivi (vr_varaus); > create index vr_serial on vrivi (vr_serial); > create index vr_alkupvm on vrivi (vr_alkupvm); > create index vr_haku on vrivi (vr_nro,vr_firma); > create index vr_haku1 on vrivi (vr_firma,vr_nro); > create index vr_haku2 on vrivi (vr_firma,vr_orgnro, vr_alkupvm);> > Index uniqueness > select tabname,idxname,nrows/nunique duplicates > from systables,sysindexes > where systables.tabid=sysindexes.tabid > and systables.tabid > 99 > and nunique > 0 > order by duplicates DESC> > tabname idxname duplicates > -- > vrivi vr_alkupvm 1878.43636363636 > vrivi vr_haku1 790.668367346939 > vrivi vr_haku2 790.668367346939 > vrivi vr_haku 427.211578221916 > vrivi vr_varaus 3.64772827577279 > vrivi vr_serial 1.00000000000000 > -- > > SQL-query > > set explain on > select count(*) from vrivi > where vr_varaus = 1077295 and> vr_firma = "NYVI" and > vr_nro = "BQ" and > vr_alkupvm = "04.07.99" and > vr_kello = "23:30" and > vr_kirjstat = "-" > set explain off> > sqexplain.out > > QUERY: > ------ > select count ( *) from vrivi > where vr_varaus = 1077295 and > vr_firma = "NYVI" and > vr_nro = "BQ" and > vr_alkupvm = "04.07.99" and > vr_kello = "23:30" and > vr_kirjstat = "-"> > Estimated Cost: 3 > Estimated # of Rows Returned: 1 > > 1) vrivi: INDEX PATH > > Filters: (vrivi.vr_varaus = 1077295 AND > (vrivi.vr_alkupvm = 04.07.99 AND > (vrivi.vr_kello = '23:30' AND vrivi.vr_kirjstat = '-' ) ) ) > > (1) Index Keys: vr_firma vr_nro > Lower Index Filter: (vrivi.vr_firma = 'NYVI' AND vrivi.vr_nro = 'BQ' ) > > -- With best regards, Yuri Dovgart, SAP R/3, Informix consultant, "Telecominvest" company. E-mail y_dovgart@tci.ukrtel.net Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

In article <7lvqid$o8r$1@tron.sci.fi>, "Pentti Tiet'v'inen" <pat@sci.fi> wrote: > Thank's Yuri for your answer! > > I have run "UPDATE STATISTICS" to the database wo. You need "UPDATE STATISTICS MEDIUM" or "UPDATE STATISTICS HIGH". I've > read somewhere from possibility to tell which index online should > use...but.. we are 5-6 people doing 4gl-programs, reports and SQL- queries > and I think it's a bit extra job to learn Online how to use indexes. Sorry, I've thought I'm writing to the DBA. To force optimizer use desired index try this SELECT --+ INDEX(table [desired_index_1,desired_index_2,...]) a,b,c FROM table WHERE ... To avoid optimiser using some indexe(s) use AVOID_INDEX. You'll see your plans with SET EXPLAIN ON. By the way, your query to determine selectivity of indexes is correct ONLY FOR SINGLE COLUMN indexes. Nunique is number of unique keys in the FIRST COLUMN of an index. So, as far vr_haku is composite index, probably it's selectivity is higher then vr_varaus index, and it's better to use vr_haku index. -- With best regards, Yuri Dovgart, SAP R/3, Informix consultant, "Telecominvest" company. E-mail y_dovgart@tci.ukrtel.net Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Thank's Yuri for your answer! I have run "UPDATE STATISTICS" to the database wo. luck. The key I'm interested (vr_varaus) is quite evenly distibuted (so far as I know) because it's increasing number serie. Does distribution help this situation? I've read somewhere from possibility to tell which index online should use...but.. we are 5-6 people doing 4gl-programs, reports and SQL-queries and I think it's a bit extra job to learn Online how to use indexes. We have also quite evolving databases so I'd rather trust the Online-engine. I'm also very curious to know why this simple query acts like that!!!!!!! Yuri Dovgart wrote in message <7lv0ff$6ec$1@nnrp1.deja.com>... >In article <7lstec$on0$1@tron.sci.fi>, > "Pentti Tiet'v'inen" <pat@sci.fi> wrote: >> How Online decides which index it uses in ONE table queries? >> Should it use the the most unique index first? > >The query path is selected according to the optimiser statistics. So, >probably, all you need to do is to UPDATE STATISTICS, or UPDATE >STATISTICS HIGH . Play with dustributions - this has great impact on >the query path. You are using 7.30 - so, you can point optimiser to the >desired index with directives. > >With best regards, Yuri Dovgart, >SAP R/3, Informix consultant, >"Telecominvest" company. >E-mail y_dovgart@tci.ukrtel.net > > >Sent via Deja.com http://www.deja.com/ >Share what you know. Learn what you don't.

>You need "UPDATE STATISTICS MEDIUM" or "UPDATE STATISTICS HIGH". OK >SELECT --+ INDEX(table [desired_index_1,desired_index_2,...]) > a,b,c FROM table >WHERE ... > >To avoid optimiser using some indexe(s) use AVOID_INDEX. You'll see >your plans with SET EXPLAIN ON. Seems to work from dbaccess, I didn't get it work from 4gl (my manuals are in other town..maybe I must take a trip..) >By the way, your query to determine selectivity of indexes is correct >ONLY FOR SINGLE COLUMN indexes. Nunique is number of unique keys in the >FIRST COLUMN of an index. So, as far vr_haku is composite index, >probably it's selectivity is higher then vr_varaus index, and it's >better to use vr_haku index. I know for sure that vr_varaus has ca. 10 duplicates, when vr_haku has hundreds.. Also response times are muuuuch longer if I can't force online to use vr_varaus index. PS! I made 1. UPDATE STATISTICS HIGH for columns that are in the beginning of index 2. UPDATE STATISTICS HIGH for first different column where there are composite indexes starting with same column. 3. UPDATE STATISTICS LOW for all composite index columns as the release note PERFDOC_7.3 suggest and got rid of my problem..BUT.. there was an other select in other program that came at least 100 times slower. I made those changes to out (almost) 7x24 production database and the phones were quite busy in the mornig... Is there any better way to play with those indexes? Pentti Tiet'v'inen

>You need "UPDATE STATISTICS MEDIUM" or "UPDATE STATISTICS HIGH". OK >SELECT --+ INDEX(table [desired_index_1,desired_index_2,...]) > a,b,c FROM table >WHERE ... > >To avoid optimiser using some indexe(s) use AVOID_INDEX. You'll see >your plans with SET EXPLAIN ON. Seems to work from dbaccess, I didn't get it work from 4gl (my manuals are in other town..maybe I must take a trip..) >By the way, your query to determine selectivity of indexes is correct >ONLY FOR SINGLE COLUMN indexes. Nunique is number of unique keys in the >FIRST COLUMN of an index. So, as far vr_haku is composite index, >probably it's selectivity is higher then vr_varaus index, and it's >better to use vr_haku index. I know for sure that vr_varaus has ca. 10 duplicates, when vr_haku has hundreds.. Also response times are muuuuch longer if I can't force online to use vr_varaus index. PS! I made 1. UPDATE STATISTICS HIGH for columns that are in the beginning of index 2. UPDATE STATISTICS HIGH for first different column where there are composite indexes starting with same column. 3. UPDATE STATISTICS LOW for all composite index columns as the release note PERFDOC_7.3 suggest and got rid of my problem..BUT.. there was an other select in other program that came at least 100 times slower. I made those changes to out (almost) 7x24 production database and the phones were quite busy in the mornig... Is there any better way to play with those indexes? Pentti Tiet'v'inen

>You need "UPDATE STATISTICS MEDIUM" or "UPDATE STATISTICS HIGH". OK >SELECT --+ INDEX(table [desired_index_1,desired_index_2,...]) > a,b,c FROM table >WHERE ... > >To avoid optimiser using some indexe(s) use AVOID_INDEX. You'll see >your plans with SET EXPLAIN ON. Seems to work from dbaccess, I didn't get it work from 4gl (my manuals are in other town..maybe I must take a trip..) >By the way, your query to determine selectivity of indexes is correct >ONLY FOR SINGLE COLUMN indexes. Nunique is number of unique keys in the >FIRST COLUMN of an index. So, as far vr_haku is composite index, >probably it's selectivity is higher then vr_varaus index, and it's >better to use vr_haku index. I know for sure that vr_varaus has ca. 10 duplicates, when vr_haku has hundreds.. Also response times are muuuuch longer if I can't force online to use vr_varaus index. PS! I made 1. UPDATE STATISTICS HIGH for columns that are in the beginning of index 2. UPDATE STATISTICS HIGH for first different column where there are composite indexes starting with same column. 3. UPDATE STATISTICS LOW for all composite index columns as the release note PERFDOC_7.3 suggest and got rid of my problem..BUT.. there was an other select in other program that came at least 100 times slower. I made those changes to out (almost) 7x24 production database and the phones were quite busy in the mornig... Is there any better way to play with those indexes? Pentti Tiet'v'inen

In article <7m220p$ca7$1@tron.sci.fi>, "Pentti Tiet'v'inen" <pat@sci.fi> wrote: > > >You need "UPDATE STATISTICS MEDIUM" or "UPDATE STATISTICS HIGH". > OK > > >SELECT --+ INDEX(table [desired_index_1,desired_index_2,...]) > > a,b,c FROM table > >WHERE ... > > > >To avoid optimiser using some indexe(s) use AVOID_INDEX. You'll see > >your plans with SET EXPLAIN ON. > Seems to work from dbaccess, I didn't get it work from 4gl (my manuals are > in other town..maybe I must take a trip..) Why ? PREPARE p_something FROM "SELECT --+ INDEX(table [desired_index_1,desired_index_2,...]) a,b,c FROM table WHERE .." after FOREACH or EXECUTE > > >By the way, your query to determine selectivity of indexes is correct > >ONLY FOR SINGLE COLUMN indexes. Nunique is number of unique keys in the > >FIRST COLUMN of an index. So, as far vr_haku is composite index, > >probably it's selectivity is higher then vr_varaus index, and it's > >better to use vr_haku index. > I know for sure that vr_varaus has ca. 10 duplicates, when vr_haku has > hundreds.. Also response times are muuuuch longer if I can't force online to > use vr_varaus index. > > PS! I made > 1. UPDATE STATISTICS HIGH for columns that are in the beginning of index > 2. UPDATE STATISTICS HIGH for first different column where there are > composite indexes starting with same column. > 3. UPDATE STATISTICS LOW for all composite index columns > as the release note PERFDOC_7.3 suggest and got rid of my problem..BUT.. > there was an other select in other program that came at least 100 times > slower. I made those changes to out (almost) 7x24 production database and > the phones were quite busy in the mornig... > > Is there any better way to play with those indexes? > > Pentti Tiet'v'inen > > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

The other program is now slow because some other table it is joining to needs its stats updated also or needs an index added. With the old query plan, without stats, it could probably work but now something needs changing. Extract the problematic query and schema of the tables and post an sqexplain.out output for that query also. Art S. Kagel "Pentti Tietäväinen" wrote: > > >You need "UPDATE STATISTICS MEDIUM" or "UPDATE STATISTICS HIGH". > OK > > >SELECT --+ INDEX(table [desired_index_1,desired_index_2,...]) > > a,b,c FROM table > >WHERE ... > > > >To avoid optimiser using some indexe(s) use AVOID_INDEX. You'll see > >your plans with SET EXPLAIN ON. > Seems to work from dbaccess, I didn't get it work from 4gl (my manuals are > in other town..maybe I must take a trip..) > > >By the way, your query to determine selectivity of indexes is correct > >ONLY FOR SINGLE COLUMN indexes. Nunique is number of unique keys in the > >FIRST COLUMN of an index. So, as far vr_haku is composite index, > >probably it's selectivity is higher then vr_varaus index, and it's > >better to use vr_haku index. > I know for sure that vr_varaus has ca. 10 duplicates, when vr_haku has > hundreds.. Also response times are muuuuch longer if I can't force online to > use vr_varaus index. > > PS! I made > 1. UPDATE STATISTICS HIGH for columns that are in the beginning of index > 2. UPDATE STATISTICS HIGH for first different column where there are > composite indexes starting with same column. > 3. UPDATE STATISTICS LOW for all composite index columns > as the release note PERFDOC_7.3 suggest and got rid of my problem..BUT.. > there was an other select in other program that came at least 100 times > slower. I made those changes to out (almost) 7x24 production database and > the phones were quite busy in the mornig... > > Is there any better way to play with those indexes? > > Pentti Tietäväinen

This was my first trip to news-groups and I like to thank all the people who want to discuss and share information (mostly uncommercial basis). I hope I learn something from online indexing strategy too. Perhaps more than get an answer to a particular problem I'd like to put out the general question: "If I can see the best strategy to dig out some information from one or two tables why can't online do it?". The way to put "statistics high" to one column and put "statistics low" to another column sounds a little bit of black magic to me. Why online do not gather enough base statistics to survive from my very humble queries? I've bee in lesson where Informix consult told us to let the work to the online optimizer, but somehow our 4GL-code is full of cursors and loops- and if-statements. I know the SQL-syntax is full of unions, subqueries, shelf-joins and so on, but.. Does anybody have the same feeling?

"Pentti Tietäväinen" wrote: > > This was my first trip to news-groups and I like to thank all the people who > want to discuss and share information (mostly uncommercial basis). I hope I > learn something from online indexing strategy too. Perhaps more than get an > answer to a particular problem I'd like to put out the general question: "If > I can see the best strategy to dig out some information from one or two > tables why can't online do it?". The way to put "statistics high" to one > column and put "statistics low" to another column sounds a little bit of > black magic to me. Why online do not gather enough base statistics to > survive from my very humble queries? I've bee in lesson where Informix > consult told us to let the work to the online optimizer, but somehow our > 4GL-code is full of cursors and loops- and if-statements. I know the > SQL-syntax is full of unions, subqueries, shelf-joins and so on, but.. Does > anybody have the same feeling? You are assuming that you know better than the optimizer what is the "better" query plan. This is rare except in one circumstance. That would be if you run the query in dbaccess with hard values and SET EXPLAIN ON and get the optimal query plan from the sqexplain.out file, assuming that stats are properly updated (I'll get back to that "Black Magic" in a bit), then when you run the query as a prepare cursor with replaceable parameters you see a different, sub-optimal, query plan, you can use optimizer directives to force the optimizer to use the plan you have determined to be best. This begs the question: Why the difference if the query is prepared with parameters that with hard values in dbaccess? Does dbaccess do something my 4GL and ESQL/C programs do not do? To the second question, no this is Informix not Microsoft, there are few secrets. To the first question, if your query is prepared with replaceable parameters the optimizer does not know at prepare time when it is deciding on a query plan what the actual values will be so it has to use a conservative plan which may be VERY different from the data specific query plan it might use for the hard valued query you tested in dbaccess. Consider a query with three replaceable parameters. Two are the first two keys in index A and the third is the first key in index B followed by the other two parameters. With no knowledge of the parameter values the optimizer decides that using index B seems the best bet because in general parameter 3 has good filter value, based on stats and data distributions and so the query plan uses index B. However when you run the query you substitute values for params 1, 2, & 3 such that param 3 is the customer id of your biggest customer and 40% of the rows in the table contain that key. The filter value of that column just went out the window and the engine now has to examine 40% of the index nodes to determine the matching rows. In dbaccess the value of param 3 is known and the optimizer knows the limited value of index B and chooses index A instead. You say: "But I know my data and indexes and can do a better job than that!" Consider a query where the parameter is replaced by a key value that is contained in one out of 50 rows and there is an index beginning with that column but containing no other filter columns. You say: "Using that index is the best plan, it's obvious!" But the optimizer looks more carefully at the stats and the rowsize and discovers that, statistically, since there are 70 rows on a data page each page contains one or two rows containing that key. Therefore, even using the index the engine is likely to have to read every page in the table so the best query plan is to perform a sequential scan and save the I/O and key comparison costs of using the index completely. As to the "Black Magic" of the update statistics, there is no magic about it. To give the optimizer the best possible information for making decisions just run UPDATE STATISTICS HIGH ... DISTRIBUTIONS ONLY on the table and UPDATE STATISTICS LOW... on each index key list. BUT, this is time consuming and requires lots of storage for the stats. So, Informix made recommendations, last updated with 7.21 in the release notes and also in the 7.3x Performance Guide, that can help you to produce stats that are "Good Enough" for most applications in minimum time. It involves running MEDIUM at the table level DISTRIBUTIONS ONLY, HIGH on the first column of each index DISTRIBUTIONS ONLY, optionally HIGH on the first column that differs if two indexes start with the same columns DISTRIBUTIONS ONLY, and LOW on each index key list. Since then those of us doing the work, ie us DBAs, have made additional 'rules' and determined for our own databases what tables and indexes need higher level stats or even lower level stats to do the best job for our query mix. I have encapsulated as much of that knowledge as possible, with appropriate options, in a utility, dostats.ec, which is available from the IIUG Software Repository in the package utils2_ak. No magic about it at all, but to paraphrase Assimov: If you don't understand it then it just seems to be magic. Art S. Kagel

In article <37861A36.3B9B6FAB@bloomberg.net>, kagel@bloomberg.net wrote: ..snipped > To the first > question, if your query is prepared with replaceable parameters the > optimizer does not know at prepare time when it is deciding on a query > plan what the actual values will be so it has to use a conservative > plan which may be VERY different from the data specific query plan it > might use for the hard valued query you tested in dbaccess. The optimizer usually 'plans' queries when they are prepared. However, if replaceable parameters exist in the WHERE clause (i.e "?" in the statement), it will not; instead, it plans out the query when the cursor is OPENed with actual values. I've tested this in v6.x 4GL with the v7.2 Engine. I would be extremely surprised if the 7.3 optimizer's behaviour has changed in this respect - unfortunately, I don't have 4GL at this site to test it. Like the other participants in this thread, I strongly discourage the use of directives in programs. Yes, the optimizer sometimes misbehaves; usually, UPDATE STATISTICS with a higher resolution corrects it. Rudy Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.