Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: dba
LOBs vs Data – when compression occurs
Just a quick tip for SECUREFILE lobs. You may be familiar with basic compression on data in tables. It is a dictionary attribute of the table, which is then taken into account when you perform: a SQL Loader direct load, or an INSERT with…
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…
Correcting datatypes with minimal downtime
Just a quick post here by request of an attendee of the September Office Hours. I had a demo converting the data type of a primary key without losing data and with minimal impact to the availability of the database using DBMS_REDEFINITION. You can…
Another little 12c improvement
You’ve got a huge table right? Massive! Immense! And then something bad happens. You get asked to remove one of the columns from that table. “No problem” you think. “I won’t run the ‘drop column’ command because that will visit every block and take…
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…
18c database creation on Windows
Hopefully you’ve followed my very simple and easy guide to downloading the 18c database software for Windows. But of course, software on its own is not much use – we need a database! So let’s get cracking and create one. Using the Start menu…
Humble pie made with NULL strings
I was helping out a client a while back with an issue where a panicked email came into my inbox along the lines of “SELECT IS BROKEN IN ORACLE!!!”, which seemed perhaps a little extreme . So I pursued it further asking for some…
Hyper-partitioned index avoidance thingamajig
As you can tell, I have no idea on a name for what I am about to describe. So let me start from the beginning, and set the scene for an idea I have to utilize a cool new 18c feature. Often in a…
18.3 As easy as 1…2…3
Well, finally it’s here! 18c for on-premise installation so the world can all get stuck into the cool new features of the latest release on their own laptops At least that is what I’ll be doing! Naturally as soon as I heard the…
More triggers are better
Yes, you heard me correctly. If you have got one trigger on a table, then you might be surprised to find that perhaps having a second one will be a better option. Then again, I also love the sweet scent of a clickbaity, inflammatory…
DDL for constraints – subtle things
The DBMS_METADATA package is very cool. I remember the days of either hand-crafting DDL statements based on queries to the data dictionary, or many a DBA will be familiar with running “imp show=y” or “imp indexfile=…” in order to then laboriously extract the DDL…
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…
