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

DATABASE

    Sponsored Links

    Next

  • 1. [Info-ingres] iietab table at MAX page size
    Hi, I have a small problem this morning, we have a BLOB table who's iietab table reached ingres' max page size (8385203). Assuming I can scrounge up enough workspace, can I modify a iietab table directly ? Or should I copy it out/in ? The table size is approx 250 GB. Table details: Row width: 2000 Number of rows: 75878786 Storage structure: btree Compression: none Duplicate Rows: allowed Number of pages: 8385203 Overflow data pages: 0 Thanks Andre
  • 2. ABF - temp tables and exec sql connect questions...
    Hello all, I have created a frame in which I am using db1 and have declared a temporary table SESSION.accounts. I then connect to another another db (db2) using 'exec sql connect' in order to retreive some data. I select the data successfully and then attempt to load it into the temp table SESSION.accounts. But this fails. I am guessing that the temp table SESSION.accounts is destroyed as soon as I connect to db2 - how can I get round this and retreive data from db2 and use it in db1 in the frame. Cheers, Manny

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

Postby lin.a.bradbrook » Fri, 27 Nov 2009 22:51:19 GMT

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
XXXX@XXXXX.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:


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.


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
XXXX@XXXXX.COM
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres




___________________________________________________________
This communication is confidential, may be privileged and is meant only for the intended recipient. If you are not the intended recipient, please notify the sender by reply and delete the message from your system. Any unauthorised dissemination, distribution or copying hereof is prohibited.

BNP Paribas Trust Corporation UK Limited, BNP Paribas UK Limited, BNP Paribas Commodity Futures Limited

Similar Threads:

1.[Info-Ingres] Enhancement to Ingres Statistics Generation and Storage

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


2.Enhancement to Ingres Statistics Generation and Storage

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?

3.[Info-ingres] Enhancements to the Ingres Open Source Site

4.[Info-Ingres] Enhancement to DB procedures

5.[Info-ingres] [ingres] [Info-ingres] Dates users were created in Ingres

6. [Info-ingres] RES: [Info-ingres] RES: [Info-ingres] SQL Injection attacks

7. [Info-ingres] RES: [Info-ingres] RES: [Info-ingres] SQL Injection attacks

8. [Info-ingres] RES: [Info-ingres] CA World Ingres Sessions



Return to DATABASE

 

Who is online

Users browsing this forum: No registered users and 0 guest