Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Recommendation on issuing DDL in applications

Re: Recommendation on issuing DDL in applications

From: Matthew Keene <dfg778_at_yahoo.com.au>
Date: 28 Aug 2003 16:45:24 -0700
Message-ID: <bb27253c.0308281545.6ba6d3db@posting.google.com>


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.) Received on Thu Aug 28 2003 - 18:45:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US