I have an employees table.

Because it’s 1984, HR has decided we need to know Twitter handles, Facebook pages, and who knows what else.

So let’s add it to our table.

Note the new columns position...
Note the new columns position…

The dialog by default puts new columns at the bottom of the table. We’ll see why in just a moment.

Make the database reflect what's in the model
Make the database reflect what’s in the model

On a side note, we’re not super fond of these ‘arrow’ buttons to launch the compares. I don’t think they’re as intuitive as they could be, but I digress…

If you approve of the changes, make sure they are 'checked'
If you approve of the changes, make sure they are ‘checked’

Now. What type of code will these changes require? Hit the ‘DDL Preview’ button.

What is all of THIS code doing?!?
What is all of THIS code doing?!?

So we’re renaming the table. We’re creating a new version of the original table with the column order you’ve specified in the model. And we’re copying the old data over. And we leave the temp copy of the table there for you to look after when you’re done.

Do you understand now, why we are a bit reluctant to put a ‘run this code against my database’ button in the tool? Running this in a dev or test environment would be just fine. In production, not so much.

But wait, maybe I don’t REALLY care that much what the column order is in the database. Maybe there’s an option?

Ahhh, that sounds better.
Ahhh, that sounds better.

Uncheck this option, click the refresh button, and spin up the code again.

ALTERs vs Rebuilds
ALTERs vs Rebuilds

That looks a lot simpler and a LOT less destructive.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

Write A Comment