View positives:
They simplify what the developer and user see, thus making SQL
logically simple
You can put hints on a view, thus forcing the optimizer to use the
view as you know best. This can sometimes be the only way to
optimize a third party application that puts all its SQL in a black
It might be the case, that the dictionary cache cannot store any information
of type LONG (which the view text is), but that requires more investigation.
If somebody knows, please inform the rest of us.
But you can just turn on sql_trace and see the query against view$ for each
parse.
/Bjørn
This is from the Steve's page: Oracle does not cache view definitions in the
library cache or dictionary cache.
If you run this sql after selecting from any view: select * from
V$DB_OBJECT_CACHE where type = 'VIEW'
You will find views get cached like any other objects and the view will
listed as
Steve Adams has some info about this, but doesn't say how he determined
this.
http://www.ixora.com.au/q+a/0104/03174106.htm
"Khedr, Waleed" wrote:
>
> How did you determine that it's not stored in the DC?
>
> The DC is not a data store but it's a cache.
>
> Just curios.
>
> Thanks,
>
> Wa
How did you determine that it's not stored in the DC?
The DC is not a data store but it's a cache.
Just curios.
Thanks,
Waleed
-Original Message-
Sent: Thursday, February 21, 2002 3:48 PM
To: Multiple recipients of list ORACLE-L
A thing about views, that nobody else seem to have men
A thing about views, that nobody else seem to have mentioned is that the view
text is not stored in the dictionary cache. Hence, each time you hard parse
a sql statement with a view, Oracle will query view$ to get the text. Hence,
if you are at the limit of scalability or performance due to h
> Can you see optimizer going nuts...
I never had such an epiphany but I once heard the voice of the oracle and it
said, "eschew obfuscation PLEASE!"
-Original Message-
Sent: Thursday, February 21, 2002 12:39 PM
To: Multiple recipients of list ORACLE-L
I have a schema that *loves* vi
and I should have finished with
and he seems to know what he's talking about..
-Original Message-
Sent: Thursday, February 21, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L
You use that word like it's a *bad* thing to be. 8^)
-Roy
(Who was originally tempted to say:
I have a schema that *loves* views ... they have a view that is a join of 4
views which individually are joins of views and tables. Can you see
optimizer going nuts so when we tested CBO, this schema owner exclaimed
'CBO doesn't work!', so we were back to using RBO.
I have finally convinced t
You use that word like it's a *bad* thing to be. 8^)
-Roy
(Who was originally tempted to say: "Look DBA, that SQL's coming to your
server--we can do it easy, or we can do it hard, but it's coming. Do you
want to have to sleuth out why your db is dog-slow every day at 3:30 when my
users are ru
uh-oh... a PROGRAMMER has been lurking...
:)
-Original Message-
Sent: Thursday, February 21, 2002 1:24 PM
To: Multiple recipients of list ORACLE-L
It seems to me that you're not dinging views per se here--you're against the
dev's intended use of production data. So if those same S
TED]>
cc:
Subject: RE: Anybody against using views?
It seems to me that you're not dinging views per se here--you're against
the
dev's intended use of production data. So if those same SELECT statements
that make up the view were instead baked into the crysta
It seems to me that you're not dinging views per se here--you're against the
dev's intended use of production data. So if those same SELECT statements
that make up the view were instead baked into the crystal report file & sent
anew every time the report was executed, it'd be the same problem (ma
I would recommend against multiple layers of views, people can become
addicted to them -- sometimes you can see five or more layers of views, then
people wonder why the server is so slow.
Especially if these views rely on database links.
2 or 3 layers of views is OK, I suppose.
Regards,
Patrice
Hello Ron
I do not know this package because we use another reporter (business
Objects).
If it is like our there are clear pro and con for using views:
Pro: you CONTROL the access to oracle.
Con: YOU control the access to oracle.
The pro means that you write the selects and can tune them and o
One of the mains reasons we used views at one place I worked was the "table
drop" factor. We never created a table that we didn't immediately create a
"select * from ..." view on. We then granted the privileges on the views
instead of the tables so that if we ever had to drop and recreate the ta
Per my morning's experience today... some DEvelopers know what they are
doing... many duhvelopers to every developer though... and the phrase "oh I
tested it" means little sometimes.
Steve... you actually get to TUNE the SQL that gets rolled out to people?
Lucky you... I inherited most of mine, a
If you're using views properly, they're wonderful and allow a phenomenal
amount of flexibility to the designer, but like all powerful beings, they
must use their power for good (complex query manipulation) rather than evil
(resource hogging).
HTH,
Bambi.
--
Please see the official ORACLE-L FAQ:
Trouble with some views is, with certain front ends (Business Objects likes
this particularly well) the only way to get it to accept the queries is to
create MULTIPLE views on the same table and do recursive "self" joins that
way. It makes for very untidy SQL that is practically un-tune-able. I
I like views... right now I'm working on the one out my office window... a
spectacular view of the Tobacco Root range across the Gallatin Valley. :-)
Regarding database views, you can't say they're entirely good or bad. They
may simplify things for reporting but they could complicate your ability
IMO views are often used as a substitute for creating reporting
structures.
Using views makes for easy report/SQL creation, but tends to be a
tuning and performance nightmare. It's hard to tune, and will likely
never perform well.
I'm going through similar issues here right now. A number of
Just yesterday a developer was having a performance problem with a complicated report
was taking about 7 minutes to complete. She was grabbing the table data and doing all
the sorting, summing, etc. in the report. I created four views to feed the report. It
now runs in less than a minute. In th
22 matches
Mail list logo
