| CTX INDEX DEFRAGMENTATION - help needed [message #76128] |
Tue, 12 October 2004 11:44  |
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  |
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>
-------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
