This is a basic tip that will save time when a query isn’t returning the expected results (typically, it’s bringing back an empty set) and you need to find out which join or WHERE clause filter is causing the problem. It’s not impossible to do, of course, but it’s nice to have a method that gets a result quickly without too many edits. Take the following query, which should run on your Oracle instance:

SELECT *
FROM DBA_VIEWS DO
JOIN DBA_TAB_COLS DTC
ON DO.VIEW_NAME = DTC.TABLE_NAME
JOIN DBA_TYPES DT
ON DTC.DATA_TYPE = DT.TYPE_NAME
WHERE DO.VIEW_NAME = 'ALL_TABLES'
AND DO.OWNER = 'SYS'
AND DTC.COLUMN_NAME = 'OWNER'
AND DTC.HIDDEN_COLUMN = 'NO'
AND DTC.NULLABLE = 'Y'
AND DT.TYPE_NAME = 'VARCHAR2'

It returns an empty set and we want to find out why. We want to start at the top of the query and work downwards until we find the problem. We select down to the first join and then execute the query, then we include the first and second joins in our selection, and so on. If we still have rows returned when we reach the WHERE clause, we know there’s nothing wrong with the joins. We select one more filter for each execution until we find what we’re looking for. What’s the problem?

If you select down to the first join, it will probably take a while to execute. Obviously this is because none of the filtering is included in this part of the query. In a real data warehouse the query could take much longer and put a load on the server. Instead, create derived tables that double up the filters from the WHERE clause. Please note that by giving the the derived table the same alias as the original table, we avoid having to edit any code further down. This query should run faster and help you to find the problem quicker:

SELECT *
FROM (SELECT * FROM DBA_VIEWS WHERE VIEW_NAME = 'ALL_TABLES') DO
JOIN (SELECT * FROM DBA_TAB_COLS WHERE COLUMN_NAME = 'OWNER') DTC
ON DO.VIEW_NAME = DTC.TABLE_NAME
JOIN DBA_TYPES DT
ON DTC.DATA_TYPE = DT.TYPE_NAME
WHERE DO.VIEW_NAME = 'ALL_TABLES'
AND DTC.COLUMN_NAME = 'OWNER'
AND DTC.HIDDEN_COLUMN = 'NO'
AND DTC.NULLABLE = 'Y'
AND DT.TYPE_NAME = 'VARCHAR2'