Getting Started With Oracle Week: Creating Indexes and Statistics


This is not a deep dive

If you’re looking for lots of internals and explanations of what happens behind the scenes, don’t read past here. I almost made a READPAST joke. It’s that kind of day. This is just a basic overview of creating some indexes and gathering statistics. Why? Because someone just paid about $47.5k for every 0.75 cores of Oracle Enterprise licensing and they probably expect some performance out of it. This isn’t MySQL. We don’t have all day to get query results.

If you remember last time, we created a couple tables of random data, HR.T1 and HR.T2. They are currently sitting in TABLESPACE, where no one can hear you scream.

The first thing you want to do is forget about clustered indexes. Oracle has Cluster Indexes, which allow frequently joined rows from separate tables to sit on the same block of data, to reduce I/O when joining tables. Oracle has Index Organized Tables, which can be defined by a Primary Key.

But that’s more than I want to bite off!

Index Gang

Creating a Primary Key ain’t too far off from SQL Server. But there are some weird points, too. For instance, when you create a Primary Key, you can let it create an associated index, specify an index to associate with, or let Oracle pick the first index it finds to associate with the Primary Key. Full disclosure: it may not be the index you’d pick, it may be the first index that has the PK column as a leading column.

Here are some examples!

If, at some point, you realize you chose the wrong Primary Key, you can drop it without dropping the index.

Can’t cluster this

You can also create some pretty familiar looking index structures. There’s even an online option, if you paid through the nose. You can, of course, define your index as UNIQUE for free (for now, anyway)!

But man oh man, the best part of this to me brings in a little something from when we created the tables and test data! Creating indexes with no logging! Creating indexes online with no logging is like perf tuning God mode. Oracle for the IDDQD!

Other options

Oracle doesn’t exactly have filtered indexes. They have function based indexes, but to my SQL Server soaked brain, they seem more like a computed column with an index on it than a filtered index.

You can also create bitmap indexes, which are good for low density columns. That’s fancy talk for ‘not very unique’. Our bit column would fall into that category. Other entrants would be stuff like gender, marital status, or Favorite Rebecca Black Song would also probably qualify.

Ain’t no STATMAN here

To create, update, or otherwise manage statistics you use the DBMS_STATS package. It has subprograms for so many things, it’s hard to list them all. Oracle treats statistics much more importantly than SQL Server does, and with good reason: THEY ARE!

I also like the advice that the Oracle crowd has had on index fragmentation, since around 2002:

My opinion — 99.9% of all reorgs, rebuilds, etc are a total and utter waste of time and
energy. We spend way way way too much time losing sleep over this non-event.
If you are going to spend time on this exercise — make sure you come up with a way to
MEASURE what you’ve just done in some quanitative fashion you can report to your mgmt
(eg: these rebuilds I spend X hours a week doing save us from doing X IO’s every day, or
let us do Y more transactions then otherwise possible, or …..) No one, but no one,
seems to do that (keep metrics). They just feel “it must be better”. Who knows — you
may actually be DECREASING performance!! (you’ll never know until you measure)

If we wanted to gather statistics on all columns in our T1 and T2 tables, we could run commands like this:

You can check on Oracle statistics in the GUI, and see that they provide pretty commensurate information to SQL Server’s statistics.

That's a thick milkshake.
That’s a thick milkshake.

I’ll revisit this down the line

But there’s a lot I want to explore here, first. Hopefully you learned a few things along the way. I know I did writing this!

Thanks for reading!

Previous Post
Getting Started With Oracle Week: NULLs and NULL handling
Next Post
[Video] Office Hours 2016 2016/06/08 (With Transcriptions)

9 Comments. Leave new

  • In Oracle, an Index-Organized Table is, in effect, a clustered index.

    • Erik Darling
      June 10, 2016 8:17 am

      Yes. I wanted to draw the distinction that you don’t explicitly create a clustered index, and that a Cluster index is a different animal.

  • You know it’s going to be a good day when you come across a 90s Doom reference while reading an article.

  • “NUM_BUCKETS” in the stats info is interesting. Does that suggest that you can specify how many histogram steps there are? One thing that burns me time and time again is the fixed maximum in SQL Server. It’s really hard to represent a billion-plus row table with 200 histogram steps!

    • Erik Darling
      June 10, 2016 12:32 pm

      Hey Ben,

      Totally with you on that one. If you’re having issues getting accurate histogram information on the whole table, you may want to look into filtered statistics to get a more granular histogram over a smaller span of data.

      • Filtered stats are great in theory. In practice, it’s hard to get the optimizer to take those into consideration. And if I want the histogram to cover all of my data, I have to play the game of “make (n) 200 step histograms that span all of my data”. It’s like partitioning, but worse.

  • Sybase has offered this for some years:


    where specifies the number of steps in the histogram (which may be reduced depending on data cardinality).

  • “This isn’t MySQL. We don’t have all day to get query results.”


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.