Frequent Query on ALL_CONS_COLUMNS And ALL_CONSTRAINTS When Using ODP.Net Statement Caching (Doc ID 1386371.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Data Provider for .NET - Version 10.1.0.3 and later
Generic Windows

***Checked for relevance on 03-Aug-2015***

Symptoms

After migrating to 11.2 Oracle Data Provider for .Net the following query is executed frequently:

SELECT C1, C2, C3
FROM (SELECT ACC.COLUMN_NAME C1, ACC.CONSTRAINT_NAME C2, AC.CONSTRAINT_TYPE C3 FROM ALL_CONS_COLUMNS ACC, ALL_CONSTRAINTS AC
WHERE (AC.CONSTRAINT_TYPE = 'P' OR AC.CONSTRAINT_TYPE = 'U')
AND AC.TABLE_NAME = :B2
AND AC.OWNER = :B1
AND AC.TABLE_NAME = ACC.TABLE_NAME
AND AC.OWNER = ACC.OWNER
AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
UNION SELECT AIC.COLUMN_NAME C1, AI.INDEX_NAME C2, 'U' C3
FROM ALL_INDEXES AI, ALL_IND_COLUMNS AIC
WHERE AI.UNIQUENESS = 'UNIQUE'
AND AI.TABLE_NAME = :B2
AND AI.TABLE_OWNER= :B1
AND AI.TABLE_NAME = AIC.TABLE_NAME
AND AI.TABLE_OWNER = AIC.TABLE_OWNER
AND AI.INDEX_NAME = AIC.INDEX_NAME
AND AI.OWNER = AIC.INDEX_OWNER)
ORDER BY 3, 2, 1



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