Find PK use

[Andrzejek]

If I have a table, and a PK field in it, and I suspect this field is used in creating a view - how can I find which view is using this PK field from my table?

I am using TOAD

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

I could be wrong, but I don't think Views have Primary Keys. You could try selecting the field(s) that comprise the PK with an AND condition so that you are sure that the views selected use all of the PK fields.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Beilstwh]

That is correct, views, as opposed to materialized views, do not have indexes. They are only stored queries that you invoke by selecting the name of the view. Any indexes used are the ones on the under laying tables that the view is selecting from.

Bill
Lead Application Developer
New York State, USA

 

[ChrisHunt]

used" how?

If I create a view like this:

CREATE OR REPLACE VIEW my_view
SELECT some_col
FROM your_table
WHERE pk_column = 2

Is that "using" the PK? How about this one:

CREATE OR REPLACE VIEW my_other_view
SELECT some_col
FROM your_table t1
INNER JOIN your_other_table t2
WHERE t1.pk_column = t2.fk_column

Or this one:

CREATE OR REPLACE VIEW my_other_other_view
SELECT pk_column
FROM your_table

Frankly, since you're using Toad, it's likely to be quicker to go to the "Used by" tab of the table, find all the views the table is used in, and manually check them for PK use (whatever that may mean).

Why do you care anyway? Shouldn't you want the PK to be used wherever possible?


-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Beilstwh]

THE OPTIMIZER DECIDES how to use the indexes on the table(s). selecting form a view is no different that simply running the query that makes it us directly.

Bill
Lead Application Developer
New York State, USA

 

[Andrzejek]

Thanks for all replies.
Let me modify my question (and forget about PK field).

Is there any way to find out which view(s) are using particular field from the table?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[ChrisHunt]

My question stands, what does "using" mean?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Andrzejek]

ChrisHunt,
Let's say I have a TabelA with fields:
ABC, [blue]XYZ[/blue], and KLM

And there are some views created in Oracle.

Let's say I have a ViewOne with the script:
Select ABC, [blue]XYZ[/blue], KLM
From TableA
Where ...

In my mind, ViewOne is 'using' (referring to / mentioning / utilizing / employing / commissioning ) [blue]XYZ[/blue] field from TableA
(there may be more technical term describing this 'using' expression)

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

I think I know what you want, but I can't provide the solution since I don't have an Oracle instance to verify my code. All databases, including Oracle, have system tables that contain information about Tables, Columns, and other database administrative information and metadata.

https://www.techonthenet.com/oracle/sys_tables/index.php

Since I am currently working with Vertica database, here's what I would code for Vertica:

SELECT COLUMN_NAME from VIEWS where COLUMN_NAME in ('a_column', 'b_column')

In this case, VIEWS is a system table and COLUMN_NAME is one of the columns in that system table.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Beilstwh]

I thought about using the following query

select owner,table_name
from all_tab_columns
where column_name in ('A_COLUMN','B_COLUMN');

but this is not a solid test because something like

create view my_view as
select a_column xxx
from my_table;

would show the column name as XXX, not as A_COLUMN.

The easiest way to do it is to find out what views reference your table and then look at the view to see if the column is used. Run the following select

SELECT A.Object_type,
A.Object_name,
A.owner view_owner,
B.Owner,
B.Object_type,
B.Object_name,
B.Object_id,
B.Status
FROM Sys.Dba_objects A,
Sys.Dba_objects B,
( SELECT Object_id, Referenced_object_id
FROM Public_dependency
START WITH Object_id IN (SELECT Object_id
FROM Sys.Dba_objects
WHERE Object_type = 'VIEW')
CONNECT BY PRIOR Referenced_object_id = Object_id) C
WHERE A.Object_id = C.Object_id
AND B.Object_id = C.Referenced_object_id
AND B.owner = 'MY_SCHEMA'
AND B.OBJECT_NAME = 'MY_TABLE'
AND A.Object_type = 'VIEW'
AND A.Owner NOT IN ('SYS', 'SYSTEM')
AND A.Object_name <> 'DUAL'
AND B.Object_name <> 'DUAL';


To see the source for the view type IN SQL*PLUS

SET LONG 32767
SET LONGC 100
SET PAGESIZE 0

SELECT TEXT
FROM ALL_VIEWS
WHERE VIEW_NAME = 'MY_VIEW'
AND OWNER = 'MY_SCHEMA';




Bill
Lead Application Developer
New York State, USA

 

[Andrzejek]

Will give it a try, thou I don't have access to Dba_objects :-(
Have to talk to my DBA...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

You can replace DBA_ with ALL_ which is a view for all the objects that you have access to. Sorry I should have used that instead

Bill
Lead Application Developer
New York State, USA

 

[Andrzejek]

Thank you Beilstwh, tried your way but I did not get any results :-(

But in TOAD, if I go to Search -> Object Search, I have a little GUI that allows me to search for anything anywhere. That's what I used. Problem solved

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

I strongly suspect that TOAD only gave you the objects to which you have permissions. That is, the ALL_ views to which Bill referred.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity