compression - order by matters

You get much better compress if you order the data as you compress
use this sql to work out the best order by clause


SELECT
', ' || dtc.column_name
, dtc.num_distinct
, dtc.avg_col_len
FROM dba_tab_cols dtc
WHERE dtc.owner = 'OWNER'
AND dtc.table_name = 'TABLE_TO_COMRESS'
ORDER BY
dtc.num_distinct
,dtc.avg_col_len DESC;

More on - Is my index used

try this SQL :-

SELECT P.object_name
--, P.operation
--, P.options
, COUNT ( 1 ) times_used
, MIN ( P.TIMESTAMP ) min_timestamp
, MAX ( P.TIMESTAMP ) max_timestamp
, AVG ( ind_size.size_mb ) size_mb
FROM dba_hist_sql_plan P
, dba_hist_sqlstat s
, (SELECT A.index_name
, A.owner
, ROUND ( SUM ( b.BLOCKS * 8 ) / 1024, 0 ) size_mb
FROM dba_indexes A
, dba_segments b
WHERE A.index_name = b.segment_name
AND A.UNIQUENESS <> 'UNIQUE'
GROUP BY A.index_name
, A.owner) ind_size
WHERE P.object_owner <> 'SYS'
AND P.operation LIKE '%INDEX%'
AND P.object_owner IN ('DWDIMS','DWFACTS')
AND P.sql_id = s.sql_id
AND P.object_owner = ind_size.owner
AND P.object_name = ind_size.index_name
GROUP BY P.object_name
--, P.operation
--, P.options
ORDER BY 4
, 1
, 2
, 3;