Search

Top 60 Oracle Blogs

Recent comments

August 2019

A new use for DML error logging

Many moons ago I did a short video on the DML error logging feature in Oracle. The feature has been around for many years now, and is a great tool for capturing errors during a large load without losing all of the rows that successfully loaded. You can watch that video below if you’re new to DML error logging.

But here is a possible new use case for DML error logging, even if you are not doing large scale loads. Let me describe the problem first, and then show how DML error logging might be a solution.

I’ll create a table with a constraint on it’s column

Optimizer Tricks 1

I’ve got a number of examples of clever little tricks the optimizer can do to transform your SQL before starting in on the arithmetic of optimisation. I was prompted to publish this one by a recent thread on ODC. It’s worth taking note of these tricks when you spot one as a background knowledge of what’s possible makes it much easier to interpret and trouble-shoot from execution plans. I’ve labelled this one “#1” since I may publish a few more examples in the future, and then I’ll have to catalogue them – but I’m not making any promises about that.

Here’s a table definition, and a query that’s hinted to use an index on that table.

Speaking at Oracle OpenWorld 2019

It’s been remarkably 9 years since I’ve been to Oracle OpenWorld, but will finally get the opportunity to present there again this year (with many thanks to the Oracle ACE Director program for making this possible). Details of my presentation are as follows: Conference: Oracle OpenWorld Session Type: Conference Session Session ID: CON1432 Session Title: […]

sql_patch

This note is a short follow-up to a note I wrote some time ago about validating foreign key constraints where I examined the type of SQL Oracle generates internally to do the validation between parent and child tables.  In that article I suggested (before testing) that you could create an SQL patch for the generated SQL to over-ride the plan taken by Oracle – a plan dictated to some extent by hints (including a “deprecated” ordered hint) embedded in the code. I did say that the strategy might not work for SQL optimised by SYS, but it turned out that it did.

Here’s a little script I ran to test a few variations on the theme:

AUSOUG Connect 2019 Conference Series

  AUSOUG will again be running their excellent CONNECT 2019 conference series this year at the following great venues: Monday 14th October – Rendezvous Hotel In Melbourne Wednesday 16th October –  Mercure Hotel in Perth As usual, there’s a wonderful lineup of speakers from both Australia and overseas including: Connor McDonald Scott Wesley Guy Harrison […]

Join View

It’s strange how one thing leads to another when you’re trying to check some silly little detail. This morning I wanted to find a note I’d written about the merge command and “stable sets”, and got to a draft about updatable join views that I’d started in 2016 in response to a question on OTN (as it was at the time) and finally led to a model that I’d written in 2008 showing that the manuals were wrong.

pgbench retry for repeatable read transactions — first (re)tries

pgbench retry for repeatable read transactions — first (re)tries

Trying a no-patch solution for pgbench running on repeatable read transactions, using a custom script with PL/pgSQL

In a previous post I was running pgBench on YugaByteDB in serializable isolation level. But Serializable is optimistic and requires that the transactions are re-tried when failed. But pgBench has no retry mode. There was a patch proposed in several commit fests for that, but patch acceptance is a long journey in PostgreSQL:

WIP: Pgbench Errors and serialization/deadlock retries

DDL invalidates your SQL right ?

I stumbled upon this post by optimizer guru Nigel Bayliss last week, so please have a read of that first before proceeding. But I wanted to show a simple demo of how management of cursors continues to improve with each version of the database.

Oracle 19c Automatic Indexing: My First Auto Index (Absolute Beginners)

I am SOOOO struggling with this nightmare block editor but here goes. Please excuse any formatting issues below: I thought it was time to show the new Oracle 19c Automatic Indexing feature in action and what better way than to go through how I created my first ever Automatic Index. To start, I create a […]

ORA-14300 partitioning key maps to a partition outside maximum permitted number of partitions and NULLs

While researching interval partitioning in Oracle 19c I came across a phenomenon I had already solved a long time ago but then forgot about. This time I decided to write about it so I don’t have to spend 5 minutes rediscovering the wheel. As always, if you are following along, make sure you are appropriately licensed for this feature

The Situation

Consider the following example: