Oracle-L: RE: Performance/Design Advice or DBA vs. Developer

From: Yosi Greenfield <ygreenfield_at_kewsystems.com>
Date: Tue, 20 May 2003 08:56:53 -0800
Message-ID: <F001.0059E010.20030520085653@fatcity.com>

Performance/Design Advice or DBA vs. DeveloperDavid,

I'd think it would be less costly to parse every record once on load than to parse every record every time they want to search that field.

Also, leaving as is makes searching for '/' or '/etc' a bear, since the / could be at the beginning, middle, or end of the field, or could be part of another element in the field. Parsing it out at load sounds way easier to me.

If they will want to compare the entire unparsed incoming field to other entire unparsed incoming field with no sorting necessary within the field, I can hear leaving it as is because of that 'ugly loopy' stuff. But if they're
going to look at elements within the field, your developer sounds l-a-z-y to me.

hth.

yosi

  -----Original Message-----
  From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of David Wagoner   Sent: Tuesday, May 20, 2003 11:57 AM
  To: Multiple recipients of list ORACLE-L   Subject: Performance/Design Advice or DBA vs. Developer

The developers have a new table that contains a varchar(2) column with multiple, space-separated values for backup targets on servers (I didn't do the modeling on this one). For example:

  host_name       backup_target
  dbserver1       / /etc /oracle /scripts /any/other/long/directory/name

This column started out as varchar(2) 150, then grew to varchar(2) 250, and could potentially grow very long.

I recommended to the developers that we create a separate table to hold the separate values as individual rows, populated via trigger from a staging table during batch loads, something like this:

  host_name       backup_target
  dbserver1       /
  dbserver1       /etc
  dbserver1       /oracle
  dbserver1       /scripts
  dbserver1       /any/other/long/directory/name

...

My reasoning is:

a.. the backup_target column will not grow as long b.. reports from this table will not have to parse the data out by space-delimiters during execution

c.. this also seems to correspond more towards 3rd Normal Form d.. I'm concerned about row-chaining on such a long column

Here is the response from one of the developers:

I can maybe be convinced otherwise, but here is my thinking in support of the "one big field" [varchar2(4000)?] theory:

a.. Our database machines are not storage constrained, nor should they ever be, therefore, a varchar2(a bunch) field shouldn't be that much of a hardship. I'm unaware of performance issues that may arise by using a varchar2 field of this size. Anybody have any info on this?

b.. Our batch loads are hulking processor hogs. When we look for changes to targets which we will probably do once per target list per load, we will need to compare an unparsed class config target list to one parsed out into 5 or 10 or 200 rows -- this is going to be very ugly, loopy, expensive. We will also need to parse out the individual entries and create a row for each.

c.. The number of times that we will need to report a given target list in a parsed format should be small; some may never be displayed. When the time does come to report a target list, a java or perl (or even owa_pattern) regular expression replace operation is not all that expensive in terms of processing.

What do you guys think- anyone have experience with this type of situation?

PS- Environment is Oracle 9.2.0.2 on Sun Solaris; the database is hybrid OLTP and reporting.

Thanks for sharing,

David B. Wagoner
Database Administrator

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Yosi Greenfield
INET: ygreenfield_at_kewsystems.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-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). Received on Tue May 20 2003 - 11:56:53 CDT