Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> slow sql help

slow sql help

From: Ed Wong <ewong_at_mail.com>
Date: 12 Jul 2004 16:50:07 -0700
Message-ID: <a5ae1554.0407121550.3c2e97b7@posting.google.com>


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
...
Received on Mon Jul 12 2004 - 18:50:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US