Home » SQL & PL/SQL » SQL & PL/SQL » query problem
query problem [message #336371] Sat, 26 July 2008 00:40 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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 #336374 is a reply to message #336371] Sat, 26 July 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how can i join this table also in the above query

You can't, you have to change your query in order to add the columns to join.

Regards
Michel
Re: query problem [message #336376 is a reply to message #336374] Sat, 26 July 2008 01:12 Go to previous messageGo to next message
saagar
Messages: 79
Registered: December 2007
Member
HI

can you please guide me how should i re-structure this query

please

Re: query problem [message #336380 is a reply to message #336376] Sat, 26 July 2008 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add the missing columns.
desc all_cons_columns
desc all_tab_columns

Regards
Michel
Re: query problem [message #336389 is a reply to message #336380] Sat, 26 July 2008 04:51 Go to previous messageGo to next message
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 #336394 is a reply to message #336389] Sat, 26 July 2008 05:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://asktom.oracle.com/tkyte/unindex/index.html

Regards

Raj
Re: query problem [message #336397 is a reply to message #336394] Sat, 26 July 2008 05:50 Go to previous messageGo to next message
saagar
Messages: 79
Registered: December 2007
Member
hi
that query returns nothin

all blank

CHeers
Re: query problem [message #336406 is a reply to message #336371] Sat, 26 July 2008 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Then you have no unindexed FK.
Re: query problem [message #336412 is a reply to message #336371] Sat, 26 July 2008 09:46 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
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

Previous Topic: ANSI SQL Correlation Problem
Next Topic: How to Make Schema Name as a Variable in Trigger
Goto Forum:
  


Current Time: Sat Dec 03 11:53:59 CST 2016

Total time taken to generate the page: 0.04471 seconds