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
How to Accidentally “Untune” a Query
Suppose one day that you’re at your desk, when you get a telephone call from someone complaining that a query is running slow. You’re given the following setup: And the query that you’re trying to tune looks like this: If… Read More
Filters in the JOIN Clause vs. WHERE Clause: Does It Matter?
Here’s a question I’ve been asked multiple times from multiple people: “Does it matter I put filters in the join clause vs in the WHERE clause? And if so, which one is better?” Example: vs. So does it matter? Answer:… Read More
When to Use APPROX_COUNT_DISTINCT
Since Oracle 12.1, users have had the ability to use the APPROX_COUNT_DISTINCT() function. This function is supposed to be significantly faster than a COUNT(DISTINCT …). Does it really offer that big of a payout? Let’s see: First, let’s make a… Read More
How to Install Oracle 19c on a DigitalOcean Droplet Without ASM (Easiest Install Ever With Tons of Pictures)
If you don’t already have a DigitalOcean account, you’ll want to use this link here to sign up: https://m.do.co/c/97b9d595c7d3 If you use the special link above to sign up, you will get $100 of credit over a 60-day period. Disclaimer… Read More
Over-The-Fence Tuning
One of the best tips on SQL Tuning I can offer you is this: DO NOT DO OVER-THE-FENCE TUNING. It’s taken me years to finally figure this out. What do I mean by over-the-fence SQL Tuning? It goes like this:… Read More
What is Cost? How Can I Use It to Tune SQL?
When most people first start learning to tune SQL, they develop a fascination with this mysterious column on the execution plan called “cost.” What is cost? And how can you use it to tune SQL? So here’s, high level, what… Read More
Autonomous Database Specialist (1Z0-931)
You have to pass exam 1Z0-931 if you want to become an Oracle Autonomous Database specialist, . I just passed the 1Z0-931 exam today, and my source of study material was the autonomous transaction processing labs handbook from a hands-on… Read More