| Index a MINUS set [message #315764] |
Tue, 22 April 2008 13:15  |
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 #315825 is a reply to message #315773] |
Tue, 22 April 2008 21:56  |
rleishman
Messages: 3728
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 |
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 |
joy_division
Messages: 4963
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 |
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 |
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: Optimize SELECT on a MINUS set [message #316041 is a reply to message #316015] |
Wed, 23 April 2008 09:58 |
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
