Re: "View" to a large schema

Hi Hypermommy, thanks for writing this:
> First, thanks to everyone for all the help. Sorry I didn't give
> enough information. That's the tough part about newsgroups -- try to
> make it succinct enough that people will read it but give enough info.
>
> Turns out I left out something important. The ASP page is something I
> have no control over at this point. If I find out I absolutely have
> to, I may be able to change them, but it's Crystal Reports Ad Hoc add
> on for their ePortfolio application. If I change the page, then it
> becomes a maintenance headache when we get a new version and I have to
> re-apply all the changes. As for whether it's pulling data from all
> the tables -- not the data from the tables but the data regarding the
> tables itself. The Ad Hoc add on allows CR administrators to set up
> data sources for their users and then the users can create ad hoc
> reports from that. So it has this ASP page where the administrator
> can select the tables to include in the data source. And it's this
> page that's trying to bring back data regarding all the tables (their
> fieldnames, etc, not the actual data in them) to all the administrator
> to choose tables and relate fields and such.
>
> Thus stems my question... sorry I wasn't clear enough before. Thanks
> again for all the help.
>
>
> Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.19736cbeab49f7119897e5_at_news.la.sbcglobal.net>...
> > 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.
>

Hmm ... I see your point. If you can't change the ASP without opening another can of worms (maintenance nightmares) and you can't change the schema (the ePortfolio application's Oracle userid that owns all the tables), then you don't have many options. Bad news.

However, there might be one technique that will work for you. I can't guarantee it because I don't know how the ASP code retrieves the metadata (data about the tables). If you can (are allowed to) add a couple of Oracle userids, one for each doctor type -- for example, one named podiatrist, one named pediatrician, and so on -- then you could grant select on the tables used by each doctor type as follows:

1. connect to the schema userid that owns all the tables
2. grant select on <table_name> to podiatrist
3. repeat step 2 for each relevant podiatrist table
4. repeat steps 2 and 3 for each doctor type

Then if the ASP selects from USER_TABLES (one of Oracle's views that returns all the tables accessible by a particular userid), you could setup an ODBC connection for each of your doctor types (eg, userid podiatrist). If, however, the ASP won't allow you to change the userid or it determines the tables used by some other method, then you're hosed. Or maybe someone else can come up with another idea.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]