*** Create a simple demo table with just the one column with just the one distinct value SQL> CREATE TABLE compress1 (ID VARCHAR2(1)); Table created. SQL> INSERT INTO compress1 SELECT 'A' FROM dual CONNECT BY LEVEL <= 100000; 100000 rows created. SQL> COMMIT; Commit complete. SQL> CREATE INDEX compress1_i ON compress1(id) PCTFREE 0; Index created. SQL> SELECT index_name, leaf_blocks FROM user_indexes WHERE index_name = 'COMPRESS1_I'; INDEX_NAME LEAF_BLOCKS ------------------------------ ----------- COMPRESS1_I 163 Leaf block dump =============== header address 179249756=0xaaf225c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 615 kdxcofbo 1266=0x4f2 kdxcofeo 1271=0x4f7 kdxcoavs 5 kdxlespl 0 kdxlende 0 kdxlenxt 75606029=0x481a80d kdxleprv 75606027=0x481a80b kdxledsz 0 kdxlebksz 8036 row#0[8025] flag: ------, lock: 0, len=11 col 0; len 1; (1): 41 col 1; len 6; (6): 04 81 12 8a 02 67 row#1[8014] flag: ------, lock: 0, len=11 col 0; len 1; (1): 41 col 1; len 6; (6): 04 81 12 8a 02 68 row#2[8003] flag: ------, lock: 0, len=11 col 0; len 1; (1): 41 col 1; len 6; (6): 04 81 12 8a 02 69 row#3[7992] flag: ------, lock: 0, len=11 col 0; len 1; (1): 41 col 1; len 6; (6): 04 81 12 8a 02 6a ... row#612[1293] flag: ------, lock: 0, len=11 col 0; len 1; (1): 41 col 1; len 6; (6): 04 81 12 8b 02 37 row#613[1282] flag: ------, lock: 0, len=11 col 0; len 1; (1): 41 col 1; len 6; (6): 04 81 12 8b 02 38 row#614[1271] flag: ------, lock: 0, len=11 col 0; len 1; (1): 41 col 1; len 6; (6): 04 81 12 8b 02 39 ----- end of leaf block dump ----- End dump data blocks tsn: 21 file#: 18 minblk 108556 maxblk 108556 *** Length of row entry = 11 bytes (1 ID column, 6 rowid, 2 length bytes, 2 flag, locks) *** overheads with first column 2 byes (1 byte column value, 1 byte column length) SQL> DROP INDEX compress1_i; Index dropped. SQL> CREATE INDEX compress1_i ON compress1(id) PCTFREE 0 COMPRESS; Index created. SQL> SELECT index_name, leaf_blocks FROM user_indexes WHERE index_name = 'COMPRESS1_I'; INDEX_NAME LEAF_BLOCKS ------------------------------ ----------- COMPRESS1_I 138 *** Note leaf block reduced from 163 to 138 ... Leaf block dump =============== header address 125968988=0x782225c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 726 kdxcofbo 1496=0x5d8 kdxcofeo 1498=0x5da kdxcoavs 2 kdxlespl 0 kdxlende 0 kdxlenxt 75606029=0x481a80d kdxleprv 75606027=0x481a80b kdxledsz 0 kdxlebksz 8036 kdxlepnro 1 kdxlepnco 1 prefix row#0[8032] flag: -P----, lock: 0, len=4 col 0; len 1; (1): 41 prc 726 row#0[8023] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 12 8b 00 42 psno 0 row#1[8014] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 12 8b 00 43 psno 0 row#2[8005] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 12 8b 00 44 psno 0 row#3[7996] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 12 8b 00 45 psno 0 ... row#723[1516] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 12 8c 00 81 psno 0 row#724[1507] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 12 8c 00 82 psno 0 row#725[1498] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 12 8c 00 83 psno 0 ----- end of leaf block dump ----- End dump data blocks tsn: 21 file#: 18 minblk 108556 maxblk 108556 *** length of prefix = 4 bytes (double that of previous) therefore for the cost of 4 bytes, we save 2 bytes for each and every index row entry .... *** length of index row entry = 9 bytes (6 rowid, 1 length rowid, 2 flags lock info) Prefix pointer is positional and so takes no space ... *** This time, create the index with unique values (although the index itself is non-unique) SQL> CREATE TABLE compress_unique (id number); Table created. SQL> INSERT INTO compress_unique SELECT rownum FROM dual CONNECT BY LEVEL <= 100000; 100000 rows created. SQL> COMMIT; Commit complete. SQL> CREATE INDEX compress_unique_i ON compress_unique(id) PCTFREE 0 COMPRESS; Index created. Leaf block dump =============== header address 184230492=0xafb225c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 380 kdxcofbo 2320=0x910 kdxcofeo 2340=0x924 kdxcoavs 20 kdxlespl 0 kdxlende 0 kdxlenxt 75626126=0x481f68e kdxleprv 75626124=0x481f68c kdxledsz 0 kdxlebksz 8036 kdxlepnro 380 kdxlepnco 1 prefix row#0[8030] flag: -P----, lock: 0, len=6 **** Note starts at position 8030 through to 8016 (15 bytes - 6 for the prefix, 9 bytes for the index entry) col 0; len 3; (3): c2 08 43 prc 1 prefix row#1[8015] flag: -P----, lock: 0, len=6 col 0; len 3; (3): c2 08 44 prc 1 prefix row#2[8000] flag: -P----, lock: 0, len=6 col 0; len 3; (3): c2 08 45 prc 1 prefix row#3[7985] flag: -P----, lock: 0, len=6 col 0; len 3; (3): c2 08 46 prc 1 ... prefix row#377[2379] flag: -P----, lock: 0, len=6 col 0; len 3; (3): c2 0c 2c prc 1 prefix row#378[2364] flag: -P----, lock: 0, len=6 col 0; len 3; (3): c2 0c 2d prc 1 prefix row#379[2349] flag: -P----, lock: 0, len=6 col 0; len 3; (3): c2 0c 2e prc 1 row#0[8021] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 aa 8b 00 69 psno 0 row#1[8006] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 aa 8b 00 6a psno 1 row#2[7991] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 aa 8b 00 6b psno 2 row#3[7976] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 aa 8b 00 6c psno 3 ... row#377[2370] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 aa 8b 01 e2 psno 377 row#378[2355] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 aa 8b 01 e3 psno 378 row#379[2340] flag: ------, lock: 0, len=9 col 0; len 6; (6): 04 81 aa 8b 01 e4 psno 379 ----- end of leaf block dump ----- End dump data blocks tsn: 21 file#: 18 minblk 128653 maxblk 128653 *** Each prefix entry is referenced by just the one index row entry ... *** Here, the prefix entries are 6 bytes (3 for the column value, 1 for the length, 2 overheads) *** Index entries are 9 bytes (6 rowid, 1 rowid length, 2 overhead)