Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> slow sql help
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