RE: Perl Issues
- From: JApplewhite@xxxxxxxxxxxxx
- To: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- Date: Fri, 22 Feb 2008 10:48:19 -0600
Very bad database design by the Vendor - Surprise, Surprise. There are
really only between 100 and 300 (depending on whether the Campus is
Elementary or Secondary and what options they implement) tables used by
the application. However, in its infinite wisdom, the Vendor chose to
implement a set of tables per Campus per SchoolYear. So, there's a set of
tables for School A, another set of tables for School B, and so on. We
have about 150 Campuses, so you can see that's a lot of tables.
Not only that, but there's a set of tables for each School per SchoolYear.
Since we now have 8 SchoolYears' worth of data, we now have over 150,000
tables (with over 230,000 indexes). So you can see why queries of
All_Tables is costly - there are about 420,000 rows in OBJ$.
If that weren't enough, each Campus actually accesses their data in local
dBase III "databases" on Windows servers. A nightly process synchronizes
the local dBase files with the central Oracle database tables. Now, I
started out using dBase II on CP/M in the early 80's, but never expected
to see dBase still around in the 21st Century! What a hoot!
Needless to say, we're very good at using SQL to write SQL scripts that
act on large numbers of tables, as well as producing large Export and
Import parameter files - we've found that the limit to the number of
tables in a Tables= list to be just short of 32,000. Our Developers (for
"home grown" apps) are very proficient at using Native Dynamic SQL -
necessary since they have to switch table names when they switch Campuses.
And then there's our Financials/HR App, written largely in COBOL .... but,
that's another Vendor and another silly story.... ;-)
Jack C. Applewhite - Database Administrator
Austin I.S.D.
414.9715 (phone) / 935.5929 (pager)
"Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
02/22/2008 09:29 AM
To
JApplewhite@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
cc
Subject
RE: Perl Issues
150,000 tables? Good grief! I admit that I know nothing about student
information applications, but that seems like an awful lot of tables. Why
so many?
Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of JApplewhite@xxxxxxxxxxxxx
Sent: Thursday, February 21, 2008 4:26 PM
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-l-bounce@xxxxxxxxxxxxx
Subject: Perl Issues
Our Developers wrote our Special Ed Student Info. application in Perl -
about which I know zip. Without going into gory details of why, their
code repeatedly checks for the existence of specific tables (out of the
150,000+ tables in our Production Student Info.) by doing
"Select Count(*) From All_Tables Where Table_Name = '<TableName>'"
whick is very inefficient, especially with 150,000 tables.
First of all, I want them to not check for table existence and code in
Exception Handling, but they are resistent to my suggestions.
Second, if they don't code the Exception Handling, I want them to use a
utility function that we have that more efficiently checks for table
existence and returns a Boolean - True if exists, False if not exists.
My two questions:
1. Can Exception Handling be easily done in Perl? If so, any good
examples I can point them to?
2. They say that Perl can't handle Boolean values. Is that true? Any
guidance here?
Thanks.
Jack C. Applewhite - Database Administrator
Other related posts:
