oracle view

[TheFaQQer]

Any object's DDL:

SET SERVEROUT ON SIZE 1000000 LONG 1000000 PAGES 9999 LINES 200
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SE SSION_TRANSFORM,'SQLTERMINATOR',true);

SELECT DBMS_METADATA.GET_DDL('<object_type>','<object_nam e>','<owner>')
FROM DUAL;

The DBA may well dob you in.

Typical DBA, overcomplicating things!

What's wrong with SELECT text FROM all_views WHERE view_name = '<blah>'?

[TheFaQQer]

Call the DBA "Burleson" - if he's got any current savvy, he'll be well pissed off.

A bit but still made me

[TheFaQQer]

i have a link to an oracle view. I want to query it and link it to some indexed tables, I am using decode quite a bit.

the view returns 3 million rows, my result is 2.5 k rows.


the problem is that my 2.5k rows takes 40 minutes to produce. The oracle dba will not supply the view sql, he will not let me build any temp tables or functions.

is there any other way I can speed this up ?

You could try for a materialized view instead, but since the DBA won't let you create temporary tables, functions, or even give you the SQL behind the view, that's a long shot.

Is it a standard view in Apps, or is it bespoke / not in Apps? If it is, then you could always ask some nice contractor / consultant / website somewhere if they can describe the view for you in their system and tell you what sits behind it?

If it were me, I'd just look at user_views, get the code and go from there, but that's just me. DBAs - who needs them?

[EternalOptimist]

You could try for a materialized view instead, but since the DBA won't let you create temporary tables, functions, or even give you the SQL behind the view, that's a long shot.

Is it a standard view in Apps, or is it bespoke / not in Apps? If it is, then you could always ask some nice contractor / consultant / website somewhere if they can describe the view for you in their system and tell you what sits behind it?

If it were me, I'd just look at user_views, get the code and go from there, but that's just me. DBAs - who needs them?

merci.

I got it in msaccess using select * from all_views (great minds eh faqqer)


now I know where the data is coming from I have a fighting chance of streamlining the process.

if I put a simple select on top of the view, will they be combined into a single statement before being executed ? I am guessing so. but what happens if I go the whole hog and put the group by on top, would that be applied before the select , or after ?