any better way to quesy DBA_INDEXES, DBA_IND_COLUMNS...?

Hi,

I'm currently writting a program that would generate a SQL script to reorganize a whole index tablespace.
I try to get the indormation from DBA_INDEXES, DBA_IND_COLUMNS, DBA_TAB_COLUMNS, DBA_SEGMENTS. Following are my SQL statements, and they took a while to retrieve the information. Please let me know if you have better idea. Also, if you like this utility, please email me at "phil_sheu_at_amax.com" . I'll email you a copy when I finished.

1. for reorg criteria SELECT * FROM DBA_INDEXES WHERE TABLESPACE_NAME = ?lcTABLESPACE_NAME
2. for index definition SELECT C.* FROM DBA_INDEXES I, DBA_IND_COLUMNS C WHERE I.OWNER = C.INDEX_OWNER AND I.INDEX_NAME = C.INDEX_NAME AND I.TABLESPACE_NAME = ?lcTABLESPACE_NAME ORDER BY C.TABLE_NAME, C.INDEX_NAME, C.COLUMN_POSITION
3. try to get the column information SELECT TC.* FROM DBA_INDEXES I, DBA_IND_COLUMNS IC, DBA_TAB_COLUMNS TC WHERE I.OWNER = IC.INDEX_OWNER AND I.INDEX_NAME = IC.INDEX_NAME

    AND IC.TABLE_OWNER = TC.OWNER
    AND IC.TABLE_NAME = TC.TABLE_NAME
    AND IC.COLUMN_NAME = TC.COLUMN_NAME

    AND I.TABLESPACE_NAME = ?lcTABLESPACE_NAME

ORDER BY
    I.OWNER,
    I.INDEX_NAME,
    IC.COLUMN_POSITION 4. Try to get the allocated space for every index, to compress all extents into one larger extent
SELECT
    a.OWNER,
    a.INDEX_NAME,
    SUM(b.BYTES) AS BYTES
FROM
    DBA_INDEXES A,
    DBA_EXTENTS B
WHERE
    A.OWNER = B.OWNER
    AND A.INDEX_NAME = B.SEGMENT_NAME     AND A.TABLESPACE_NAME = ?lcTABLESPACE_NAME     AND B.SEGMENT_TYPE = 'INDEX'
GROUP BY
    A.OWNER,
    A.INDEX_NAME Received on Fri Aug 03 2001 - 06:36:10 CDT