Re: "View" to a large schema

While I haven't seen this ASP code, I have seen some 3rd party apps that think it is a good idea to query from ALL_TABLES or ALL_OBJECTS as the *first* thing after signing on to the database. Quite maddening when it takes a long time just to sign on to the db, and you have little control over it. Noticed this first when the app tried to connect to an Oracle database for the first time that some wise developers granted SELECT on tons of tables to PUBLIC!!! Argh!!!!!

Cheers,
Brian

Karsten Farrell wrote:
>
> Hi Hypermommy, thanks for writing this:
> > Hi all,
> >
> > You'll have to forgive me if I don't use the right terms. I am
> > nowhere near an Oracle programmer and don't know the proper
> > termanology. But I hope you'll bear with me....
> >
> > Something I'm working on accesses an Oracle database we have at the
> > office. Now this database has hundreds tables in one area (schema?)
> > and we can't change that. However, the program that's accessing it
> > (which is an ASP thing) is timing out. And I suspect it's due to the
> > number of tables and such that is there becuase it works fine with
> > smaller databases.
> >
> > So, now, my issue is... what can we do in Oracle, without splitting up
> > this schema, to pull over references to just a few tables at a time.
> > For instance, if we have 10 tables that deal with podiatrists and
> > that's all we want to look at is podiatrists and not all of the
> > pediatricians and so forth, is there some way -- without moving the
> > tables themselves -- to set up a view just to the podiatrist type
> > information? Then I could set up an ODBC connection to that view
> > (whatever it is or however it's done) and see just podiatrist
> > information without the system trying to slog through all the doctors?
> >
> > Thanks in advance for whatever assistance you can give me.
> >
> > --== Hyper ==--
> >
>
> Unless your ASP code is attempting to retrieve data from every table in
> the database (which would surprise me), it shouldn't matter how many
> tables you have. So you need to narrow it down to the tables that are
> being used in the ASP and concentrate on those.
>
> When you say it works on a "smaller database," do you mean smaller as in
> fewer tables ... or do you mean smaller as in an Access (as opposed to
> an Oracle) database?
>
> Do you have a DBA or other support person whom you can contact if you
> have problems? I assume the ASP code for the SELECT (or SELECTs) has a
> WHERE clause to limit the data returned (maybe by selecting from a drop-
> down list). If it doesn't have a WHERE clause, then that's probably the
> error (since that would return every record in the table). Does the
> table have an INDEX defined (so you can more quickly retrieve just the
> records you're looking for)?
>
> Maybe you can post the relavent portion of the ASP code (where the
> SELECT clause is), so we have something more to go on. That is, if you
> have access to the ASP code.
>
> It could also be a web server parameter that's set too low, causing the
> pages to time out before they're displayed. It could also be a network
> issue. It could also be ... well, you get the point ... it's kinda hard
> to diagnose what's happening without more info regarding what you're
> trying to retrieve.
> --
> [:%s/Karsten Farrell/Oracle DBA/g]

-- 
===================================================================

Brian Peasland
oracle_dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"