A query with an outer join has poor performance because it uses table scan instead of range scan.
Last updated on FEBRUARY 05, 2018
Applies to:Oracle TimesTen In-Memory Database - Version 184.108.40.206.0 to 220.127.116.11.10 [Release 11.2]
Oracle TimesTen In-Memory Database - Version 18.104.22.168.0 to 22.214.171.124.7 [Release 11.2]
Information in this document applies to any platform.
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 126.96.36.199.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
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 188.8.131.52.0 showed it was able to use range scan:
OPERATION: RowLkRangeScan <--- what it should have used
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%'
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