My Oracle Support Banner

Query With Large IN Clause Causes Database Server To Become Unresponsive (Doc ID 2359974.1)

Last updated on FEBRUARY 16, 2018

Applies to:

Primavera P6 Enterprise Project Portfolio Management - Version to [Release 15.2]
Information in this document applies to any platform.



In Oracle Primavera P6 WebAccess when running a tracer tool, How To Trace SQL And Java From The P6 Web Access Server (Doc ID 894429.1), we notice that the following query, with a large IN clause is consuming significant resources and causing delays in the software. The query takes the form of:

SELECT projwbs.proj_id,
  FROM projwbs, project
 WHERE project.proj_id = projwbs.proj_id
  AND ( ( (projwbs.proj_id >= 3306) AND (projwbs.proj_id <= 3320))
  OR ( (projwbs.proj_id >= 3756) AND (projwbs.proj_id <= 3767))
  OR ( (projwbs.proj_id >= 4034) AND (projwbs.proj_id <= 4043))
  OR ( (projwbs.proj_id >= 4071) AND (projwbs.proj_id <= 4090))
  OR ( (projwbs.proj_id >= 4095) AND (projwbs.proj_id <= 4119))
  OR ( (projwbs.proj_id >= 4121) AND (projwbs.proj_id <= 4131))
  OR ( (projwbs.proj_id >= 4149) AND (projwbs.proj_id <= 4166))
  OR ( (projwbs.proj_id >= 4188) AND (projwbs.proj_id <= 4197))
  ¿..Query continues to list OR and AND statements.




To view full details, 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 a vibrant support community of peers and Oracle experts.