Tag: optimizer

Being generous to the optimizer

In a perfect world, the optimizer would reach out from the server room, say to us: “Hey, lets grab a coffee and have a chat about that query of yours”. Because ultimately, that is the task we are bestowing on the optimizer – to…

Statistics on Object tables

Way back in Oracle 8.0 we introduced the “Object-Relational” database, which was “the next big thing” in the database community back then. Every vendor was scrambling to show just how cool their database technology was with the object-oriented programming paradigm. Don’t get me wrong…

EXPORT not GATHER with DBMS_STATS

Just a short post today on something that came in as a question for the upcoming Office Hours session which I thought could be covered quickly in a blog post without needing a lot of additional discussion for which Office Hours is more suited…

Another day…another "use the right datatype" post

Here’s an interesting little oddity (aka bug) with scalar queries. We’ll start with a simple working example SQL> create table t1 ( c1 number ); Table created. SQL> insert into t1 values (1); 1 row created. SQL> create table t2 ( c1 int, c2…

Standard Edition–different optimizer but still cool

One cool technique that the optimizer can employ is the BITMAP CONVERSION TO ROWIDS method to take advantage of B-tree indexes in a means that we would normally associate with a bitmap index. This can be particularly useful with multiple predicates on individually indexed…

Hybrid histograms

Just a quick post here so I could consolidate some information about histograms in 12c. On my last Office Hours session, one of the questions that was posed was asking for an explanation of the new types of histograms in use in 12c.  So…

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…