Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: indexes
Being generous to the optimizer
In a perfect world, the optimizer would reach out from the server room, say to us: “Hey, lets grab a coffee and have a chat about that query of yours”. Because ultimately, that is the task we are bestowing on the optimizer – to…
Take care with automatic indexes
This one came in via an AskTOM question. You need to be careful when disabling constraints that are underpinned by an index, especially when it comes to the storage for that index. Let’s take a look at a simple example. I’ll explicitly nominate the…
Partial Indexes–Take Care With Truncate
Partial indexes are a very cool feature that came along with Oracle 12c. The capability at partition level to control index existence allows for a couple of obvious use cases: 1) You index the data in recent partitions only, because small amounts of data…
Partial indexing – get the dictionary definitions right
Just a quick post to clear up some confusion that can be seen on the partial indexing capabilities in Oracle Database 12c and above. I was at an event in the Oracle Perth office last week, and an attendee mentioned that they thought the…
Text indexes for numbers
We had an AskTOM question recently about being able to search for numbers within a concatenated list. The particular issue was a list of mobile phone numbers, but this is really just about tackling the larger issue of finding numbers within list. Let’s create…
Updating indexes with partition maintenance
An index is basically a structure that maps keys (values) in columns to the physical location of their corresponding rows in a table. So if you move the rows (ie, change the physical location of a row) then the index entries for those rows…
Index compression–quick tip
If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the setting a tablespace to automatically add compression as a default at a nominated level in the database. From the docs: Here is an example of that in…
