RE: Perl Issues

From: <JApplewhite_at_austinisd.org>
Date: Fri, 22 Feb 2008 10:48:19 -0600
Message-ID: <OF569D2FA2.9C3595A4-ON862573F7.005A5A83-862573F7.005C4FF1@austinisd.org>


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_at_credit-suisse.com>
02/22/2008 09:29 AM

To
JApplewhite_at_austinisd.org, oracle-l_at_freelists.org 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_at_credit-suisse.com
www.credit-suisse.com

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of JApplewhite_at_austinisd.org
Sent: Thursday, February 21, 2008 4:26 PM To: oracle-l_at_freelists.org
Cc: oracle-l-bounce_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 22 2008 - 10:48:19 CST

Original text of this message