Larger indexes than tables

[mariogus]

Personal,

Using the command below I have the indexes that are larger than the tables, however, when reexecuting they always appear ...

the reason?

Select all

 
declare 
cursor CTAB is 
SELECT SEGMENT_NAME,BYTES FROM user_SEGMENTS WHERE SEGMENT_TYPE='TABLE'; 
begin 
dbms_output.enable(90000); 
for CTABDET in CTAB loop 
  for CINDEX in (select S.SEGMENT_NAME,S.BYTES from user_INDEXES I, user_SEGMENTS S where I.TABLE_NAME=CTABDET.SEGMENT_NAME AND S.SEGMENT_NAME=I.INDEX_NAME) loop 
     IF CINDEX.BYTES>CTABDET.BYTES THEN 
        dbms_output.put_line('INDEX '||CINDEX.SEGMENT_NAME||' = '||CINDEX.BYTES/1024/1024||' MAIOR QUE A TABELA '||CTABDET.SEGMENT_NAME||' = '||CTABDET.BYTES/1024/1024); 
     END IF; 
  end loop; 
end loop; 
end;

[stcoutinho]

Mariogus,

All right?

Maybe you are performing many DML (INSERT / DELETE / UPDATE) operations in the tables to which these indexes belong. Or, you have indexed columns and are performing a lot of update in them.

If your indexes are Bitmap type and you are update in the columns that make it up, these indexes will grow. A similar situation occurs with Context / Domain Indexes (Oracle Text Indexes): The more Updates / Delete / Insert you make, the bigger the size that these indexes will reach.

A suggestion would be to elect one of these related rates in your queries. Evaluate the size he has now and then make a reorganization / rebuild of the same (eg alter index ... rebuild). Then run the queries again to see if it still shows size larger than the table.

Refer to OTN Forums, I verified that they indicated a metalink note to evaluate when or not the reorganization of the indexes should be done. I did not have time to see the link to this note, but search for Note: 989093.1 - Index Rebuild, The Need vs The Implications
If the size of the indexes worries you And this isted impacting on your environment (lack of space) it would be interesting to evaluate if Oracle is using even all indexes created for your table. There are articles that mention about how to evaluate the use of indexes.

There is an interesting article on this subject in: http://www.palominodb.com/blog/2011/10/ ... ctual-data
Hugs,

Sergio Coutinho

[fbifabio]

Mariogus,

Only to complement Coutinho's response, it is normal Some indexes are larger than the table and to verify when rebuilding BTree indexes see on the MOS The Note C / ID 122008.1. In short ... BTREE indexes should only be rebuilt if they have blevel greater than 4 and / or 20% or more of deleted lines (del_lf_rows). Below is a script to help you evaluate this:

Select all

 
SET SERVEROUTPUT ON 
SET VERIFY OFF 
DECLARE 
  V_SCHEMANAME VARCHAR2(30):=UPPER('&SCHEMA_NAME'); 
  V_INDEXNAME VARCHAR2(30):=UPPER('&INDEX_NAME'); 
  V_RESULTADO VARCHAR2(4); 
  V_BLEVEL NUMBER; 
  V_PERC_LD NUMBER; 
BEGIN 
   
    FOR I IN (SELECT  INDEX_NAME, OWNER 
              FROM    DBA_INDEXES 
              WHERE   OWNER = V_SCHEMANAME 
              AND     INDEX_NAME = NVL(V_INDEXNAME,INDEX_NAME) 
              AND     INDEX_TYPE = 'NORMAL' 
              AND     OWNER NOT IN ('SYS','SYSTEM') 
              ) 
    LOOP 
        EXECUTE IMMEDIATE 'ANALYZE INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' COMPUTE STATISTICS'; 
        EXECUTE IMMEDIATE 'ANALYZE INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' VALIDATE STRUCTURE'; 
         
        BEGIN 
            SELECT      CASE                                
                              WHEN DECODE(S.DEL_LF_ROWS,0,0,S.DEL_LF_ROWS/S.LF_ROWS*100) >= 20 OR IND.BLEVEL > 4  THEN 'SIM' 
                              ELSE 'NÃO'  
                        END AS REORGANIZE, 
                        BLEVEL,  
                        DECODE(S.DEL_LF_ROWS,0,0,S.DEL_LF_ROWS/S.LF_ROWS*100)                           
                        INTO V_RESULTADO, V_BLEVEL, V_PERC_LD 
            FROM        INDEX_STATS S 
            INNER JOIN  DBA_INDEXES IND 
                ON      S.NAME = IND.INDEX_NAME 
            WHERE       IND.OWNER = I.OWNER; 
             
            IF V_RESULTADO = 'SIM' THEN 
                DBMS_OUTPUT.PUT_LINE('É necessário reconstruir o índice ' || I.OWNER || '.' || I.INDEX_NAME || '(blevel=' || TO_CHAR(V_PERC_LD)  
                                  || ', % linhas del.=' || to_char(V_PERC_LD) || ')'); 
            ELSE 
                DBMS_OUTPUT.PUT_LINE('NÃO é necessário reconstruir o índice ' || I.OWNER || '.' || I.INDEX_NAME); 
            END IF; 
			 
			DBMS_STATS.GATHER_INDEX_STATS(i.OWNER, i.INDEX_NAME); -- coleta estatistica do indice (necessario apos o ANALYZE) 
        EXCEPTION 
          WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE(' Erro ao calcular indice ' || i.owner || '.' || i.index_name); 
        END; 
    END LOOP; 
END;