4 Replies Latest reply: Mar 5, 2013 3:27 PM by Zoltan Kecskemethy RSS

    Context Indexes

    skas
      I have statement like as follows that creates context index on user.tablename:

      CREATE INDEX myindex_cidx
      ON user.tablename (column_name)
      INDEXTYPE IS CTXSYS.CONTEXT
      PARAMETERS ('STOPLIST STOPLIST_NAME')
      ;


      When I create this indext it also creates four tables in user schema. Is there any way I can create the index in user A but index table in user B. I have lot of tables in user A and I need to created context indexes on them and I don't want to create these tables created by Oracle. I am using Oracle 11gR2.

      Thanks
        • 1. Re: Context Indexes
          Zoltan Kecskemethy
          Let me tell you how I found information about your question so you can follow this route next time.
          I started at [url http://www.oracle.com/pls/db112/homepage]Oracle 11g Documentation Library
          Switched to [url http://www.oracle.com/pls/db112/portal.all_books]Maer book list (top left)
          [url http://www.oracle.com/pls/db112/portal.all_books#index-TEX]clicked TEX in Shortcuts to Book Titles at the top
          So found [url http://docs.oracle.com/cd/E11882_01/text.112/e24436/toc.htm]Text reference
          Opened [url http://docs.oracle.com/cd/E11882_01/text.112/e24436/cdatadic.htm#BHCDHIAI]Oracle Text Indexing Elements chapter
          Opened [url http://docs.oracle.com/cd/E11882_01/text.112/e24436/cdatadic.htm#i1009245]Storage types url
          I think that's what you looking for.

          HTH, Zoltan
          • 2. Re: Context Indexes
            Jonathan Lewis
            Zoltan Kecskemethy wrote:
            Let me tell you how I found information about your question so you can follow this route next time.
            I started at [url http://www.oracle.com/pls/db112/homepage]Oracle 11g Documentation Library
            Switched to [url http://www.oracle.com/pls/db112/portal.all_books]Maer book list (top left)
            [url http://www.oracle.com/pls/db112/portal.all_books#index-TEX]clicked TEX in Shortcuts to Book Titles at the top
            So found [url http://docs.oracle.com/cd/E11882_01/text.112/e24436/toc.htm]Text reference
            Opened [url http://docs.oracle.com/cd/E11882_01/text.112/e24436/cdatadic.htm#BHCDHIAI]Oracle Text Indexing Elements chapter
            Opened [url http://docs.oracle.com/cd/E11882_01/text.112/e24436/cdatadic.htm#i1009245]Storage types url
            I think that's what you looking for.

            Good answer, but probably solving a more complicated problem that the OP had in mind.
            Even with context indexes it should be possible to do:
            create index userB.index_name on userA.table_name ...
            Assuming that userB has the privileges to manage context objects, of course.
            It works on 11.1.0.7, I'd have to check it for older versions.

            Regards
            Jonathan Lewis
            • 3. Re: Context Indexes
              skas
              It works with following ddl. I had to grant SELECT on user_A.table to CTXSYS before running the ddl.

              CREATE INDEX CTXSYS.myindex_cidx
              ON user_A.tablename (column_name)
              INDEXTYPE IS CTXSYS.CONTEXT
              PARAMETERS ('STOPLIST CTXSYS.stoplist_name')
              ;
              • 4. Re: Context Indexes
                Zoltan Kecskemethy
                Duh! That was way too easy. :D