Home » SQL & PL/SQL » SQL & PL/SQL » Index a MINUS set
Index a MINUS set [message #315764] Tue, 22 April 2008 13:15 Go to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Any suggestions on the best way to index the following:

SELECT A.*, B.*
FROM ( SELECT * FROM T1 MINUS T3) A,
     ( SELECT * FROM T2 MINUS T3) B
WHERE A.C1 = B.C1;


Since there is no table "A" or "B", what should be indexed? Does indexing tables T1 and T2 help here?

Drew
Re: Index a MINUS set [message #315773 is a reply to message #315764] Tue, 22 April 2008 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them.

First you should post valid SQL.

Regards
Michel
Re: Index a MINUS set [message #315825 is a reply to message #315773] Tue, 22 April 2008 21:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Let's assume that you had posted syntactiaclly correct SQL.

A MINUS without constraining WHERE clauses on either set will perform a full scan of each set. Indexes typically won't help, especially with a SELECT * query.

The join will be performed as a HASH join - you don't need an index for that.

Ross Leishman

[Updated on: Tue, 22 April 2008 21:57]

Report message to a moderator

Re: Index a MINUS set [message #315997 is a reply to message #315764] Wed, 23 April 2008 08:00 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Well, that's the SQL that I'm actually testing with, and it works. What is not "valid" about it?

I understand how the MINUS is handled. My question was, is there a way to optimize the SELECT from the resulting set (A.*, B.*) either with indexes on the original tables, or by some other means.

What I'm leaning towards is storing the resulting set in a new table, then indexing that new table. Is this necessary?
Re: Index a MINUS set [message #315998 is a reply to message #315997] Wed, 23 April 2008 08:06 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
drewsmith70 wrote on Wed, 23 April 2008 09:00
Well, that's the SQL that I'm actually testing with, and it works. What is not "valid" about it??

orcl10g FOO>l
  1  SELECT A.*, B.*
  2  FROM ( SELECT * FROM T1 MINUS T3) A,
  3       ( SELECT * FROM T2 MINUS T3) B
  4* WHERE A.C1 = B.C1
rsbgcdd1 GSA>/
FROM ( SELECT * FROM T1 MINUS T3) A,
                              *
ERROR at line 2:
ORA-00928: missing SELECT keyword

[Updated on: Wed, 23 April 2008 08:07]

Report message to a moderator

Re: Index a MINUS set [message #316013 is a reply to message #315764] Wed, 23 April 2008 08:54 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Ahh - my apologies.

As far as posting rules, I read them, and I answered the three questions YES YES YES, which is why I posted here. I guess I will re-post in NEWBIE since these answers are apparently incorrect.
Optimize SELECT on a MINUS set [message #316015 is a reply to message #315764] Wed, 23 April 2008 08:58 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Any suggestions on the best way to index the following:

SELECT A.*, B.*
FROM ( SELECT * FROM T1 MINUS SELECT * FROM T3) A,
     ( SELECT * FROM T2 MINUS SELECT * FROM T3) B
WHERE A.C1 = B.C1;


Is there a way to optimize the SELECT from the resulting set (A.*, B.*) either with indexes on the original tables, or by some other means?

What I'm leaning towards is storing the resulting set in a new table, then indexing that new table. Is this necessary?
Re: Index a MINUS set [message #316016 is a reply to message #316013] Wed, 23 April 2008 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As far as posting rules, I read them, and I answered the three questions YES YES YES

You are lying to yourself, you are NOT an expert.
The proof:
Quote:
Well, that's the SQL that I'm actually testing with, and it works. What is not "valid" about it?

And then joy_division's answer.

So now post in Newbie forum.

Regards
Michel
Re: Index a MINUS set [message #316027 is a reply to message #315764] Wed, 23 April 2008 09:07 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
You're right...I'm not a typing expert ...

Also, I just got a PM to not "cross post" - which I did per your suggestion - *sigh*.
Re: Index a MINUS set [message #316030 is a reply to message #315764] Wed, 23 April 2008 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>You're right...I'm not a typing expert ...

CUT & PASTE are your friends, but only when you use them!
Re: Optimize SELECT on a MINUS set [message #316041 is a reply to message #316015] Wed, 23 April 2008 09:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the tables change infrequently, you could create a materialized view on the Minus, and index this.
Otherwise, you're just looking at Full Table Scans and Hash Joins.
Re: Optimize SELECT on a MINUS set [message #316147 is a reply to message #316041] Wed, 23 April 2008 23:11 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT T1.*, T2.*
FROM   T1
JOIN   T2 ON T1.C1 = T2.C1
WHERE NOT EXISTS (
    SELECT 1
    FROM   T3
    WHERE  T3.C1 = T1.C1
    AND    T3.C2 = T1.C2
    AND    ...
)
WHERE NOT EXISTS (
    SELECT 1
    FROM   T3
    WHERE  T3.C1 = T2.C1
    AND    T3.C2 = T3.C2
    AND    ...
)


Be careful with NULLs though. This will act differently to MINUS if any of the columns are NULL.

You could index T1(C1), T2(C2), and T3(C1, C2, ...).

Note that indexed access will probably ONLY be faster if there are a large number of rows in T1 that have no matching row in T2 or vice-versa.

For what it's worth, I don't regard this as a Newbie question; however I would take care to post syntactically correct SQL if you want people to take you seriously.

Ross Leishman
Previous Topic: cost of balance items recently purchased
Next Topic: Help in NOT EXISTS
Goto Forum:
  


Current Time: Thu Dec 08 10:39:44 CST 2016

Total time taken to generate the page: 0.06830 seconds