Home » Server Options » Text & interMedia » SQL with CONTAINS problem
| SQL with CONTAINS problem [message #194041] |
Wed, 20 September 2006 09:22  |
inapal
Messages: 14
Registered: September 2006
|
Junior Member |
|
|
Hi all,
I've a problem with the results returned by a Query aganst an Oracle 9i DB. The statement is:
SELECT
Documentos.DocID AS Documentos_DocID, Temas_Docs.TemaID,
Temas_Docs.DocID AS Temas_Docs_DocID, TiposDoc.Denominacion AS TD_Denom
FROM Documentos
INNER JOIN Temas_Docs ON (Documentos.DocID = Temas_Docs.DocID)
INNER JOIN TiposDoc ON (Documentos.TipoDocID = TiposDoc.TipoDocID)
WHERE
Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
AND
(CONTAINS(Documentos.FT, '(emporio) WITHIN XML') > 0
OR CONTAINS(Documentos.DocOri, 'emporio') > 0)
The obtained result is the following, and is not correct ( I will explain myself below)
Documentos_DocID TEMAID Temas_Docs_DocID TD_DENOM
------------------------------------------------------------
969 K165 968 Oficio
969 K166 969 Oficio
If you take a look at the SQL Sentence you'll see that the column "Documentos_DocID" should be equal to the column "Temas_Docs_DocID", this is established on the first INNER JOIN. Nevertheless, on the first row of the result set, there are different values (969 y 968).
Here goes some variations I was testing, which make the Query work. If i take out the column "TD_Denom" from the output, even if I keep the INNER JOIN to the table "TipoDoc" as follows:
SELECT
Documentos.DocID AS Documentos_DocID, Temas_Docs.TemaID,
Temas_Docs.DocID AS Temas_Docs_DocID
FROM Documentos
INNER JOIN Temas_Docs ON (Documentos.DocID = Temas_Docs.DocID)
INNER JOIN TiposDoc ON (Documentos.TipoDocID = TiposDoc.TipoDocID)
WHERE
Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
AND
(CONTAINS(Documentos.FT, '(emporio) WITHIN XML') > 0
OR CONTAINS(Documentos.DocOri, 'emporio') > 0)
The result is the expected one:
Documentos_DocID TEMAID Temas_Docs_DocID
---------------------------------------------
969 K166 969
Also, if I put back the column "TD_Denom" to the output, and I remove part of the full-text search, like in the following example:
SELECT
Documentos.DocID AS Documentos_DocID, Temas_Docs.TemaID,
Temas_Docs.DocID AS Temas_Docs_DocID, TiposDoc.Denominacion AS TD_Denom
FROM Documentos
INNER JOIN Temas_Docs ON (Documentos.DocID = Temas_Docs.DocID)
INNER JOIN TiposDoc ON (Documentos.TipoDocID = TiposDoc.TipoDocID)
WHERE
Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
AND
(CONTAINS(Documentos.FT, '(emporio) WITHIN XML') > 0)
The result is again correct .
Documentos_DocID TEMAID Temas_Docs_DocID TD_DENOM
------------------------------------------------------------
969 K166 969 Oficio
The problem show up with the 1st case, which returns rows without taking into account the all query filters, what is what I need.
Any ideas?
Thank you in advance
Ignacio.
[Updated on: Wed, 20 September 2006 09:27] Report message to a moderator |
|
|
|
| Re: SQL with CONTAINS problem [message #194065 is a reply to message #194041] |
Wed, 20 September 2006 11:34  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I am unable to reproduce your undesirable results. Can you provide a complete script that reproduces those results? What happens if you add parentheses around your join conditions or use the old join syntax, as shown below?
-- parentheses around join conditions:
SELECT Documentos.DocID AS Documentos_DocID,
Temas_Docs.TemaID,
Temas_Docs.DocID AS Temas_Docs_DocID,
TiposDoc.Denominacion AS TD_Denom
FROM ((Documentos
INNER JOIN Temas_Docs ON (Documentos.DocID = Temas_Docs.DocID))
INNER JOIN TiposDoc ON (Documentos.TipoDocID = TiposDoc.TipoDocID))
WHERE Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
AND (CONTAINS (Documentos.FT, '(emporio) WITHIN XML') > 0
OR CONTAINS (Documentos.DocOri, 'emporio') > 0)
/
-- old join syntax:
SELECT Documentos.DocID AS Documentos_DocID,
Temas_Docs.TemaID,
Temas_Docs.DocID AS Temas_Docs_DocID,
TiposDoc.Denominacion AS TD_Denom
FROM Documentos, Temas_Docs, TiposDoc
WHERE Documentos.DocID = Temas_Docs.DocID
AND Documentos.TipoDocID = TiposDoc.TipoDocID
AND Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
AND (CONTAINS (Documentos.FT, '(emporio) WITHIN XML') > 0
OR CONTAINS (Documentos.DocOri, 'emporio') > 0)
/
|
|
|
|
| Re: SQL with CONTAINS problem [message #194080 is a reply to message #194041] |
Wed, 20 September 2006 13:54 |
inapal
Messages: 14
Registered: September 2006
|
Junior Member |
|
|
I've checked your suggestions, but I've gotten same wrong results.
I send following script to create tables, relations and indexes:
CREATE TABLE Tiposdoc
(Tipodocid VARCHAR2(15) NOT NULL,
Denominacion VARCHAR2(50),
Denom_Plural VARCHAR2(50),
CONSTRAINT TIPOSDOC_PK PRIMARY KEY (Tipodocid) USING INDEX)
/
CREATE TABLE Temas
(Temaid VARCHAR2(15) NOT NULL,
Padreid VARCHAR2(15) NOT NULL,
Ordenh NUMBER(38,0) NOT NULL,
Descripcion VARCHAR2(50),
Tipo CHAR(1) NOT NULL,
Verenweb CHAR(1) DEFAULT 'N' NOT NULL,
Acumulanivel CHAR(1) DEFAULT 'N' NOT NULL,
CONSTRAINT TEMAS_PK PRIMARY KEY (Temaid) USING INDEX)
/
CREATE TABLE Temas_Docs
(Temaid VARCHAR2(15) NOT NULL,
Docid NUMBER(38,0) NOT NULL,
CONSTRAINT TEMAS_DOCS_PK PRIMARY KEY (Temaid, Docid) USING INDEX)
/
CREATE TABLE Documentos
(Docid NUMBER(38,0) NOT NULL,
Tipodocid VARCHAR2(15),
Nrodoc VARCHAR2(20),
Nroexpe VARCHAR2(20),
Detalle CLOB,
Fecha DATE,
Enweb CHAR(1) DEFAULT 'N' NOT NULL,
Esnovedad CHAR(1) DEFAULT 'N' NOT NULL,
Docftype VARCHAR2(10),
Docfname VARCHAR2(255),
Docori BLOB,
Docpdf BLOB,
Estado CHAR(1) DEFAULT 'P' NOT NULL,
Pubuser VARCHAR2(15) NOT NULL,
Pubfecha DATE DEFAULT CURRENT_DATE NOT NULL,
Ft CLOB,
CONSTRAINT DOCUMENTOS_PK PRIMARY KEY (Docid) USING INDEX)
/
ALTER TABLE Documentos
ADD CONSTRAINT Tiposdoc_Documentos_Fk1 FOREIGN KEY (Tipodocid) REFERENCES Tiposdoc (Tipodocid)
/
ALTER TABLE Temas_Docs
ADD CONSTRAINT Documentos_Temas_Docs_Fk1 FOREIGN KEY (Docid) REFERENCES Documentos (Docid)
/
ALTER TABLE Temas_Docs
ADD CONSTRAINT Temas_Temas_Docs_Fk1 FOREIGN KEY (Temaid) REFERENCES Temas (Temaid)
/
CREATE INDEX DOCUMENTOS_FT
ON DOCUMENTOS(FT) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('SECTION GROUP CTXSYS.AUTO_SECTION_GROUP')
/
CREATE INDEX DOCUMENTOS_BINARIO
ON DOCUMENTOS(DOCORI) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS (' FILTER CTXSYS.INSO_FILTER')
/
CREATE TRIGGER Documentos_Ft
BEFORE INSERT OR UPDATE ON Documentos
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
len INTEGER;
BEGIN
:NEW.FT := to_clob('<XML>');
IF :NEW.NroDoc IS NOT NULL THEN
:NEW.FT := :NEW.FT || to_clob('<NRODOC>' || :NEW.NroDoc || '</NRODOC>');
END IF;
IF :NEW.NroExpe IS NOT NULL THEN
:NEW.FT := :NEW.FT || to_clob('<NROEXPE>' || :NEW.NroExpe || '</NROEXPE>');
END IF;
len := dbms_lob.getlength(:NEW.Detalle);
IF len IS NOT NULL THEN
:NEW.FT := :NEW.FT || to_clob('<DETALLE>');
dbms_lob.append(:NEW.FT, :NEW.Detalle);
:NEW.FT := :NEW.FT || to_clob('</DETALLE>');
END IF;
:NEW.FT := :NEW.FT || to_clob('</XML>');
END;
/
I've attached "DataScript.SQL" file, to insert data to these tables.
Heed that Insert statements on "Documentos" table do not include CLOB and BLOB type columns (FT, DOCORI and DOCPDF columns), because I can't export them on this mode.
Some comments:
1. There are two full-text indexes, on "Documentos.FT" AND "Documentos.DocOri" fields.
2. "Documentos.FT" is a CLOB datatype field, and it contains some XML data, filled by "Documentos_FT" trigger on inserts and updates operations.
3. "Documentos.DocOri" is a BLOB datatype filed, and it contains binary type documentos (doc, xls, pdf), using INSO filters.
Thank you,
Ignacio.
[Updated on: Wed, 20 September 2006 14:23] Report message to a moderator |
|
|
|
| Re: SQL with CONTAINS problem [message #194366 is a reply to message #194080] |
Thu, 21 September 2006 13:18 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I found that the bug is apparently dependent upon the execution plan. It seems to produce correct results with a hash join, but not with other plans. Notice the changes after gathering statistics or using a use_hash hint below. Since hints may be ignored, using a use_hash hint is not reliable. I have encountered similar things whenever there are multiple contains clauses in one query. The only workaround that seemed to work consistently, regardless of execution plan, is to use an inline view, as shown below, but I don't know if there aren't cases where that will also fail, and I don't know how it may affect performance. I tested with the full tables, trigger, and data that you provided, but the following is a much simpler reproduction, in case anyone else wants to experiment with it.
scott@ORA92> SELECT banner FROM v$version
2 /
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
scott@ORA92> CREATE TABLE Tiposdoc
2 (Tipodocid VARCHAR2(15) NOT NULL,
3 Denominacion VARCHAR2(50),
4 CONSTRAINT TIPOSDOC_PK PRIMARY KEY (Tipodocid) USING INDEX)
5 /
Table created.
scott@ORA92> CREATE TABLE Documentos
2 (Docid NUMBER(38,0) NOT NULL,
3 Tipodocid VARCHAR2(15),
4 Docori BLOB,
5 Ft CLOB,
6 CONSTRAINT DOCUMENTOS_PK PRIMARY KEY (Docid) USING INDEX,
7 CONSTRAINT Tiposdoc_Documentos_Fk1 FOREIGN KEY (Tipodocid)
8 REFERENCES Tiposdoc (Tipodocid))
9 /
Table created.
scott@ORA92> CREATE TABLE Temas_Docs
2 (Temaid VARCHAR2(15) NOT NULL,
3 Docid NUMBER(38,0) NOT NULL,
4 CONSTRAINT TEMAS_DOCS_PK PRIMARY KEY (Temaid, Docid) USING INDEX,
5 CONSTRAINT Documentos_Temas_Docs_Fk1 FOREIGN KEY (Docid)
6 REFERENCES Documentos (Docid))
7 /
Table created.
scott@ORA92> INSERT ALL
2 INTO "TIPOSDOC" VALUES ('OF','Oficio')
3 INTO "TIPOSDOC" VALUES ('RES','Resolución')
4 SELECT * FROM DUAL
5 /
2 rows created.
scott@ORA92> INSERT INTO "DOCUMENTOS" ("DOCID","TIPODOCID", docori)
2 VALUES (968,'RES',NULL)
3 /
1 row created.
scott@ORA92> INSERT INTO "DOCUMENTOS" ("DOCID","TIPODOCID", docori)
2 VALUES (969,'OF',UTL_RAW.CAST_TO_RAW ('emporio'))
3 /
1 row created.
scott@ORA92> INSERT ALL
2 INTO "TEMAS_DOCS" VALUES ('K165',968)
3 INTO "TEMAS_DOCS" VALUES ('K166',969)
4 SELECT * FROM DUAL
5 /
2 rows created.
scott@ORA92> CREATE INDEX DOCUMENTOS_FT
2 ON DOCUMENTOS(FT) INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS ('SECTION GROUP CTXSYS.AUTO_SECTION_GROUP')
4 /
Index created.
scott@ORA92> CREATE INDEX DOCUMENTOS_BINARIO
2 ON DOCUMENTOS(DOCORI) INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS (' FILTER CTXSYS.INSO_FILTER')
4 /
Index created.
scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> -- reproduction of bug without statistics:
scott@ORA92> SELECT Documentos.DocID AS Documentos_DocID,
2 Temas_Docs.TemaID,
3 Temas_Docs.DocID AS Temas_Docs_DocID,
4 TiposDoc.Denominacion AS TD_Denom
5 FROM Documentos
6 INNER JOIN Temas_Docs ON (Documentos.DocID = Temas_Docs.DocID)
7 INNER JOIN TiposDoc ON (Documentos.TipoDocID = TiposDoc.TipoDocID)
8 WHERE Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
9 AND (CONTAINS (Documentos.FT, '(emporio) WITHIN XML') > 0
10 OR CONTAINS (Documentos.DocOri, 'emporio') > 0)
11 /
DOCUMENTOS_DOCID TEMAID TEMAS_DOCS_DOCID TD_DENOM
---------------- --------------- ---------------- --------------------------------------------------
969 K165 968 Oficio
969 K166 969 Oficio
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=4091)
1 0 NESTED LOOPS (Cost=5 Card=1 Bytes=4091)
2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=4055)
3 2 INLIST ITERATOR
4 3 INDEX (RANGE SCAN) OF 'TEMAS_DOCS_PK' (UNIQUE) (Cost=2 Card=1 Bytes=22)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTOS' (Cost=3 Card=1 Bytes=4033)
6 5 BITMAP CONVERSION (TO ROWIDS)
7 6 BITMAP OR
8 7 BITMAP CONVERSION (FROM ROWIDS)
9 8 SORT (ORDER BY)
10 9 DOMAIN INDEX OF 'DOCUMENTOS_FT' (Cost=0 Card=82)
11 7 BITMAP CONVERSION (FROM ROWIDS)
12 11 SORT (ORDER BY)
13 12 DOMAIN INDEX OF 'DOCUMENTOS_BINARIO' (Cost=0 Card=82)
14 1 TABLE ACCESS (BY INDEX ROWID) OF 'TIPOSDOC' (Cost=1 Card=82 Bytes=2952)
15 14 INDEX (UNIQUE SCAN) OF 'TIPOSDOC_PK' (UNIQUE)
scott@ORA92> -- use_hash hint without statistics:
scott@ORA92> SELECT /*+ USE_HASH(documentos temas_docs) */
2 Documentos.DocID AS Documentos_DocID,
3 Temas_Docs.TemaID,
4 Temas_Docs.DocID AS Temas_Docs_DocID,
5 TiposDoc.Denominacion AS TD_Denom
6 FROM Documentos
7 INNER JOIN Temas_Docs ON (Documentos.DocID = Temas_Docs.DocID)
8 INNER JOIN TiposDoc ON (Documentos.TipoDocID = TiposDoc.TipoDocID)
9 WHERE Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
10 AND (CONTAINS (Documentos.FT, '(emporio) WITHIN XML') > 0
11 OR CONTAINS (Documentos.DocOri, 'emporio') > 0)
12 /
DOCUMENTOS_DOCID TEMAID TEMAS_DOCS_DOCID TD_DENOM
---------------- --------------- ---------------- --------------------------------------------------
969 K166 969 Oficio
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=4091)
1 0 NESTED LOOPS (Cost=6 Card=1 Bytes=4091)
2 1 HASH JOIN (Cost=5 Card=1 Bytes=4055)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTOS' (Cost=2 Card=1 Bytes=4033)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP OR
6 5 BITMAP CONVERSION (FROM ROWIDS)
7 6 SORT (ORDER BY)
8 7 DOMAIN INDEX OF 'DOCUMENTOS_FT' (Cost=0)
9 5 BITMAP CONVERSION (FROM ROWIDS)
10 9 SORT (ORDER BY)
11 10 DOMAIN INDEX OF 'DOCUMENTOS_BINARIO' (Cost=0)
12 2 INLIST ITERATOR
13 12 INDEX (RANGE SCAN) OF 'TEMAS_DOCS_PK' (UNIQUE) (Cost=2 Card=1 Bytes=22)
14 1 TABLE ACCESS (BY INDEX ROWID) OF 'TIPOSDOC' (Cost=1 Card=82 Bytes=2952)
15 14 INDEX (UNIQUE SCAN) OF 'TIPOSDOC_PK' (UNIQUE)
scott@ORA92> -- correct results using inline view wihtout statistics:
scott@ORA92> SELECT Docs.DocID AS Documentos_DocID,
2 Temas_Docs.TemaID,
3 Temas_Docs.DocID AS Temas_Docs_DocID,
4 TiposDoc.Denominacion AS TD_Denom
5 FROM (SELECT DocID, TipoDocID
6 FROM Documentos
7 WHERE CONTAINS (Documentos.FT, '(emporio) WITHIN XML') > 0
8 OR CONTAINS (Documentos.DocOri, 'emporio') > 0) Docs
9 INNER JOIN Temas_Docs ON (Docs.DocID = Temas_Docs.DocID)
10 INNER JOIN TiposDoc ON (Docs.TipoDocID = TiposDoc.TipoDocID)
11 /
DOCUMENTOS_DOCID TEMAID TEMAS_DOCS_DOCID TD_DENOM
---------------- --------------- ---------------- --------------------------------------------------
969 K166 969 Oficio
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=4091)
1 0 NESTED LOOPS (Cost=7 Card=1 Bytes=4091)
2 1 HASH JOIN (Cost=6 Card=1 Bytes=4055)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTOS' (Cost=2 Card=1 Bytes=4033)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP OR
6 5 BITMAP CONVERSION (FROM ROWIDS)
7 6 SORT (ORDER BY)
8 7 DOMAIN INDEX OF 'DOCUMENTOS_FT' (Cost=0)
9 5 BITMAP CONVERSION (FROM ROWIDS)
10 9 SORT (ORDER BY)
11 10 DOMAIN INDEX OF 'DOCUMENTOS_BINARIO' (Cost=0)
12 2 TABLE ACCESS (FULL) OF 'TEMAS_DOCS' (Cost=2 Card=82 Bytes=1804)
13 1 TABLE ACCESS (BY INDEX ROWID) OF 'TIPOSDOC' (Cost=1 Card=82 Bytes=2952)
14 13 INDEX (UNIQUE SCAN) OF 'TIPOSDOC_PK' (UNIQUE)
scott@ORA92> SET AUTOTRACE OFF
scott@ORA92> -- gather statistics:
scott@ORA92> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'DOCUMENTOS')
PL/SQL procedure successfully completed.
scott@ORA92> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TEMAS_DOCS')
PL/SQL procedure successfully completed.
scott@ORA92> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TIPOSDOC')
PL/SQL procedure successfully completed.
scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> -- original query o.k. after gathering statistics,
scott@ORA92> -- but may not be stable, depending on your data:
scott@ORA92> SELECT Documentos.DocID AS Documentos_DocID,
2 Temas_Docs.TemaID,
3 Temas_Docs.DocID AS Temas_Docs_DocID,
4 TiposDoc.Denominacion AS TD_Denom
5 FROM Documentos
6 INNER JOIN Temas_Docs ON (Documentos.DocID = Temas_Docs.DocID)
7 INNER JOIN TiposDoc ON (Documentos.TipoDocID = TiposDoc.TipoDocID)
8 WHERE Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
9 AND (CONTAINS (Documentos.FT, '(emporio) WITHIN XML') > 0
10 OR CONTAINS (Documentos.DocOri, 'emporio') > 0)
11 /
DOCUMENTOS_DOCID TEMAID TEMAS_DOCS_DOCID TD_DENOM
---------------- --------------- ---------------- --------------------------------------------------
969 K166 969 Oficio
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=28)
1 0 NESTED LOOPS (Cost=7 Card=1 Bytes=28)
2 1 HASH JOIN (Cost=6 Card=1 Bytes=16)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTOS' (Cost=2 Card=1 Bytes=7)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP OR
6 5 BITMAP CONVERSION (FROM ROWIDS)
7 6 SORT (ORDER BY)
8 7 DOMAIN INDEX OF 'DOCUMENTOS_FT' (Cost=0)
9 5 BITMAP CONVERSION (FROM ROWIDS)
10 9 SORT (ORDER BY)
11 10 DOMAIN INDEX OF 'DOCUMENTOS_BINARIO' (Cost=0)
12 2 TABLE ACCESS (FULL) OF 'TEMAS_DOCS' (Cost=2 Card=2 Bytes=18)
13 1 TABLE ACCESS (BY INDEX ROWID) OF 'TIPOSDOC' (Cost=1 Card=2 Bytes=24)
14 13 INDEX (UNIQUE SCAN) OF 'TIPOSDOC_PK' (UNIQUE)
scott@ORA92> -- use_hash hint with statistics:
scott@ORA92> SELECT /*+ USE_HASH(documentos temas_docs) */
2 Documentos.DocID AS Documentos_DocID,
3 Temas_Docs.TemaID,
4 Temas_Docs.DocID AS Temas_Docs_DocID,
5 TiposDoc.Denominacion AS TD_Denom
6 FROM Documentos
7 INNER JOIN Temas_Docs ON (Documentos.DocID = Temas_Docs.DocID)
8 INNER JOIN TiposDoc ON (Documentos.TipoDocID = TiposDoc.TipoDocID)
9 WHERE Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
10 AND (CONTAINS (Documentos.FT, '(emporio) WITHIN XML') > 0
11 OR CONTAINS (Documentos.DocOri, 'emporio') > 0)
12 /
DOCUMENTOS_DOCID TEMAID TEMAS_DOCS_DOCID TD_DENOM
---------------- --------------- ---------------- --------------------------------------------------
969 K166 969 Oficio
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=28)
1 0 NESTED LOOPS (Cost=7 Card=1 Bytes=28)
2 1 HASH JOIN (Cost=6 Card=1 Bytes=16)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTOS' (Cost=2 Card=1 Bytes=7)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP OR
6 5 BITMAP CONVERSION (FROM ROWIDS)
7 6 SORT (ORDER BY)
8 7 DOMAIN INDEX OF 'DOCUMENTOS_FT' (Cost=0)
9 5 BITMAP CONVERSION (FROM ROWIDS)
10 9 SORT (ORDER BY)
11 10 DOMAIN INDEX OF 'DOCUMENTOS_BINARIO' (Cost=0)
12 2 TABLE ACCESS (FULL) OF 'TEMAS_DOCS' (Cost=2 Card=2 Bytes=18)
13 1 TABLE ACCESS (BY INDEX ROWID) OF 'TIPOSDOC' (Cost=1 Card=2 Bytes=24)
14 13 INDEX (UNIQUE SCAN) OF 'TIPOSDOC_PK' (UNIQUE)
scott@ORA92> -- correct results using inline view with statistics:
scott@ORA92> SELECT Docs.DocID AS Documentos_DocID,
2 Temas_Docs.TemaID,
3 Temas_Docs.DocID AS Temas_Docs_DocID,
4 TiposDoc.Denominacion AS TD_Denom
5 FROM (SELECT DocID, TipoDocID
6 FROM Documentos
7 WHERE CONTAINS (Documentos.FT, '(emporio) WITHIN XML') > 0
8 OR CONTAINS (Documentos.DocOri, 'emporio') > 0) Docs
9 INNER JOIN Temas_Docs ON (Docs.DocID = Temas_Docs.DocID)
10 INNER JOIN TiposDoc ON (Docs.TipoDocID = TiposDoc.TipoDocID)
11 /
DOCUMENTOS_DOCID TEMAID TEMAS_DOCS_DOCID TD_DENOM
---------------- --------------- ---------------- --------------------------------------------------
969 K166 969 Oficio
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=28)
1 0 NESTED LOOPS (Cost=7 Card=1 Bytes=28)
2 1 HASH JOIN (Cost=6 Card=1 Bytes=16)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTOS' (Cost=2 Card=1 Bytes=7)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP OR
6 5 BITMAP CONVERSION (FROM ROWIDS)
7 6 SORT (ORDER BY)
8 7 DOMAIN INDEX OF 'DOCUMENTOS_FT' (Cost=0)
9 5 BITMAP CONVERSION (FROM ROWIDS)
10 9 SORT (ORDER BY)
11 10 DOMAIN INDEX OF 'DOCUMENTOS_BINARIO' (Cost=0)
12 2 TABLE ACCESS (FULL) OF 'TEMAS_DOCS' (Cost=2 Card=2 Bytes=18)
13 1 TABLE ACCESS (BY INDEX ROWID) OF 'TIPOSDOC' (Cost=1 Card=2 Bytes=24)
14 13 INDEX (UNIQUE SCAN) OF 'TIPOSDOC_PK' (UNIQUE)
scott@ORA92> SET AUTOTRACE OFF
[Updated on: Thu, 21 September 2006 13:22] Report message to a moderator |
|
|
|
| Re: SQL with CONTAINS problem [message #194508 is a reply to message #194041] |
Fri, 22 September 2006 07:56 |
inapal
Messages: 14
Registered: September 2006
|
Junior Member |
|
|
Barbara,
I've tested your suggestion, using in-line view.
With this reduced SQL statement, in-line view works fine, but the query is only a demo to understand the problem at this forum.
The original SQL query is:
SELECT
Documentos.DocID AS ID, Documentos.TipoDocID, Documentos.NroDoc AS NDoc,
Documentos.Detalle, Documentos.Fecha, 'D' AS TipoContenido, Temas_Docs.TemaID,
TiposDoc.Denominacion AS TD_Denom, TO_CHAR(Documentos.Fecha, 'DD-MM-YYYY') AS FechaView_
FROM Documentos
INNER JOIN Temas_Docs ON (Documentos.DocID = Temas_Docs.DocID)
INNER JOIN TiposDoc ON (Documentos.TipoDocID = TiposDoc.TipoDocID)
WHERE Documentos.EnWeb = 'S'
AND Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
AND (CONTAINS(FT, '(emporio) WITHIN XML') > 0 OR CONTAINS(DocOri, 'emporio') > 0)
Using your suggestion, the Query results:
SELECT
Documentos_IN.DocID AS ID, Documentos_IN.TipoDocID, Documentos_IN.NroDoc AS NDoc,
Documentos_IN.Detalle, Documentos_IN.Fecha, 'D' AS TipoContenido, Temas_Docs.TemaID,
TiposDoc.Denominacion AS TD_Denom, TO_CHAR(Documentos_IN.Fecha, 'DD-MM-YYYY') AS FechaView_
FROM
(SELECT
Documentos.DocID, Documentos.TipoDocID, Documentos.NroDoc,
Documentos.Detalle, Documentos.Fecha, Documentos.EnWeb
FROM Documentos
WHERE
CONTAINS(FT, '(emporio) WITHIN XML') > 0 OR
CONTAINS(DocOri, 'emporio') > 0 ) Documentos_IN
INNER JOIN Temas_Docs ON (Documentos_IN.DocID = Temas_Docs.DocID)
INNER JOIN TiposDoc ON (Documentos_IN.TipoDocID = TiposDoc.TipoDocID)
WHERE Documentos_IN.EnWeb = 'S' AND
Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
Running this query, The result is again incorrect:
ID TIPODOCID NDOC DETALLE FECHA TIPOCONTENIDO TEMAID TD_DENOM FECHAVIEW_
----- ---------- ---------------- ---------- -------------------- ------------- --------------- ---------------------------------------- ----------
969 OF 32/2004 (CLOB) 14-Jul-2004 D K165 Oficio 14-07-2004
969 OF 32/2004 (CLOB) 14-Jul-2004 D K166 Oficio 14-07-2004
2 row(s) retrieved
|
|
|
|
| Re: SQL with CONTAINS problem [message #194593 is a reply to message #194508] |
Fri, 22 September 2006 19:22 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please try the code below. It worked in my test. It may seem like overkill on the join conditions, but that seems to be what it takes to get it to join properly and avoid the issue of two contains clauses on the same table. I generaly prefer the old join syntax as the ansi sql syntax seems to be more prone to problems.
SELECT D1.DocID AS ID, D1.TipoDocID, D1.NroDoc AS NDoc,
D1.Detalle, D1.Fecha, 'D' AS TipoContenido, Temas_Docs.TemaID,
TiposDoc.Denominacion AS TD_Denom, TO_CHAR(D1.Fecha, 'DD-MM-YYYY') AS FechaView_
FROM Documentos d1, Documentos d2, Temas_Docs, TiposDoc
WHERE d1.DocID = d2.DocID
AND D1.DocID = Temas_Docs.DocID
AND D2.DocID = Temas_Docs.DocID
AND D1.TipoDocID = TiposDoc.TipoDocID
AND D2.TipoDocID = TiposDoc.TipoDocID
AND D1.EnWeb = 'S'
AND D2.EnWeb = 'S'
AND Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
AND (CONTAINS (d1.FT, '(emporio) WITHIN XML') > 0 OR CONTAINS (d2.DocOri, 'emporio') > 0)
/
|
|
|
|
| Re: SQL with CONTAINS problem [message #194803 is a reply to message #194508] |
Mon, 25 September 2006 07:07 |
inapal
Messages: 14
Registered: September 2006
|
Junior Member |
|
|
Thank you, Barbara. That's works fine.
Now, I've changed my query, using another join on "Documentos" table (applying your idea), and it works fine too.
SELECT
Documentos_IN.DocID AS ID, Documentos_IN.TipoDocID, Documentos_IN.NroDoc AS NDoc,
Documentos_IN.Detalle, Documentos_IN.Fecha, 'D' AS TipoContenido, Temas_Docs.TemaID,
TiposDoc.Denominacion AS TD_Denom, TO_CHAR(Documentos_IN.Fecha, 'DD-MM-YYYY') AS FechaView_
FROM
(SELECT
D1.DocID, D1.TipoDocID, D1.NroDoc,
D1.Detalle, D1.Fecha, D1.EnWeb
FROM Documentos D1
INNER JOIN Documentos D2 ON (D1.DocID = D2.DocID)
WHERE
CONTAINS(D1.FT, '(emporio) WITHIN XML') > 0 OR
CONTAINS(D2.DocOri, 'emporio') > 0 ) Documentos_IN
INNER JOIN Temas_Docs ON (Documentos_IN.DocID = Temas_Docs.DocID)
INNER JOIN TiposDoc ON (Documentos_IN.TipoDocID = TiposDoc.TipoDocID)
WHERE Documentos_IN.EnWeb = 'S' AND
Temas_Docs.TemaID IN ('K108','K165','K166','K167','K168','K109','K110')
Really, it doesn't please me at all these solutions.
I believe that my original query is well-formed, but...
Thank you again.
Ignacio.
[Updated on: Mon, 25 September 2006 07:14] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sat Nov 23 17:10:29 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
