Skip to main content

I call for a do over - index organized tables

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)

Comments

Popular posts from this blog

Entry level cost for CoreOS+Tectonic

CoreOS and Tectonic start their pricing at 10 servers. Managed CoreOS starts at $1000 per month for those first 10 servers and Tectonic is $5000 for the same 10 servers. Annualized that is $85K or at least one employee depending on your market. As a single employee company I'd rather hire the employee. Specially since I only have 3 servers.

The pricing is biased toward the largest servers with the largest capacities; my dual core 32GB i5 IntelNuc can never be mistaken for a 96-CPU dual or quad core DELL

If CoreOS does not figure out a different barrier of entry they are going to follow the Borland path to obscurity.

UPDATE 2017-10-30: With gratitude the CoreOS team has provided updated information on their pricing, however, I stand by my conclusion that the effective cost is lower when you deploy monster machines. The cost per node of my 1 CPU Intel NUC is the same as a 96 CPU server when you get beyond 10 nodes. I'll also reiterate that while my pricing notes are not currently…

eGalax touch on default Ubuntu 14.04.2 LTS

I have not had success with the touch drivers as yet.  The touch works and evtest also seems to report events, however, I have noticed that the button click is not working and no matter what I do xinput refuses to configure the buttons correctly.  When I downgraded to ubuntu 10.04 LTS everything sort of worked... there must have been something in the kermel as 10.04 was in the 2.6 kernel and 4.04 is in the 3.x branch.

One thing ... all of the documentation pointed to the wrong website or one in Taiwanese. I was finally able to locate the drivers again: http://www.eeti.com.tw/drivers_Linux.html (it would have been nice if they provided the install instructions in text rather than PDF)
Please open the document "EETI_eGTouch_Programming_Guide" under the Guide directory, and follow the Guidline to install driver.
download the appropriate versionunzip the fileread the programming manual And from that I'm distilling to the following: execute the setup.sh answer all of the questio…

Prometheus vs Bosun

In conclusion... while Bosun(B) is still not the ideal monitoring system neither is Prometheus(P).

TL;DR;

I am running Bosun in a Docker container hosted on CoreOS. Fleet service/unit files keep it running. However in once case I have experienced at least one severe crash as a result of a disk full condition. That it is implemented as part golang, java and python is an annoyance. The MIT license is about the only good thing.

I am trying to integrate Prometheus into my pipeline but losing steam fast. The Prometheus design seems to desire that you integrate your own cache inside your application and then allow the server to scrape the data, however, if the interval between scrapes is shorter than the longest transient session of your application then you need a gateway. A place to shuttle your data that will be a little more persistent.

(1) storing the data in my application might get me started more quickly
(2) getting the server to pull the data might be more secure
(3) using a push g…