Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query on ALL_CONS_COLUMNS And ALL_CONSTRAINTS When Using ODP.Net

karthi_Jul 4 2019 — edited Jul 8 2019

How to disable this query running from ODP when the application runs.

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

TkProf :

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse     7802      0.21       0.19          0          0          0           0

Execute   7802      3.67       3.60          0          0          0           0

Fetch     7802    580.19     583.00          0   27988736          0       25966

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    23406    584.08     586.80          0   27988736          0       25966

This post has been answered by Mark Williams on Jul 7 2019
Jump to Answer

Comments

Post Details

Added on Jul 4 2019
7 comments
640 views