| This can't be right...... [message #20122] |
Tue, 30 April 2002 07:19  |
pvdalen
Messages: 14
Registered: April 2002
|
Junior Member |
|
|
Given this statement:
select * from table_A where column_1 not in
(select column_1 from table_A where column_2 in
(select column_2 from table_A where condition))
Is there an easier or more elegant way to do this? One that doesn't make three separate calls to the same table, which is HUGE? As you can hopefully see, the result depends on two different criteria from the same table.
Thanks again!
|
|
|
|
| Re: This can't be right...... [message #20124 is a reply to message #20122] |
Tue, 30 April 2002 07:39  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
You better give a code example, so that, it would be easier to analyze the problem. As I see in your example, you are checking the nonexistence of column1 by filtering the column1 in the same table, that in turn checks the existence of column2 in the same with another filter. It is not clear what you want to do exactly. If you could give a code example, I could be of some help to resolve your query.
Good luck :)
|
|
|
|
| Re: This can't be right...... [message #20131 is a reply to message #20122] |
Tue, 30 April 2002 09:34 |
pvdalen
Messages: 14
Registered: April 2002
|
Junior Member |
|
|
I have a table, Polly, that has a field, Ingrid, that refers to another row, or rows in the same table. This record is the hierarchical top, referenced in column Name as starting with "TT", while the other records are subordinate. The records exist in groups according to the valus of Ingrid. A second column, Zeke, contains a value that, for the control value of Ingrid, again having Name like 'TT%', is the standard against which all other records are matched. I'm trying to find the members of the groups of data, having the same value of Ingrid, that have a value of Zeke not equal to the value of Zeke for the record whose Name is like 'TT%'.
Whew. I hope that's clearer.
So, I started like this:
select * from Polly where Zeke not in
(select Zeke from Polly where Ingrid in
(select Ingrid from Polly where Name like 'TT%'))
but I think that's overkill. Actually, I started out trying to reference the table twice (...from polly p1, polly p2 where p1.Ingrid = p2.Ingrid...) but the system didn't seem to like this.
Any suggestions???
Thank you again.
|
|
|
|
| Re: This can't be right...... [message #20134 is a reply to message #20131] |
Tue, 30 April 2002 10:25 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
There are undoubtedly other, possibly better ways but here is one possibility that should at least make it more efficient - and I believe it is the functional equivalent:
14:15:48 ==> select * from polly
14:16:07 2 minus
14:16:07 3 (select * from polly
14:16:07 4 where Ingrid in (select Ingrid from polly where Name like 'TT%'))
14:16:07 5 ;
ZEKE INGRID NAME
--------- --------- --------------------
5 7 RR
6 8 RR
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 MINUS
2 1 SORT (UNIQUE)
3 2 TABLE ACCESS (FULL) OF 'POLLY'
4 1 SORT (UNIQUE)
5 4 MERGE JOIN
6 5 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'POLLY'
8 5 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'POLLY'
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
3 consistent gets
0 physical reads
0 redo size
273 bytes sent via SQL*Net to client
236 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
AS OPPOSED TO YOURS:
14:16:14 ==> select * from polly where zeke not in
14:16:24 2 (select zeke from polly where Ingrid in
14:16:24 3 (select Ingrid from polly where Name like 'TT%'))
14:16:24 4 ;
ZEKE INGRID NAME
--------- --------- --------------------
5 7 RR
6 8 RR
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'POLLY'
3 1 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'POLLY'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'POLLY'
Statistics
----------------------------------------------------------
0 recursive calls
44 db block gets
13 consistent gets
0 physical reads
0 redo size
273 bytes sent via SQL*Net to client
248 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
2 rows processed
14:16:32 ==> 12 db block gets vs. 44; 3 consistent gets vs. 13.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
