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 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

The following query returns no results when we expect 3514 rows

SELECT
 XMLSERIALIZE(CONTENT XMLELEMENT("p",
  XMLATTRIBUTES(
  word_id as "id",
  latlon as "latlon",
  cnt as "hits"
  )
 )) elem
FROM
(
  select * from
  (select word_id, count(*) cnt
  from
  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' )))
  GROUP BY
  word_id
  ) 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_Aliases(SDO_RDF_Alias('prof','http://www.sail-labs.com/profs/'),
  SDO_RDF_Alias('type','http://www.sail-labs.com/profs/pt#'),
  SDO_RDF_Alias('prop','http://www.sail-labs.com/profs/prop/'),
  SDO_RDF_Alias('key','http://www.sail-labs.com/profs/key/')), null,null, ' REWRITE=F '))) l --,null,null, ' REWRITE=F '
  where
  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

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