Can you pass the EMPTY SCHEMA test?

I had a question come in recently on AskTom and (paraphrasing) it ran like this:

“Why isn’t my materialized view being used to speed up my data warehouse query?”

Now before you think “Here we go, Connor is going to bitch about the question not having a test case”, this is not the case (well…sort of). 😊

The person posting the question posted the following information:

• The DDL for the materialized view,
• The query that they were trying to speed with query rewrite,
• The execution plan of the query in isolation (without the materialized view being present),
• The execution plan of the query in isolation after the materialized view was created,
• Test case with an execution of DBMS_MVIEW.EXPLAIN_MVIEW,
• The subsequent output from the MV_CAPABILITIES_TABLE table.

So this isn’t an ignorant or poorly thought out question. Someone has indeed put in plenty of work investigating this issue and they’ve hit a road block, which is exactly the reason AskTom exists. Here is the “pseudo-SQL” of the query they were trying to improve.

with my_cte as
( select ...
  from   table1,
         table2,
         view1,
         view2
  where ...
),
my_next_cte as 
(  select ...
   from   my_cte,
          table3,
          other_existing_mview
)
select col1, col2, max(...)
from   my_next_cte,
       more_tables
where  (joins etc etc)

But even though our poster has put in a lot of work, they have made one small assumption that ultimately renders their situation unresolvable, and that assumption is:

I can see their database.

This is such an easy mistake to make. You spend all day working with a schema, and you naturally assume that anyone you reach out to has knowledge of (and access to) that schema. That probably is the case with your work colleagues, but obviously is not the case when you reach out beyond your own company’s walls. I should say that hopefully that is the case, otherwise it is not SQL issue you’re trying to solve but a giant security hole issue you need to look at.😊

That is why you should try hold yourself to what I call the EMPTY SCHEMA test. The EMPTY SCHEMA test is simple – If you gave your script(s) to a stranger, could they reasonably expect to be able to successfully run your script in an empty schema in their own database, and have a better than average probability of reproducing your issue.

Note that you don’t need to be ridiculous about this. Whilst it is always going to be appreciated if your script starts the whole way back at creating a user, granting the correct privs and then building your test case in that new schema, more often than not it is going to be fine to just start with object creation, because most people will run the script in a schema with typical developer or DBA level privileges. If you can do this, you’re already ahead of the game.

I can summarise this to a 2-step process:

• Step 1: Provide a test case
• Step 2: Make sure the case passes the EMPTY SCHEMA test.

Don’t get me wrong. I often fail the “empty schema” test myself when I’m either building my own test cases, or writing solutions for customers who post on AskTom. But I try to catch myself when I do so and improve them.

So whenever you are posting on any community platform, whether it be AskTom, StackOverflow, the Database Forums, or even if you are just reaching out to a colleague on email, Twitter – just take that extra moment to double check your script. Does it pass the Empty Schema test? If it does, you’ll be surprised how many people in the Oracle community are going to be happy to pitch in and try help you out.