| query problem [message #336371] |
Sat, 26 July 2008 00:40  |
saagar
Messages: 79
Registered: December 2007
|
Member |
|
|
hi all
this is my query
SELECT A.CONSTRAINT_NAME,B.STATUS,B.DEFERRED,B.RELY,B.INVALID,A.COLUMN_NAME,A.POSITION,B.INDEX_NAME
FROM all_cons_columns a , dba_constraints b
where A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND b.owner not in('SYS','SYSTEM' ) AND B.CONSTRAINT_TYPE ='R';
where it gives me all FK....okies
now i have a table dba_tab_columns where in avg_col_len i have to add to show avg lenght of the FK how can i join this table also in the above query
|
|
|
|
| Re: query problem [message #336372 is a reply to message #336371] |
Sat, 26 July 2008 00:45  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
| saagar wrote on Fri, 25 July 2008 22:40 | hi all
this is my query
SELECT A.CONSTRAINT_NAME,B.STATUS,B.DEFERRED,B.RELY,B.INVALID,A.COLUMN_NAME,A.POSITION,B.INDEX_NAME
FROM all_cons_columns a , dba_constraints b
where A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND b.owner not in('SYS','SYSTEM' ) AND B.CONSTRAINT_TYPE ='R';
where it gives me all FK....okies
now i have a table dba_tab_columns where in avg_col_len i have to add to show avg lenght of the FK how can i join this table also in the above query
|
huh?
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above
|
|
|
|
|
|
|
|
|
|
| Re: query problem [message #336389 is a reply to message #336380] |
Sat, 26 July 2008 04:51 |
saagar
Messages: 79
Registered: December 2007
|
Member |
|
|
hi micheal
thank you for guiding me but then i found only FK with there avg length but i want to find unindexed foreign keys and with that there avg length
how would i got for it
CHeers
|
|
|
|
|
|
|
|
|
|
| Re: query problem [message #336412 is a reply to message #336371] |
Sat, 26 July 2008 09:46 |
Lynn T
Messages: 8
Registered: July 2008
|
Junior Member |
|
|
SELECT A.CONSTRAINT_NAME,A.COLUMN_NAME,A.POSITION,B.INDEX_NAME,
B.STATUS,B.DEFERRED,B.RELY,B.INVALID,
C.Avg_Col_len
FROM all_cons_columns a ,
dba_constraints b,
dba_tab_columns c
where A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND b.owner not in('SYS','SYSTEM' )
AND B.CONSTRAINT_TYPE ='R'
And a.owner = c.owner
and a.table_name = c.table_name
and a.column_name = c.column_name;
This seems to work. Common keys between dba_tab_columns and all_cons_columns are: owner, table_name, and column_name. I think if you link those two as shown in my last 3 lines, you'll get what you want.
|
|
|
|
| Re: query problem [message #336414 is a reply to message #336412] |
Sat, 26 July 2008 10:07 |
 |
Michel Cadot
Messages: 68727
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Actually "owner" in %cons% views is owner of the constraint not the table.
Logically, it is inconsistent to use ALL% and DBA% views, you either use ones or the others.
Regards
Michel
[Updated on: Mon, 28 July 2008 00:58] Report message to a moderator |
|
|
|
| Re: query problem [message #336507 is a reply to message #336414] |
Sun, 27 July 2008 23:14 |
saagar
Messages: 79
Registered: December 2007
|
Member |
|
|
hi
Micheal , Lynn T
thank you very much for the guidance you gave to me on this
and also the suggestions you both gave to me...
CHeers
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
