Richard Bucker

I call for a do over - index organized tables

Posted at — Jul 21, 2012

I was talking to a DBA recently and he was trying to impress me with his intimate knowledge of everything Oracle. I never claimed to be a DBA and I certainly never claimed to be an Oracle DBA but he was throwing out this particular feature: Index Organized Tables. It’s an interesting feature but one that I would never use.First there is the FUD. Index organized tables are re-packaged in order to recover space and facilitate performance. The fact that the data needs to be rewritten means that there is always a chance that data can and will be destroyed. Whereas in the normal table format the data is essentially static. No moving parts, not data loss or at least there is a better chance to recover the data.But forget the FUD, it’s not even important. There are two hard and fast rules you want from your PK (primary key) for optimum performance and flexibility in an RDBMS and this applies to Oracle, MS SQL Server, Informix, Postgres and MySQL.Your page free space should be minimal, extent size as big as you can afford, the PK should be a sequence number of some kind and the index type should be a hash. This will accomplish several important things about the physical structure on disk. (a) the pages will be full so there is no dead space and reshuffling will be left to a minimum (b) extending the file with plenty of extents will allow for efficient high volume inserts (c) if you do not do any deleting then the PKs will be in insert order (d) hashes are the fastest way to locate the record on the order of O(1). Everything else should be left to secondary indexes.Secondary indexes are a completely different animal. Sometimes you need to use covered indexes in order to reduce the number of reads. But for the most part you want to tread lightly. Rebuilding indexes, depending on the amount of data, can take hours and in many cases this can take you out of service. But you want to move as little data as possible… and use hashes wherever you can. Specially in OLTP systems.You need to know your tools to be an effective programmer or database developer. This feature might just be TMI (too much information)