Five Become One

I got a call from my team lead yesterday. He said he was swamped with the task that is due in a few days. He needed help writing a stored procedure to create a temporary table. The query was apparently all ready. It just needed to do it in PL/SQL. Although I was busy, I jumped at the opportunity.

Later I got the specs for the task. It was more complicated than I thought. there were a total of 5 queries. This was tricky because each of the 5 queries produced different columns of results. However they all needed to be combined into a big table.

I decided there was no way to CREATE a table AS SELECT FROM because there were multiple queries. So I determined the list of columns, and manually created the table with code. The create statement was so huge that I had to break the table creation down into one CREATE TABLE and multiple ALTER TABLE dynamic statements.

It was quite a chore to handle all the cases where a row in the resultant big table was sparse. Then I ran into more trouble when my dynamic statements to update pieces of the resultant table choked on some NULL inputs. That required some custom logic. By the time I was done, my script was around 1000 lines long. I did not leave work until midnight. What a chore.

Back To Basics

We have a new developer on our team. He is smart. He has a PhD in computer science. But there were some concepts he was not getting. So I had to break it down for him.

There is a text file that contains the source code for an Oracle PL/SQL package. You need to log in as the schema owner. Then you can compile the source code into the database. Once that is done, there is no relation between the source code file and the stored procedure in the database. The code gets stored within the database at compile time.

My peer thought that he could somehow step through the source code file on the hard drive. That's not how you do thing. I recommended he just add some logging to his new functions. He later came back saying he encountered problems. It did not take long to diagnose the problem.

I mentioned earlier that the source code upon compilation generates a package in the schema of the user logged in during compilation. We have users which access tables and packages in the main schema by use of synonyms. However you need to log in as the schema owner to modify that package. The code is not written to explicitly reference the schema owner. I have been doing these things for a long time and take them all for granted. Sometimes you need to step back and understand another person's perspective to help them see the light.

Oracle 11g Release 2

I have read some blurbs about the Oracle 11g Release 2 of the database. So I thought I would mention some things I have learned that are going to be in this release.

You will be able to run 11g on a cluster of non-Unix machines. For example you could string together a couple Intel machines and spend less money. Conversely you can run 11g RAC on a single machine, and still take advantage of RAC features.

11g R2 has new and improved data compression. Anything that saves disk space is a good thing in my book as long as it works correctly. Finally you will be able to do a hot application upgrade in 11g without shutting down the system. That's hot.

Database Design

We are getting down to the physical design of the new changes in our system. Here is where we need to figure out which database tables get the new columns. We may also create a new table or two.

I am working with another team on a back end piece of the system. We will tie the front end to the back with some new database columns. They back end developers just thought we would add a column to an existing table.

This approach normally makes sense. You add the column to the table that represents the object that is getting a new property. However you need to look deeper to see how the new data will be used. You might want to separate some data by creating a new table. It might make you queries easier and the system faster. We are going to have the design debate later this week. This is a just a specific instance of the greater problem of good database design.

Speedy GROUP BY

We had a big problem with our production system today. The customer determined we had not been transferring some files for a couple week. And those are some of our most crucial files. We had a big TO DO list to work this problem. I signed up to analyze the damage that was done to the business.

At first I ran some queries on a pretty large set of data. Our server is a massive one with tons of CPUs and memory. However these queries took a long time. I published the results. The managers wanted more details. They were looking for a way to spin the data to do the least amount of damage to our image. I told them this would take a long time. So I went home and brought my computer with me.

Here was the surprising thing. When I ran the SQL and divided up the result with a GROUP BY function, the queries seemed to run faster. Perhaps the database was able to split the pieces of the query amongst the CPUs better. Or maybe my prior query results were cached, and the database could take advantage of the result set. Whatever the reason, I was happy that my homework was not taking too long. I was able to knock out the tasks and move on to the next emergency. I even got time to eat dinner and watch a movie this evening. Thanks Oracle10g.

Back End Development

My project at work seems to be loaded with too much to do with little resources. Yes I know that happens to everybody. But there is no way were are going to even be close to completing everything on time. My team leader has been tasked with trying to come up with a plan to get everything done. Today he had a brilliant idea. We could carve out the pieces of the newest changes that are for the back end. Then we could assign those to a junior PL/SQL developer that we have.

I gave this idea some thought. At first I thought there was maybe 10% of the work that needed to be done in PL/SQL packages and database triggers. However on second thought, the number might be closer to 15-20%. Out PL/SQL developer has some work to do. However she could help out this latest development task.

Here is the difficulty in this arrangement. The actual PL/SQL coding is not difficult. The hard part is understanding the business. This is why we cannot give this part to some new developers on the team. The business of our customer takes a couple years to understand. I guess we could give the junior developer some hints, and let her code away. We shall see how this would pan out. I was hoping that I would get to do the back end work. I like writing PL/SQL code. And I know the business inside out.

Definer and Invoker Rights

In our system, we have the stored procedures owned by a central schema. Users have their own database accounts (schemas) where we stored temporary tables. The stored procs frequently create these tables. One developer reported that a rewrite of some stored procedures broke this pattern. The stored procedure, owned by the central schema, was creating temp tables in the central schema. That is no good, as each user needs their own copy of the temp table.

When I was asked for help, I said that we have the technology to make this work. We have it set up in the old stored procedures. However I referred the developer to our DBA Team. The lead DBA told us to set up invoker’s rights for the procedure. Normally we seemed to use whatever the default was. And it seemed to work before. We tried all kinds of debugging, but could not get this to work without using invoker’s rights.

Definer’s rights s the default in Oracle. Whoever owns the stored procedure (the definer) has the credentials that determine what other code the stored proc can call, and what tables and objects are used by default. This is not what we want on our project.

You have the ability to override the default behavior and specify invoker’s rights. This is done by specifying the AUTHID CURRENT_USER keywords when creating the stored procedure. This causes the context of the current user to be used when a stored procedure is called. Sure enough when we made this change, the temporary tables started getting created in the calling users’ schemas. Now we got some new stored proc code to update.