Subject | Re: Max length of SQL statement |
---|---|
Author | Stephen Boyd |
Post date | 2007-06-21T17:54:53Z |
I finally figured it out.
If I have 2 tables:
CREATE TABLE DOCUMENTS (
DOC_NUM INTEGER NOT NULL,
...,
PRIMARY KEY (DOC_NUM) );
and
CREATE TABLE KEYWORDS (
KEYWORD VARCHAR(50) NOT NULL,
DOC_NUM INTEGER NOT NULL,
PRIMARY KEY (KEYWORD, DOC_NUM) );
CREATE INDEX DOC_KEY ON KEYWORDS (DOC_NUM, KEYWORD);
If I want all documents containing any of the specified keywords (KEY1
or KEY2) I do this:
SELECT Z.*
FROM KEYWORDS A
LEFT JOIN DOCUMENTS Z ON Z.DOC_NUM = A.DOC_NUM
WHERE A.KEYWORD = 'KEY1' OR
A.KEYWORD = 'KEY2' ...
Simple and obvious. If I want all documents containing all of the
specified keywords (KEY1 and KEY2) I do this:
SELECT Z.*
FROM KEYWORDS A
INNER JOIN KEYWORDS B ON B.DOC_NUM = A.DOC_NUM AND
B.KEYWORD = 'KEY1'
INNER JOIN KEYWORDS C ON C.DOC_NUM = A.DOC_NUM AND
B.KEYWORD = 'KEY2'
LEFT JOIN DOCUMENTS Z ON Z.DOC_NUM = A.DOC_NUM
WHERE A.KEYWORD = 'KEY1' OR
A.KEYWORD = 'KEY2' ...
Not nearly so obvious, but this gives me the result I need without
complete table scans and it is reasonably fast.
If I have 2 tables:
CREATE TABLE DOCUMENTS (
DOC_NUM INTEGER NOT NULL,
...,
PRIMARY KEY (DOC_NUM) );
and
CREATE TABLE KEYWORDS (
KEYWORD VARCHAR(50) NOT NULL,
DOC_NUM INTEGER NOT NULL,
PRIMARY KEY (KEYWORD, DOC_NUM) );
CREATE INDEX DOC_KEY ON KEYWORDS (DOC_NUM, KEYWORD);
If I want all documents containing any of the specified keywords (KEY1
or KEY2) I do this:
SELECT Z.*
FROM KEYWORDS A
LEFT JOIN DOCUMENTS Z ON Z.DOC_NUM = A.DOC_NUM
WHERE A.KEYWORD = 'KEY1' OR
A.KEYWORD = 'KEY2' ...
Simple and obvious. If I want all documents containing all of the
specified keywords (KEY1 and KEY2) I do this:
SELECT Z.*
FROM KEYWORDS A
INNER JOIN KEYWORDS B ON B.DOC_NUM = A.DOC_NUM AND
B.KEYWORD = 'KEY1'
INNER JOIN KEYWORDS C ON C.DOC_NUM = A.DOC_NUM AND
B.KEYWORD = 'KEY2'
LEFT JOIN DOCUMENTS Z ON Z.DOC_NUM = A.DOC_NUM
WHERE A.KEYWORD = 'KEY1' OR
A.KEYWORD = 'KEY2' ...
Not nearly so obvious, but this gives me the result I need without
complete table scans and it is reasonably fast.