Performance Issue With Search For an Empty Value in a String Attribute Field In The Classification List (Query String Is '') (Doc ID 1959983.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Agile Engineering Data Management - Version 6.1.1.0 and later
Information in this document applies to any platform.

Symptoms

On Oracle Agile e6.1.1, e6.1.2.2 and e6.1.3.0, Classification of ATT-type classes
Find that if a query for an empty value (query string is '') is executed in a string attribute field of a classification list, the execution takes longer than executing a query that checks for NOT empty values (query string is !'') and the same number of records are returned.
The time for execution of query with !'' is about the same as for the execution of an exact query (if the same number of records are returned), while the execution time with query string '' is much longer.
Depending on the amount of string data in the classification list (number of filled attribute fields) the performance with the query string '' goes down dramatically.

Expected Behavior:
==============
Executing a query with query string '' (find all records that do not have a value in that field) in a string attribute of a classification list should not take significantly longer than other queries in that field.


The issue can be reproduced at will with the following steps:
==========================================
1. Create a Class (Type: ATT) for EDB-ARTICLE that has 5 string attributes assigned
2. Assign 100 items to the class
3. In three attributes add a value for every item record
4. In the fourth attribute 11 records have a value and 89 do not have one
5. In the fifth attribute 89 records have a value (the same value!) and 11 do not have one
6. Turn on SQL trace to measure the times and see the actions on database level
7. In the fourth attribute execute a query '' and another one with query string !''
8. Do the same in the fifth attribute.
10. Execute an exact query that the 89 records with that same value are found
11. Evaluate the SQL traces:
- Queries with '' always take more time than those with !" when the same number of records are found
- Queries with !" take about the same time as the exact match queries.
- Queries with !" and exact matches execute the same number of inserts into T_REC_DAT as records are found - Queries with '' insert a lot more records into T_REC_DAT

According to the customer the difference between the time used for " queries rises compared to !'' queries depending on the number of string values in attribute fields of the classification list. In those cases the number of Inserts into T_REC_DAT on database level goes up dramatically in '' queries.



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