I have a badly designed database that has inefficient sql based on views on top of views on top of views etc. Such SQL is proving to be very inefficient. Ultimately this will get redesigned but in the meantime...
What I would like is a tool to a) Expand up the SQl in a query and thus resolve / remove the view references and then b) work out the unnecessary columns selected and remove them from the statement.
I know that there are tools that will do a) but I'm not so sure of b).
Any help would be greatly appreciated.
For a:
In 12c we added dbms_utility.expand_sql_text. Which allows you to pass in a query and fully resolve all the references so you can see the underlying tables:
set serveroutput on
declare
l_clob clob;
begin
dbms_utility.expand_sql_text (
input_sql_text => 'select * from user_source',
output_sql_text => l_clob
);
dbms_output.put_line(l_clob);
end;
/
select "A1"."NAME" "NAME",
"A1"."TYPE" "TYPE",
"A1"."LINE" "LINE",
"A1"."TEXT" "TEXT",
"A1"."ORIGIN_CON_ID" "ORIGIN_CON_ID"
from (
select "A2"."NAME" "NAME",
"A2"."TYPE" "TYPE",
"A2"."LINE" "LINE",
"A2"."TEXT" "TEXT",
"A2"."ORIGIN_CON_ID" "ORIGIN_CON_ID"
from "SYS"."INT$DBA_SOURCE" "A2"
where "A2"."OWNER" = sys_context ('USERENV','CURRENT_USER')
) "A1"I don't think there's
any tool that can do b!
The only way to know for sure whether the column is "needed" is to look at your application and see how it uses the values. This can be particularly tricky in a layered architecture. Here you may have 1 .. N levels of abstractions which "need" the value. But only to pass it up to the next layer.
Ultimately you need to map fields in the end client to database columns. And strip out those that aren't referenced in the client. That's the only way I know of to figure out which are the unnecessary columns in a query.