| urgent please modify this query frenz sloww performance [message #533884] |
Thu, 01 December 2011 09:46  |
 |
arivazhagancsegmailcom
Messages: 8
Registered: December 2010
Location: chennai
|
Junior Member |
|
|
CURSOR C1 IS
SELECT 'SELECT ''1'' FROM '||UCC.TABLE_NAME||' WHERE '|| UCC.COLUMN_NAME ||' = '''||P_COL_VAL||''' UNION ' COL, UCC.TABLE_NAME TAB
FROM USER_CONS_COLUMNS UCC,
USER_CONSTRAINTS UC
WHERE NVL(UC.CONSTRAINT_TYPE, 'X') = 'R'
and UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND EXISTS (SELECT CONSTRAINT_NAME
FROM USER_CONS_COLUMNS UCC1
WHERE TABLE_NAME = P_TABLE_NAME
AND COLUMN_NAME = P_COLUMN_NAME
AND CONSTRAINT_NAME = R_CONSTRAINT_NAME )
|
|
|
|
| Re: urgent please modify this query frenz sloww performance [message #533885 is a reply to message #533884] |
Thu, 01 December 2011 09:47  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
From information given:
SELECT 'SELECT ''1'' FROM '||UCC.TABLE_NAME||' WHERE '|| UCC.COLUMN_NAME ||' = '''||P_COL_VAL||''' UNION ' COL, UCC.TABLE_NAME TAB
FROM USER_CONS_COLUMNS UCC,
USER_CONSTRAINTS UC
WHERE NVL(UC.CONSTRAINT_TYPE, 'X') = 'R'
and UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND EXISTS (SELECT CONSTRAINT_NAME
FROM USER_CONS_COLUMNS UCC1
WHERE TABLE_NAME = P_TABLE_NAME
AND COLUMN_NAME = P_COLUMN_NAME
AND CONSTRAINT_NAME = R_CONSTRAINT_NAME
and 1=2
hth.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: urgent please modify this query frenz sloww performance [message #534010 is a reply to message #533884] |
Fri, 02 December 2011 02:43 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
As this post is stated to be urgent, I realized that I have more urgent things for me. So, I come now with just a few remarks:
As the posted query is based on static system views, it would be useful to check its structure in Reference book. It is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
Using TABLE_NAME column for joining them could help.
That NVL expression can be omitted: if CONSTRAINT_TYPE would be NULL, the query would not return that row anyway.
As you did not post, what is this query supposed to return, it is quite tricky to modify it without changing its result set. By the way, what are P_TABLE_NAME and P_COLUMN_NAME identifiers in the subquery condition? Maybe putting the subquery in EXISTS clause to the main SELECT statement could also help as it seems to be quite selective.
My last remark is: you posted the declaration cursor. But, as cursors are only pointers to the result set, they are not fast or slow. Their usage may be, but as you did not post it here, the only advice I may give you is: also check whether its call cannot be rewritten to be fast (by the way, how much time does "sloww" take?).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
