Jim Kennedy wrote:
> "Gama Franco" <tiago_at_cern.ch> wrote in message
> news:2q6r9hFr7qesU1_at_uni-berlin.de...
>
>>Sybrand Bakker wrote:
>>
>>>On Tue, 07 Sep 2004 17:08:24 +0100, Gama Franco <gama_franco_at_clix.pt>
>>>wrote:
>>>
>>>
>>>
>>>>Unfortunatelly I really have to.
>>>
>>>
>>>You only think so. Also no real DBA would accept procedures are
>>>creating tables on the fly. Allowing this would mean the db is out of
>>>control.
>>>
>>>
>>>--
>>>Sybrand Bakker, Senior Oracle DBA
>>
>>Hi,
>>
>>Ok, here it goes.
>>
>>I'm programing a database based on a temporal extension to the
>>relational model. That means that I need to simulate the DBMS, and every
>>table created by the user will be a temporal table (dealing with time
>>intervals). The database will have tables to manage all the meta info,
>>and temporal tables will be created on demand (just like the way that
>>you create a table when you need in Oracle, but these one are temporal).
>>This means that the program will need to manage and create new tables,
>>and OCCI is a major requirement. It's CERN's official programming
>>language...
>>Because the system will be used by a large number of scientists, there
>>will be no DBA. To manage all this chaos, an hierarchical model will
>>handle the location of the tables (we simulate something like a file
>>system on the top of the hierarchical model, and every table will be
>>located at a leaf of the tree).
>>
>>This is just a brief introduction, and I would like to know if someone
>>already made the performance tests on Embedded SQL vs Stored Procedures.
>>If this is not the case, I will have to make the tests but I was looking
>>for a shortcut.
>>
>>I'm not crazy or stupid. This system is already running using MySQL (I
>>now what you're going to say) as the repository. It is already
>>collecting data and the database is somewhat big. We need to create an
>>implementation in Oracle and make some performance tests.
>>If you have a lot of scientist running their own experiments with
>>specific use cases, you need to figure out a clever way to give them
>>freedom without throwing them into chaos.
>>
>>Best regards,
>> Gama Franco
>>
>
>
> If you mean you are creating these tables permanently then it might be okay,
> but if you are creating and dropping them on the fly then it is a real bad
> idea. I am guessing that mysql creates a variety of locks and that's why
> people create temp tables on the fly. In Oracle it is rare to need to do
> that. Stored procedures are nice because you control a lot of stuff and can
> redefine something's without changing code on the client. Your biggest
> gains will be to read the application developer's guide, use
> asktom.oracle.com and USE BIND VARIABLES.
> Jim
>
>
Hi,
The tables are not dropped after creation. The point is that no DBMS
provides temporal tables (tables based on the temporal extension to the
relational model), so we need a system that provides them. This is the
main issue here. Now, I'm afraid that a stored procedure that does all
the actions needed to create such tables might be slower than doing them
in procedures on OCCI (code those procedures in C++ rather than in PL/SQL)
Best regards,
Gama Franco
Received on Wed Sep 08 2004 - 03:47:19 CDT
