Home » Server Options » Text & interMedia » CTX INDEX DEFRAGMENTATION - help needed
CTX INDEX DEFRAGMENTATION - help needed [message #76128] Tue, 12 October 2004 11:44 Go to next message
lily
Messages: 44
Registered: September 2000
Member
Hi,

I have a very large table EMP (35million records) with monthly partition in it. The table has Text Indexes(with partitions) on it. Some of the partitions are badly fragmented and I am using the following methods to defragment it. Pl advice if there is any other/best way to defragment various partitions..

regards,

Lily

 

------methods-------

1**Finding how many '$I' tables are existing in the Base Index of the table..

select table_name from all_tables where table_name like 'DR#EMP%$I';
TABLE_NAME
------------------------------
DR#EMP_10013$I
DR#EMP_10014$I
DR#EMP_10015$I
DR#EMP_10002$I
DR#EMP_10003$I
DR#EMP_10004$I
DR#EMP_10005$I
DR#EMP_10006$I
DR#EMP_10007$I
DR#EMP_10008$I
DR#EMP_10009$I

TABLE_NAME
------------------------------
DR#EMP_10010$I
DR#EMP_10011$I
DR#EMP_10012$I
DR#EMP_10001$I
DR#EMP_10016$I
DR#EMP_10017$I

 

2**Finding how many IXP_ID, IXP_INDEX_PARTITION_NAME is existing in the particular CTX Index Partition....this helps to find out the latest CTX Index partition's IXP_ID which can be later used
to check the fragmented level of that latest CTX Index partition..

select ixp_id,ixp_index_partition_name from ctxsys.ctx_index_partitions where ixp_index_name like 'EMP%';

    IXP_ID IXP_INDEX_PARTITION_NAME
---------- ------------------------------
         3 EMP_00
         4 EMP_01_Q1
         5 EMP_01_Q2
         6 EMP_01_Q3
         7 EMP_01_Q4
         8 EMP_02_Q1
         9 EMP_02_Q2
        10 EMP_02_Q3
        11 EMP_02_Q4
        12 EMP_03_Q1
        13 EMP_03_Q2

    IXP_ID IXP_INDEX_PARTITION_NAME
---------- ------------------------------
        14 EMP_03_Q3
        15 EMP_03_Q4
        16 EMP_04_Q1
        17 EMP_04_Q2
         1 EMP_98
         2 EMP_99

 

NOTE Starts : Checking the description of latest CTX Index partition
=================================================
SQL> connect xxxx/xxxx
Connected.
SQL> desc DR#EMP_10017$I
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TOKEN_TEXT                                NOT NULL VARCHAR2(64)
 TOKEN_TYPE                                NOT NULL NUMBER(3)
 TOKEN_FIRST                               NOT NULL NUMBER(10)
 TOKEN_LAST                                NOT NULL NUMBER(10)
 TOKEN_COUNT                               NOT NULL NUMBER(10)
 TOKEN_INFO                                         BLOB
================================================
NOTE ends

 

3**Checking the Fragmantation Level in a particular CTX Index partition...

SELECT AVG(COUNT(*)) FROM ignus.DR<i_name>$I GROUP BY TOKEN_TEXT HAVING COUNT(*) > 1;
or

select count(1)/count(distinct token_text) from DR#EMP_10017$I;

COUNT(1)/COUNT(DISTINCTTOKEN_TEXT)
----------------------------------
                        27.2491528

NOTE: here the frag level is 27 (this many rows per work/token was in the $I table.
This should be <=10. Oracle recommendation is <=4.

 

4**Optimizing the latest (Fragmented 27 level) CTX Index partition...

exec ctx_ddl.optimize_index('EMP_1' ,'FULL', 120, NULL, 'EMP_04_Q2');

or

exec ctx_ddl.optimize_index('EMP_1' ,'FULL', 120, 'BANK', 'EMP_04_Q2');

NOTE: in the above query, bank is a token. this way you can put any token which are taking time...
it would be a good idea if specific TOKEN's are found which is more fragmented..and can be defragmented using ctx_ddl.

---------end------------------------

 

 

 
Re: CTX INDEX DEFRAGMENTATION - help needed [message #76129 is a reply to message #76128] Tue, 12 October 2004 15:34 Go to previous message
lily
Messages: 44
Registered: September 2000
Member
I have also checked the Empty Blocks in $I table. I have 744 empty blocks. Any suggestion on how to Dellocate/clean the Empty Blocks? Is it same as "ALTER TABLE ExampleTable DEALLOCATE UNUSED;" ?

--------
SQL>
PROMPT

col owner form a12
col table_name form a20
col empty_blocks form 999,999 heading "Empty Blks"
col blocks form 999,999 heading "Blks"
col pct form 99

select owner, table_name, num_rows, chain_cnt, (chain_cnt*100/num_rows) pct, empty_blocks, blocks
from dba_tables
where table_name='DR$EMP_IDM_1$I'
/
SQL>

Table analyzed.

Elapsed: 00:00:30.57
SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL> Table Fragmentation Report
SQL>
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4
OWNER TABLE_NAME NUM_ROWS CHAIN_CNT PCT Empty Blks Blks
------------ -------------------- ---------- ---------- --- ---------- --------
IGNUS DR$EMP_IDM_1$I 20039784 0 0 744 141,591

Elapsed: 00:00:00.05
SQL> SQL> SQL> SQL> SQL>
-------------------------------
Previous Topic: not a valid time zone error
Next Topic: problems in starting text mining in Oracle 10g
Goto Forum:
  


Current Time: Wed Oct 01 11:03:02 CDT 2014

Total time taken to generate the page: 0.11909 seconds