Reply to: oracle view

[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 ?

[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?

[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]

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>'?

[expat]

Is that advisable? Given that the DBA is against seeing the SQL? I say don't, as it's an obvious try at circumvention. He will probably be watching out for this - if he's any good.

I agree with that. Ask him (by email) to help. When he doesn't, go to the next contact point. Ask for the resources you need. Raise the slow response of the view (or its unsuitability for this requirement) as an issue.

You're still talking about the view as an issue at this stage, not the boneheadedness of the DBA. BTW if the DBA really is boneheaded, it will have been noticed already.

[Bob Dalek]

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

[Spacecadet]

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.

Add the following to the statement:
/* if you spent your time helping rather than spying on me then I wouldn't have to do this */

include any choice swearwords as you see fit

[Bob Dalek]

are you saying that there is a system table that will hold the views sql string ?
(if so, i am getting a stiffy)

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.

[EternalOptimist]

yes

and will it be in any sort of schema ? like dbo.


select * from dbo.user_views

[Spacecadet]

are you saying that there is a system table that will hold the views sql string ?
(if so, i am getting a stiffy)

yes

[EternalOptimist]

ok - so its not the view holding thigs up

can you query the user_views table?

are you saying that there is a system table that will hold the views sql string ?
(if so, i am getting a stiffy)

[Bob Dalek]

All he said was that the DBA won't supply the SQL, not that he can't

Yes. That'd sound very convincing if the w@nky DBA decides to play the "Security" card with the boss. 10 points.

[Spacecadet]

Is that advisable? Given that the DBA is against seeing the SQL? I say don't, as it's an obvious try at circumvention. He will probably be watching out for this - if he's any good.

All he said was that the DBA won't supply the SQL, not that he can't

[Bob Dalek]

ok - so its not the view holding thigs up

can you query the user_views table?

Is that advisable? Given that the DBA is against seeing the SQL? I say don't, as it's an obvious try at circumvention. He will probably be watching out for this - if he's any good.

[Bob Dalek]

DBAs and specifically Oracle DBAs are always part of the problem. They seem to think that their job means stopping people from doing anything useful, rather than the reverse.
Typically the sort of person who WANTS to be a DBA should be the last person chosen for that role

Sadly often true. I'm an Oracle DBA and (cue Land of Hope and Glory on the gramophone) give the developers/whoever as much info. as I can (sensibly, of course) about queries, schemas, etc. Why the frig would I want to do the leg work? Your DBA is a tw@t.