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!
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!
ALTER TABLE HR.T1 ADD CONSTRAINT pk_t1_id PRIMARY KEY (ID);
--I created this one a little out of order to show the USING syntax
ALTER TABLE HR.T2 ADD CONSTRAINT pk_t2_id PRIMARY KEY (ID) USING INDEX IX_T2_ID_OID_CID;
If, at some point, you realize you chose the wrong Primary Key, you can drop it without dropping the index.
ALTER TABLE HR.T2 DROP CONSTRAINT pk_t2_id KEEP 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)!
CREATE INDEX IX_T1_ID_OID_CID ON HR.T1 (ID, ORDER_ID, CUSTOMER_ID);
CREATE INDEX IX_T2_ID_OID_CID ON HR.T2 (ID, ORDER_ID, CUSTOMER_ID);
CREATE INDEX IX_T1_ID_OID_CID ON HR.T1 (ID, ORDER_ID, CUSTOMER_ID) ONLINE;
CREATE INDEX IX_T2_ID_OID_CID ON HR.T2 (ID, ORDER_ID, CUSTOMER_ID) ONLINE;
CREATE UNIQUE INDEX IX_T1_ID_OID_CID ON HR.T1 (ID, ORDER_ID, CUSTOMER_ID) ONLINE;
CREATE UNIQUE INDEX IX_T2_ID_OID_CID ON HR.T2 (ID, ORDER_ID, CUSTOMER_ID) ONLINE;
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!
ALTER TABLE HR.T1 ADD CONSTRAINT pk_t1_id PRIMARY KEY (ID) NOLOGGING;
CREATE UNIQUE INDEX IX_T1_CID_PHN_NL ON HR.T1 (CUSTOMER_ID, CUST_PHONE) ONLINE NOLOGGING;
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.
CREATE UNIQUE INDEX IX_T2_OD_PD_SD ON HR.T2 (UPPER(FIRST_NAME)) ONLINE;
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.
CREATE BITMAP INDEX IX_T1_BM_ISS ON HR.T1 (IS_SOMETHING);
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:
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'
You can check on Oracle statistics in the GUI, and see that they provide pretty commensurate information to SQL Server’s statistics.
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!