Re: Recommendation on issuing DDL in applications

Matthew Keene wrote:

> Thanks everybody for replying, I must just add a bit more information.
>
> I looked at the Ask Tom site and Metalink before posting this query
> and the reasons that I could find for not using DDL in applications
> basically came down to:
>
> 1. It's expensive
> 2. It causes outstanding work to be committed
>
> Are there any others that I overlooked ? What degree of contention
> might be caused by this ?
>
> I do understand why they want to use a temporary table as they have to
> essentially flatten out a metastructure that in essence looks
> something like this (hope this diagram gets formatted properly):
>
> ------------
> | |
> | Table |
> | |
> ------------
> |
> |
> /|\
> ------------
> | |
> | Record |
> | |
> ------------
> |
> |
> /|\
> ------------
> | |
> | Attribute|
> | |
> ------------
>
> They have to take this structure and insert the records into a more
> conventional 3rd normal form type of target, so they essentially don't
> know the structure before they run the query. We don't want to create
> a 'mirror' of the other database's structure because it belongs to a
> third party and creating our own version of it would create a
> maintenance nightmare. So there are valid reasons for wanting to do
> this, and global temporary tables are not the answer.
>
> The alternative that I proposed was a generic table with ATTR1, ATTR2,
> ATTR3... etc, and the carrying of some meta data to identify which
> generic column is populated with which attribute for a specific table,
> although this may be more obtuse than the other solution.
>
> These extracts will run once per day, in batch, so I'm not really
> concerned about the DDL being expensive or being impacted by
> contention (unless we're talking about elapsed times of 15 minutes or
> more), and causing a commit is not a problem either. We may however
> run up to 10 in parallel, meaning that there will be quite a lot of
> concurrent DDL activity. Is this likely to cause us any problems such
> as corruption or other scary undesirable effects ?
>
> (This is probably too much detail, I hope people can be bothered to
> read and understand it.)

I don't recall your original post but you can always prevent DDL from committing transactions by doing it in a procedure defined by PRAGMA AUTONOMOUS_TRANSACTION.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)