Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: performance
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…
Cool stuff with partition elimination
Sometimes in the IT world, the term “surprise” is not a good one. “I woke up this morning and got a surprise…my database was down.” “I ran a SELECT COUNT(*) on my most important table, and got a surprise result of zero rows.” and…
ROWNUM and ORDER BY
Just a quick revisit on an old topic so I could link to this post in a Stack Overflow discussion. SQL> create table t ( x int ); Table created. SQL> SQL> insert into t 2 select dbms_random.value(1,1000) 3 from dual 4 connect by…
Execution plans on LiveSQL
To protect the integrity of people’s data, and isolate sessions on LiveSQL, we lock down the environment. Clearly if you are doing some testing with sensitive data, you don’t want an anonymous member of the user community mining V$SQL to see what commands you…
NVL vs COALESCE
Jonathan Lewis just published a blog post about NVL and COALESCE and the optimizer costings for each. There is also perhaps a significant difference between NVL and COALESCE in that the former seems to have an in-built optimization for handling bind variables and nulls. …
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…
Identity columns in 12c … just a sequence ?
This question came to me over Twitter, so I thought I’d whip out a quick post on it Yes, we do implement the IDENTITY column via a sequence. (Digression – I think this is a smart thing to do. After all, we’ve had…
