Performance issue when creating relationships using TCA API

(Doc ID 386479.1)

Last updated on JULY 12, 2017

Applies to:

Oracle Trading Community - Version: 11.5.10
This problem can occur on any platform.
API HZ_RELATIONSHIP_V2PUB.create_relationship backage

Symptoms

1. When creating relationships using TCA API, you find a performance issue with HZ_RELATIONSHIP_V2PUB.create_relationship package.

2.The most expensive SQL statement  in the trace file is :

Select level, relationship_id, subject_id, object_id, status, start_date,
end_date, subject_table_name, subject_type
From
hz_relationships where relationship_id <> :b3 start with object_id = :b7 and
object_table_name = :b6 and object_type = :b5 and direction_code = 'p' and
relationship_type = :b4 and relationship_id <> :b3 and start_date <= :b2
and end_date >= :b1 and status='a' connect by object_id = prior subject_id
and object_type = prior subject_type and object_table_name = prior
subject_table_name and direction_code = 'p' and relationship_type = :b4 and
relationship_id <> :b3 and start_date <= prior end_date and end_date >=
prior start_date and start_date <= :b2 and end_date >= :b1 and status='a'

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