Re: Update Statistics - Cost of using a Function as an Index...

On Jun 13, 2:08 pm, Cesar Inacio Martins <cesar_inacio_mart...@yahoo.com.br> wrote: > Hi, > > I executed some tests here with function as an index. > > 1) I created an index , using a function: > create index ix_dt on tst1 (dt_periodo(hora));> 2) Ran update statistics on the column where the function was applied : > update statistics low for table tst1(hora);> 3) Then I executed the query using this function... the optimizer used the index but the costs have changed just a little... > select * , dt_periodo(hora) periodo from tst1 where dt_periodo(hora) = "tarde" > 4) Ran an update statistics on the table without specified any column: > update statistics low for table tst1;> 5) Finally I re-executed the query using this function... this time the costs went down considerably... > > I looked up on manuals but didn't find any information on how I could > execute an update statistics only for this index. Does anybody know if > this is possible or if I have to execute an update statistics on all > columns of the table?? Dostats parses the column names from the argument list of the index function and updates stats on those. So in the case of your index on dt_periodo, dostats would include 'hora' in the list of columns it performs an update statistics HIGH on. That's the best I've been able to do. There's some indication that the engine is assuming that since the indexing function is non-variant that it is also deterministic, so the relative weights of values of the arguments to the function are proportional to the weights of the resulting key value as returned by the function. Art S. Kagel

Art S. Kagel wrote: > On Jun 13, 2:08 pm, Cesar Inacio Martins > <cesar_inacio_mart...@yahoo.com.br> wrote: >> Hi, >> >> I executed some tests here with function as an index. >> >> 1) I created an index , using a function: >> create index ix_dt on tst1 (dt_periodo(hora));>> 2) Ran update statistics on the column where the function was applied : >> update statistics low for table tst1(hora);>> 3) Then I executed the query using this function... the optimizer used the index but the costs have changed just a little... >> select * , dt_periodo(hora) periodo from tst1 where dt_periodo(hora) = "tarde" >> 4) Ran an update statistics on the table without specified any column: >> update statistics low for table tst1;>> 5) Finally I re-executed the query using this function... this time the costs went down considerably... >> >> I looked up on manuals but didn't find any information on how I could >> execute an update statistics only for this index. Does anybody know if >> this is possible or if I have to execute an update statistics on all >> columns of the table?? > > Dostats parses the column names from the argument list of the index > function and updates stats on those. > > So in the case of your index on dt_periodo, dostats would include > 'hora' in the list of columns it performs an update statistics HIGH > on. > > That's the best I've been able to do. There's some indication that > the engine is assuming that since the indexing function is non-variant > that it is also deterministic, so the relative weights of values of > the arguments to the function are proportional to the weights of the > resulting key value as returned by the function. > I thought that the optimiser bases its cost on the function's per-call cost and selectivity. These can be specified in the WITH (...) modifier and can be either constants or functions in there own right.

On Jun 14, 9:00 am, Richard Harnden <richard.harn...@lineone.net> wrote: > Art S. Kagel wrote: > > On Jun 13, 2:08 pm, Cesar Inacio Martins > > <cesar_inacio_mart...@yahoo.com.br> wrote: > >> Hi, > > >> I executed some tests here with function as an index. > > >> 1) I created an index , using a function: > >> create index ix_dt on tst1 (dt_periodo(hora));> >> 2) Ran update statistics on the column where the function was applied : > >> update statistics low for table tst1(hora);> >> 3) Then I executed the query using this function... the optimizer used the index but the costs have changed just a little... > >> select * , dt_periodo(hora) periodo from tst1 where dt_periodo(hora) = "tarde" > >> 4) Ran an update statistics on the table without specified any column: > >> update statistics low for table tst1;> >> 5) Finally I re-executed the query using this function... this time the costs went down considerably... > > >> I looked up on manuals but didn't find any information on how I could > >> execute an update statistics only for this index. Does anybody know if > >> this is possible or if I have to execute an update statistics on all > >> columns of the table?? > > > Dostats parses the column names from the argument list of the index > > function and updates stats on those. > > > So in the case of your index on dt_periodo, dostats would include > > 'hora' in the list of columns it performs an update statistics HIGH > > on. > > > That's the best I've been able to do. There's some indication that > > the engine is assuming that since the indexing function is non-variant > > that it is also deterministic, so the relative weights of values of > > the arguments to the function are proportional to the weights of the > > resulting key value as returned by the function. > > I thought that the optimiser bases its cost on the function's per-call > cost and selectivity. These can be specified in the WITH (...) modifier > and can be either constants or functions in there own right. That may be, though as I said, there's some indication that the columns passed to the function are also used in the weighting calculation. Anyway, the function cost and selectivity are not effected by UPDATE STATISTICS so if the optimizer's not doing it's best possible job, I'd try adding distributions for the argument column and see what happens. Art S. Kagel