This discussion is archived
7 Replies Latest reply: Oct 4, 2012 9:54 AM by Barbara Boehmer RSS

trying to understand catsearch-help

949210 Newbie
Currently Being Moderated
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
PL/SQL Release 10.2.0.1.0 on windows server 2003 service pack 1
Trying to understand catsearch..
i am using
http://docs.oracle.com/cd/A91202_01/901_doc/text.901/a90121/csql3.htm
http://docs.oracle.com/cd/A91202_01/901_doc/text.901/a90121/cdatadi6.htm
http://docs.oracle.com/cd/B14117_01/text.101/b10730/cddlpkg.htm
Failed to create CTXCAT Index with index set
i started with the first link that i mentioned above with a little alteration in create statement
CREATE INDEX auction_titlex ON auction(title) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS
('index set auction_iset');
added 'CTXSYS.' before 'CTXCAT' thats the only alteration
using the post of Barbara Boehmer in the forum's link that i have mentioned
the query that i am using is
SELECT REG_ID,NAME,PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM
FROM(SELECT ROWNUM,REG_ID,NAME,NVL(SUBSTR(CAT_TYPES,1,INSTR(CAT_TYPES, ',', 1, 8)-1),CAT_TYPES) AS PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM FROM MV_CAT_SEG_REG_PROD
where CATSEARCH(cat_types,'Solar water heater system*',NULL)>0 and rownum<11 order by act_status desc)
please only look at the query after where clause(i know the rest of it may look complicated but that does not help you answering my question so forget it)
the very first row that this query returns is
"SOLAR WIND HYBRID SYSTEMS,SOLAR STREET LIGHT MANUFACTURERS,SOLAR WATER HEATER DEALERS,SOLAR HOME LIGHTING,SOLAR DC AND AC LIGHTING SYSTEMS"
so the question is why is it returning this row when there is no "Solar water heater system" in that row
:
:
so i am trying to copy the above thing to a table that i created in my local dummy machine
here is what i am doing
CREATE TABLE auction (category_id number primary key, title varchar2(200),
bid_close date);
INSERT INTO auction values(1, 'Sony CD Player', '20-FEB-2000');
INSERT INTO auction values(2, 'Sony CD Player', '24-FEB-2000');
INSERT INTO auction values(3, 'Pioneer DVD Player', '25-FEB-2000');
INSERT INTO auction values(4, 'Sony CD Player', '25-FEB-2000');
INSERT INTO auction values(5, 'Bose Speaker', '22-FEB-2000');
INSERT INTO auction values(6, 'Tascam CD Burner', '25-FEB-2000');
INSERT INTO auction values(7, 'Nikon digital camera', '22-FEB-2000');
INSERT INTO auction values(8, 'Canon digital camera', '26-FEB-2000');
inserting few more my own rows
Insert into AUCTION (CATEGORY_ID,TITLE,BID_CLOSE) values (10,'Sony Bravia',to_date('24-FEB-00','DD-MON-RR'));
Insert into AUCTION (CATEGORY_ID,TITLE,BID_CLOSE) values (11,'Sony VAIO Laptops',to_date('24-FEB-00','DD-MON-RR'));
Insert into AUCTION (CATEGORY_ID,TITLE,BID_CLOSE) values (12,'Sony CD Player cum Writer',to_date('24-FEB-00','DD-MON-RR'));
Insert into AUCTION (CATEGORY_ID,TITLE,BID_CLOSE) values (13,'Sony CD Player and Burner',to_date('24-FEB-00','DD-MON-RR'));
exec ctx_ddl.create_index_set('auction_iset');
exec ctx_ddl.add_index('auction_iset','bid_close');
CREATE INDEX auction_titlex ON auction(title) INDEXTYPE IS CTXCAT PARAMETERS
('index set auction_iset');
there is no sony reader in auction table so the following query does not return any rows
SELECT * FROM auction WHERE CATSEARCH(title, 'sony reader*', NULL)> 0;
SELECT * FROM auction WHERE CATSEARCH(title, 'sony-reader*', NULL)> 0;
so if here it is not returning any rows (which is what i was expecting) even above it should not be returning any rows right??
but it is returning.. why?? how??
please help me understand catsearch
please let me know if you need some more data
thanks in advance

Edited by: 946207 on Sep 29, 2012 3:35 PM
  • 1. Re: trying to understand catsearch-help
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    When you search for '"word1 word2"' with double quotes, it returns anything that contains the phrase "word1 word2". This ignores things like hyphens, unless you specify otherwise, by setting the hyphen as skipjoins or printjoins.

    When you search for 'word1-word2' without any spaces around the hyphen, it is the same as the above, unless you set the hyphen as skipjoins or printjoins.

    When you search for 'word1 word2*', it returns anything that contains both the word "word1" and any word that starts with "word2".

    When you search for 'word1 - word2*' with a space on each side of the hyphen, it returns anything that contains the word "word1" but does not contain any word that starts with "word2".

    All of the above is explained in the first link that you provided.

    Please see the simplified example below.
    SCOTT@orcl_11gR2> CREATE TABLE auction
      2    (category_id  NUMBER,
      3       title          VARCHAR2(60))
      4  /
    
    Table created.
    
    SCOTT@orcl_11gR2> BEGIN
      2    INSERT INTO auction VALUES (1, 'Solar water heater system');
      3    INSERT INTO auction VALUES (2, 'SOLAR WIND, WATER HEATER DEALERS, LIGHTING SYSTEMS');
      4    INSERT INTO auction VALUES (3, 'sony reader');
      5    INSERT INTO auction VALUES (4, 'sony-reader');
      6    INSERT INTO auction VALUES (5, 'sony CD reader');
      7    INSERT INTO auction VALUES (6, 'sony CD Player');
      8  END;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> CREATE INDEX auction_titlex
      2  ON auction (title)
      3  INDEXTYPE IS CTXSYS.CTXCAT
      4  /
    
    Index created.
    
    SCOTT@orcl_11gR2> -- any title with the phrase "solar water heater system" in it:
    SCOTT@orcl_11gR2> SELECT * FROM auction WHERE
      2  CATSEARCH(title, '"Solar water heater system"', NULL)> 0
      3  /
    
    CATEGORY_ID TITLE
    ----------- ------------------------------------------------------------
              1 Solar water heater system
    
    1 row selected.
    
    SCOTT@orcl_11gR2> -- any title with the word "solar" and the word "water" and the word "heater"
    SCOTT@orcl_11gR2> -- and any word that starts with "system":
    SCOTT@orcl_11gR2> SELECT * FROM auction WHERE
      2  CATSEARCH(title, 'Solar water heater system*', NULL)> 0
      3  /
    
    CATEGORY_ID TITLE
    ----------- ------------------------------------------------------------
              1 Solar water heater system
              2 SOLAR WIND, WATER HEATER DEALERS, LIGHTING SYSTEMS
    
    2 rows selected.
    
    SCOTT@orcl_11gR2> -- any title with the phrase "sony reader", ignoring hyphens
    SCOTT@orcl_11gR2> -- (would be different if the hyphen was defined as skipjoin or printjoin):
    SCOTT@orcl_11gR2> SELECT * FROM auction WHERE
      2  CATSEARCH(title, '"sony reader"', NULL)> 0
      3  /
    
    CATEGORY_ID TITLE
    ----------- ------------------------------------------------------------
              3 sony reader
              4 sony-reader
    
    2 rows selected.
    
    SCOTT@orcl_11gR2> -- same as above:
    SCOTT@orcl_11gR2> SELECT * FROM auction WHERE
      2  CATSEARCH(title, 'sony-reader*', NULL)> 0
      3  /
    
    CATEGORY_ID TITLE
    ----------- ------------------------------------------------------------
              3 sony reader
              4 sony-reader
    
    2 rows selected.
    
    SCOTT@orcl_11gR2> -- any title with the word "sony" and any word that starts with "reader":
    SCOTT@orcl_11gR2> SELECT * FROM auction WHERE
      2  CATSEARCH(title, 'sony reader*', NULL)> 0
      3  /
    
    CATEGORY_ID TITLE
    ----------- ------------------------------------------------------------
              3 sony reader
              4 sony-reader
              5 sony CD reader
    
    3 rows selected.
    
    SCOTT@orcl_11gR2> -- any title that contains the word "sony"
    SCOTT@orcl_11gR2> -- but does not contain any word that starts with "reader":
    SCOTT@orcl_11gR2> SELECT * FROM auction WHERE
      2  CATSEARCH(title, 'sony - reader*', NULL)> 0
      3  /
    
    CATEGORY_ID TITLE
    ----------- ------------------------------------------------------------
              6 sony CD Player
    
    1 row selected.
  • 2. Re: trying to understand catsearch-help
    949210 Newbie
    Currently Being Moderated
    So
    Insert into LOOKING4.AUCTION (CATEGORY_ID,TITLE,BID_CLOSE) values (14,'sony washing machine reader',to_date('24-FEB-00','DD-MON-RR'));
    Insert into LOOKING4.AUCTION (CATEGORY_ID,TITLE,BID_CLOSE) values (15,'toshiba wahing machine reader',to_date('24-FEB-00','DD-MON-RR'));
    SELECT * FROM auction WHERE CATSEARCH(title, 'SONY reader*', NULL)> 0;will return
    14     sony washing machine reader     24-FEB-00
    and
    SELECT * FROM AUCTION WHERE CATSEARCH(TITLE, 'reader*', NULL)> 0;
    will return
    14     sony washing machine reader     24-FEB-00
    15     toshiba wahing machine reader     24-FEB-00
    thanks
  • 3. Re: trying to understand catsearch-help
    949210 Newbie
    Currently Being Moderated
    the query that is fired after "same as above" should return
    sony cd reader
    also but it is not
    i am not able to understand why
  • 4. Re: trying to understand catsearch-help
    949210 Newbie
    Currently Being Moderated
    so i marked the thread as unanswered again

    Edited by: 946207 on Oct 4, 2012 12:14 PM
  • 5. Re: trying to understand catsearch-help
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    I am not getting the same results that you seem to be describing. Please provide a copy and paste of a run of a complete test case, as I have done below. If you have inserted rows after creating the index, then you need to make sure that you have synchronized the index after committing the inserts.
    SCOTT@orcl_11gR2> CREATE TABLE auction
      2    (category_id  NUMBER,
      3       title          VARCHAR2(60))
      4  /
    
    Table created.
    
    SCOTT@orcl_11gR2> BEGIN
      2    INSERT INTO auction VALUES (1, 'sony CD reader');
      3    Insert into AUCTION values (2,'sony washing machine reader');
      4    Insert into AUCTION values (3,'toshiba wahing machine reader');
      5  END;
      6  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> CREATE INDEX auction_titlex
      2  ON auction (title)
      3  INDEXTYPE IS CTXSYS.CTXCAT
      4  /
    
    Index created.
    
    SCOTT@orcl_11gR2> SELECT * FROM auction WHERE
      2  CATSEARCH (title, 'SONY reader*', NULL) > 0
      3  /
    
    CATEGORY_ID TITLE
    ----------- ------------------------------------------------------------
              1 sony CD reader
              2 sony washing machine reader
    
    2 rows selected.
    
    SCOTT@orcl_11gR2> SELECT * FROM auction WHERE
      2  CATSEARCH (title, 'reader*', NULL) > 0
      3  /
    
    CATEGORY_ID TITLE
    ----------- ------------------------------------------------------------
              1 sony CD reader
              2 sony washing machine reader
              3 toshiba wahing machine reader
    
    3 rows selected.
  • 6. Re: trying to understand catsearch-help
    949210 Newbie
    Currently Being Moderated
    >
    SCOTT@orcl_11gR2> -- same as above:
    SCOTT@orcl_11gR2> SELECT * FROM auction WHERE
    2 CATSEARCH(title, 'sony-reader*', NULL)> 0
    3 /

    CATEGORY_ID TITLE
    ----------- ------------------------------------------------------------
    3 sony reader
    4 sony-reader

    2 rows selected.
    HERE we should be getting sony cd reader also
    why dont we get it
    see the first post of yours in this thread after "same as above"
  • 7. Re: trying to understand catsearch-help
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    As previously stated, when you search for 'word1-word2' with a hyphen without spaces around it, it is the same as searching for the exact phrase '"word1 word2"' within double quotes without any words in between. So, if you search for 'sony-reader*', then "sony cd reader" should not be in the result set.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points