die Seilerwerks

Chronicling Life, Love, Linux and Oracle database administration.

Posts Tagged ‘indexes

The Rumble Down Under

with one comment

Looks like Richard Foote won’t be on Howard Rogers’ Christmas card list for much longer (see the comments).

Written by Don Seiler

December 19, 2007 at 10:53 am

Posted in oracle

Tagged with , ,

Functions Can Trump Indexes

with one comment

It seems that I’m dense enough to simply read over the first sentence of this post (which I linked to earlier) and not bother to think what it means. Last night I read about it in the 10g DBA Handbook and it stuck, probably because they focused on it with some examples.

Using a function on a B*Tree (normal) indexed column in your WHERE clause means that Oracle won’t use that index. For example:


select * from emp
where UPPER(lastname) = 'JONES';

will most likely result in a full table scan, despite an index on lastname. However, you could create a function-based index on UPPER(lastname), like this:


create index emp_upper_lastname on
emp(upper(lastname));

Of course, the whole point of Jeff’s post was to describe an alternative to using a function-based index by using UPPER() check constraints that the CBO can utilize.

The world becomes clearer every day.

Written by Don Seiler

November 15, 2006 at 10:35 pm

Posted in Uncategorized

Tagged with , ,

Compound Primary Keys Must Die

with one comment

It turns out that a lot of the headaches of the past few weeks at work are all due to an incredibly shitty primary key on a large warehouse table. Before I get into specifics there, I’d like to formally proclaim my allegiance to the use of surrogate keys and my hatred for compound keys. Many of the Oracle “gurus” (including Tom Kyte) agree as well. You can use the Google to find reasons to use them. I’ll list smaller indexes on both the table in question AND tables with foreign keys as a big reason off the top.

Now, on this table at work, which I’ll call FOO, there is currently a 10-field compound primary key. Yeah, that’s right. I take some shame in having this in my database, although the design was inherited, and I’m just now feeling comfortable and confident enough to start suggesting radical changes to our developers. So this primary key begins with columns LOC, CUST_NO, YEAR, and MONTH. Then it includes 5 fields that are rarely, if ever queried (I turned on FGA on this table and verified that). Then the last field is the relatively new FOO_SEQ_NO, which is unique for a LOC, CUST_NO, YEAR and MONTH. Virtually every query against this table uses LOC, CUST_NO, YEAR and MONTH. FYI, this table is also range partitioned on YEAR and MONTH.

The problem is that after we load a new large batch of records from a previously unloaded market into the current partition, the optimizer no longer uses the big PK index, instead choosing to use a second index which doesn’t even use CUST_NO. This causes a LOT of I/O and painfully slow query times, until we are able to gather_table_stats() on it, which can take up to 2 hours.

What became painfully obvious after I turned on fine-grained auditing, is that 95% of the queries only use LOC, CUST_NO, YEAR and MONTH, so the massively compound primary key index is almost 50% useless. On a development instance, I dropped the PK constraint and index and built a new non-unique index on just LOC, CUST_NO, YEAR and MONTH. I do plan to add a unique constraint on the former PK fields just to keep things sane in case some part of the application relied on that. The sample query ran fine before and after the dataload, using the new index right away without having to gather stats.

Now, the case of the FOO table was an EXTREME of an ugly compound key/index, and the problem wasn’t so much the compoundedness of it as much as the index not being reflective of what user queries needed. But I still loathe compound keys.

Written by Don Seiler

November 10, 2006 at 1:27 pm

Posted in Uncategorized

Tagged with , , ,

Reverse Key Indexes

leave a comment »

So I started reading the Oracle 10g DBA Handbook because I’ve deluded myself into thinking that I’ll just memorize everything in there. The first chapter seemed to cover very basic stuff that even I had a grasp on, but it didn’t take long for me to learn something new. 18 pages in, I read of the magic of reverse key indexes. This is a pretty fundamental index type that I had somehow never heard about before this, so I thought I’d share it here.

A reverse key index does just that, stores the indexed values in reverse, so an id value of 123456 would be stored as 654321. One immediate advantage in doing so is reducing contention for blocks by distributing inserts across all leaf keys of the index. For example, inserting orders 1234, 1235, 1236 and 1237 would normally probably all go into the same block (if space is available), and the writers would wait for each other to finish with it. With a reverse key index, they would be spread to 4321, 5321, 6321 and 7321, which are not close together and probably not on the same node.

For a complete analysis, the link to Jonathan Lewis’ FAQ above is quality stuff.

Written by Don Seiler

November 7, 2006 at 4:48 pm

Posted in Uncategorized

Tagged with , ,

Follow

Get every new post delivered to your Inbox.