As you said, there are only few values not null, so first, i think you
should cut off
null rows, then ask for join. optimizer "should" be told to make such
steps : 1. filter nulls 2. join. (ps. 2.1. t1.a = t2.b is same as t2.a
=t1.b, because same table)
So, your query ( or its variations) would be
1.

Select
*
From
tbl t1
Where
t1.a is not null and t1.b is not null
and
exists (
select 1
from tbl t2
where (t2.a = t1.b
/////// and t2.a is not null and t1.b is not null) /// this is not found already as nulls ommited from first condition
/////// or (t2.b = t1.a and t2.b is not null and t1.a is not null) ////// if t2.a = t1.b, so in reverse would be found anyway
)
2. I would make join, and there wouldnt be any overhead iterations with "where exists"
select * from tbl t1
inner join tbl t2
on
t1.a is not null
and
t1.b is not null
and
t2.a = t1.b
Try several variations of it.(as a and b has different indices, "t1.a is not null and t1.b is not null" may not be fast enaugh,you might want join table for 3 time to speed up filtering of nulls)
Adomas



Rick Debay wrote:

>I need to determine what rows match on two columns within a table.
>The two columns have non-unique indexes. The query I'm using is:
>
>Select
> *
>From
> tbl t1
>Where
> exists (
> select 1
> from tbl t2
> where (t2.a = t1.b and t2.a is not null and t1.b is not null)
> or (t2.b = t1.a and t2.b is not null and t1.a is not null)
> )
>
>The table has 120,045 rows and the query is taking hours. The plan is:
>
>PLAN (T1 NATURAL)
>PLAN (T2 INDEX (I_TBL_A,I_TBL_B))
>
>The duplicates are all null values, there are only a dozen or so
>non-null duplicates
>
> Index I_TBL_B (1)
> Depth: 2, leaf buckets: 189, nodes: 120045
> Average data length: 0.00, total dup: 98543, max dup: 98498
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 171
> 60 - 79% = 13
> 80 - 99% = 5
>
> Index I_TBL_A (2)
> Depth: 2, leaf buckets: 252, nodes: 120045
> Average data length: 4.00, total dup: 17384, max dup: 17371
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 178
> 60 - 79% = 51
> 80 - 99% = 23
>
>The only change I could come up with would be to append the primary key
>to the index, but I doubt it would help (and I can't interrupt the query
>to test it).
>
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>