Apart from anything else, there is a hard limit on the
number of elements in
an explicit IN list, 1000 in 10G I think.
Mike.
----- Original Message -----
To: "Multiple recipients of list ODTUG-SQLPLUS-L"
<ODTUG-SQLPLUS-L fatcity.com>
Sent: Wednesday, September 24, 2008 6:50 PM
> Hi,
>
> Here's the situation:
>
> We have a database with information from log files. The
log files are
> generally FTP logs - which files got downloaded, where
they went, how big
> they are, etc - and SQL*Loader logs - rows read, rows
rejected, start
> time, etc.
>
> We are building a web page front end for the database
that allows users to
> query the logs.
>
> So, for instance, we have a query page for the
SQL*Loader logs which
> allows the user to choose a database and/or choose a
schema and/or choose
> a table and/or choose a log file and/or choose a date,
then query the
> database for those parameters.
>
> The web developer has drop-downs for each of the
possible parameters. He
> initializes each drop-down when loading the page, so
that the database
> drop-down contains all the possible databases, for
example. Each drop-down
> has a default value of "all".
>
> Here's the question:
>
> If the user selects "all" on a given drop
down, the web developer takes
> all the values in the array he uses to populate the
drop down, parses them
> into a comma-delimited string, passes that into stored
procedure via a
> VARCHAR2 variable, then uses a function to put the
delimited string into
> an array which is cast to an object and used in an IN
clause in the query.
>
> This screams "scalability problems" at me
every time I look at it. We're
> running the query against the development database
which returns small
> result sets for the databases, schemas, tables, and
logs. If we go into
> production and a lot of people start putting log files
into the database,
> we're looking at possibly hundreds of databases,
thousands of schemas,
> tens of thousands of tables, and millions of log
files.
>
> Do you agree with me? Or is the developer taking the
correct approach?
>
> If you agree with me, what would be your approach?
>
>
> Tom Berthoff
> Enterprise Technology
> x1024
> 484-562-1024
> tom.berthoffsig.com
>
>
> IMPORTANT: The information contained in this email
and/or its attachments
> is confidential. If you are not the intended recipient,
please notify the
> sender immediately by reply and immediately delete this
message and all
> its attachments. Any review, use, reproduction,
disclosure or
> dissemination of this message or any attachment by an
unintended recipient
> is strictly prohibited. Neither this message nor any
attachment is
> intended as or should be construed as an offer,
solicitation or
> recommendation to buy or sell any security or other
financial instrument.
> Neither the sender, his or her employer nor any of
their respective
> affiliates makes any warranties as to the completeness
or accuracy of any
> of the information contained herein or that this
message or any of its
> attachments is free of viruses.
>
> --
> For more information on this topic or to become a
member, visit our Web
> site at http://www.ODTUG.com
>
> Join ODTUG for The PL/SQL and The APEX Expert Gathering
of the Year!
> OPP2008 and APEXposed! 2008 will be held on October
29-30, 2008 at the
> Wyndham O'Hare, Chicago. Visit www.odtugopp.com or
> www.odtugapextraining.com for more details.
>
>
> --
> Author: Berthoff, Tom
> INET: Tom.Berthoffsig.com
>
> Fat City Hosting, San Diego, California -- http://www.fatcity.com
>
------------------------------------------------------------
---------
> To REMOVE yourself from this mailing list, send an
E-Mail message
> to: ListGurufatcity.com (note EXACT spelling of
'ListGuru') and in
> the message BODY, include a line containing: UNSUB
ODTUG-SQLPLUS-L
> (or the name of mailing list you want to be removed
from). You may
> also send the HELP command for other information (like
subscribing).
>
--
For more information on this topic or to become a member,
visit our Web site at http://www.ODTUG.com
Join ODTUG for The PL/SQL and The APEX Expert Gathering of
the Year! OPP2008 and APEXposed! 2008 will be held on
October 29-30, 2008 at the Wyndham O'Hare, Chicago. Visit
www.odtugopp.com or www.odtugapextraining.com for more
details.
--
Author: Mike Barratt
INET: mikeodtugf2s.com
Fat City Hosting, San Diego, California -- http://www.fatcity.com
------------------------------------------------------------
---------
To REMOVE yourself from this mailing list, send an E-Mail
message
to: ListGurufatcity.com (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB
ODTUG-SQLPLUS-L
(or the name of mailing list you want to be removed from).
You may
also send the HELP command for other information (like
subscribing).
|
