--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 10:30 AM 13/07/2006, you wrote:
> >Hi all,
> >
> >I have a table which acts like a term-list defined index
> >
> >create table jclone_occurrence_pmid(
> > pmid numeric(10,0) not null,
> > clone char(128) not null);
> >
> >There are 18 million rows of records in this table.
> >My task now is to answer this question: what is the set of pmids that
> >has clone A and clone B?
> >
> >So I ran a query:
> >select pmid from jclone_occurrence_pmid where clone = 'ACT' and pmid
> >in (select pmid from jclone_occurrence_pmid where clone = 'ACE')
> >
> >This query took me 2 hours to run. The problem is, I have 13 million
> >of these queries, means it will take 3000 years to run. clearly
> >unacceptable.
> >
> >So, I thought that part of the reason was that the query had to do a
> >self join (please tell me if I am wrong)
>
> You need either:
>
> select
> jcop1.pmid from jclone_occurrence_pmid jcop1
> join jclone_occurrence_pmid jcop2
> on jcop2.pmid = jcop1.pmid
> where jcop2.clone = 'ACE'
>
> or
>
> select
> jcop1.pmid from jclone_occurrence_pmid jcop1
> where
> jcop1.clone = 'ACT'
> and exists
> (select 1 from jclone_occurrence_pmid jcop2
> where jcop2.pmid = jcop1.pmid
> and jcop2.clone = 'ACE')
>
>

Adding an index on (clone,pmid): "create index
jclone_occur_pmid_clonepmid on jclone_occurrence_pmid (clone, pmid)"
changed the plan from
PLAN (JCOP2 INDEX (JCLONE_OCCUR_PMID_CLONE))
PLAN (JCOP1 INDEX (JCLONE_OCCUR_PMID_CLONE))
to
PLAN (JCOP2 INDEX (JCLONE_OCCUR_PMID_CLONEPMID))
PLAN (JCOP1 INDEX (JCLONE_OCCUR_PMID_CLONE))

and the query "select jcop1.pmid from jclone_occurrence_pmid jcop1
where jcop1.clone = 'ACT' and exists (select 1 from
jclone_occurrence_pmid jcop2 where jcop2.pmid = jcop1.pmid and
jcop2.clone = 'ACE')" takes 6 seconds to run instead of 2 hours.

Thanks
Maurice