Re: [Info-Ingres] Enhancement to Ingres Statistics Generation and Storage

[lin.a.b...@uk.bnpparibas.com]

I think part 1 is a great idea.

Having to generate stats using a separate process outside of the database
has always been a nuisance.

The sooner we can do it using SQL the better.

Lin

Internet
schendel@kbcom
puter.com To
info-...@kettleriverconsulting.com
Sent by: cc
info-ingres-bo
unces@kettleri Subject
verconsulting. Re: [Info-Ingres] Enhancement to Ingres
com Statistics Generation and Storage

26/11/2009
13:40


Please respond
to
info-ingres@ke
ttleriverconsu
lting.com

On Nov 26, 2009, at 5:39 AM, rthdavid wrote:

>
> Part 1:-
> ....
> MODIFY <table> TO <structure> WITH <clause>, STATISTICS = (flags);
>
> CREATE [unique] INDEX <index> ON <table> (column list) WITH <clause>,
> STATISTICS = (flags);
>
> This should significantly reduce the end-to-end processing time
> because the base table is only scanned once.

It would also get the DBMS server itself involved in statistics
creation, opening the door to auto-creation of stats. Not a bad
thing.

However, I'm not sure how much this would reduce overall
runtime. Part of the issue with stats creation is that you have
to summarize the table a bunch of different ways. The base
table scan time may not be the major factor. I've never
really looked at how optimizedb time breaks down, though.

>
> Part 2:-
>
> I would also like to see statistics moved to 's-tabs' (a bit like 'e-
> tabs' but for statistics rather than long objects) that exist
> alongside a base table.
>
> I appreciate that this would be a major change to how Ingres generates
> and uses statistics (OPF), but it would eliminate pressure on system
> catalogs, improve concurrency and provide a mechanism of having
> statistics on sub-table objects like individual table partitions?

I don't think I follow this one entirely. The proliferation of tables
would add to DMF memory pressure for the TCB's and S-table
pages, and clog up the DMF cache lists. The existing stats
tables are capable of per-partition stats, but neither RDF nor
the optimizer would have any idea what to do with them.

I'd suggest instead looking at removing the control lock taken
when stats are updated -- it's not really necessary, and is the
main concurrency bottleneck.

Karl

_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres