Home » Server Options » Text & interMedia » SQL with CONTAINS problem
SQL with CONTAINS problem [message #194041] Wed, 20 September 2006 09:22 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous message
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

Previous Topic: need 10g intermedia sample code
Next Topic: Error using INSO Filters
Goto Forum:
  


Current Time: Thu Mar 28 13:57:12 CDT 2024