Hi All ,
I have a scenario in my application where i need to create multiple tables and query the dynamically created table.so i will dynamically create / drop table , columns . I m doing this in java.
My doubt will it cause any performance issue or any other technical problem if i frequrently do this create and drop actions ??
Any suggestions will be appreciated.
----------------------
Thanks in advance

In most DBMSs this is not a very good idea because it makes database administration and storage management harder.

The obvious question to ask yourself is why do you think you need to do this? Where does the requirement come from for these new "dynamically created" tables and why can't you service that requirement through normal change management processes?

hi dportas ,
thanks for your reply.
I analysed the requirement and there is no other way other than to create things dynamically.
so wanted to know will i get into any technical problems when i constantly modify the structure of the table.
When i say technical problem i mean with repect to querying will i get any issues ??or internally db cannot handle too much of table structure modification ??.
frankly i dont know what kind of technical problem i can face because of this approach .
All i can see is this is not a usual way of doing it in DBMs so just wanted to know will i later struck somewhere because of this approach ??.

thanks once again for ur prompt reply

It would be worth stating your requirements here so we can suggest another, better alternative. I would be very surprised if this is the optimal approach.

This will likely be an administrative nightmare. In terms of performance, assuming you are flinging tables around (as it sounds you are) then the optimiser will have very poor (if any) statistics and indexes to use to optimise queries. There will also be a very high overhead when these are required as they will essentially be built and collated at run time. There will also likely be no, or very limited, cached plans so you will also get compilation delays.

pootle flump ur codings are working excelent.

I am not convinced of that. Dynamic table creation is not a business requirement, it's a possible solution that you have chosen. Please explain the actual requirement and maybe someone can suggest some alternatives. For instance you could make a more flexible design which you won't have to change at runtime. Trivially, one exterme of flexibility is: Tbl {id,obj}, ie a BLOB store. That's a static schema but you can put pretty much anything in it!

There are lots of potential pitfalls to modifying a schema at runtime. How they affect you may depend on the actual reuirements, which you still haven't told us about.

Then you arrived at the wrong conclusion.

The posts so far are probably right. But I've done stuff that was almost a database running on a database where it made sense to do this.

They're always concerned about performance. For whatever reason, it's just never an issue for me. I'm usually more concerned about design issues, such as whether it fits comfortably, but that's just me.

The answer is "it depends on the DBMS."

Having lots of tables shouldn't matter much. The system tables and views are tuned by people with a fair amount of experience. You can always test this: just time some regular queries, then construct a however many tables, and time those queries again. Do post results.

You'll need to know how to quote symbols such as table names, column names, etc. (Or you can avoid using reserved words; making all your table names start with a prefix like FOO_ is one way. But quoting Just Works.) The ANSI SQL standard is, confusingly, almost identical to quoting strings. To quote a symbol, you wrap it in double-quotes, and escape any double-quote characters.

The biggest problem, IMHO, is whether you can mix DDL (any CREATE, ALTER or DROP statement) and DML in a transaction. That is, the following code is guaranteed to run completely or not at all:

That's important, because it is virtually impossible to use dynamic tables without adding some information of your own. You can't modify the system tables, so you need to keep some data in user tables in synch with them.

In most DBMSs, DDL will force a transaction to commit. (You can test this by executing the above statements followed by ROLLBACK. Then test two insert statements, just to make sure you're not automatically committing or something.) In the above example, the insert statement will happen in one transaction, the DBMS will see the CREATE statement and commit the INSERT. It begins the CREATE statement in a separate transaction. But if the CREATE statement fails for some reason, the INSERT has already been committed. Your database is now inconsistent. If it seems like a simple problem to work around, you don't understand transactions.

The UI is a bear. You'll find that it's very hard to handle cases where the user wants to change the type of a column and doesn't understand why the system locked up for a few hours. Or, if someone has a lock on a table, any attempt to drop or alter that table will have to wait on that lock. All the DDL is designed assuming that a DBA is doing it, and can guess how long it will take (by checking the stats or just prior experience) and is able to kill sessions if it hangs. There's a reason they pay those guys.

The logic for handling types and constraints is extremely tricky. Even if you're only dealing with the relational model on a blackboard it's hard enough, add in SQL and your vendor's own weirdness and it gets to be a bit of a nightmare. Most DBMS tools will handle a limited set of cases and simply throw an error if the user tries to do anything complicated. Further, it's possible to create impossible constraints like foreign keys that point to other foreign keys, or check constraints that are logically or practically impossible to fulfill. Detecting those is hard.

Hi ashok,

I think most users want to know - in general - whats the special case that needs to etc.

Most times, you dynamically drop and create tables based on some pre-existing structure and rarely drop columns. This should be OK. If you need to drop columns, it is better to have one pre-existing structure for each type (eg. one with the orriginal column and one with the column dropped).