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
Tune Your SQL Without Hints or Indexes: Sold Out!
THANK YOU SO MUCH FOR THE SUCCESSFUL BOOK CAMPAIGN!!! The first book I’ve ever written has officially sold out–I’ve just packed and mailed the last copy a short while ago. I only had a limited number of copies of the… 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
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges is probably my least favorite error, because more often than not, it’s an indication of a people problem, and people problems are oftentimes more difficult to fix than technical problems. Having said that, sometimes people issues can… Read More
ORA-30354: Query Rewrite Not Allowed on SYS Relations
The most common reason people get the ORA-30354: Query Rewrite Not Allowed on SYS Relations error is because they’re trying to create a materialized view on a table that exists in the SYS schema. This is a security feature in… Read More