APPS-ORACLE.RU

Oracle 12c introduced Partial indexing, which works well for simple partitioned tables with literals. However, it has several significant issues:

For instance, consider the following simple partitioned table:

All Oracle specialists know that any predicate X=NULL can't be true and we should use “X is NULL” in such cases. Oracle optimizer knows about that, so if we create a table like this:

All Oracle specialists know that a predicate X=NULL can never be true and we should use “X is NULL” in such cases. The Oracle optimizer knows about that, so if we create a table like this:

A tweet from Franck Pachot [July 2021] about fully qualified names in Postgres prompted me to highlight a note I wrote a few years ago about using the label mechanism in Oracle's PL/SQL to avoid collisions between variable names and table names. This led to a brief twitter [...]

There's a question on the Oracle developer forum at the moment asking how a tablescan could be reported as taking 94,000 seconds so far when a count(*) shows that it holds only a couple of hundred thousand rows (and it's not storing megabytes of LOB per row if that's [...]

I don't really do tips because I often see simple tip that have a specific purpose being mis-used abused; but it's a special day in the Oracle community, so here's a quick tip in tribute to a great sharer.

I've been doing some work with datapump recently, and needed to get [...]

A recent question on the Oracle Developer Community forum asked for help with a statement that was taking a long time to run. The thread included the results from a trace file that had been passed through tkprof so we have the query and the actual execution plan with [...]

In the past I've sometimes had to dump the contents of the redo log to a trace file when I needed to find out what work Oracle was doing behing the scenes. To minimise the volume dumped by the “alter system dump logfile” command and make it easier to find [...]

“Why did my query do parallel?”

It's a question that crops up from time to time, usually followed by a list of reasons why it shouldn't have gone parallel – no hints in the query, table is not declared parallel, parallel_degree_policy is set to manual etc.

When the question appeared recently [...]

If you want to do something about “wasted” space in an index what are the differences that you need to consider between the following three options (for the purposes of the article I'm ignoring “rebuild” and “rebuild online”):

Looking [...]

Here's a strange problem (with possible workaround) that appeared in a thread on the Oracle developer forum a couple of days ago. It looks like the sort of problem that might be a memory overflow problem in a rarely use code path, given that it seems to need a [...]

This note is about some testing I did on the consequences of the (new in 12c) “deferred global index maintenance” feature that Oracle introduced as a strategy to reduce the impact of dropping partitions from a partitioned table.

Looking at my notes I see that created my first test in August [...]

This is just a lightweight note on the risks of hinting (which might also apply occasionally to SQL Plan Baselines). I've just rediscovered a little script I wrote (or possibly last tested/edited) in 2007 with a solution to the problem of how to structure a query to use an [...]

Prompted by an unexpectedly high CPU usage on a hash join of two large dadta sets Stefan Koehler posted a poll on twitter recently asking for opinions on the following code fragment:

While [...]

There's a thread on the MOSC database tuning forum (needs an account) at the moment asking why a “fetch first N” query to fetch next 41 rows with an offset of 8602 rows takes longer to run than the same query when the offset is zero rows. Here's [...]

There's a thread on the Oracle developer forum at present asking why calls to dbms_lob.getlength() and calls to dbms_space.space_usage() produce such different results for the storage used by a LOB column.

It's a really good question to demonstrate two points. First that it's hard to supply all the details that [...]

A recent thread on the MOS database admin forum (needs an account) demonstrated a number of little issues with bugs, debugging, complex syntax, and the never-ending list of “not quite complete” code that shows up when features (in this case LOBs and partitioning) collide.

It's a silly little thing, but [...]

This is a little case study on working around a problem caused by a 3rd-party application that couldn't be changed quickly and easily. There's a touch of irony in this case as the problem I'll address is a side effect of the best possible workaround to a more generic design [...]

This is the follow-up to an initial post that covered some details of using the errorstack and ksq traces as and aid to finding the cause of an intermittent ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. We were (hypothetically) looking at a scenario where a [...]

If you don't recognise the number the relevant extract from the oraus.msg file is:

The error is typically the result of application code trying to do some [...]

This is another little “case study” on solving a problem. It's based on a question on the MOSC DBA Forum (needs an account) about trying to drop a tablespace and seeing the error “ORA-29857: domain indexes and/or secondary objects exist in the tablespace” when (apparently) there are no domain [...]

The question “How do you trouble-shoot a performance problem” came up in an online session I did for the AIOUG (All-India OUG) today. It's a very broad question and the only possible answers are either extremely generic, or very specific – so here's a specific example that I talked [...]

I've written a couple of notes about deleting from join views and (ultimately) the fact that if you have muliple key-preserved tables in join view then the first key preserved table in the from clause is the one where the delete is applied. The topic came up on the [...]

It's a long time since I've done a quiz night – but here's something that fooled me (briefly) when it appeared on the Oracle Developers' Forum. Here's a table definition – and I'm not going to make it easy by giving you a “create table” statement, but it's just a [...]

This note is about a surprising performance difference between the lead() and lag() analytic functions (which turns out to be due to the behaviour of the nth_value() function) when the option to “ignore nulls” is included in their use (jump to conclusion). The detail I'll be writing about was [...]

Here's a note about a data error generated by using (possibly mis-using) the dbms_redefinition package. The original code to demonstrate the problem comes from a note on the Oracle Developer forum, and was brought to my attention by a tweet from Daniel Stein.

The critical feature of the demo [...]