Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: indexes
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…
Apex Interactive Grid and IOT’s
I love the interactive grid in Application Express. And here’s why… (Warning: Ranting mode is now on ) You can tell people here’s an application built with almost no code, in fact, you probably could have built it yourself it’s multi-user, with optimistic locking…
How important is "up to date" to you ?
I wrote a post a while back showing how one of the options with an Oracle Text index is to keep that index perfectly updated with its underlying table. (Long termers with Oracle may know that this is a relatively recent addition to Text…
Direct mode operations on IOT’s
An AskTom contributor brought to my attention, that direct mode insert on index organized tables now appears possible in 12c. We can see the difference by running a simple script in both v11 and v12 SQL> select * from v$version; BANNER ——————————————————————————– Oracle Database…
Quick tip on Function Based Indexes
For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes. SQL> create table blah ( x varchar2(30)); Table created. SQL> create index blah_ix on blah ( upper(x)); Index created. SQL> select…
Up to date text indexes
If you really need your text indexes to be “super dooper” up to date, that is, always in sync with your data, you can set the SYNC level to be ON COMMIT. For example, SQL> create table t1 ( 2 x int, 3 y…
Index compression–working out the compression number
Richard Foote did a series of informative posts on Index Compression which concludes that there is whole lot of positives about index compression, and very little negatives. But obviously one critical thing is choosing the right number of leading columns to compress. Is it…
Partial uniqueness
I had an interesting request recently from a developer. “ I have a table created as per below create table C_TEST ( col_1 varchar2(3), col_2 varchar2(3), col_3 number ); The rows defined by col_1, col_2, col_3 must be unique but only when col_3 is…
