Re: programmatically updating table statistics

On Dec 10, 5:06 am, Wisser <RalfWis..._at_gmx.de> wrote:
> On 8 Dez., 00:03, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
> > On Dec 7, 6:51 am, Wisser <RalfWis..._at_gmx.de> wrote:
>
> > Jonathan Lewis talks about this approach in his latest book. It's not
> > an easy read exactly but worth working through.
>
> > Yes it is possible ... usually something you want to try to avoid if
> > you can. That's the short response.
>
> Sorry for not asking precisely enough. I never thought about
> manipulating the statistics directly, but by calling stored
> procedures.

Well both approaches are possible. Using dbms_stats get_xxx_stats and set_xxx_stats along with some related code is the stored procedure approach. Jonathan's book talks about this approach in chapter 5.

Is it possible to issue direct update statements against the oracle dictionary? Well yes but this is strongly discouraged could lead to database corruption and an unsupportable system. This is hacking level here ...

>
> > One possible approach is avoiding having stats for the table and using
> > dynamic sampling. Do you use bind variables in your applications so
> > your statements stay in the shared pool?
>
> No, I don't use bind variables. What is "dynamic sampling".
> Is it possible to tell the DB that a table has "voilatile cardinalty",
> as one can do with db2?

I am no longer familiar enough with db2 to attempt to answer your last question. Dynamic sampling is available with later releases but as I understand only applies when used as an optimizer hint or if the table does not have statistics. Tom Kyte covers this pretty well http://asktom.oracle.com Received on Mon Dec 10 2007 - 07:53:20 CST