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
[Info-Ingres] Enhancement to Ingres Statistics Generation and Storage
1 posts
• Page 1 of 1
- Windows XP
- Visual Basic
- PowerPoint
- Windows
- dotnet framework
- IBM PC
- robots
- msn
- PERL
- javascript
Jump to:
Sponsored Links
- 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
Next
Re: [Info-Ingres] Enhancement to Ingres Statistics Generation and Storage
by lin.a.bradbrook » Fri, 27 Nov 2009 22:51:19 GMT
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
1 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 0 guest