Re: Re: Informix limitations, should we be using Oracle?

Hey, hey, hey!! It works... ! Now there is something new I know... Chucho -----Original Message----- From: "Brian Foster" <bc_foster@hotmail.com> To: informix-list@iiug.org Date: Mon, 17 Nov 2003 19:16:13 -0500 Subject: Re: Informix limitations, should we be using Oracle? you can select null, you just need to give your column a datatype. i.e. select null::integer from sometable; -Brian select case when 1=2 then 1 else null end::integer null_value from table(set{1}) Jean Sagi wrote in message ... > >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 >> Oracle will do. If you don't have enough disk - well you're SOL. >> >> >>>Oracle (or Informix IDS) does not employ the Resource Grant Manager >>>architecture. Small queries use small memory, and large queries use >>>large amounts of memory as requ