CREATE INDEX <index_name>
 
ON (<first_column>, <second_column>)
PCTFREE <integer>
TABLESPACE <tablespace_name>
COMPRESS <integer *>;
 
* Must be AT LEAST 1 less than the NUMBER OF indexed columns
 
CREATE INDEX ix_pcodes
ON postal_code (state_abbrev, city_name, zip_code)
PCTFREE 0
TABLESPACE uwdata;
 
ANALYZE INDEX ix_pcodes VALIDATE STRUCTURE;
 
exec DBMS_STATS.gather_index_stats(USER, 'IX_PCODES');
 
SELECT opt_cmpr_count, opt_cmpr_pctsave
FROM index_stats;
 
SELECT SUM(bytes)
FROM user_segments
WHERE segment_name = 'IX_PCODES';
 
DROP INDEX ix_pcodes;
 
CREATE INDEX ix_pcodes
ON postal_code (state_abbrev, city_name, zip_code)
PCTFREE 0
TABLESPACE uwdata
COMPRESS 1;
 
ANALYZE INDEX ix_pcodes VALIDATE STRUCTURE;
 
exec DBMS_STATS.gather_index_stats(USER, 'IX_PCODES');
 
SELECT opt_cmpr_count, opt_cmpr_pctsave
FROM index_stats;
 
SELECT SUM(bytes)
FROM user_segments
WHERE segment_name = 'IX_PCODES';
 
DROP INDEX ix_pcodes;
 
CREATE INDEX ix_pcodes
ON postal_code (state_abbrev, city_name, zip_code)
PCTFREE 0
TABLESPACE uwdata
COMPRESS 2;
 
ANALYZE INDEX ix_pcodes VALIDATE STRUCTURE;
 
exec DBMS_STATS.gather_index_stats(USER, 'IX_PCODES');
 
SELECT opt_cmpr_count, opt_cmpr_pctsave
FROM index_stats;
 
SELECT SUM(bytes)
FROM user_segments
WHERE segment_name = 'IX_PCODES';
 
-- so pay attention to opt_comp_count