Optimize query with NULLable column in Oracle

Question

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:

contrno in ('8','8A','8B','8C','8D','8E','8PH3A','8PH3B','8PH3C','8PHD')
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.

I can't see obvious mistakes (I'm sure query optimizer will merge conditions). Show us execution plan — Alexander Malakhov, Jun 07 '11 at 08:47
If you're not familiar with execution plans, download [Oracle's SQL Developer](http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html). It's pretty easy to get plans there. Then you could read [this SO question](http://stackoverflow.com/questions/860450/understanding-the-results-of-execute-explain-plan-in-oracle-sql-developer) — Alexander Malakhov, Jun 07 '11 at 08:57

score 2 · Accepted Answer · answered Jun 07 '11 at 17:06

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.

score -1 · Answer 2 · edited Jun 07 '11 at 08:34

-1

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)

edited Jun 07 '11 at 08:34

ErikE

48,881
23
151
196

answered Jun 07 '11 at 07:01

Akhil

7,570
1
24
23

depends on the need actually. If Application is OKAY with Dirty Read, thats all you are hinting SQL Server with. – Akhil Jun 07 '11 at 16:26
1

Recommending NOLOCK as a general performance tool is irresponsible. Please see [this](http://www.sqlservercentral.com/articles/performance+tuning/2764/) and [this which shows you can get duplicated rows](http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx). It is only for use in narrow situations and requires the knowledge of an expert to be sure it is being used properly. Also, you don't need `trsf_date is not null` because it will be excluded by the equality operator. – ErikE Jun 07 '11 at 16:39
to his question 'Is there a way to optimize following query', i just mentioned to him the available Query Hint in SQL Server. Good/Bad, to use it or not is his decision. ive just told it exists in SQL Server!! – Akhil Jun 07 '11 at 16:46

score -1 · Answer 3 · edited Jun 07 '11 at 08:37

-1

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

edited Jun 07 '11 at 08:37

ErikE

48,881
23
151
196

answered Jun 07 '11 at 07:03

tony

1

In Oracle instead of `isnull` you write `NVL(trsf_date,...)` – Alexander Malakhov Jun 07 '11 at 08:43

Optimize query with NULLable column in Oracle

3 Answers3