This discussion is archived
3 Replies Latest reply: Jun 7, 2012 1:10 PM by Barbara Boehmer RSS

Moving text index to another user

823526 Newbie
Currently Being Moderated
I have a database schema with at least 50 tables. One table contains approximately 10million clobs from OCR or scanned pages.
Currently the export dump file is over 50GB. I imagine 80% of the size is the clob table
We use the exp utility to backup this database

This database is used for many things besides searching this full text table.
The clob table is pretty static. Every so often we add a few hundred thousand new rows.
After the inserts we rebuild the index on the CLOB field & off we go. Its worked pretty well for 5 years.
Currently we run 11g.

Recently I wondered about moving this CLOB table to another user.
I thought about creating a new user & schema with a table containing only a primary key, foreign key (to the original user.table.field) and the CLOB itself
We thought we could isolate the clobs & make our backup take minutes rather than hours.
Perhaps we could gain some other efficiencies. I just don't know.

Is this a stupid thought?

Are there any query (i.e joins,views) repercussions we run into for doing such a thing.


Thanks in advance
  • 1. Re: Moving text index to another user
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    it is a possibility but other options exist:
    1. Use RMAN as backup mechanism, at first make a full backup and then an incremental
    2. Use EXPDP instead of EXP. EXPDP can parallize exports, so it can also be quicker.
    3. Use a Transportable Tablespace export, that is, copy the tablespaces and make the export

    There are maybe options to use read-only tablespaces with the combination of partitioning of the table.

    But for 11G I strongly suggest to make the backup with RMAN or EXPDP and do not use EXP anymore.

    Herald ten Dam
    http://htendam.wordpress.com
  • 2. Re: Moving text index to another user
    823526 Newbie
    Currently Being Moderated
    Thanks for your post.
    Perhaps I can explain a little more.

    I'm not so much interested in a discussion of backup strategy.

    My real interest is in physically segregating the text index and CLOBS from the rest of the database.
    I thought one way of doing this is to create another user, build a schema containing only one table with the clob that links (foreign key) to the other user & schema

    Can anyone see any possible problems I might run into by doing this.

    Perhaps sql syntax that might be problematic in such an approach.
    Real performane issues?
  • 3. Re: Moving text index to another user
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    The following is my understanding of what you are proposing, which would result in needing to re-write every query and some lesser performance, but I don't know how much. If I were you, I would test before doing this. The following only addresses the queries. You will also need to change your process for inserts, updates, and deletes. I would lean toward not doing this and selecting a better backup method instead.

    -- what you have now:
    SCOTT@orcl_11gR2> create table main_tab
      2    (id           number,
      3       other_data    varchar2(30),
      4       clob_col      clob)
      5  /
    
    Table created.
    
    SCOTT@orcl_11gR2> alter table main_tab
      2  add constraint main_tab_id_pk
      3  primary key (id)
      4  /
    
    Table altered.
    
    SCOTT@orcl_11gR2> insert into main_tab values (1, 'whatever', 'clob test data')
      2  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> insert into main_tab
      2  select object_id, object_name, object_name
      3  from   user_objects
      4  where  object_id > 1
      5  /
    
    402 rows created.
    
    SCOTT@orcl_11gR2> create index main_tab_idx
      2  on main_tab (clob_col)
      3  indextype is ctxsys.context
      4  /
    
    Index created.
    
    SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (user, 'MAIN_TAB')
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> set linesize 120
    SCOTT@orcl_11gR2> set autotrace on explain
    SCOTT@orcl_11gR2> column clob_col format a30
    SCOTT@orcl_11gR2> select id, other_data, clob_col
      2  from   main_tab
      3  where  contains (clob_col, 'test data') > 0
      4  /
    
            ID OTHER_DATA                     CLOB_COL
    ---------- ------------------------------ ------------------------------
             1 whatever                       clob test data
    
    1 row selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 558626586
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |              |     1 |   153 |     0   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| MAIN_TAB     |     1 |   153 |     0   (0)| 00:00:01 |
    |*  2 |   DOMAIN INDEX              | MAIN_TAB_IDX |       |       |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("CTXSYS"."CONTAINS"("CLOB_COL",'test data')>0)
    
    SCOTT@orcl_11gR2> set autotrace off
    -- what you want to do:
    SCOTT@orcl_11gR2> grant select, references on main_tab to clob_user
      2  /
    
    Grant succeeded.
    
    SCOTT@orcl_11gR2> connect clob_user/clob_user
    Connected.
    CLOB_USER@orcl_11gR2> create table clob_tab as
      2  select id clob_id, -- use an alias to avoid ambiguous names in queries
      3           clob_col
      4  from   scott.main_tab
      5  /
    
    Table created.
    
    CLOB_USER@orcl_11gR2> alter table clob_tab
      2  add constraint clob_tab_id_fk
      3  foreign key (clob_id) references scott.main_tab (id)
      4  /
    
    Table altered.
    
    CLOB_USER@orcl_11gR2> create index clob_tab_idx
      2  on clob_tab (clob_col)
      3  indextype is ctxsys.context
      4  /
    
    Index created.
    
    CLOB_USER@orcl_11gR2> exec dbms_stats.gather_table_stats (user, 'CLOB_TAB')
    
    PL/SQL procedure successfully completed.
    
    CLOB_USER@orcl_11gR2> grant select on clob_tab to scott
      2  /
    
    Grant succeeded.
    
    CLOB_USER@orcl_11gR2> connect scott/tiger
    Connected.
    SCOTT@orcl_11gR2> drop index main_tab_idx
      2  /
    
    Index dropped.
    
    SCOTT@orcl_11gR2> alter table main_tab drop column clob_col
      2  /
    
    Table altered.
    -- how you will need to query thereafter, adding the table to the from clause, and adding a join condition:
    SCOTT@orcl_11gR2> set linesize 120
    SCOTT@orcl_11gR2> set autotrace on explain
    SCOTT@orcl_11gR2> select id, other_data, clob_col
      2  from   main_tab, clob_user.clob_tab
      3  where  contains (clob_col, 'test data') > 0
      4  and    main_tab.id = clob_user.clob_tab.clob_id
      5  /
    
            ID OTHER_DATA                     CLOB_COL
    ---------- ------------------------------ ------------------------------
             1 whatever                       clob test data
    
    1 row selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1372561422
    
    ------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                |     1 |   158 |     1   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                 |                |       |       |            |          |
    |   2 |   NESTED LOOPS                |                |     1 |   158 |     1   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| CLOB_TAB       |     1 |   137 |     0   (0)| 00:00:01 |
    |*  4 |     DOMAIN INDEX              | CLOB_TAB_IDX   |       |       |     0   (0)| 00:00:01 |
    |*  5 |    INDEX UNIQUE SCAN          | MAIN_TAB_ID_PK |     1 |       |     0   (0)| 00:00:01 |
    |   6 |   TABLE ACCESS BY INDEX ROWID | MAIN_TAB       |     1 |    21 |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("CTXSYS"."CONTAINS"("CLOB_COL",'test data')>0)
       5 - access("MAIN_TAB"."ID"="CLOB_TAB"."CLOB_ID")
    
    SCOTT@orcl_11gR2>

Legend

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