RE: DBIx::Recordset and Oracle
- From: catharine_drozdowski@xxxxxxxxxx (Catharine Drozdowski)
- Date: Tue, 26 Jul 2005 11:05:07 -0700
Hi Folks,
First of all, I want to second the database independence comment. As an
Oracle DBA who has watched application vendors try to create
applications which "run on all databases" I have seen anything from an
ISAM format kludged into a relational model which ran slower than a dead
dog no matter what hardware the mgnt threw at it.... To modern
applications, no names here, who literally waste the oracle database
because they do not use any of its features and dump all the stuff which
would fly on a database server down the the client / application tier
where is also like a very bloated dead dog.... My 2cents....
Also you will run into the semantic differences between vendors as far
as what the term database, login, schema, etc mean... As well as the
differences in implementation of security, access and privilege...
However I would imagine the writes of the generic methods have taken
that into account...
While not meaning this next comment in a personal manner, in my very
humble opinion, we need to be aware of dumbing down the DBI and
providing new perl users with little incentive to be come experts at the
ins and outs of their database of choice...knowing the sql words does
not a database expert make...On the other hand in our modern
say,learn,know in a day world, perhaps being an expert is an outdated
concept....
Secondly what is the purpose of the metadeta method... In oracle, if you
want to query the tablelist for a given user, you could approach it from
several directions.
The perl login would need to have access to the all_x views but would
need to filter on the "owner" of the table...This can be achieved by
granting the select_catalog_role to the perl login or its assigned
role...
In the case where the perl login owns the tables in question, you can
simply directly query the user_X views...no additional access
required...
If you use the all_X view then you need to be able to filter not
necessarily by the perl login, but by the oracle account who owns the
tables, usually the owner column in most views...
Unless your DBA is unusually trusting, or you yourself control the
database, the login which the perl program is given may not be the same
login as "owns" the tables in the schema.... In working reality that is
the norm...
So after all this, I would suggest that if you are looking for the
tables of a specific schema, that you should be able to filter on OWNER,
not table and I hope the method allows for that...
Be aware that some larger industrial applications may have 10s of
thousands of rows in the return set....
HTH,
catharine
-----Original Message-----
From: Job Miller [mailto:jobmiller@xxxxxxxxx]
Sent: Tuesday, July 26, 2005 10:07 AM
To: Reidy, Ron; Tielman de Villiers; dbi-users@xxxxxxxx
Subject: RE: DBIx::Recordset and Oracle
list tables or metadata queries usually go against all_tables and
all_tab_columns.
The method typically passes in a schema owner and a table name. I have
traced the equivalent jdbc method before and there is usually a
placeholder for the schema owner so that you can make it equivalent to
user_tables.
The user you are connected to can see those tables, otherwise they
wouldn't be coming back in the results.
The problem isn't the queries performance I imagine, it is that the
query is returning much more data than you actually want (which makes it
take a lot longer than it should).
That's the problem with all this generic wrapper stuff. :) Database
independence is extremely overrated.
--- "Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx> wrote:
> What takes so long for you? What query is being executed and what are
> the DB waits generated? Are you getting SYS owned objects as
> described in that old posting?
>
> -----------------
> Ron Reidy
> Lead DBA
> Array BioPharma, Inc.
>
>
> -----Original Message-----
> From: Tielman de Villiers
> [mailto:tvilliers@xxxxxxxxx]
> Sent: Tuesday, July 26, 2005 10:40 AM
> To: dbi-users@xxxxxxxx
> Subject: DBIx::Recordset and Oracle
>
>
>
> I have the same problem as described here:
> http://www.nntp.perl.org/group/perl.dbi.users/7343
> (searching from Oracle databases take extremely long because
> DBIx::Recordset tries to get metadata for all tables), even though I
> use modern versions of DBIX::Recordset (0.26), the
> DBI(1.40) and perl (5.8.0).
>
> I have tried to set '!TableFilter' with the same values I feed to
> '!Tables', but without luck. As a temporary measure I have changed
> #342 in DBIx/Database.pm to "
> my $ListTables = 0; ##DBIx::Compat::GetItem ($drv,
> 'ListTables') ;
> "
> which is not ideal, but works.
>
> Any other suggestions?
>
> Thanks
>
> --tielman
>
>
>
>
> This electronic message transmission is a PRIVATE communication which
> contains information which may be confidential or privileged.
> The information is intended
> to be for the use of the individual or entity named above. If you are
> not the intended recipient, please be aware that any disclosure,
> copying, distribution or use of the contents of this information is
> prohibited. Please notify the sender of the delivery error by
> replying to this message, or notify us by telephone (877-633-2436,
> ext. 0), and then delete it from your system.
>
>
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
.
- Follow-Ups:
- RE: DBIx::Recordset and Oracle
- From: Darren Duncan
- RE: DBIx::Recordset and Oracle
- From: Job Miller
- RE: DBIx::Recordset and Oracle
- Prev by Date: RE: DBIx::Recordset and Oracle
- Next by Date: RE: DBIx::Recordset and Oracle
- Previous by thread: RE: DBIx::Recordset and Oracle
- Next by thread: RE: DBIx::Recordset and Oracle
- Index(es):
Relevant Pages
|
