Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 349689

Summary:

[DB] ORA-01795 when using Oracle as an IN() contains more than 1000 elements

Product:

[Modeling] EMF

Reporter:

Erdal Karaca <erdal.karaca.de>

Component:

cdo.net4j.db

Assignee:

Stefan Winkler <stefan>

Status:

NEW ---

QA Contact:

Eike Stepper <stepper>

Severity:

enhancement

Priority:

CC:

robert.blust

Version:

4.13

Target Milestone:

---

Hardware:

OS:

Windows XP

See Also:

https://git.eclipse.org/r/126322
https://git.eclipse.org/r/126378

Whiteboard:

Attachments:

Description	Flags
Optimization for querying xrefs	none
Previous patch introduced a NPE when no cross refererences were available.	none

Description Erdal Karaca

2011-06-17 09:43:05 EDT

I am getting an Oracle specific error message when trying to remove an object: ORA-01795.
This means that an IN() is constructed that contains more than 1000 elements.
Currently, the list passed to IN() could be optimized by just checking if the object to be deleted has any relation to the object that is affected.

Comment 1 Erdal Karaca

2011-06-17 09:50:39 EDT

Created attachment 198181 [details]
Optimization for querying xrefs

This reduces the IN() drastically.
In my case: reduced the IN() list to one instead of 1036

Comment 2 Erdal Karaca

2011-06-22 08:17:19 EDT

Created attachment 198393 [details]
Previous patch introduced a NPE when no cross refererences were available.

Comment 3 Eike Stepper

2011-06-23 03:57:47 EDT

Moving all open enhancement requests to 4.1

Comment 4 Eike Stepper

2011-07-06 03:50:20 EDT

Stefan, can you please look at this?

Erdal, in case Stefan is fine with these changes, I guess we'd need to have this ported to the other mapping strategies, as well.

Comment 5 Stefan Winkler

2011-07-15 10:09:50 EDT

The optimization is basically a good idea. From a first look there might be problems with object inheritance. If the test suite passes this optimization, I should try to add a test case for this.

@Eike: if this kind of optimization works, it is not Oracle-specific

@Erdal: I guess you are aware, that you only optimize the query length for specific cases. The main issue still remains: in larger databases/hierarchies, the IN()-expression could still exceed the limits ...

Comment 6 Eike Stepper

2011-09-25 09:48:16 EDT

If the IN() length is an issue can't/shouldn't we split the stmt into  several small statements?

If there's some vendor-specific aspect, we could add an IDBAdapter2 interface to 4.1.

Comment 7 Eike Stepper

2012-08-14 22:51:42 EDT

Moving all open issues to 4.2. Open bugs can be ported to 4.1 maintenance after they've been fixed in master.

Comment 8 Eike Stepper

2013-06-27 04:06:42 EDT

Moving all outstanding enhancements to 4.3

Comment 9 Eike Stepper

2014-08-19 09:24:31 EDT

Moving all open enhancement requests to 4.4

Comment 10 Eike Stepper

2014-08-19 09:35:40 EDT

Moving all open enhancement requests to 4.4

Comment 11 Eike Stepper

2015-07-14 02:10:41 EDT

Moving all open bugzillas to 4.5.

Comment 12 Eike Stepper

2016-07-31 00:53:16 EDT

Moving all unaddressed bugzillas to 4.6.

Comment 13 Eike Stepper

2017-12-28 01:15:29 EST

Moving all open bugs to 4.7

Comment 14 Eclipse Genie

2018-07-19 09:00:46 EDT

New Gerrit change created: https://git.eclipse.org/r/126322

Comment 15 Eclipse Genie

2018-07-20 07:55:28 EDT

New Gerrit change created: https://git.eclipse.org/r/126378

Comment 16 Eike Stepper

2019-11-08 02:04:41 EST

Moving all unresolved issues to version 4.8-

Comment 17 Eike Stepper

2019-12-13 12:54:47 EST

Moving all unresolved issues to version 4.9

Comment 18 Eike Stepper

2020-12-11 10:37:56 EST

Moving to 4.13.