Join With Sem_match Returns No Results If Partitioned Index Exists On Join Column

(Doc ID 1603159.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version and later
Information in this document applies to any platform.


The following query returns no results when we expect 3514 rows

  word_id as "id",
  latlon as "latlon",
  cnt as "hits"
 )) elem
  select * from
  (select word_id, count(*) cnt
  cu_ned cu
  WHERE ((cu.day_id >= to_date( '06-feb-2013 00:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND
  cu.day_id < to_date( '09-feb-2013 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )))
  ) cu,
  (SELECT profId$rdfvid rdfvid, latlon FROM TABLE(SEM_MATCH( '
  ?locProp prop:Key key:Location .
  ?locProp prop:Value ?latlon .
  ?profId prop:Value ?locProp .
  ?profId prof:Type type:LOCATION
  }', SDO_RDF_Models('sailprofiles'), null,
  SDO_RDF_Alias('key','')), null,null, ' REWRITE=F '))) l --,null,null, ' REWRITE=F '
  word_id = l.rdfvid

The query is having two tables in the FROM clause (cu and l)
The cu alias is used for access to table CU_NED
The l alias is used for the SEM_MATCH query

The two row sources are joined via condition cu.word_id = l.rdfvid
which seems to be the WORD_ID column in CU_NED table and the rdfvid column from the SEM_MATCH output

The output is taken by the XMLSERIALIZE.

The problem is that the query returns no rows when:

  1. There is a partitioned index on CU_NED.WORD_ID column AND
  2. REWRITE = T

The query returns rows but the performance is unacceptable (30seconds) if:

  1. The index on column WORD_ID is dropped
  2. REWRITE=F is specified


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