1 2 3 Previous Next 33 Replies Latest reply: Jul 11, 2007 4:59 PM by Barbara Boehmer Go to original post RSS
      • 30. Re: Loading an Oracle Thesaurus:
        507076
        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
          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
            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
              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