performance of an intersect query

From: <hkrebs_at_my-deja.com>
Date: Thu, 23 Nov 2000 19:04:38 GMT
Message-ID: <8vjpo6$omv$1@nnrp1.deja.com>

servas!

i have a problem with a query that uses intersect, because sometimes the query take more than 10 seconds.

there are two tables
table 1 : (number integer, title char(255)) (about 250.000 rows) table 2 : (number integer, word char(16)) (about 7.000.000 rows)

now i'm doing the query:
SELECT number,title from table1 where number in ( SELECT number from table2 WHERE word LIKE 'KEYWORD1%' INTERSECT
SELECT number from table2 WHERE word LIKE 'KEYWORD2%');

if i run explain plan for such an query the result is:

---

---

| Operation | Name | Rows | Bytes| Cost |
Pstart| Pstop |

---

---

| SELECT STATEMENT | | 7K| 633K| 16845 |
| |
| NESTED LOOPS | | 7K| 633K| 16845 |
| |
| VIEW |VW_NSO_1 | 7K| 100K| 1026 |
| |
| INTERSECTION | | | | |
| |
| SORT UNIQUE | | 3K| 266K| 513 |
| |
| INDEX RANGE SCAN |IDX_TITEL | 3K| 266K| 209 |
| |
| SORT UNIQUE | | 3K| 266K| 513 |
| |
| INDEX RANGE SCAN |IDX_TITEL | 3K| 266K| 209 |
| |
| TABLE ACCESS BY INDEX RO|REZEPTIND | 255K| 16M| 2 |
| |
| INDEX UNIQUE SCAN |IDX_NUMME | 255K| | 1 |
| |

---

---

is there a better solution for that query?

yours

   H.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 23 2000 - 13:04:38 CST