Enhancement to Ingres Statistics Generation and Storage

[rthdavid]

Hi Gang,

Well, I've gone and done it. I've actually written to Santa and asked
him for an enhancement to Ingres!

I'm usually quite skeptical about doing this kind of thing, but I am
told, by those who care about such things, that my request would get
more focus if I could drum up support from hard, fee paying Ingres
users out there in the real world!

Here is my enhancement request:-

=============================================

Part 1:-

Currently, the way to carry out regular, general housekeeping is to
run MODIFY and CREATE INDEX DDL followed by optimizedb. The whole end-
to-end process can take quite some time.

I would like to see Ingres enhanced to add the ability to create
statistics to MODIFY and CREATE INDEX DDL e.g.

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.

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?

=============================================

Any comments?

Cheers,

Richard

PS If you think that my idea(s) have merit then please can you raise
your own Category 4 Support Issue referencing Issue 141222?

[Karl Schendel]

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