Wednesday, June 3, 2009

Create Database Link - Loopback Edition

I've been tasking with making some fairly drastic changes. When I say drastic I mean that I'm changing the name of a table and updating all of the dependencies. The drastic part comes in because there are about 11 Trillion dependencies.

So not to affect the other developers while I hammer this out, I created my own instance and one-by-one brought over the necessary objects (and their dependent objects). I know there are other ways to do this, develop in your named schema in the development database just changing or repointing the objects to your own.

Since I am new however, I thought it would be very beneficial to do it on my own. There are a couple of advantages:1. I won't interfere with any developer's progress2. It's a great opportunity to learn how everything fits together3. I have ApEx installed (development doesn't...yet).4. Look what I can do

Anyway, one procedure had an INSERT into our reporting environment (another database) so it wouldn't compile. It used the INSERT INTO [table]@database_link syntax. How can I replicate that? I know I've seen or read the solution somewhere, but I decided to do without Google this time to see how long it would take me.

I'll create TEST_USER, where the procedure lives and REPORT_DATA where the table lives. Following is performed with DBA privs:

9 comments:

First the little one: When you are creating deployment code, you will want to avoid any schema references as much as possible. Your "create procedure test_user.update_t" creates constricted developer sandbox and production deployment flexibility.

Second the bigger one: In general you will not want to develop source code with the dblink syntax in it.

This also gives you code with more developer sandbox and production deployment flexibility. This is especially true if you are using the dblink for some component in your development sandbox and no such dblink exists when it comes time to deploy to production.

re: Little OneI've been having the same conversation with @serge_a_storms the past year. I don't believe that scripts should include the schema qualifier.

re: Big OneCompletely agree. Unfortunately I am not the creator nor do I have the ability to change that...yet. From what I can tell, it's essentially the L (of ETL), just staging the data. An ideal would be either batching it (once a day or whatever) or using something like CDC to do it automagically.

I beg to differ on the 'Big Issue'.Having an object (table or view) on a remote DB has implications for the optimizer (and with user defined types). I'd want future developers to KNOW that whenever they look at the relevant SQL statement. I want anyone reviewing the code to see it too.If the synonym is named such that it obviously uses a DB link, or the team and environment are such that everyone knows it anyway, that's fine. Pros and Cons.

If, in production, the object is referenced over a DB link, I'd want it to do that in development too. Synonym or no synonym.

@Gary Using synonyms has no effect on the optimizer. Nor does it conceal the dblink dependency in data dictionary queries.

My thinking is that the developer should KNOW what the optimizer implications are by explain plans of statements, profiling the code and data dictionary queries. If they the think are KNOWING anything by visually examining the source code of a stored procedure, they are standing in quicksand.

Putting dblink syntax into source code is a pattern of failure. It sacrifices flexibility for no legitimate gain.

"It sacrifices flexibility for no legitimate gain."Sacrifices flexibility ? Why ? You think that it isn't possible to change the code later ?After all, if you do change which DB the object is in, you'll need to test the code set anyway. Changing a reference or two is hardly a chore.Besides, moving schemas between databases is going to be rare enough that I'll never worry about it.

@gary You ask "why". Here is my answer. "Being able to change the code later" is not the way I think.

The underlying connection information encapsulated by a database link as well as the meta-information of the dblink's name itself are an ENVIRONMENTAL components to your app, analogous to the host name or IP address. Things important to the overall execution of all software in the mix, but they are something to avoid at all costs as a dependency in your code.

Philosophically, I am opposed to the very concept of an environmental component changing and it requires my code to be modified and recompiled.

I can tell you have a lower threshold of concern for changing application source when in fact you are not changing its functionality in any way. I am very resistant to doing that.