RE: Sanity check

---

From: &nbsp;"Berthoff, Tom" <Tom.Berthoffsig.com>;
Reply-To: &nbsp;ODTUG-SQLPLUS-Lfatcity.com
To: ; Multiple recipients of list ODTUG-SQLPLUS-L <ODTUG-SQLPLUS-Lfatcity.com>
Subject: &nbsp;Sanity check
Date: &nbsp;Wed, 24 Sep 2008 09:30:39 -0800
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. An example follows at the end of the message.

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?

Here's the procedure, with query:

&nbsp; &nbsp; PROCEDURE reporting_sqlldrlog (
 &nbsp; &nbsp;   p_rc_metrics &nbsp; &nbsp; &nbsp;  OUT&nbsp; &nbsp;   ;rc_generic,
 &nbsp; &nbsp; &nbsp; p_str_database &nbsp;   ; IN&nbsp; &nbsp;   ; VARCHAR,
&nbsp;   ; &nbsp; p_str_schema &nbsp;   ; &nbsp; IN&nbsp;   ; &nbsp; VARCHAR,
&nbsp;   ; &nbsp; p_str_instance_id&nbsp;  IN &nbsp; &nbsp;   VARCHAR
&nbsp; &nbsp; )
 &nbsp; &nbsp;IS
&nbsp; &nbsp; BEGIN
 &nbsp;   ;  DBMS_APPLICATION_INFO.set_action ('reporting_sqlldrlog');

   ; &nbsp; &nbsp; &nbsp; OPEN p_rc_metrics FOR
   ; &nbsp; &nbsp; &nbsp; &nbsp;  SELECT&nbsp;  e.lmc_metr_ins_file_name, a.cpu_time, a.elapsed_time,
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  a.lmc_db_name, a.lmc_db_schema_name, a.lmc_db_table_name,
 &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp;a.nbr_rows_data_errors, a.nbr_rows_discarded,
 &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ;a.nbr_rows_loaded, a.nbr_rows_read, a.nbr_rows_rejected,
  ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; a.nbr_rows_skipped, a.run_begin, a.run_end,
 &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ;a.lmc_metrics_instance_id
   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; FROM vw_lmc_sqlldr_metrics a,
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  (SELECT TO_CHAR (COLUMN_VALUE) AS in_database
 &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; FROM THE
   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp;  (SELECT CAST
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  (sf_sdba_csvstr2tbl (p_str_database) AS tt_sdba_csvstr2tab
  ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; )
 &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; FROM DUAL
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  )) b,
 &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp;(SELECT TO_CHAR (COLUMN_VALUE) AS in_schema
 &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; FROM THE
   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp;  (SELECT CAST
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  (sf_sdba_csvstr2tbl (p_str_schema) AS tt_sdba_csvstr2tab
  ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; )
 &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; FROM DUAL
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  )) c,
 &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp;(SELECT TO_NUMBER (COLUMN_VALUE) AS in_instance_id
&nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp;  FROM THE
   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp;  (SELECT CAST
&nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; (sf_sdba_csvstr2tbl
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; (p_str_instance_id) AS tt_sdba_csvstr2tab
  ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; )
 &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; FROM DUAL
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  )) d,
 &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp;lmc_metrics_instances e
   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp;WHERE a.lmc_db_name = b.in_database
 &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp;AND a.lmc_db_schema_name = c.in_schema
 &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp; AND a.lmc_metrics_instance_id = d.in_instance_id
&nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;AND a.lmc_metrics_instance_id = e.lmc_metrics_instance_id
   ; &nbsp; &nbsp; &nbsp; &nbsp;  ORDER BY e.lmc_metr_ins_date DESC, a.lmc_db_table_name ASC;

&nbsp; &nbsp; &nbsp;  DBMS_APPLICATION_INFO.set_action (NULL);
&nbsp; &nbsp; END reporting_sqlldrlog;


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).&nbsp; You may
also send the HELP command for other information (like subscribing).