Re: slow sql help

Ed Wong wrote:
> I have this "batch" table with over 10m rows. Each id has multiple
> batches. (see below)
>
> SQL> select * from batch;
> ID BATCH PROJECT
> -------------------- ---------- --------------------
> CORP-0001 1 Internal A
> CORP-0001 2 Internal B
> CORP-0001 3 Internal B
> CORP-0002 1 Internal A
> CORP-0002 2 External A
> ...
>
>
> I'd like to select all the ID in which ALL batches of the ids have
> project_name like 'Internal%'. For example, if there are total of
> three batches of an id, only if all the three batches LIKE "Internal%"
> do I want the id to be returned by the query. I tried the query below
> and it's extremely slow because of the NOT LIKE I assume. Is there
> any other way to write this query? Thanks.
>
>
> SQL> select distinct id from batch where project like 'Internal%'
> minus
> select distinct id from batch where project not like 'Internal%';
> ID
> --------------------
> CORP-0001
> ...

If column batch.project literally is either "Internal something" or "External something", I reckon it would be faster to just check the first char. I.e. substr(project, 1, 1)='I'

As for a rewrite on the sql, try

   select distinct id from batch b where substr(project, 1, 1)='I'    and not exists (
     select 0 from batch where b.id=id and not substr(project, 1, 1)='I'    )

/Bent Received on Mon Jul 12 2004 - 20:16:11 CDT