Debugging sql joins and filters in Oracle

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'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.