"View" to a large schema

#1

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 ==--

#2

hypermommy (AT) comcast (DOT) net (Hypermommy) wrote:

Quote:

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 ==--

You can design the ASP code that accesses the Oracle data to
'screen' for podiatrists' data ( or any other criteria that can be 'determined' by the database) - your query
would include a 'WHERE' clause to limit the data returned..

I suspect that, if you really have 10 tables that relate to podiatrists, and you need some data from each of them, you need
to see about a redesign of the database structure or you will want to create some views or tables that pre-join the tables
to get the fields you want for reporting purposes.
Go to
http://tahiti.oracle.com and
review the docs on Sql statements ...

#3

Hi Hypermommy, thanks for writing this:

Quote:

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]

#4

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:

Quote:

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 (DOT) 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"

#5

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 (DOT) com> wrote

Quote:

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.

#6

Karsten,

Thanks SO VERY MUCH for the assistance... not only for the idea but
also for explaining it so clearly so that a non-Oracle person could
get it. You've given me a very clear avenue of exploration and I
appreciate it greatly!!!

-= Hypermommy =-

Karsten Farrell <kfarrell (AT) belgariad (DOT) com> wrote

<<< snipped for brevity >>>

Quote:

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.

#7

Karsten Farrell <kfarrell (AT) belgariad (DOT) com> wrote

<snipped for brevity>

Quote:

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.

Hi again, Karsten,

I've got a bit of a follow up on this but don't know if this is an
Oracle related problem or ASP or my machine or what at this point.

I got permission from mgmt to possibly make *small* changes in the ASP
code. So I got to looking at the code that was giving me problems.
Basically, what it does is opens a recordset listing information about
the tables defined in the catalog that are accessible to a given user.

set objTableRS = objConn.OpenSchema(adSchemaTables)

That sounds like it would do what we were talking about -- the
selection of only tables that have had rights granted to the user that
logs in.

But then I got to wondering... becuase this is all stemming from the
idea that the page is hanging and we thought it was due to the number
of records. So I put replaced the "do until rs.EOF" with a for loop
that would let it run 10 times. That worked like a charm (except for
one thing, which I'll get to in a moment). And then I said to myself,
well, how many tables do I have in here so I tried to print out a
recordcount of the obTableRS. Well, turns out I didn't have any. So
I checked for BOF and EOF and sure enough, I seem to have an empty
recordset. Which would explain why objTableRS("Table_Name") kept
coming back blank.

So I've got an empty recordset it says...... but then there's one more
twist. I can set my upper limit on the for loop to 1000 and
everything runs just fine. However, if I go back to my "do until
rs.EOF" loop, the darn page hangs up. Can't be something else that's
hanging the page becuase when I replace the do loop with a for loop,
all works well. Can't be that it's not moving becuase I did
double-check that I've got an objTableRS.MoveNext in there.

I'm just stumped..... do you see anything in here, anyone, that you
can clue me into?

Thanks.

#8

Hi Hypermommy, thanks for writing this:

Quote:

Karsten Farrell <kfarrell (AT) belgariad (DOT) com> wrote

snipped for brevity

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.

Hi again, Karsten,

I've got a bit of a follow up on this but don't know if this is an
Oracle related problem or ASP or my machine or what at this point.

I got permission from mgmt to possibly make *small* changes in the ASP
code. So I got to looking at the code that was giving me problems.
Basically, what it does is opens a recordset listing information about
the tables defined in the catalog that are accessible to a given user.

set objTableRS = objConn.OpenSchema(adSchemaTables)

That sounds like it would do what we were talking about -- the
selection of only tables that have had rights granted to the user that
logs in.

But then I got to wondering... becuase this is all stemming from the
idea that the page is hanging and we thought it was due to the number
of records. So I put replaced the "do until rs.EOF" with a for loop
that would let it run 10 times. That worked like a charm (except for
one thing, which I'll get to in a moment). And then I said to myself,
well, how many tables do I have in here so I tried to print out a
recordcount of the obTableRS. Well, turns out I didn't have any. So
I checked for BOF and EOF and sure enough, I seem to have an empty
recordset. Which would explain why objTableRS("Table_Name") kept
coming back blank.

So I've got an empty recordset it says...... but then there's one more
twist. I can set my upper limit on the for loop to 1000 and
everything runs just fine. However, if I go back to my "do until
rs.EOF" loop, the darn page hangs up. Can't be something else that's
hanging the page becuase when I replace the do loop with a for loop,
all works well. Can't be that it's not moving becuase I did
double-check that I've got an objTableRS.MoveNext in there.

I'm just stumped..... do you see anything in here, anyone, that you
can clue me into?

Thanks.

Again, without the code in front of me, I'm just taking a shot in the
dark. Since it seems to work with a FOR loop, but not a DO-UNTIL loop,
it's beginning to sound like you are trying to open a connection AFTER
you've already reached the rs.EOF. Do you see anywhere that you close
the connection? If the code already detects an EOF when it comes to the
DO-UNTIL statement, then the DO won't get executed and you'll have an
empty RS. The FOR would execute, however, because it loops a specific
number of times.

I'm not an ASP expert (we use the similar JSP), so I hope someone else
will jump in here.
--
[:%s/Karsten Farrell/Oracle DBA/g]

#9

Karsten Farrell <kfarrell (AT) belgariad (DOT) com> wrote

Quote:

Hi Hypermommy, thanks for writing this:
Karsten Farrell <kfarrell (AT) belgariad (DOT) com> wrote

snipped for brevity

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.

Hi again, Karsten,

I've got a bit of a follow up on this but don't know if this is an
Oracle related problem or ASP or my machine or what at this point.

I got permission from mgmt to possibly make *small* changes in the ASP
code. So I got to looking at the code that was giving me problems.
Basically, what it does is opens a recordset listing information about
the tables defined in the catalog that are accessible to a given user.

set objTableRS = objConn.OpenSchema(adSchemaTables)

That sounds like it would do what we were talking about -- the
selection of only tables that have had rights granted to the user that
logs in.

But then I got to wondering... becuase this is all stemming from the
idea that the page is hanging and we thought it was due to the number
of records. So I put replaced the "do until rs.EOF" with a for loop
that would let it run 10 times. That worked like a charm (except for
one thing, which I'll get to in a moment). And then I said to myself,
well, how many tables do I have in here so I tried to print out a
recordcount of the obTableRS. Well, turns out I didn't have any. So
I checked for BOF and EOF and sure enough, I seem to have an empty
recordset. Which would explain why objTableRS("Table_Name") kept
coming back blank.

So I've got an empty recordset it says...... but then there's one more
twist. I can set my upper limit on the for loop to 1000 and
everything runs just fine. However, if I go back to my "do until
rs.EOF" loop, the darn page hangs up. Can't be something else that's
hanging the page becuase when I replace the do loop with a for loop,
all works well. Can't be that it's not moving becuase I did
double-check that I've got an objTableRS.MoveNext in there.

I'm just stumped..... do you see anything in here, anyone, that you
can clue me into?

Thanks.

Again, without the code in front of me, I'm just taking a shot in the
dark. Since it seems to work with a FOR loop, but not a DO-UNTIL loop,
it's beginning to sound like you are trying to open a connection AFTER
you've already reached the rs.EOF. Do you see anywhere that you close
the connection? If the code already detects an EOF when it comes to the
DO-UNTIL statement, then the DO won't get executed and you'll have an
empty RS. The FOR would execute, however, because it loops a specific
number of times.

I'm not an ASP expert (we use the similar JSP), so I hope someone else
will jump in here.

As for jumping in, I hope so too, but I do want to thank you for all
the valliant help you're giving me.

I'll post the code Monday when I'm back at my work computer. Now that
I've been able to dig into it a little bit I can certainly post it. I
just didn't do that Friday becuase it was right before leaving time
and I had friends to do and places to see :-).

thanks again for all your help!!! Have a great weekend.

#10

Karsten Farrell <kfarrell (AT) belgariad (DOT) com> wrote

<snipped for brevity>

Quote:

Again, without the code in front of me, I'm just taking a shot in the
dark. Since it seems to work with a FOR loop, but not a DO-UNTIL loop,
it's beginning to sound like you are trying to open a connection AFTER
you've already reached the rs.EOF. Do you see anywhere that you close
the connection? If the code already detects an EOF when it comes to the
DO-UNTIL statement, then the DO won't get executed and you'll have an
empty RS. The FOR would execute, however, because it loops a specific
number of times.

I'm not an ASP expert (we use the similar JSP), so I hope someone else
will jump in here.

Hi all,

I'm posting the code, now that I kind of understand what's going on.
For a quick recap, I've found that if I put in a for loop instead of
the do while not loop then I can iterate up to 1000 times and the page
doesn't freeze up. However, if I use the do loop, it just freezes. A
couple of things I don't understand that I hope you can help me
with.....

1) The OpenSchema method doesn't appear to be working since I get back
an empty recordset (as evidenced by the fact that when I check for BOF
and EOF they're both true). Any clue why? It does work with a SQL
server table... shouldn't it also work with Oracle? I know that the
login being used in the DSN has full rights to at least see the
tables.

2) If EOF is true, why would the do while cause things to freeze up.
I can use a 1000 iteration for loop, but not a do while but if it's
EOF then the do while should never come into play, right?

Thanks in advance for any help you can give me.

Function listTablesWeb(nDSN)
dim objConn, objTableRS, objNoneSupportedTableRS
dim bFound, aNoneSupportedTablesArray, sTempText
dim nCounter
Response.Write "<br>" & nDSN

on error resume next
Set objConn = Server.CreateObject("ADODB.Connection")
'objConn.open nDSN
objConn.Open sDSN, sUsername, sPassword', sDriveOptions

If Err.Number <> 0 Then
localError = Err.Description
else
sTempText = ""
on error resume next
set objTableRS = objConn.OpenSchema(adSchemaTables)

objTableRS.movelast
objTableRS.movefirst
' response.write ("<option>There are " & objTableRS.recordcount & "
tables.</option>")
if objTables.BOF then
response.write("<option>We are BOF</option>")
end if
if objTables.EOF then
response.write("<option>We are EOF</option>")
end if

' Do While Not objTableRS.EOF
' If LCase(objTableRS("TABLE_TYPE").Value) = LCase("TABLE") or
LCase(objTableRS("TABLE_TYPE").value) = LCase("VIEW") Then
' Response.Write("<option value='" &
trim(objTableRS("Table_Name").Value) & "'>" &
trim(objTableRS("Table_Name").Value) & "</option>" & vbCrLf)
' End If
' objTableRS.MoveNext
' Loop
objTableRS.Close
objConn.Close
end if

Set objTableRS = Nothing
Set objConn = Nothing
end function