Re: slow sql help

ewong_at_mail.com (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.

Don't assume. If you do both selects separately, how long does each take and what is the execution path of each? With the minus, what is the execution path? What indices are defined? What fraction of batches are Internal? How many rows do you expect to be returned?

> Is there
> any other way to write this query? Thanks.

Of course there are. Many. But the real question is whether any of them will be faster. You haven't given us enough information to know.

select id, min(project) mn, max(project) mx from batch group by id having mn like 'Internal%' and mx like 'Internal%';

select distinct a.id from batch a where project like 'Internal%' and   not exists (

                select 1 from batch b where a.id=b.id and
                    b.project not like  'Internal%'
             );

>
> SQL> select distinct id from batch where project like 'Internal%'
> minus
> select distinct id from batch where project not like 'Internal%';

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB