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')