Optimize query with NULLable column in Oracle

Is there a way to optimize following query? It returns the right records but takes more than a minute 开发者_如何转开发to execute.

select STATUS, SUBNO, TRUNC(TRSF_DATE) TRSF_DATE
from
(
    select STATUS, SUBNO, TRUNC(TRSF_DATE) TRSF_DATE
    from tbl        where 
       trsf_date is not null and
       contrno in ('8', '8A', '8B', '8C', '8D', '8E', '8PH3A', '8PH3B', '8PH3C', '8PHD')
)
where trsf_date = to_date('5/21/2011', 'mm/dd/yyyy')**

The requirements are to return records where:

1. contrno in ('8','8A','8B','8C','8D','8E','8PH3A','8PH3B','8PH3C','8PHD')
2. trsf_date = some specific date

Note that the trsf_date column is NULLable and I have to use trsf_date in the WHERE clause. That is why I used an inner query to first fetch NOT NULL rows, then select rows from that. Otherwise the query will get stuck and not return any rows.

DBMSes treat NULLs as unknown when abiding by ANSI. This means that an expression like Column = /value/ will automatically exclude NULLs with no further conditions. So the following simplified query should do the job:

SELECT
   STATUS,
   SUBNO,
   TRUNC(TRSF_DATE) TRSF_DATE
FROM crm_user_info
WHERE
   TRSF_DATE = To_Date('5/21/2011', 'mm/dd/yyyy')
   AND CONTRNO IN ('8', '8A', '8B', '8C', '8D', '8E', '8PH3A', '8PH3B', '8PH3C', '8PHD')

To speed this up you can put indexes on the TRSF_DATE and CONTRNO columns.

You don't need an inner query. You can combine the WHERE conditions into 1 query:

select STATUS, SUBNO, TRUNC(TRSF_DATE) TRSF_DATE
    from crm_user_info
    where 
       trsf_date is not null and
       trsf_date = to_date('5/21/2011', 'mm/dd/yyyy') and
       contrno in ('8', '8A', '8B', '8C', '8D', '8E', '8PH3A', '8PH3B', '8PH3C', '8PHD')

Also, to speed up the query, you can use query hints such as WITH(NOLOCK) in SQL Server:

select STATUS, SUBNO, TRUNC(TRSF_DATE) TRSF_DATE
        from crm_user_info WITH(NOLOCK)

select STATUS, SUBNO, TRSF_DATE
from crm_user_info
where isnull(trsf_date,'01/Jan/1753') = '07/Jun/2011'
and contrno in ('8', '8A', '8B', '8C', '8D', '8E', '8PH3A', '8PH3B', '8PH3C', '8PHD')