A query with an outer join has poor performance because it uses table scan instead of range scan.

(Doc ID 1966419.1)

Last updated on AUGUST 29, 2016

Applies to:

Oracle TimesTen In-Memory Database - Version 11.2.2.6.0 to 11.2.2.6.10 [Release 11.2]
Oracle TimesTen In-Memory Database - Version 11.2.2.7.0 to 11.2.2.7.7 [Release 11.2]
Information in this document applies to any platform.

Symptoms

A query with an outer join has poor performance or high cpu use because it uses table scan instead of range scan.  This query worked fine in 11.2.2.5.0.  

Query has a FROM clause specifying a Left Outer Join condition (+):

FROM tablename2, tablename
WHERE tablename2.type = 'User'
AND tablename2.uid = tablename.uid(+)
AND tablename.type(+) = 'XTYPE'
AND( TABLENAME.VALUE LIKE '%100%' );

User noticed that the query plan had table scan and claimed there was no index it could use:

OPERATION: TblLkSerialScan <---- unexpected table scan
TBLNAME: TABLENAME
IXNAME: <NULL> <---- index not being used.
INDEXED CONDITION: <NULL> <---- claims no indexed condition
NOT INDEXED: TABLENAME2.UID = TABLENAME.UID AND TABLENAME.TYPE =
'XTYPE' AND TABLENAME.VALUE LIKE '%100%'

The plan from 11.2.2.5.0 showed it was able to use range scan:

OPERATION:           RowLkRangeScan     <--- what it should have used
TBLNAME:              TABLENAME
IXNAME:                TTFOREIGN_566            <--- found an index to use
INDEXED CONDITION:   TABLENAME2.UID = TABLENAME.UID   <--- Indexed condition is mentioned

NOT INDEXED:         TABLENAME.TYPE = 'XTYPE' AND TABLENAME.VALUE LIKE '%100%'

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