Search

Top 60 Oracle Blogs

Recent comments

19c

METHOD_OPT Default In Oracle Autonomous Databases (She’ll Drive The Big Car)

  In a recent post on Invisible Automatic Indexes, I was puzzled by a couple of “oddities” in relation to some behaviour in the Oracle Autonomous Database Cloud environments. The first one was how Oracle appeared to be creating Histograms on a much more regular basis than it had previously. As one can see in […]

Oracle 19c Automatic Indexing: Function-Based Indexes? Part II (If You Can See Me)

In my previous post, I discussed how Automatic Indexing does not currently support creating an index based on a function or expression predicate, even if it’s an equality predicate. You must manually create the associated function-based index. However, if you have access to the application, there’s a better strategy when frequently searching on a function-based […]

Oracle 19c Automatic Indexing: Function-Based Indexes? (No Plan)

I previously discussed how Automatic Indexing only currently supports Equality based predicates. The question I have today is does Automatic Indexing support function-based indexes? Let’s take a look. The below DAVID table has the key column NAME which is an effectively unique VARCHAR2 column: If we look at the current details of the table columns: […]

Blockchain tables are here in 19c!

Slow down there….Take a breath. Because if you’ve read the title of this post and you’re already logging on to your 19c database, I need to you STOP.  Before you go anywhere, please read this post first.

OK … lets proceed, now that you’re not going to end up with a table you can’t drop!

There’s been a lot of interest in blockchain technology and rather than having to go out and purchase a completely bespoke solution, there has naturally been a lot of interest in being able to simply utilise blockchain technology in the same place where your other business critical data is located, namely, an Oracle database!

Oracle Database 19c Automatic Indexing: Invisible Indexes Oddity (Wild Eyed Boy From Freecloud)

There have been a couple of “oddities” in relation to both Oracle Autonomous Databases and Automatic Indexing behaviour that I’ve seen frequently enough now (on Oracle 19.5.0.0.0) to make it worth a quick blog article. The following is a simple test case that highlights both these issues. I’ll begin with a basic table, that has […]

Oracle Database 19c Automatic Indexing: Index Compression Update (New Morning)

  I was reminded in a recent comment by Rajeshwaran Jeyabal that I hadn’t updated my post on Automatic Indexing with Advanced Compression that’s in need of a couple of amendments. Initially when Automatic Indexing was released, the ability to set Advanced Compression was NOT included in the official documentation, although the EXEC DBMS_AUTO_INDEX.CONFIGURE( ‘AUTO_INDEX_COMPRESSION‘ […]

Oracle 19c Automatic Indexing: Non-Equality Predicates Part II (Let’s Spend The Night Together)

In my previous post in this series, I discussed out Automatic Indexing currently does not consider Non-Equality predicates. Automatic Indexing will index columns based only on Equality predicates. So how does Oracle handle the scenario when an SQL has a mixture of both Equality and Non-Equality predicates? I’ll begin by creating two very similar tables, […]

Oracle 19c Automatic Indexing: Non-Equality Predicates Part I (Lucy Can’t Dance)

  I’ve been waiting a while before posting a series on the various limitations associated with Automatic Indexing, in order to see how the feature matures over time. The following have all been re-tested post 1 January 2021 on the Autonomous ATP Database Cloud service, using Oracle Database version 19.5.0.0.0. In the Oracle Documentation (including […]

Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part II (Neighbourhood Threat)

In my first post on Automatic Indexing on Partitioned Tables, I discussed how Automatic Indexing (AI) can now create a Non-Partitioned index if deemed the most effective indexing structure (this wasn’t supported when AI was initially released). A Non-Partitioned index is indeed likely the most efficient indexing structure if the underlying table has many partitions […]

A mostly academic post about startup triggers and when they fire in Real Application Clusters

Out of curiosity I wanted to figure out when and how often startup triggers fire in RAC 19c. Do they fire when all instances of the database are up (e.g. only once), or do they fire for once each instance? A little experiment reveals the behaviour. As the title reads this is mostly academic, I hope it’ll save me 5 minutes some time in the future. Maybe it saves you 5 minutes right now?

I am using Oracle 19.8.0 RAC in a two node configuration, with the database created as a container database (CDB). To figure out what happens with the startup trigger I created a little bit of infrastructure, like so: