|Subject||Re: Max length of SQL statement|
>In that case, maybe someone can show me a better way to do what I am
> The IN limit is reached before the maximum SQL size limit.
> It's about 1500 items or so.
trying to do.
I want to be able to do a quasi full text search on a group of
documents. I have created a simple table to hold the keywords and
their related document numbers.
CREATE TABLE KEYWORDS (
PRIMARY KEY (KEYWORD, DOC_NUM));
CREATE INDEX DOC_IDX ON KEYWORDS (DOC_NUM);
I have tried various combinations of clauses on SELECT statements but
I haven't been able to find a combination that gives me what I want
in a reasonable length of time. The most promising, in terms of
providing the desired functionality, seems to be:
SELECT * FROM DOCUMENTS A
INNER JOIN KEYWORDS B ON B.DOC_NUM = A.DOC_NUM
WHERE EXISTS (SELECT DOC_NUM FROM KEYWORDS WHERE KEYWORD = 'KEY1')
EXISTS (SELECT DOC_NUM FROM KEYWORDS WHERE KEYWORD = 'KEY2')
and so on. This will work for AND and OR relationships bewteen the
keywords but is actually slower that just scanning the full text of
all of the documents looking for the desired words.
What I am doing now is simply scanning the KEYWORD table looking for
the document numbers for the matching documents. For each keyword I
build a bitmap in memory and then just AND or OR the bitmaps to get
the desired list of document numbers. This is actually quite fast
but I run into the 1500 document limit.
Any suggestions are welcome.