MUCH faster (1.8s), added WHERE T1.A IS NOT NULL to the rows to exclude
the many nulls. Thanks.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Steve Wiser
Sent: Tuesday, November 15, 2005 12:44 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Long query help

Not sure if this is faster:

select *
from tbl t1 join tbl t2 on t1.a = t2.b
union
select *
from tbl t1 join tbl t2 on t1.b = t2.a

-steve

On Tue, 2005-11-15 at 11:23 -0500, 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).
>
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
>
>
>
>


[Non-text portions of this message have been removed]



------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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