This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Jul 11, 2007 2:59 PM by Barbara Boehmer Go to original post RSS
  • 30. Re: Loading an Oracle Thesaurus:
    507076 Newbie
    Currently Being Moderated
    Thanks. That helped. I think I missed the single quotes for rowid column.

    I will look more into the Indexes. To clarify my question,
    1.Create Index ix_raw_data with xml section group identifiers.
    2.Insert XML Data into rawdata column
    3.Now insert regular text into rawdata column.
    4. Query the rawdata with 'WITHIN' clause - it works and performs.
    5. Query the rawdata, contains(rawdata,'sometext')>0 (Without within clause) for the regular text we inserted. - It works great and performs.

    Question is:
    How does it indexes both XML dataformat and regular text.
    If we consider that it is indexing all the sections (<ROW> </ROW>) then isn't it a waste of duplicate data. If not how are we getting the performance. Just curious.

    If it is not clear, thats Ok. i was too curious to know how it is indexing inside.

    Thanks a lot for all your help.

    Naren
  • 31. Re: Loading an Oracle Thesaurus:
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    If the regular text is the same data as the xml data, then yes you have unnecessary duplicate rows and I would eliminate the rows with the regular text. I don't know exactly how the indexing works internally, but you can query the xml data with or without the within clause, so you do not need the duplicate data. To give a little more detail, the individual words are stored in the token_text column of the dr$ix_raw_data$i table. The information for the section groups is stored in other tables. So if you query without the within clause, it should only need to search the dr$ix_raw_data$i table, but if you query with the within clause, it needs to join to the additional tables to check whether the words are within the sections. This is a simplified explanation. There are other tables involved. Text indexes are not quite like other indexes. The information is contained in database tables that you can view.
  • 32. Re: Loading an Oracle Thesaurus:
    507076 Newbie
    Currently Being Moderated
    Hi Barbara,
    I am back on this thread with question to CREATE_RELATION and DROP_RELATION.

    I used create_relation and created a synonym on default THES.
    When I query the
    SELECT * FROM CTX_THES_PHRASES
    This is the result.
    DEFAULT      NAREN
    DEFAULT      NICK

    Now I used drop_relation

    BEGIN
    CTX_THES.DROP_RELATION('DEFAULT','NAREN','SYN','NICK');
    END;
    /

    When I requery the CTX_THES_PHRASES
    I still see
    DEFAULT      NAREN
    DEFAULT      NICK


    Why are they not getting deleted.

    Usecase for us here is:
    Into the Thesaurus table we will be adding new synonms and there might be case we want to drop a existing synonym.

    Please let me know if i am missing anything here.

    Naren
  • 33. Re: Loading an Oracle Thesaurus:
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    DROP_RELATION only drops the relation, not the phrase. You have to use DROP_PHRASE to drop the phrase, as demonstrated below, but you should be careful to make sure there are not any other relations before you drop such a phrase.

    SCOTT@10gXE> EXEC CTX_THES.CREATE_RELATION ('DEFAULT', 'NAREN', 'SYN', 'NICK')

    PL/SQL procedure successfully completed.

    SCOTT@10gXE> COLUMN thp_phrase FORMAT A40 WORD_WRAPPED
    SCOTT@10gXE> SELECT thp_thesaurus, thp_phrase FROM CTX_THES_PHRASES
      2  WHERE  thp_phrase IN ('NAREN', 'NICK')
      3  /

    THP_THESAURUS                  THP_PHRASE
    ------------------------------ ----------------------------------------
    DEFAULT                        NAREN
    DEFAULT                        NICK

    SCOTT@10gXE> CREATE TABLE test_tab (test_col  VARCHAR2 (30))
      2  /

    Table created.

    SCOTT@10gXE> INSERT INTO test_tab (test_col) VALUES ('NAREN')
      2  /

    1 row created.

    SCOTT@10gXE> INSERT INTO test_tab (test_col) VALUES ('NICK')
      2  /

    1 row created.

    SCOTT@10gXE> CREATE INDEX test_ind ON test_tab (test_col)
      2  INDEXTYPE IS CTXSYS.CONTEXT
      3  /

    Index created.

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'SYN (NAREN, DEFAULT)') > 0
      2  /

    TEST_COL
    ------------------------------
    NAREN
    NICK

    SCOTT@10gXE> BEGIN
      2    CTX_THES.DROP_RELATION ('DEFAULT','NAREN','SYN','NICK');
      3  END;
      4  /

    PL/SQL procedure successfully completed.

    SCOTT@10gXE> SELECT thp_thesaurus, thp_phrase FROM CTX_THES_PHRASES
      2  WHERE  thp_phrase IN ('NAREN', 'NICK')
      3  /

    THP_THESAURUS                  THP_PHRASE
    ------------------------------ ----------------------------------------
    DEFAULT                        NAREN
    DEFAULT                        NICK

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'SYN (NAREN, DEFAULT)') > 0
      2  /

    TEST_COL
    ------------------------------
    NAREN

    SCOTT@10gXE> BEGIN
      2    CTX_THES.DROP_PHRASE ('DEFAULT', 'NAREN');
      3    CTX_THES.DROP_PHRASE ('DEFAULT', 'NICK');
      4  END;
      5  /

    PL/SQL procedure successfully completed.

    SCOTT@10gXE> SELECT thp_thesaurus, thp_phrase FROM CTX_THES_PHRASES
      2  WHERE  thp_phrase IN ('NAREN', 'NICK')
      3  /

    no rows selected

    SCOTT@10gXE>
    SCOTT@10gXE> spool off
1 2 3 Previous Next