There are two ways to tell if statistics are stale in Oracle. One way is to allow Oracle to tell you if it thinks the statistics are stale. The other way is to compare the statistics of what Oracle thinks… Read More
How To Do a CREATE OR REPLACE TABLE In Oracle
I’ve always appreciated the CREATE OR REPLACE functionality in Oracle for views, packages and triggers, and I’ve often wanted a similar CREATE OR REPLACE TABLE. Many times, I’ll find myself needing to quickly drop and re-create a table while I’m… Read More
Conditional Compilation Errors in Pl/SQL
Here’s a list of common Pl/SQL Conditional compilation errors, along with what may have caused them. This should allow you to know where to look in your code for errors. This isn’t a complete list, but it should be enough… Read More
Index Range Scan vs. Leading Wildcard
Can you use an index range scan with a LIKE if you have a leading wildcard in your expression? e.g. Most people would say “no” but, I actually found a way to “trick” Oracle into doing so using bind variables,… Read More
Oracle: Coding Around NULL Values
In Oracle, NULLs can complicate your code–a lot. Suppose, for example, you want to see if two different values are the same in a SQL query. You might be tempted to write something like this: The problem with the above… Read More
The *LOWEST* Possible Cost For a Query
Quick: If you had to pick the least-expensive-cost query you could think of, what would that query look like? …(hums Jeopardy theme)…. Ding! Time’s up!! Most people respond with something “select from a one-block table.” Let’s give it a shot:… Read More
Get the Oracle Database Version Number
If you’re looking for how to get the Oracle Database Version, there are lots of ways to do so, depending on what your end-goal is. Running A Quick Query The easiest, simplest, fastest method is to run the following query:… Read More
ORA-28104: input value for statement_types is not valid.
If you’re receiving the ORA-28104: input value for statement_types is not valid error, it might be because you are trying to apply a row-level security policy to a table in a database that has been upgraded to 12.2 or greater… Read More
Are Single-Table Hash Clusters USELESS?!?
I remember being at an Oracle conference a few years ago, and I attended a session that was related to SQL Tuning. One of the things that was discussed in this lecture was Oracle Single Table Hash Clusters, and I… Read More
Calculating Scalar Subquery Cache Size
Whenever you have a scalar subquery in Oracle, there are a few different options that Oracle can use for executing the subquery. Oracle can either run the query once for each row returned by the records in the from clause.… Read More