Slow Performance of Query with Parameter On Column Containing Mostly NULL Values (Doc ID 859743.1)

Last updated on DECEMBER 20, 2016

Applies to:

TimesTen Data Server - Version 6.0.17 to 7.0.5.9.0 [Release 6.0 to 7.0]
Information in this document applies to any platform.
***Checked for relevance on 24-Mar-2011***
***Checked for relevance on 18-Sept-2013***


Symptoms

A query that has a parameter involving a column that has mostly NULL values may have performance that is considered slow, customer saw that it was ~.05 seconds to execute if there data contained all NULL values and took only from .000175- .000375 seconds when there were few or no NULL values and the query is cached (i.e. queries that are run move into a command cache for reuse).

The bad query plan the user a table lock scan in the first step of the query plan.  If a workaround is used then it would use row locks in the first step of the query plan instead.

First two steps of bad query plan shown, it attempts to use index IDX_SVC on table with column I named MOSTLYNULLS that has mostly NULL values and is being used as the parameter in this query:

   
   STEP:        1 
   LEVEL:       4 
   OPERATION:   TblLkTtreeScan 
   TBLNAME:     MY.ATTR 
   IXNAME:      TTFOREIGN_231 
   PRED:        <NULL> 
   OTHERPRED:   ATTR.ATTR_NAME = 'Public' OR 
ATTR.ATTR_NAME = 'Private'  
   
. 
   STEP:        2 
   LEVEL:       4 
   OPERATION:   RowLkHashScan 
   TBLNAME:     MY.SVC 
   IXNAME:      IDX_SVC 
   PRED:        SVC.ID = ATTR.ID 
   OTHERPRED:   SVC.MOSTLYNULLS = qmark_1 
   
.

First two steps of good woraround query plan, because it can use a meaninful index of the table with no NULLs:

   STEP:        1 
   LEVEL:       4 
   OPERATION:   RowLkTtreeScan 
   TBLNAME:     MY.SVC 
   IXNAME:      TTFOREIGN_200 
   PRED:        SVC.MOSTLYNULLS = qmark_1 
   OTHERPRED:   <NULL> 
   
. 
   STEP:        2 
   LEVEL:       4 
   OPERATION:   TblLkTtreeScan 
   TBLNAME:     MY.ATTR 
   IXNAME:      IDX_ID 
   PRED:        =ATTR.ID = SVC.ID 
   OTHERPRED:   ATTR.ATTR_NAME = 'Public' OR 
ATTR.ATTR_NAME = 'Private' 
 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms