Minus set operator
Parameshwar Pathri asked whether Informix 9.x supports the MINUS set operator. Art Kagel said it does not and suggested SELECT ... WHERE NOT EXISTS (subselect) instead. cberner99 said MINUS performed far better than NOT EXISTS on Informix 8.30 and Oracle 8i in 2002. Jack Parker replied that NOT EXISTS is a hash join and the engine must be set up properly for it.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi All,
Is there minus set operator in informix 9.x. I want to do a
select * from x minus select * from y. is this allowed in informix 9.3. if nothow can we achieve it.
Bye.
Like everything else in SQL land, a MINUS can be performed a different way,
using a NOT EXISTS clause:
SELECT * from x
WHERE NO EXISTS (
SELECT 1 FROM y WHERE x.key1 = y.key1 and ...
);
Informix does not support MINUS directly.
Art S. Kagel
----- Original Message -----
From: Parameshwar.... <pcdudyala@yahoo.com>
At: 2/23 3:28
> Hi All,
> Is there minus set operator in informix 9.x. I want to do a
> select * from x minus select * from y. is this allowed in informix 9.3. if
not> how can we achieve it.
>
>
> Bye.
I just wanted to Add. A coworker (Zafar) and I found great performance gains using the MINUS function instead of NO EXISTS, it was day and night. Using the MINUS our performance was 80 percent or higher. The NO EXISTS performed very poorly with both versions, Informix 8.30 and Oracle 8i, this was back in 2002. As soon as Zafar, modified the code to use MINUS we were amazed of the performance gain. I then put in a request to Informix (2002) to have a MINUS function to be added within the Informix sql set, that did not happen.
A 'not exists' is a hash join. You have to have your engine set up properly to perform a hash join quickly. Not sure I remember how the minus is performed, although Oracle is sure a fan of indexed reads. j. ----- Original Message ----- From: <cberner99@juno.com> To: <ids@iiug.org> Sent: Thursday, February 24, 2005 10:53 AM Subject: Re: Minus set operator [4336] > > I just wanted to Add. > > A coworker (Zafar) and I found great performance gains using the MINUS function instead of NO EXISTS, it was day and night. Using the MINUS our performance was 80 percent or higher. The NO EXISTS performed very poorly with both versions, Informix 8.30 and Oracle 8i, this was back in 2002. As soon as Zafar, modified the code to use MINUS we were amazed of the performance gain. I then put in a request to Informix (2002) to have a MINUS function to be added within the Informix sql set, that did not happen. >