Re: Informix limitations, should we be using Oracle?

I should have been more temperate. I'll have to give the NULL a try. By 'full outer join' do you mean that the join between these two lists A C B D C E Returns A B C D E? Regards, Jack Parker -.-- --- ..- / -. . . -.. / - --- / --. . - / .- / .-.. .. ..-. . .-.-.- / ... --- / -.. --- / .. .-.-.- ----- Original Message ----- From: "Jean Sagi" <jeansagi@myrealbox.com> To: "Jack Parker" <vze2qjg5@verizon.net> Cc: <informix-list@iiug.org> Sent: Monday, November 17, 2003 12:38 PM Subject: Re: Informix limitations, should we be using Oracle? > I read all you post and it was very interesting... > > I have only 2 thing to say and have in count that I don't know anything > about XPS: > > 1. In IDS 9.x, you can't select a NULL, you can use it in an UPDATE, > INSERT or DELETE. You can workaround this by creating an sp sp_genull() > who return a null and that do it... but is ugly... > If XPS can do it... it's good, inf fact there are some very cool > features only in the XPS SQL I wish to have in IDS. > > 2. In IDS there is no FULL OUTER JOIN... in some cases this is very > usefull. If XPS have it it's good. > > As I said, I wish SQL-IDS has more features of XPS-SQL > > Chucho! > > Jack Parker wrote: > > I must have been offline during this discussion. I ran across it today > > looking for something else. Although late, I will add my .02. (embedded). > > > > The next time one of these come around and I'm not visible - please alert me > > to it? > > > > cheers > > j. > > > > > >>Hi, > >> > >>We are trying to implement what will become a multi-terrabyte data > >>warehouse on Informix XPS but have hit a number of significant > >>problems. We are at the point where we are considering switching > >>database providers to Oracle but want to be sure that the problems we > >>are encountering are indeed valid issues. We have prepared a document > >>of which I have included an extract that details the issues we are > >>having. If anyone can provide me with feedback on these issues to let > >>me know if I'm barking up the wrong tree or if indeed they are issues, > >>it would be greatly appreciated. We are currently running Informix XPS > >>8.3.1 on a 4CPU, 4Gb HP N4000 with HP-UX 11.11. > >> > >> > >>Informix XPS Issues and Comparison to Oracle > >> > >>Performance > >> > >>As detailed on TPC websites > >>http://www.tpc.org/tpch/results/tpch_results.asp?orderby=dbms > >>http://www.tpc.org/information/benchmarks.asp > > > > > > We all know about benchmarks - regardless - TPC-C is not the benchmark you > > would want to use to measure data warehouse performance, you would really > > want TPC-H, (as you mention below) - but even that is flawed in that it > > insists upon ongoing transactions (which are not normal warehouse processes) > > during the benchmark. > > > > > >>Looking at the TPC-H results for a 1000Gb database running on an > >>HP9000 Superdome > >>runs 2.65 times faster than XPS. The pricing of the two databases as > >>reflected in the Price/QphH also shows that Oracle represents 3.5 > >>times more 'bang for the buck' than XPS. > >> > > > > > > Alas these results are long gone, IBM is also not in the business of > > benchmarking XPS, however; having worked with both databases, I can assure > > you that XPS will scale infinitely while Oracle will not. > > > > > >>Issues : > >> > >>Memory Management - CRITICAL > >> > >>XPS has an essential flaw in it's memory management implementation for > >>parallel Decision Support System Queries. The Resource Grant Manager > >>configuration makes it necessary to allocate either large memory > >>segments or small memory segments to all sessions utilising Decision > >>Support Resources (large joins, sorts, ordering etc). > >> > >>It is expected that intention is so that Decision Support System (i.e. > >>Warehouse queries - DSS) queries can preallocate huge memory segments > >>through this configuration. > > > > > > The intent of memory allocation is to pre-allocate resources to intensive > > queries. This is by no means a requirement for XPS, nor is it a bad thing. > > With judicious use this memory can be put to very advantageous use in index > > building, hash joins, groups and sorts - I gather Oracle has a similar > > capability, I have not seen it clearly put to use yet. > > > > > >>When DSS queries are issued the memory is allocated up to the > >>DS_MEMORY_TOTAL. When this occurs, all other DSS queries are queued. > >>It should be noted that the memory allocated is a fixed amount, > >>regardless of the complexity or priority of the query being executed - > >>i.e. Simple counts are allocated the same amount of memory as massive > >>join and sort queries > > > > > > Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO - > > which is under the control of the DBA. I realize that with Oracle a 'simple > > count' requires a full table (or index) scan, with Informix it is a single > > read against the table header and virtually instantaneous. You can also use > > a light scan for a filtered read (count(*) ... where condition) - this does > > not chew up memory. Oracle has no equivalent to the light scan - which is > > on average 4x faster than a traditional read. > > > > Yes, you can give a query enough memory so that other queries are gated and > > will not interfere with your process while it runs. This is preferable to > > the thrashing which would occur if this were not an option. > > > > > >>ETL tools parallelise their processing for enhanced performance. Using > >>XPS, it is not uncommon that queries issued in the same program may > >>expend all of the DS_TOTAL_MEMORY and other SQL statements within the > >>same program are queued. This is the cause of the classic Dawa problem > >>- 'the locked plan'. > > > > > > Is it not a wonderful thing when you can fully utilize the power of the > > database and the machine with one query? At the same time you can prevent > > this from occuring. > > > > > >>This situation is exacerbated by the fact that the other statements > >>within the program still retain their memory in Informix - so all SQL > >>statements within the database are 'locked'. > > > > > > Not quite, only those which require DSS resources, so your 'simple count' > > would go straight through. > > > > > >>A resolution to this is to create small memory allocations for each > >>session. Underallocating the memory segment size causes Reports (which > >>do lots or GROUPING and ORDERING) to run extremely slowly, or exceed > >>temp space allocation and fail. > > > > > > Exceeding temp space is a DBA matter - much akin to exceeding the size of a > > rollback segment under Oracle. Either you are properly sized or you are > > not. XPS, and all Informix engines, will use what memory is available to > > the process and swap to disk what is not - this is the same thing that > > O

Jack Parker wrote: > I should have been more temperate. > > I'll have to give the NULL a try. By 'full outer join' do you mean that the > join between these two lists > > A C > B D > C E > > Returns > A B C D E? No, the full outer join is: A NULL B NULL C C NULL D NULL E -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

On Wed, 19 Nov 2003 00:59:29 -0500, Jonathan Leffler wrote: Note that a full bi-directional outer join can easily be implemented using a UNION of two (assuming only two tables are involved) LEFT OUTER JOINs: select ... from tab1 left outer join tab2 on tab1.key = tab2.key where ... UNION select ... from tab2 left outer join tab1 on tab1.key = tab2.key where ......; And this is parallelizable so the two sides of the UNION can be executed concurrently. Art S. Kagel > Jack Parker wrote: > >> I should have been more temperate. >> >> I'll have to give the NULL a try. By 'full outer join' do you mean that the >> join between these two lists >> >> A C >> B D >> C E >> >> Returns >> A B C D E? > > No, the full outer join is: > > A NULL > B NULL > C C > NULL D > NULL E > >