Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: constraints
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…
Take care with regular expressions
In an Office Hours session a couple of months back, I covered an important change that comes to regular expressions once you upgrade to 12c Release 2. You can see the video covering the issue here: but for the TL;DR brigade reading this post:…
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…
The simplest things….can be risky
Java and Oracle expert Lukas Eder tweeted yesterday about a potential optimization that could be done when reviewing database SQL code. This looks to be a logical thing to do. Why scan the table T twice to perform an update, when the same job…
NULL’s vs NOT NULL’s and Performance
When it comes to giving the cost based optimiser the best possible chance to make the “right” decisions, many DBA’s are diligent in keeping statistics up to date, using histograms where appropriate, creating more indexes (or removing surplus indexes). However one often neglected area…
Please keep your foreign keys
I came across an interesting blog post the other day about whether databases should be (declaratively) enforcing the foreign key relationships between tables. The blog post discussed the issue of foreign keys being temporarily disabled to perform data loading, and then encountering the problem…
Duplicate constraints are impossible right ?
Here’s a very simple example of a table called PARENT being a (surprise surprise) parent in a referential integrity relationship to a (drum roll for my choice of name) CHILD table SQL> create table parent ( p int, constraint PAR_PK primary key (p) );…
