Insert As Select Using SEM_MATCH Is Very Slow When Using SQL Developer (Doc ID 1624310.1)

Last updated on FEBRUARY 08, 2017

Applies to:

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

Symptoms

INSERT .. SELECT SEM_MATCH is taking very long time in SQL Developer when including RDFTERM

If we replace TO_CHAR(x$RDFTERM) by TO_CHAR(x) (meaning not using RDFTERM), query execution is down to 0.9 seconds in both SQL Developer and SQL Plus.


Test case

1. Create model test

exec sem_apis.drop_sem_model('test');
drop table test_tpl;

create table test_tpl(triple sdo_rdf_triple_s);
exec sem_apis.create_sem_model('test','test_tpl','triple');
truncate table test_tpl;


2. Create table to insert into

drop table SPARQL_INSERT_STAGING_TABLE;
create table SPARQL_INSERT_STAGING_TABLE
(RDF$STC_SUB VARCHAR2(4000) NOT NULL,
 RDF$STC_PRED VARCHAR2(4000) NOT NULL,
 RDF$STC_OBJ VARCHAR2(4000) NOT NULL,
 RDF$STC_GRAPH VARCHAR2(4000));

3. Insert into the Model

declare
var1 varchar2(100);
begin
for c in 1..100000
loop
var1 := '''"' || to_char(c) || '"^^<http://www.w3.org/2001/XMLSchema#integer>''';
insert into test_tpl values(sdo_rdf_triple_s('test','','', var1));
end loop;
commit;
end;
/

4. Query the model (INSERT .. SELECT SEM_MATCH...)

set timing on
INSERT /*+ APPEND PARALLEL(8) */
INTO sparql_insert_staging_table
SELECT
  (CASE WHEN k=1 THEN '' ELSE '' END),
  (CASE WHEN k=1 THEN '' ELSE '' END),
  (CASE WHEN k=1 THEN TO_CHAR(x$RDFTERM) ELSE TO_CHAR(x$RDFTERM) END),
  NULL
FROM TABLE(SEM_MATCH(
  'SELECT ?x
  WHERE
  {
   ?x .
  }',
  SEM_Models('test'),
  NULL,
  NULL,
  NULL,
  NULL,
  'PLUS_RDFT=T'
)), (
  SELECT 1 AS k FROM DUAL
  UNION ALL
  SELECT 2 FROM DUAL
) dummy;

This takes 28 minutes from SQL Developer

but only 17 seconds in sqlplus.


5. If we take TO_CHAR(x$RDFTERM) out and have only TO_CHAR(x), it takes under a second in SQL Developer

set timing on
INSERT /*+ APPEND PARALLEL(8) */
INTO sparql_insert_staging_table
SELECT
  (CASE WHEN k=1 THEN '' ELSE '' END),
  (CASE WHEN k=1 THEN '' ELSE '' END),
  (CASE WHEN k=1 THEN TO_CHAR(x) ELSE TO_CHAR(x) END),
  NULL
FROM TABLE(SEM_MATCH(
  'SELECT ?x
  WHERE
  {
   ?x .
  }',
  SEM_Models('test'),
  NULL,
  NULL,
  NULL,
  NULL,
  'PLUS_RDFT=T'
)), (
  SELECT 1 AS k FROM DUAL
  UNION ALL
  SELECT 2 FROM DUAL
) dummy;

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