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...p?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 required. This means the database slows
> >>down, but does not lock on memory.
> >
> >
> > Actually IDS and XPS have the same memory allocation features
(PDQPRIORITY),
> > although under IDS it's called Memory Grant Manager, but they're the
same
> > thing. I have never seen XPS 'lock on memory'.
> >
> >
> >>It should be noted that XPS (Extended Parallel Server) refers to
> >>parallelism in Platforms - it is evident that for SQL queries that it
> >>is NOT optimised for parallelism.
> >>
> >
> >
> > What have you been smoking? Where do you get this 'it is evident'? XPS
> > performs in parallel with everything, across all horizontal and vertical
> > portions of an operation. It exhibits the highest degree of parallelism
> > that has ever been offered to the public.
> >
> >
> >>Removing Sessions
> >>
> >>Informix XPS 8.31 D has an outstanding bug in which it is not possible
> >>to remove a Informix client session with assurance. Failed attempts to
> >>issue the remove session command have resulted in:
> >>· reboot of database instance
> >>· reboot of Unix
> >>· inability to restart the database instance
> >
> >
> > This is an issue which was corrected in 8.32, which was released 2.5
years
> > ago. At your writing XPS was up to version 8.4
> >
> >
> >>Neither does it appear possible to link an Informix client session to
> >>a Unix session id / thread - making it impossible to sessions to be
> >>identified and removed at the Operating System level.
> >
> >
> > And this is a good thing. Alas with Oracle (and db2) all user sessions
are
> > operating systems processes, this means that context switching is
removed
> > from the control of the database and handed to the operating system
which
> > really has no clue as to what's going on inside the engine. Believe me,
you
> > want the threads within the engine and not subject to the OS overhead.
You
> > can in fact determine which sessions are tied to which clients with a
simple
> > onstat command.
> >
> >
> >>Oracle, however, runs each session as an easily identifiable Unix
> >>process, and sessions can be removed either through DBA commands, or
> >>at the Operating System Level.
> >
> >
> > And sometimes this will even remove the locks held by these sessions
when
> > they are thusly killed.
> >
> >
> >>Exceeding lock count
> >>
> >>Informix locks database records using memory latches. A maximum number
> >>of memory latches is specified in the config file. When this limit is
> >>exceeded the database server crashes.
> >
> >
> > Not quite. At the time you wrote this, a counter was incremented to
> > indicate that the lock threshold had been exceeded. However in current
> > releases Informix has dynamic locks, more locks are allocated as needed.
> >
> >
> >>This is exacerbated when the database restarts and attempt to rollback
> >>the transaction that caused the locks to be exceeded - this again
> >>exceeds the maximum locks. In our experience, the only solution is to
> >>destroy and recreate the database.
> >
> >
> > There indeed were issues with XPS 8.31 where restarting the instance
could
> > cause issues. Normally, those with support contracts would call the
'Down
> > Systems Group' which will answer the phone in under a minute and provide
> > sunset support (i.e. 24x7) until your engine is back on line. Those
without
> > support would call the same group and be gently scolded - and then
supported
> > in the same fashion. The last time I called Oracle support, I got a
call
> > back three days later telling me to upgrade.
> >
> >
> >>Oracle does not use memory latches to lock records. It's standard
> >>row-level-locking is managed in the rollback segment architecture.
> >>Exceeding the rollback segment will not crash the database - it causes
> >>the session to rollback the current SQL transaction.
> >>
> >
> >
> > Which can be a lot of fun. But this is a moot point. It's a question
of
> > sizing - if you size your rollback segments improperly - or choose the
wrong
> > one with which to perform your transaction - you are hosed. The lesson
is
> > that if you size things properly they work - whether they are Oracle or
> > Informix.
> >
> >
> >>Lack of diagnostic/monitoring tools
> >>
> >>No user friendly or graphical monitoring tools are available. This
> >>makes diagnosis and performance monitoring extremely difficult.
> >>
> >
> >
> > There is indeed no 'Informix' tool to compare to the Oracle Enterprise
> > Manager (did I get that right?). However there is a single standard
command
> > (onstat) to which all monitoring activity has been tied - not to mention
the
> > SQL interface to this same data. This can be much more effective than
> > waiting around for a GUI to load and display data which you then have to
> > drill into. I have to admit as an Oracle neophyte that I find the
Oracle
> > GUI useful for handling tasks without any understanding of what lies
beneath
> > them.
> >
> >
> >>Many monitoring and diagnostic tools are available for Oracle. Many of
> >>them are free. Administration, diagnosis and monitoring is vastly
> >>simplified.
> >>
> >>Raw partitions exacerbate the problem.
> >>
> >
> >
> > I'm not sure to which problem you refer. Raw partitions are very simple
to
> > manage.
> >
> >
> >>Raw partitions and storage management
> >>
> >>Standard data storage in Informix is via RAW unix partitions. These
> >>partitions cannot be monitored via standard Unix utilities. They are
> >>more difficult to manage and create - Unix 'root' privileges are
> >>required to manage them. Furthermore, raw partitions used by Informix
> >>cannot be resized or grown. A completely new partition must be
> >>allocated.
> >>
> >
> >
> > 'Standard' is either 'cooked files' (OS files) or raw devices. You get
a
> > 15% performance improvement with raw devices. Oracle has the same
feature.
> > There is no similar benefit to raw devices under Oracle - I don't
remember
> > why at present. With a raw device Informix gets to control I/O to the
> > device itself instead of going through the Unix file system - which
tends to
> > not only slow things down, but opens a window of vulnerability - you
write
> > to disk and commit, the database writes it to disk and thinks it made
it,
> > but the OS didn't get that far - so your 'committed transaction' wasn't.
> > Management of raw spaces is incredibly simple - especially under XPS
where
> > you can usewildcards to manage hundreds of such spaces at once.
> >
> >
> >>Informix can use cooked partitions, but it is not recommended by
> >>Informix for performance reasons.
> >
> >
> > True.
> >
> >
> >>Oracle standard data storage is via datafiles, which are 'normal' unix
> >>files. These files can be monitored for growth and utilisation much as
> >>any other Unix file. No special system privileges are required to add
> >>or resize datafiles.
> >
> >
> > Oh right - I forgot about the resize thing - I've already mentioned the
Unix
> > filesystem issues. How can you resize a disk? If you buy an 80 GB
disk -
> > can you make it larger? So I guess you're right, under Informix you
can't
> > resize a raw disk, however you can add more of them and include them in
the
> > same dbspace (tablespace) so that the new space is usable by tables
which
> > need to grow in that space. So in fact you can 'resize' a dbspace.
> >
> >
> >>Fast Loading
> >>
> >>Our data warehouse employs fast loading capabilities (bypassing ODBC)
> >>in order to upload Mainframe EBCDIC data.
> >
> >
> > That is a good thing. We are delighted that Oracle has come to the
table
> > with a fast (direct path) loader. We hope someday that it shows the
same
> > sort of performance as is capable with the Informix parallel loader. We
> > further hope that some day it will allow you to unload data in the same
> > fashion.
> >
> >
> >>This feature is particular in Informix to XPS; in Oracle it is
> >>standard functionality.
> >
> >
> > Actually HPL and the light append has been around since 7.x, I first
used it
> > around 1996.
> >
> >
> >>The reject output from Informix is a single-file mixture of ASCII
> >>error report and EBCDIC data. It is impossible to re-upload rejected
> >>records from an Informix EBCDIC reject file - All packed decimal, for
> >>instance, is corrupt.
> >
> >
> > Actually this is true with XPS. You do get a header to each row
indicating
> > the error, you have to trim the header to retrieve your original data.
> > Interesting - this would be a good feature request if not already done.
> > With 7.x and 9.x engines the error is written to a separate file than
the
> > error message or you have the option of allowing it to load and then
picking
> > it out of a violation table later. For more on this topic visit
> > www.artentech.com/downloads.htm and look for the Informix Load FAQ.
> >
> >
> >>Oracle produces two output files when EBCDIC uploading - an error log,
> >>and an EBCDIC reject file - ready for reprocessing.
> >>
> >
> >
> > If you have an error, then there is a problem. In a DSS world, you
don't
> > want to go chasing singleton rows, you want to do the whole thing again.
> >
> >
> >>We currently have to reprocess complete datasets, or reject complete
> >>datasets. With Oracle this would not be necessary.
> >
> >
> > This is ok when you are dealing with a small number of rows and can
afford
> > the luxury of debugging each one. When you are dealing with billions of
> > rows and you have a problem, you really don't want to get into the
details
> > of any individual row. I guess it's a question of how big you intend to
> > get.
> >
> >
> >>The administration of the External Table definitions in Informix is
> >>excessively onerous also. For instance - 6 sales invoice files for
> >>three states necessitates 18 external table definitions and 18
> >>internal table definitions. Addition of EBCDIC datafiles for other
> >>states necessarily implies additional external and internal
> >>definitions.
> >
> >
> > Not sure what you're saying here. If you have 18 files to load, under
XPS
> > you can define them all as a single external table. You can define this
> > single external table across filename, filesystems, coservers(each
instance
> > in a cluster) and servers, you can define it with a single file
statement
> > even (not 18) - it's pretty powerful. Furthermore, this file (or
external
> > table) is visible to the database - you don't have to go through sqlldr
to
> > get it into the database, you can treat it as a table and select columns
> > from it directly, or join it with other tables - whatever. It's really
> > cool. Even cooler - you can unload to it directly with a 'select
<columns>
> > from <> insert into <>' statement.
> >
> >
> >>With Oracle only 6 internal tables are necessary - generic to the
> >>Invoice, they could be used for all states. This vastly simplifies
> >>Data Dictionary management.
> >>
> >
> >
> > See above.
> >
> >
> >>Limitation comparisons
> >>
> >>Informix XPS
> >>18 characters for database objects
> >
> > Ok.
> >
> >
> >>256 byte maximum row size in Indexes
> >
> > You use indices? Are you in a data warehouse environment or an OLTP
> > environment? XPS can perform the hash join so quickly that indices are
a
> > detriment, not a requirement. What you might consider a fast join
between
> > two tables with indices can be performed 100 times faster with a hash
join -
> > but then you need some memory to play these sorts of games. Have a look
at
> >
http://www7b.boulder.ibm.com/dmdd/zo...rticle/parker/
> > 0502parker.html for some examples.
> >
> >
> >>255 byte limit on varchar fields
> >
> > True, although you can go to text and have at least 2GB last time I
> > checked.
> >
> >
> >>SQL Restrictions -
> >>Cannot SELECT NULL
> >
> > What? Sure you can.
> >
> >
> >>No general DUAL table
> >
> > Create one. It's the same thing. I don't generally bother, I 'select
> > 'whatever' from systables where tabid=1'; It's the same thing.
> >
> >
> >>No MINUS operations
> >
> > True, that's a neat Oracle feature.
> >
> >
> >>No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND QUERY,
> >
> >
> >>EMBEDDED QUERY
> >
> > Haven't tried this. Never needed it.
> >
> >
> >>No Full OUTER JOIN
> >
> > Sure there is. Or do you mean that you want all rows from table 'a' and
'b'
> > even where neither intersect. Who's been dreaming up your data models?
You
> > need to clean up your data if this is a requirement. But then I guess
> > Informix doesn't do that particular join.
> >
> >
> >>No Implicit ROWID
> >
> > Not anymore, you can ask for one if you need it.
> >
> >
> >>XPS - No true database flatfile Import Export
> >
> > Totally untrue. For that matter, has Oracle come up with a true
flatfile
> > export yet?
> >
> >
> >>Varchar fields not treated as ANSI standard
> >
> > Well, you can use the ANSI standard if you like, you can also allocate a
> > minimum to each varchar which is incredibly useful when it comes time to
> > update. Unlike Oracle, which compresses data as much as possible to
> > preserve space - and then when you need to update it - oooops won't fit
into
> > it's original space - have to move it to a new page - which can be time
> > consuming - the reservation of space allows the intelligent DBA to
> > counteract this sort of thing.
> >
> >
> >>No DISABLE CONSTRAINT commands - Must DROP and RECREATE constraints -
> >
> > Bullshit. You need to spend more time with the manual.
> >
> >
> >>Additional processing
> >>Stored procedures are not prepared - Object dependence not checked
> >>until runtime
> >
> >
> > Stored procedures, when compiled are checked against what is available -
> > compilation will fail if a required object is not present. What you
should
> > properly say is that Oracle's PL/SQL is far superior to the Informix
stored
> > procedure language. Within Oracle you can develop entire applications
with
> > PL/SQL, the Informix approach to Stored procedures is more basic - to
> > perform this or that function - program logic does not truly belong in
an
> > Informix SPL - more properly in one of it's more standard languages 4GL
or
> > esql/c. Actually I'm not that crazy about the way Oracle will mark a
> > procedure as invalid when something that it depends on changes. This
can be
> > a real pain to manage. I know what I need to rebuild - I don't need the
> > database thinking it knows better than me.
> >
> >
> >>Page size can only be 2 or 4k
> >
> >
> > or 8K. This isn't really an issue, unless you have some reason to need
a
> > 48K page? You have data rows which are bigger than 8k? Why? Have you
> > considered a relational model? Informix will still support big rows,
but
> > they will span pages.
> >
> >
> >>Oracle
> >>30 characters - more meaningful names
> >
> > If you can't fit it into 18 - why is 30 better? Why don't you bring up
9.4
> > and it's support for object names of 128? IDS is the engine you should
be
> > comparing to, not XPS. Oracle doesn't have anything to compare to XPS.
> >
> >
> >>Unlimited
> >
> > (this was the index size - not sure why you want this)
> >
> >
> >>4000 limit
> >
> > (2GB limit on text)
> >
> >
> >>No comparable restrictions
> >
> > How about 'order by 1,2,3'? You guys sure do like to type, especially
those
> > 30 character names.
> > You're 'comparable restrictions' refer perhaps to the ANSI standard
which is
> > a subset of all RDBMS vendors SQL.
> >
> >
> >>Flatfile IMPORT EXPORT available for backups and rebuilds
> >
> > But not a true flatfile.
> >
> >
> >>No comparable ANSI incompatibility
> >
> > Moot.
> >
> >
> >>DISABLE ENABLE DEFER VALIDATE NOVALIDATE CONSTRAINT possible
> >
> > The same basic stuff is available. Since this is not ANSI standard the
name
> > may vary slightly.
> >
> >
> >>Stored procedures are compiled as PCODE
> >
> > ??? Informix stored procedures are also compiled into code within the
> > engine. Not sure where you are getting this stuff.
> >
> >
> >>Page size can be as large as 64K - 16 times fewer reads for DSS table
> >>scans
> >
> >
> > No, actually 8 times more reads. When I want one row, I don't want to
pull
> > 64K, or 8 times the standard disk buffer - hence 8 disk reads instead of
1.
> >
> >
> >>Warehouse functional comparisons
> >>
> >>Informix
> >>Tables and related indexes are partitioned on the same key -
> >
> >
> > Untrue again. Hybrid fragmentation has been around for some time -
> > including back in 8.31.
> >
> >
> >>inflexible configuration
> >>GK Indexes - Index prejoin only
> >
> >
> > You need to be pulling out materialized views here - which is the proper
> > comparison to the GK index.
> >
> >
> >>Necessary to manage security utilising both OS and database levels
> >
> >
> > Sorry? This is an Oracle issue - you need two layers of security,
Informix
> > uses the OS security.
> >
> >
> >>Sagent supports ODBC only for Informix
> >
> >
> > Who?
> >
> >
> >>Oracle
> >>Partitioning tables and indexes on alternate keys - flexible
> >>configuration for performance
> >
> > Moot.
> >
> >
> >>Materialised Views - Database managed JOINS of tables - Physically
> >>existing summary tables for enhanced performance. Time savings in
> >>aggregations
> >
> >
> > Yes, but then you have to refresh that summary table. I have no real
> > quarrel here, but you're comparing quasi-identical features and claiming
> > it's bad there and good here.
> >
> >
> >>Security and permission totally managed within the database
> >
> >
> > Ah, for clients perhaps. Still Informix is relying on their ability to
> > connect to the server, not on an additional layer of security within the
> > database.
> >
> >
> >>Sagent support for High Speed Loading into Oracle
> >>
> >
> >
> > I guess if Sagent is a requirement you should evaluate whether they are
Open
> > enough for your current and future requirements.
> >
> > -----
> >
> > But then you are comparing volkswagens and ferraris. XPS is a true
> > clustered shared nothing environment which can scale literally
infinitely -
> > ok I am getting away from myself - it can only have 32K coservers and
each
> > can only support 32 Pedabytes of data - so if you need to go beyond that
> > you'd have to pick up the phone and ask the engineers to bump the
counter -
> > the thing is that with XPS this is not a theoretical limit, performance
does
> > not drop off as you add more instances (coservers) to the cluster - it
> > continues to scale linearly. With Oracle this will only happen on a
slide
> > projector. There are only two other databases currently available which
> > have the architecture to scale in this fashion - DB2 (pdf or whatever
it's
> > called now) and Teradata. If you want to compare Oracle - you need to
be
> > comparing to Informix 9.
> >
> > Yeah sure RAC - give me a break, it's all controlled through a single
> > manager. Bottleneck city.
> >
> > Perhaps obviously you have (had) a need to justify going to Oracle over
XPS.
> > Perhaps you know Oracle better and are more comfortable with it - fine -
> > that's a good enough reason. DBA skills are more generally expensive
than
> > the database itself. Just don't try to justify it by trying to compare
an
> > apple with a mango.
> >
> > As far as features as concerned, you've mentioned quite a few that XPS
> > doesn't have, or that you haven't uncovered yet. You might also want to
> > look at the XPS 'UPDATE JOIN' and 'DELETE JOIN' statements - talk about
> > supporting a warehouse - they rock. There are others, but I digress.
> >
> > Tell you what. Send me a copy of your schema, some data and some code,
and
> > I'll put my XPS instance (P4 2.4Ghz, 2xIDE box) up against whatever you
> > have. Maybe IBM won't benchmark, but I will.
> >
> > cheers
> > j.
> >
> >
> >
> >
>
> --
>
>
> Atte,
>
>
> Jesús Antonio Santos Giraldo
> jeansagi@myrealbox.com
> jeansagi@netscape.net
>
>


sending to informix-list

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
>
>