Wednesday, July 2, 2008
Compound Triggers
Seriously, I don't like triggers. But if you have to maintain them, you might as well make the best of it.
While trying to figure out my problem the other day, I ran across Compound Triggers. I hadn't read about it in the 11g New Features guide, but since I don't use triggers, I wasn't sure if it was new or not. Apprently it is...
In essence, you can combine multiple triggers into one. I won't go into the gory details (because I don't know the gory details), but I will provide the example from the docs for your perusal.
While trying to figure out my problem the other day, I ran across Compound Triggers. I hadn't read about it in the 11g New Features guide, but since I don't use triggers, I wasn't sure if it was new or not. Apprently it is...
In essence, you can combine multiple triggers into one. I won't go into the gory details (because I don't know the gory details), but I will provide the example from the docs for your perusal.
At the very minimum (if you have to use them), you might as well combine them into one and save a bit on maintenance/debugging!
CREATE TRIGGER compound_trigger
FOR UPDATE OF sal ON emp
COMPOUND TRIGGER
-- Declaration Section
-- Variables declared here have firing-statement duration.
threshold CONSTANT SIMPLE_INTEGER := 200;
BEFORE STATEMENT IS
BEGIN
...
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
...
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
...
END AFTER EACH ROW;
END compound_trigger;
/
Labels: database, oracle, triggers
¶ 7/02/2008 09:19:00 PM 1 Comments Links to this post
Tuesday, July 1, 2008
Lookup Tables
Also known as reference, crosswalk and a few other names.
I love 'em.
I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.
I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.
I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.
Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.
To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.
Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!
Just don't ask Duke Ganote whether type is a good name or not!
I love 'em.
I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.
I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.
I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.
Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.
To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.
Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!
Just don't ask Duke Ganote whether type is a good name or not!
Labels: apex, constraints, database, design, development
¶ 7/01/2008 09:52:00 PM 3 Comments Links to this post
Thursday, June 26, 2008
Design - The Entity
I love designing databases. Specifically, data modeling. I love trying to figure out how the data fits together. Is this an attribute of that? Is it shared with something else? What's the relationship? One to many? One to one? Many to many? Hierarchical?
Let's start with people and addresses. A fairly easy mapping. One person can have one or more addresses, or one to many. The basics of that are drawn out below:
The primary key (whether natural or surrogate, defined here as a surrogate) for PEOPLE is stored as a Foreign Key in the ADDRESSES table. To guarantee that a PEOPLE key is supplied for each address, you should add a NOT NULL constraint along with the FK constraint.
Not too difficult.
What if down the road you need to add organizations? Do you shove them into the people table or do you create a new table for them? My initial thought is to create a new table called ORGANIZATIONS.
Easy enough right?
Now I want to track addresses for the ORGANIZATION table. Hmmm...how to do that? My first thought was to just create another address table.
Hmmm...now I have addresses in two different tables. I've introduced the chance of having non-standardized addresses (two entry points, or maybe I don't have the same attributes in both tables). Writing a query to get all the addresses (for a mailing perhaps) will be a bit more difficult.
How about just adding another column to the original ADDRESS table?
That works...sort of. I must remove the NOT NULL constraint for PEOPLE and I definitely can't add one for ORGANIZATION. But they're in one table and I can have one entry point (it would have to be conditional though). What if in the future I add another table? Do I just add another column (FKd) to ADDRESS.
It starts to become a bit unwieldy.
So, enter the ENTITY.
The ENTITY just becomes a placeholder, providing keys for PEOPLE (one to one) and ORGANIZATIONS (one to one). ENTITYID is stored in the ADDRESS table and voila! Perhaps this is called a supertype (not real sure on my terminology here). You now have one entry to the ADDRESS table (well...hopefully). To pull all addresses is easy.
You can use the surrogate key from ENTITY as your primary key in PEOPLE and ORGANIZATIONS, so if you want to bypass the ENTITY table, you can do so easily.
That's my solution anyway. How about you? Same, different? Am I crazy (I'm starting to think so...)? ¶ 6/26/2008 11:08:00 PM 10 Comments Links to this post
Let's start with people and addresses. A fairly easy mapping. One person can have one or more addresses, or one to many. The basics of that are drawn out below:
The primary key (whether natural or surrogate, defined here as a surrogate) for PEOPLE is stored as a Foreign Key in the ADDRESSES table. To guarantee that a PEOPLE key is supplied for each address, you should add a NOT NULL constraint along with the FK constraint.
Not too difficult.
What if down the road you need to add organizations? Do you shove them into the people table or do you create a new table for them? My initial thought is to create a new table called ORGANIZATIONS.
Easy enough right?
Now I want to track addresses for the ORGANIZATION table. Hmmm...how to do that? My first thought was to just create another address table.
Hmmm...now I have addresses in two different tables. I've introduced the chance of having non-standardized addresses (two entry points, or maybe I don't have the same attributes in both tables). Writing a query to get all the addresses (for a mailing perhaps) will be a bit more difficult.
How about just adding another column to the original ADDRESS table?
That works...sort of. I must remove the NOT NULL constraint for PEOPLE and I definitely can't add one for ORGANIZATION. But they're in one table and I can have one entry point (it would have to be conditional though). What if in the future I add another table? Do I just add another column (FKd) to ADDRESS.
It starts to become a bit unwieldy.
So, enter the ENTITY.
The ENTITY just becomes a placeholder, providing keys for PEOPLE (one to one) and ORGANIZATIONS (one to one). ENTITYID is stored in the ADDRESS table and voila! Perhaps this is called a supertype (not real sure on my terminology here). You now have one entry to the ADDRESS table (well...hopefully). To pull all addresses is easy.
You can use the surrogate key from ENTITY as your primary key in PEOPLE and ORGANIZATIONS, so if you want to bypass the ENTITY table, you can do so easily.
That's my solution anyway. How about you? Same, different? Am I crazy (I'm starting to think so...)? ¶ 6/26/2008 11:08:00 PM 10 Comments Links to this post
Wednesday, May 21, 2008
Multi Table INSERTs
I learned something new today! Multi Table INSERTs.
I was trying to capture the new records into a staging table using RETURNING BULK COLLECT INTO, but that doesn't work (yet).
I remembered reading about Multi Table INSERTs, but never had the need for using them, so never bothered to learn it.
Awesome!
I was trying to capture the new records into a staging table using RETURNING BULK COLLECT INTO, but that doesn't work (yet).
I remembered reading about Multi Table INSERTs, but never had the need for using them, so never bothered to learn it.
CREATE TABLE t
(
x NUMBER PRIMARY KEY,
y NUMBER,
z NUMBER
);
CREATE TABLE s
(
x NUMBER PRIMARY KEY
);
INSERT ALL
INTO t ( x, y, z )
VALUES ( myrownum, ran1, ran2 )
INTO s ( x )
VALUES ( myrownum )
SELECT
rownum myrownum,
dbms_random.value ran1,
dbms_random.value ran2
FROM dual
CONNECT BY LEVEL < 101;
CJUSTICE@ORA10GR2>SELECT COUNT(*) FROM t;
COUNT(*)
----------
100
1 row selected.
Elapsed: 00:00:00.01
CJUSTICE@ORA10GR2>SELECT COUNT(*) FROM s;
COUNT(*)
----------
100
1 row selected.
Elapsed: 00:00:00.02
Awesome!
Labels: database, datawarehouse, sql
¶ 5/21/2008 05:17:00 PM 1 Comments Links to this post
Tuesday, May 13, 2008
ApEx: Oracle Marketing WTF?
At the time of writing, the score is 68 have not used ApEx leaving only 8 who have. Obviously this poll isn't scientific, but it does have to represent a small bit of the community. Perhaps Oracle Mix would be a better place to ask the question?
I guess my followup poll would be why? Why haven't you used it?
Did you not know about it?
If that's the case, then it's definitely an Oracle Marketing WTF.
Did you know about it but just never got around to trying it?
I'm completely dumb(quit snickering)-founded.
I believe the very first time I heard anything about the product was when it was referred to as Marvel (Project Marvel?) on AskTom. Am I the only one who read the site for enjoyment (probably, but it was the only thing I could read at work). That must have been 4 or 5 years ago. I started using it in May of 2005, now three years back.
Yes, Oracle has boatloads of products. I still don't know what the whole Fusion thing is (please don't say middleware, I don't know what that is either).
As someone pointed out in the comment section, it's one of the busiest forums on OTN.
I wonder if John and Dmitri run into this? Probably makes cold calling difficult.
As a [ApEx] community, what can we do to help promote it?
I guess my followup poll would be why? Why haven't you used it?
Did you not know about it?
If that's the case, then it's definitely an Oracle Marketing WTF.
Did you know about it but just never got around to trying it?
I'm completely dumb(quit snickering)-founded.
I believe the very first time I heard anything about the product was when it was referred to as Marvel (Project Marvel?) on AskTom. Am I the only one who read the site for enjoyment (probably, but it was the only thing I could read at work). That must have been 4 or 5 years ago. I started using it in May of 2005, now three years back.
Yes, Oracle has boatloads of products. I still don't know what the whole Fusion thing is (please don't say middleware, I don't know what that is either).
As someone pointed out in the comment section, it's one of the busiest forums on OTN.
I wonder if John and Dmitri run into this? Probably makes cold calling difficult.
As a [ApEx] community, what can we do to help promote it?
Labels: apex, database, oracle, wtf
¶ 5/13/2008 10:01:00 PM 4 Comments Links to this post
Tuesday, May 6, 2008
Oracle at Home: The Results
Almost 70% of you responded that you do not have Oracle installed at home. That was a bit of a shocker to me.
I figured that it would be the other way around; people who read blogs, Oracle blogs specifically, would be more likely to have Oracle installed at home.
So, why not?
I figured that it would be the other way around; people who read blogs, Oracle blogs specifically, would be more likely to have Oracle installed at home.
So, why not?
Labels: database, home, oracle, work
¶ 5/06/2008 10:39:00 PM 5 Comments Links to this post
Monday, May 5, 2008
Oracle at Home
I participated in an interview today. One of the questions I usually ask is "Do you have Oracle installed at home?"
It's more a question to gauge their nerdiness than anything, see if they're obsessed like me. Does it always mean that they aren't smart or capable? No, not really. That will come out through the other technical questions. For me at least, it does indicate a curiousity about how the software works.
So, do you have Oracle installed at home?
¶ 5/05/2008 11:53:00 PM 9 Comments
Links to this post
It's more a question to gauge their nerdiness than anything, see if they're obsessed like me. Does it always mean that they aren't smart or capable? No, not really. That will come out through the other technical questions. For me at least, it does indicate a curiousity about how the software works.
So, do you have Oracle installed at home?
Thursday, April 24, 2008
ApEx Presenting = FUN!
Wow...that's all I can say.
At lunch today, I put together my power point presentation. I meant to do it sooner of course, but it's been another busy week. In fact, I hadn't done a thing to prepare other than walking through it in my head.
I did not want this to be a presentation so much as a demo. Six slides is all they got.
I suddenly got very nervous around 4 today. I just want to get over there and start. I ended up leaving work just before 5.
The meeting was at the PriceWaterhouseCoopers building in Tampa, just across the street from the Tampa Bay Buccaneers headquarters. Very nice building.
I was introduced by the SOUG president promptly at 6:30. Roughly 40 people showed up (filled the room).
I had an hour and a half to complete my presentation...ummm...WHAT? I've got like 10 minutes worth of material! How the hell am I going to manage this??
Anyway, I walked through some of the features of APEX: Load/Unload data, SQL Commands, that kind of stuff, just trying to get to the application builder.
A couple of nights ago I began walking through and building a basic little reporting application tailored to the DBAs. Reports on roles and privileges basically. I never even got to that.
I created a 2 line csv (yes, I should have done it before hand), uploaded it to demonstrate how easy it was and then off the creating a report on that table.
"Can you create a form to update that record?"
Sure, here's how you do it. One minute later the form was done and I had updated the record. True to form...it's just so damn easy.
I answered a few other questions and then I got stuck. A member of the audience started answering the questions that I couldn't. We (the member in the audience) had spoken before the meeting and I believe he's just as passionate about APEX as I am). It worked rather well, so well in fact, that we're going to try and work out a dual presentation at the upcoming Technology Day SOUG puts on.
Someone would ask a question, I would answer them by showing them how to do it. Have I mentioned how much I like APEX? Rocks.
Next thing I know, it's a little past 8 and I can see the President trying to end the meeting. No way dude, too many questions to answer!
I got out of there around 9:30. I answered questions to the best of my ability...if I didn't know, pointed them to resources that could.
Exhilarating!
Can't wait to do it again...but next time I'll practice!
update
BTW, great crowd tonight. I couldn't have asked for a better group. Thanks to everyone for their support, and thanks to Tom and LewisC for asking me to do it.
Did I mention how cool it was? ;)
At lunch today, I put together my power point presentation. I meant to do it sooner of course, but it's been another busy week. In fact, I hadn't done a thing to prepare other than walking through it in my head.
I did not want this to be a presentation so much as a demo. Six slides is all they got.
I suddenly got very nervous around 4 today. I just want to get over there and start. I ended up leaving work just before 5.
The meeting was at the PriceWaterhouseCoopers building in Tampa, just across the street from the Tampa Bay Buccaneers headquarters. Very nice building.
The Presentation
I was introduced by the SOUG president promptly at 6:30. Roughly 40 people showed up (filled the room).
I had an hour and a half to complete my presentation...ummm...WHAT? I've got like 10 minutes worth of material! How the hell am I going to manage this??
Anyway, I walked through some of the features of APEX: Load/Unload data, SQL Commands, that kind of stuff, just trying to get to the application builder.
A couple of nights ago I began walking through and building a basic little reporting application tailored to the DBAs. Reports on roles and privileges basically. I never even got to that.
I created a 2 line csv (yes, I should have done it before hand), uploaded it to demonstrate how easy it was and then off the creating a report on that table.
"Can you create a form to update that record?"
Sure, here's how you do it. One minute later the form was done and I had updated the record. True to form...it's just so damn easy.
I answered a few other questions and then I got stuck. A member of the audience started answering the questions that I couldn't. We (the member in the audience) had spoken before the meeting and I believe he's just as passionate about APEX as I am). It worked rather well, so well in fact, that we're going to try and work out a dual presentation at the upcoming Technology Day SOUG puts on.
Someone would ask a question, I would answer them by showing them how to do it. Have I mentioned how much I like APEX? Rocks.
Next thing I know, it's a little past 8 and I can see the President trying to end the meeting. No way dude, too many questions to answer!
I got out of there around 9:30. I answered questions to the best of my ability...if I didn't know, pointed them to resources that could.
Exhilarating!
Can't wait to do it again...but next time I'll practice!
update
BTW, great crowd tonight. I couldn't have asked for a better group. Thanks to everyone for their support, and thanks to Tom and LewisC for asking me to do it.
Did I mention how cool it was? ;)
Labels: apex, database, oracle, presentation
¶ 4/24/2008 10:49:00 PM 3 Comments Links to this post
Friday, April 18, 2008
Datawarehouse: Testing
Until recently, I have been doing support/enhancements and new development for one particular project.
In our development environment, I have essentially been doing full load testing, using all of the records that are in production.
Needless to say, our development iterations were slow. Make a change to the code and then reload some 100 million records which are bounced against one another. The good thing is that I've picked up some really good tuning skills. The bad thing is my project was recently 2 months late.
Now that I've had a chance to breathe, I've been able to rethink some of the processes. First and foremost being how we test (both in development and QA).
If I had taken the time one year ago to build a "build" and "teardown" script, I probably could have shaved months of the project life cycle.
I used SQLUnit a few years ago and found that to be a pretty good tool. At the time, it consisted of writing out XML files and was quite time consuming, but gave me more confidence in the code I wrote. It also taught me to think like a tester forcing me to write better code as I could predict much easier where the problems might be.
We've used no such tool at WellCare.
I believe database testing to be extremely difficult. Our unit tests have consisted of running the procedures and then pasting that to a Word document...not really my idea of good testing, but it's what we have.
So are there any tools that you use to test database specific items? If you don't use tools, how do you do it? Why does database testing seem so difficult?
In our development environment, I have essentially been doing full load testing, using all of the records that are in production.
Needless to say, our development iterations were slow. Make a change to the code and then reload some 100 million records which are bounced against one another. The good thing is that I've picked up some really good tuning skills. The bad thing is my project was recently 2 months late.
Now that I've had a chance to breathe, I've been able to rethink some of the processes. First and foremost being how we test (both in development and QA).
If I had taken the time one year ago to build a "build" and "teardown" script, I probably could have shaved months of the project life cycle.
I used SQLUnit a few years ago and found that to be a pretty good tool. At the time, it consisted of writing out XML files and was quite time consuming, but gave me more confidence in the code I wrote. It also taught me to think like a tester forcing me to write better code as I could predict much easier where the problems might be.
We've used no such tool at WellCare.
I believe database testing to be extremely difficult. Our unit tests have consisted of running the procedures and then pasting that to a Word document...not really my idea of good testing, but it's what we have.
So are there any tools that you use to test database specific items? If you don't use tools, how do you do it? Why does database testing seem so difficult?
Labels: database, datawarehouse, testing
¶ 4/18/2008 07:52:00 PM 0 Comments Links to this post
Tuesday, April 15, 2008
How Do You Audit?
I'm not necessarily talking about system auditing, which I understand to be pretty much like throwing a switch, I'm talking about table level auditing.
Given the following table:
I typically do something like this:
I know there are other ways, but I'd like to hear some of your ideas/methods.
Updated
I realized that I should not have put ID as the primary key, I should have used a unique key (SSN) instead but generated a surrogate key (ID). I've updated it to use SSN as the unique key, so the primary key can and will change (it's a surrogate), but the SSN cannot.
Updated II
Then it would fail when you create a new record because the SSN would no longer be unique. Oy, I usually read and re-read anything technically related.
So I'll remove the UNIQUE constraint from SSN and that should do the trick. SSN will be used to find the record of the appropriate person, along with END_DATE IS NOT NULL (a "current" row).
Given the following table:
The requirement is to track any changes to everything but the SSN. ID is just a surrogate key.
CREATE TABLE t
(
id NUMBER(15) PRIMARY KEY,
ssn VARCHAR2(9)UNIQUE,
last_name VARCHAR2(40),
first_name VARCHAR2(30),
dob DATE
);
I typically do something like this:
I added the create_ and end_ columns to see who did what when. If someone comes in and decides to change the record, the end_date and end_user are populated (thereby "terminating" the record) and a new record is created with the updated values. That then becomes the "current" record.
CREATE TABLE t
(
id NUMBER(15) PRIMARY KEY,
ssn VARCHAR2(9)UNIQUE,
last_name VARCHAR2(40),
first_name VARCHAR2(30),
dob DATE,
create_date DATE,
create_user VARCHAR2(30),
end_date DATE,
end_user VARCHAR2(30)
);
I know there are other ways, but I'd like to hear some of your ideas/methods.
Updated
I realized that I should not have put ID as the primary key, I should have used a unique key (SSN) instead but generated a surrogate key (ID). I've updated it to use SSN as the unique key, so the primary key can and will change (it's a surrogate), but the SSN cannot.
Updated II
Then it would fail when you create a new record because the SSN would no longer be unique. Oy, I usually read and re-read anything technically related.
So I'll remove the UNIQUE constraint from SSN and that should do the trick. SSN will be used to find the record of the appropriate person, along with END_DATE IS NOT NULL (a "current" row).
Labels: database, design, development
¶ 4/15/2008 07:44:00 PM 5 Comments Links to this post
Thursday, March 27, 2008
How Does Oracle Make Development Easier?
Continuing on the theme of late, what are the basic things that you can do to reduce the amount of code that needs to be written?
In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.
This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.
This will give you much more reliable data, which as we all know, is the most important thing.
I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.
What kind of solutions do you have or do you use?
In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:
ID - is just a sequence generated key, no big deal there.
CREATE TABLE t
(
id NUMBER(10)
CONSTRAINT pk_id_t PRIMARY KEY,
first_name VARCHAR2(30)
CONSTRAINT nn_firstname_t NOT NULL,
middle_name VARCHAR2(30),
last_name VARCHAR2(40)
CONSTRAINT nn_lastname_t NOT NULL,
gender VARCHAR2(1)
CONSTRAINT nn_gender_t NOT NULL
CONSTRAINT ck_morf_gender_t CHECK ( gender IN ( 'M', 'F' ) ),
ssn VARCHAR2(9)
CONSTRAINT nn_ssn_t NOT NULL
CONSTRAINT ck_9_ssn_t CHECK ( LENGTH( ssn ) = 9 )
CONSTRAINT ck_numeric_ssn_t CHECK ( REGEXP_INSTR( ssn, ?, ?, ? ) )
CONSTRAINT uq_ssn_t UNIQUE
);
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.
This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.
This will give you much more reliable data, which as we all know, is the most important thing.
I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.
What kind of solutions do you have or do you use?
Labels: code, constraints, database, oracle, simplicity
¶ 3/27/2008 10:28:00 PM 0 Comments Links to this post
Tuesday, March 25, 2008
Use the [Oracle] Database dammit!
Dom Brooks recently posted an article about the Dea(r)th of the Oracle RDBMS. It seemed to struck a chord.
I've written about MySQL Friday or Application Developers vs. Database Developerswhich were similar in thought; the database is a bucket.
Ultimately, my take is that application developers don't know and don't want to learn how to use a database. PL/SQL specifically, is a platform in and of itself. You can do so much in the database now that you essentially need an application only for display, to determine the row color if you will.
The usual caveat follows:
If you are building applications that are supposed to be database independent, then the logic belongs in the application. The database is a bucket.
If you are building business applications specific to Oracle though, use the damn thing. Application/web developers are then forced to work on the design and user interface, not application/transaction logic.
Easy steps to actually utilize your database:
1. Use as many constraints as humanly possible - This will reduce the amount of code you have to write and you'll have the security of knowing the data will be what you constrain it to be.
2. DEFAULT columns in table definitions - create_date or load_date can be default to SYSDATE and thus left out of any application code. I've gone so far as to use SYS_CONTEXT( 'MY_CONTEXT', 'USERID' ) as the DEFAULT value for the create_user column. That along with a NOT NULL (or CHECK) constraint, makes life that much easier.
3. Did I mention constraints? Primary Key and Foreign Key constraints are very important to maintain data integrity (ensure you have the data you expect). Don't forget to index those foreign keys.
4. Security - VPD (Virtual Private Database) or Fine Grained Access Control. No longer do you need to maintain two separate schemas (or databases), just add a column and only allow those with the value set see that data. If you are using ApEx, this is incredibly easy to do.
5. Security (Roles and Privileges) - No more table based authorization, let the database do it through roles and privileges. GRANT EXECUTE ON my_package TO some_user
That's my short list for today. Like Dom, this makes me angry. If there were some rational logic behind it, great, convince me. I haven't seen it yet though.
I've written about MySQL Friday or Application Developers vs. Database Developerswhich were similar in thought; the database is a bucket.
Ultimately, my take is that application developers don't know and don't want to learn how to use a database. PL/SQL specifically, is a platform in and of itself. You can do so much in the database now that you essentially need an application only for display, to determine the row color if you will.
The usual caveat follows:
If you are building applications that are supposed to be database independent, then the logic belongs in the application. The database is a bucket.
If you are building business applications specific to Oracle though, use the damn thing. Application/web developers are then forced to work on the design and user interface, not application/transaction logic.
Easy steps to actually utilize your database:
1. Use as many constraints as humanly possible - This will reduce the amount of code you have to write and you'll have the security of knowing the data will be what you constrain it to be.
2. DEFAULT columns in table definitions - create_date or load_date can be default to SYSDATE and thus left out of any application code. I've gone so far as to use SYS_CONTEXT( 'MY_CONTEXT', 'USERID' ) as the DEFAULT value for the create_user column. That along with a NOT NULL (or CHECK) constraint, makes life that much easier.
3. Did I mention constraints? Primary Key and Foreign Key constraints are very important to maintain data integrity (ensure you have the data you expect). Don't forget to index those foreign keys.
4. Security - VPD (Virtual Private Database) or Fine Grained Access Control. No longer do you need to maintain two separate schemas (or databases), just add a column and only allow those with the value set see that data. If you are using ApEx, this is incredibly easy to do.
5. Security (Roles and Privileges) - No more table based authorization, let the database do it through roles and privileges. GRANT EXECUTE ON my_package TO some_user
That's my short list for today. Like Dom, this makes me angry. If there were some rational logic behind it, great, convince me. I haven't seen it yet though.
Labels: constraints, database, development, oracle, work
¶ 3/25/2008 03:16:00 PM 2 Comments Links to this post
Wednesday, February 20, 2008
Application Developers vs Database Developers
It started innocently enough with this article. I sent it out to about 20 colleagues.
The best line from the article:
"Jerry: "Yeah, databases cause lots of headaches. They crash all the time, corrupt data, etc. Using text files is better."
One of my more recently arrived colleagues (I'll call him Mr. M) replied to everyone with this statement:
"Kind of funny actually, databases are less and less important at the large investment banks, where they basically load everything up into a data grid across a several hundred node cluster. Writing to the db is way too slow."
This started a day long exchange of emails. What follows is the entire thread (up until my last post tonight).
Me:
"I would just argue that they don’t necessarily know how to write to databases. I would however love to see benchmarking done on both methods. Would be an interesting test..."
Mr. M:
"Well, my understanding is they just can’t scale out the db enough. Even something like Oracle RAC won’t work. And outside of the military, these are probably the top 1% of programmers in the world building this stuff."
Me:
"A benchmark would be the only way I would believe it.
If you said the top 1% of database developers tried it and failed, I would be more likely to agree.
My experience is that application developers != database developers. Different type of thinking involved."
Mr. M:
"'A benchmark would be the only way I would believe it.'
Do you need a benchmark before you would believe in-memory retrieval is faster than disk retrieval? Essentially, this is what we’re talking about.
'If you said the top 1% of database developers tried it and failed, I would be more likely to agree. My experience is that application developers != database developers. Different type of thinking involved.'
Why? It’s an issue to do with application performance not simply database performance. Database concerns are a subset of application concerns, essentially a specialization, requiring less encompassing knowledge. ;)
From the article you linked to (http://www.watersonline.com/public/showPage.html?page=432587)
"Better data management is the answer, says Lewis Foti, manager of high-performance computing and grid at The Royal Bank of Scotland (RBS) global banking and markets. "For very large compute arrays, the key issue is data starvation and saturation. This problem requires data grids with high bandwidth and scalable, parallel access,
...
Banks are learning that data management in a distributed grid environment is very different from online transaction processing. "With so many data sources, distribution channels, demands for aggregation and analytics, surges in data volumes and complex dynamics between the flows, we need to manage 'data in motion' and give up the notion that data is somehow stored. It's dynamic, not static," says Michael Di Stefano, vice president and architect for financial services at GemStone Systems
...
There is even some debate over how small a unit of work can be put on today's grids. Di Stefano at GemStone, for example, says, "One client has gone from 200 trades per second in a program trading application to more than 6,000 trades per second. This shows what the technology can do."
Yep, the writing is on the wall. Oracle knows it too.
http://www.google.com/search?hl=en&q=oracle+buys+tangosol&btnG=Google+Search"
Me:
"Good points. If it is in-memory it would be faster. I have not had the pleasure to work on such a system.
I do disagree with the database concerns being a subset of application concerns. The data drives the app. We’re probably getting religious at this point (or am I)."
Mr. M:
"‘The data drives the app.”
Exactly, but who’s to say where the data comes from or in what format? My application data may reside completely in xml files, or maybe I get it from some third party web services a la the en vogue “mashup.” Heck, I may not even need to worry about a database anymore…. http://www.amazon.com/gp/browse.html?node=16427261 The database is only one particular concern of the overall application. And it’s the application that matters. Data is useless if it just sits on a disk somewhere. It’s the ways in which the application lets the users view and manipulate the data that adds value to the business.
Yep, definitely a different type of thinking between application developers and database developers."
Me:
"Definitely religious now.
Applications come and go, data stays the same. Think Green Screens, EJBs, Ruby…what’s next?"
Mr. M:
"'Applications come and go'
Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.
'data stays the same.'
Nonsense. Otherwise UPDATE would not be an SQL reserved word. If you mean database technology stays the same, well, I’m more inclined to agree with that.
'Think Green Screens, EJBs, Ruby...what’s next?'
Whatever comes along to let the business more effectively respond to current market realities. Application platforms have evolved much faster than database platforms have. They’ve had to, their sphere of operation is much broader than that of databases, this is only natural, they deal with much broader concerns than do databases. Databases in the internet era function in essentially the same role they did in the era of dumb terminals. Clearly application platforms have evolved orders of magnitude more. Hence the statement, database concerns are a subset of application concerns.
Here’s a simple test….if I take some business application and I’m forced to throw away one or the other, either the database or the appl- wait a second, it doesn’t even make sense to finish it, does it? The business can live without the database. I could do all kinds of things with the data, I could stick it anywhere. The business can’t live without the application though. Another way to look at is, what do the business users look at, test, approve, and use? The database? Of course not, they look at the application. They could care less whether the data sits on disk in an RDBMS, xml, or flat files."
Me:
"We obviously violently disagree.
Without the database (and I use database and data interchangebly), the business could no longer function. The app is meaningless. How would you contact your customer? You couldn’t find it.
'Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.'
Poorly designed applications…that is all."
A Feisty Colleague:
"Using data and database interchangeably is incorrect. A database is a mechanism for data storage. XML data sets and flat files are mechanisms for data storage, too. So is a file cabinet, because, the data doesn’t have to be electronic, it could be … gasp! … on paper, and the application to use that data would be hands for holding the paper and a pencil to update and add data to the page."
Me:
"No it isn’t. I take into account xml files, flat files, web services (but not paper, unless it’s scanned) and all that. It would be consumed by the database and then accessed by the application via SQL.
(that’s for Mr. M and the feisty one)"
At which point someone forwarded the home page for Oracle's TimesTen In-Memory Database.
Me:
"A database on/in the mid-tier...Perfect!"
Mr. M:
"Implicit acknowledgment that disk IO operations that come with traditional database access simply can’t match the performance of in-memory data access (a point which you previously were unconvinced of but now seem perfectly accepting of the idea once you see it’s got Oracle’s imprimatur on it).
Of course, why any application developer would want to program against an SQL interface if they weren’t forced to is beyond me. It is orthogonal to the programming model of most application platform languages.
Surely Oracle recognize this fact too or they wouldn’t be buying Tangosol and other data grid technologies. Of course, most of those products are far more technically advanced than TimesTen or anything Oracle has in that space.
Incidentally, it’s illustrative to note that Coherence and other products like it were for the most part designed and built by application programmers. The development of all these products is pretty much driven by the needs of the large investment banks on Wall Street. These trading applications simply had too many concurrent transactions to use an RDBMS (a problem quite a number of public domains now share, most famously google.com, nope, no RDBMS there, yet miraculously there is still data). The database just simply would not scale to such a degree. So the application developers, by necessity, came up with an alternate solution that did work, a fully transactional cache of data replicated across a cluster with node numbers in the thousands, and no relational model whatsoever to speak of. A perfect example of how database concerns are only one, sometimes small, concern amongst many that application developers must be aware of and ready to solve."
Me:
"Like you said initially, the top 1%.
Many of us will never touch a system like this.
I will certainly concede that it is faster (still would love to see benchmarking though), but that still leaves 99% of the applications out there that do not require that kind of performance."
Me (again):
"And don’t forget, I use data and database interchangeably. Applications are nothing without the data right?
As to the object/relational impedance mismatch...well, more people that don’t know how to work in sets. Looping is what they understand. I understand the application side more than you seem to give me credit for.
I’m not saying applications aren’t important, they are. Data (databases) and applications go hand in hand. If the application went away though, they could still access their data via SELECT statements (yes, via an application client tool), however painful that may be. Applications make retrieving data that much easier for our users.
If anyone wants to unsubscribe from this mailing list, just let us know. This is fun for me (I’m guessing Mr. M too)."
Needless to say it was a fun day. It didn't get [too] personal. More than anything I'm happy to have an equally passionate colleague.
Besides, he claims he was just fracking around with me. ;)
The best line from the article:
"Jerry: "Yeah, databases cause lots of headaches. They crash all the time, corrupt data, etc. Using text files is better."
One of my more recently arrived colleagues (I'll call him Mr. M) replied to everyone with this statement:
"Kind of funny actually, databases are less and less important at the large investment banks, where they basically load everything up into a data grid across a several hundred node cluster. Writing to the db is way too slow."
This started a day long exchange of emails. What follows is the entire thread (up until my last post tonight).
Me:
"I would just argue that they don’t necessarily know how to write to databases. I would however love to see benchmarking done on both methods. Would be an interesting test..."
Mr. M:
"Well, my understanding is they just can’t scale out the db enough. Even something like Oracle RAC won’t work. And outside of the military, these are probably the top 1% of programmers in the world building this stuff."
Me:
"A benchmark would be the only way I would believe it.
If you said the top 1% of database developers tried it and failed, I would be more likely to agree.
My experience is that application developers != database developers. Different type of thinking involved."
Mr. M:
"'A benchmark would be the only way I would believe it.'
Do you need a benchmark before you would believe in-memory retrieval is faster than disk retrieval? Essentially, this is what we’re talking about.
'If you said the top 1% of database developers tried it and failed, I would be more likely to agree. My experience is that application developers != database developers. Different type of thinking involved.'
Why? It’s an issue to do with application performance not simply database performance. Database concerns are a subset of application concerns, essentially a specialization, requiring less encompassing knowledge. ;)
From the article you linked to (http://www.watersonline.com/public/showPage.html?page=432587)
"Better data management is the answer, says Lewis Foti, manager of high-performance computing and grid at The Royal Bank of Scotland (RBS) global banking and markets. "For very large compute arrays, the key issue is data starvation and saturation. This problem requires data grids with high bandwidth and scalable, parallel access,
...
Banks are learning that data management in a distributed grid environment is very different from online transaction processing. "With so many data sources, distribution channels, demands for aggregation and analytics, surges in data volumes and complex dynamics between the flows, we need to manage 'data in motion' and give up the notion that data is somehow stored. It's dynamic, not static," says Michael Di Stefano, vice president and architect for financial services at GemStone Systems
...
There is even some debate over how small a unit of work can be put on today's grids. Di Stefano at GemStone, for example, says, "One client has gone from 200 trades per second in a program trading application to more than 6,000 trades per second. This shows what the technology can do."
Yep, the writing is on the wall. Oracle knows it too.
http://www.google.com/search?hl=en&q=oracle+buys+tangosol&btnG=Google+Search"
Me:
"Good points. If it is in-memory it would be faster. I have not had the pleasure to work on such a system.
I do disagree with the database concerns being a subset of application concerns. The data drives the app. We’re probably getting religious at this point (or am I)."
Mr. M:
"‘The data drives the app.”
Exactly, but who’s to say where the data comes from or in what format? My application data may reside completely in xml files, or maybe I get it from some third party web services a la the en vogue “mashup.” Heck, I may not even need to worry about a database anymore…. http://www.amazon.com/gp/browse.html?node=16427261 The database is only one particular concern of the overall application. And it’s the application that matters. Data is useless if it just sits on a disk somewhere. It’s the ways in which the application lets the users view and manipulate the data that adds value to the business.
Yep, definitely a different type of thinking between application developers and database developers."
Me:
"Definitely religious now.
Applications come and go, data stays the same. Think Green Screens, EJBs, Ruby…what’s next?"
Mr. M:
"'Applications come and go'
Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.
'data stays the same.'
Nonsense. Otherwise UPDATE would not be an SQL reserved word. If you mean database technology stays the same, well, I’m more inclined to agree with that.
'Think Green Screens, EJBs, Ruby...what’s next?'
Whatever comes along to let the business more effectively respond to current market realities. Application platforms have evolved much faster than database platforms have. They’ve had to, their sphere of operation is much broader than that of databases, this is only natural, they deal with much broader concerns than do databases. Databases in the internet era function in essentially the same role they did in the era of dumb terminals. Clearly application platforms have evolved orders of magnitude more. Hence the statement, database concerns are a subset of application concerns.
Here’s a simple test….if I take some business application and I’m forced to throw away one or the other, either the database or the appl- wait a second, it doesn’t even make sense to finish it, does it? The business can live without the database. I could do all kinds of things with the data, I could stick it anywhere. The business can’t live without the application though. Another way to look at is, what do the business users look at, test, approve, and use? The database? Of course not, they look at the application. They could care less whether the data sits on disk in an RDBMS, xml, or flat files."
Me:
"We obviously violently disagree.
Without the database (and I use database and data interchangebly), the business could no longer function. The app is meaningless. How would you contact your customer? You couldn’t find it.
'Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.'
Poorly designed applications…that is all."
A Feisty Colleague:
"Using data and database interchangeably is incorrect. A database is a mechanism for data storage. XML data sets and flat files are mechanisms for data storage, too. So is a file cabinet, because, the data doesn’t have to be electronic, it could be … gasp! … on paper, and the application to use that data would be hands for holding the paper and a pencil to update and add data to the page."
Me:
"No it isn’t. I take into account xml files, flat files, web services (but not paper, unless it’s scanned) and all that. It would be consumed by the database and then accessed by the application via SQL.
(that’s for Mr. M and the feisty one)"
At which point someone forwarded the home page for Oracle's TimesTen In-Memory Database.
Me:
"A database on/in the mid-tier...Perfect!"
Mr. M:
"Implicit acknowledgment that disk IO operations that come with traditional database access simply can’t match the performance of in-memory data access (a point which you previously were unconvinced of but now seem perfectly accepting of the idea once you see it’s got Oracle’s imprimatur on it).
Of course, why any application developer would want to program against an SQL interface if they weren’t forced to is beyond me. It is orthogonal to the programming model of most application platform languages.
Surely Oracle recognize this fact too or they wouldn’t be buying Tangosol and other data grid technologies. Of course, most of those products are far more technically advanced than TimesTen or anything Oracle has in that space.
Incidentally, it’s illustrative to note that Coherence and other products like it were for the most part designed and built by application programmers. The development of all these products is pretty much driven by the needs of the large investment banks on Wall Street. These trading applications simply had too many concurrent transactions to use an RDBMS (a problem quite a number of public domains now share, most famously google.com, nope, no RDBMS there, yet miraculously there is still data). The database just simply would not scale to such a degree. So the application developers, by necessity, came up with an alternate solution that did work, a fully transactional cache of data replicated across a cluster with node numbers in the thousands, and no relational model whatsoever to speak of. A perfect example of how database concerns are only one, sometimes small, concern amongst many that application developers must be aware of and ready to solve."
Me:
"Like you said initially, the top 1%.
Many of us will never touch a system like this.
I will certainly concede that it is faster (still would love to see benchmarking though), but that still leaves 99% of the applications out there that do not require that kind of performance."
Me (again):
"And don’t forget, I use data and database interchangeably. Applications are nothing without the data right?
As to the object/relational impedance mismatch...well, more people that don’t know how to work in sets. Looping is what they understand. I understand the application side more than you seem to give me credit for.
I’m not saying applications aren’t important, they are. Data (databases) and applications go hand in hand. If the application went away though, they could still access their data via SELECT statements (yes, via an application client tool), however painful that may be. Applications make retrieving data that much easier for our users.
If anyone wants to unsubscribe from this mailing list, just let us know. This is fun for me (I’m guessing Mr. M too)."
Needless to say it was a fun day. It didn't get [too] personal. More than anything I'm happy to have an equally passionate colleague.
Besides, he claims he was just fracking around with me. ;)
Labels: database, development, funny, partition, work
¶ 2/20/2008 10:29:00 PM 5 Comments Links to this post
Wednesday, February 6, 2008
It's the Data, Stupid!
Search for the phrase on Google and you'll get plenty of results.
After reflecting for a few days on reaction to MySQL, I think I've realized what is at the heart of it all. Data.
Application developers are not stewards of data. They believe that to be the job of the DBA.
Someone recently asked one of our architects what features of MySQL convinced them to choose this as our new database engine. It's open source!
Of course, that makes perfect sense.
Can it connect to Oracle?
I don't know.
Our architects are made up primarily of former application developers, be it web or client server apps. Data was never that important...
They are currently driving our tool set to favor the application developers, which makes perfect sense to them. It's all about the interface.
But it's not. In the health-care industry, data is king. For any industry really.
I've been trying to convince everyone that this million dollar piece of software called Oracle is not just a bucket, it's feature rich. Streams, Queueing, all kinds of really cool tools. According to our DBAs, none of that stuff is used.
No wonder we're moving to MySQL.
So my quest is to convince the powers that be is to stop wasting money on our million dollar buckets and use them to their full capabilities.
If you have any information to help in this fight, links, slideshows, whatever, please send them on to me (myfirstname.mylastname@gmail.com), please!
Help me turn the tide back to Oracle, back to the data!
After reflecting for a few days on reaction to MySQL, I think I've realized what is at the heart of it all. Data.
Application developers are not stewards of data. They believe that to be the job of the DBA.
Someone recently asked one of our architects what features of MySQL convinced them to choose this as our new database engine. It's open source!
Of course, that makes perfect sense.
Can it connect to Oracle?
I don't know.
Our architects are made up primarily of former application developers, be it web or client server apps. Data was never that important...
They are currently driving our tool set to favor the application developers, which makes perfect sense to them. It's all about the interface.
But it's not. In the health-care industry, data is king. For any industry really.
I've been trying to convince everyone that this million dollar piece of software called Oracle is not just a bucket, it's feature rich. Streams, Queueing, all kinds of really cool tools. According to our DBAs, none of that stuff is used.
No wonder we're moving to MySQL.
So my quest is to convince the powers that be is to stop wasting money on our million dollar buckets and use them to their full capabilities.
If you have any information to help in this fight, links, slideshows, whatever, please send them on to me (myfirstname.mylastname@gmail.com), please!
Help me turn the tide back to Oracle, back to the data!
Labels: database, mysql, open source, oracle
¶ 2/06/2008 10:30:00 PM 1 Comments Links to this post
Monday, February 4, 2008
Is It Arrogance?
I wrote on Friday night about my experiences that day.
I am a very opinionated person. I believe, whole-heartedly, that the database is severely under-utilized, especially at my current employer.
I believe that one of the big draws of MySQL is that it's easy for web/application people to pick up. I also believe, in our situation, that's it's a way for application developers to skirt the whole "data" problem. They'll just pawn it off on the Production DBAs to keep the database running.
Amusingly, some of our application developers brought down one of our Oracle instances, more than once. Pretty tough thing to do I always thought.
I've read articles on bind variables since the beginning, but since it had been drilled into me, I found it quaint. Who would do that?
From a C# app someone passed in hundreds of thousands of un-bound INSERT statements. It flooded the shared pool (is that right?) and brought it to a screeching halt.
Anyway, back to the point.
I've been very vocal lately about MySQL. A few of my friends have begun to warn me that I may be crossing the line towards arrogance. That I will come off as someone resistant to change.
I don't see it. But sometimes we're the last to see our own reflection.
I don't believe that I am resistant to change. I like change. I just want it to be proven, that's all. I embraced ApEx because it made my life easier. That's all I want.
Does this make me arrogant?
I am a very opinionated person. I believe, whole-heartedly, that the database is severely under-utilized, especially at my current employer.
I believe that one of the big draws of MySQL is that it's easy for web/application people to pick up. I also believe, in our situation, that's it's a way for application developers to skirt the whole "data" problem. They'll just pawn it off on the Production DBAs to keep the database running.
Amusingly, some of our application developers brought down one of our Oracle instances, more than once. Pretty tough thing to do I always thought.
I've read articles on bind variables since the beginning, but since it had been drilled into me, I found it quaint. Who would do that?
From a C# app someone passed in hundreds of thousands of un-bound INSERT statements. It flooded the shared pool (is that right?) and brought it to a screeching halt.
Anyway, back to the point.
I've been very vocal lately about MySQL. A few of my friends have begun to warn me that I may be crossing the line towards arrogance. That I will come off as someone resistant to change.
I don't see it. But sometimes we're the last to see our own reflection.
I don't believe that I am resistant to change. I like change. I just want it to be proven, that's all. I embraced ApEx because it made my life easier. That's all I want.
Does this make me arrogant?
Labels: database, mysql, oracle, sql, work
¶ 2/04/2008 10:57:00 PM 3 Comments Links to this post
Friday, February 1, 2008
MySQL Friday
Each month we have an IT All-Hands meeting.
Last month I was promoted to Senior Vice President (SVP), because of my superior management techniques.
Today I was promoted to CEO! Unfortunately it only lasted for a few minutes. I happen to resemble our new CEO (and I'm always pining for a promotion) and they thought it would be funny (again) to bring me up.
I hugged the guy behind me, shook hands with people next to me and ran up to the front. I wanted to shriek, like the people do on The Price is Right, but I didn't have it in me. You gotta have fun at work right?
Well, after that it got serious. Our new Director (at WellCare, Directors are executives, one step up from managers and one below VPs) who heads our architecture team (and release management) got up to discuss where he would be taking us.
Slide one:
From 3 database engines to 1.
From 4 programming languages to 2.
From 3 OSs to 1.
Wanna guess what question I had?
"So, what database engine are we going to use?"
I knew the answer, but I take every single opportunity I get to make my point.
"MySQL."
Being on the datawarehouse team, I was confident that Oracle was not going away.
He went on to explain:
"Legacy applications would be maintained but everything going forward would be done in MySQL."
A flurry of questions came from the crowd so I was unable to followup immediately. I could feel the room come alive...it was weird (I think I'm still hopped up from the events that took place today).
Our CIO asked if there were any more questions or comments.
I spoke up.
I have two points.
1. If it's about cost, move all of the one-off applications into just a few Oracle instances. From what I can tell, we have somewhere in the neighborhood of 100. Let's say 5 databases, datawarehouse, our production OLTP and one for others. All you need to do is assign them different schemas, voila! Cost is much lower and there is a very big chance to reuse code.
2. Actually, I can't remember what my other point was. I think it had something to do with putting the logic in the database, that Java was the fad a few years ago, Ruby was the big thing now, what would it be in 5 years? Will we have to rewrite all of the logic then? (I guess I do sorta remember).
After that, someone asked about the two programming languages. Not a great answer from the crowd's reaction. Then someone asked about the OS.
The crowd was riotous (if that's a word). The CIO had to calm us all down.
I made a remark that he hadn't danced yet (one of our former hazing techniques for new employees) because I didn't want it to be completely personal, or just to ease something that I started.
After the meeting, I spoke with the Director. Oracle will be gone in 20 years because of the open source databases, it's being commoditized (not sure what that means). SOA is the wave of the future.
It was a polite conversation. I told him I look forward to learning from him but that I will probably never be sold on that idea. Fewer moving parts, simplicity, that's what I want.
I then spoke with the CIO, told him that once the decision was made, I would support it and keep my mouth shut (or find a new job).
I sent an email to the VP of the Director's group (after a couple of beers...idiot!) explaining my rationale.
One of the biggest reasons we chose to come to Tampa, to WellCare specifically, was because it was so young and immature. I would have the opportunity, if I could prove myself, to shape the future of IT here.
It's nice to have a voice.
Anyway, it's Friday, I'm prepped to spend all weekend at work to get this project delivered that was due in November. Have a good weekend!
Last month I was promoted to Senior Vice President (SVP), because of my superior management techniques.
Today I was promoted to CEO! Unfortunately it only lasted for a few minutes. I happen to resemble our new CEO (and I'm always pining for a promotion) and they thought it would be funny (again) to bring me up.
I hugged the guy behind me, shook hands with people next to me and ran up to the front. I wanted to shriek, like the people do on The Price is Right, but I didn't have it in me. You gotta have fun at work right?
Well, after that it got serious. Our new Director (at WellCare, Directors are executives, one step up from managers and one below VPs) who heads our architecture team (and release management) got up to discuss where he would be taking us.
Slide one:
From 3 database engines to 1.
From 4 programming languages to 2.
From 3 OSs to 1.
Wanna guess what question I had?
"So, what database engine are we going to use?"
I knew the answer, but I take every single opportunity I get to make my point.
"MySQL."
Being on the datawarehouse team, I was confident that Oracle was not going away.
He went on to explain:
"Legacy applications would be maintained but everything going forward would be done in MySQL."
A flurry of questions came from the crowd so I was unable to followup immediately. I could feel the room come alive...it was weird (I think I'm still hopped up from the events that took place today).
Our CIO asked if there were any more questions or comments.
I spoke up.
I have two points.
1. If it's about cost, move all of the one-off applications into just a few Oracle instances. From what I can tell, we have somewhere in the neighborhood of 100. Let's say 5 databases, datawarehouse, our production OLTP and one for others. All you need to do is assign them different schemas, voila! Cost is much lower and there is a very big chance to reuse code.
2. Actually, I can't remember what my other point was. I think it had something to do with putting the logic in the database, that Java was the fad a few years ago, Ruby was the big thing now, what would it be in 5 years? Will we have to rewrite all of the logic then? (I guess I do sorta remember).
After that, someone asked about the two programming languages. Not a great answer from the crowd's reaction. Then someone asked about the OS.
The crowd was riotous (if that's a word). The CIO had to calm us all down.
I made a remark that he hadn't danced yet (one of our former hazing techniques for new employees) because I didn't want it to be completely personal, or just to ease something that I started.
After the meeting, I spoke with the Director. Oracle will be gone in 20 years because of the open source databases, it's being commoditized (not sure what that means). SOA is the wave of the future.
It was a polite conversation. I told him I look forward to learning from him but that I will probably never be sold on that idea. Fewer moving parts, simplicity, that's what I want.
I then spoke with the CIO, told him that once the decision was made, I would support it and keep my mouth shut (or find a new job).
I sent an email to the VP of the Director's group (after a couple of beers...idiot!) explaining my rationale.
One of the biggest reasons we chose to come to Tampa, to WellCare specifically, was because it was so young and immature. I would have the opportunity, if I could prove myself, to shape the future of IT here.
It's nice to have a voice.
Anyway, it's Friday, I'm prepped to spend all weekend at work to get this project delivered that was due in November. Have a good weekend!
Labels: cio, database, funny, mysql, oracle, sql, work
¶ 2/01/2008 10:52:00 PM 2 Comments Links to this post
Thursday, January 31, 2008
Love Your DBA
I consider myself a Developer/DBA.
That said, you've probably either read about or experienced the typical riff between the developer and the DBA.
At my current employer, I am finally surrounded by true Production DBAs. Initially, I found it difficult to work them. When I would ask "Why can't I do that?" I would rarely get a response.
Over time though, things have changed...for the better.
I believe it's called trust.
Trust that I am trying to do the right thing.
Trust that I want to learn.
Trust that I will listen to their suggestions.
Trust that I won't hack their DEV/QA instances using CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE.
Trust that I want to build a scalable and robust application.
So love your DBA. Give them time to get to know you. Give them time to learn your style, your methodology. Maybe someday they'll love you back and your job will get infinitely easier.
That said, you've probably either read about or experienced the typical riff between the developer and the DBA.
At my current employer, I am finally surrounded by true Production DBAs. Initially, I found it difficult to work them. When I would ask "Why can't I do that?" I would rarely get a response.
Over time though, things have changed...for the better.
I believe it's called trust.
Trust that I am trying to do the right thing.
Trust that I want to learn.
Trust that I will listen to their suggestions.
Trust that I won't hack their DEV/QA instances using CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE.
Trust that I want to build a scalable and robust application.
So love your DBA. Give them time to get to know you. Give them time to learn your style, your methodology. Maybe someday they'll love you back and your job will get infinitely easier.
Labels: database, dba, development, work
¶ 1/31/2008 12:15:00 AM 6 Comments Links to this post
Sunday, January 27, 2008
Open Source Obsession?
Since our new CIO came on board last January, there seems to a big movement towards Open Source tools.
While I have nothing against them, I've used various tools in the past; I just don't see what our obsession is with them.
Let's start with Ruby on Rails. An open source framework built on top of Ruby. It's used for web development. It's supposed to be a much more user intuitive language which makes it so easy. Fair enough. I'm always for something that will make my job easier. Our corporate website and provider portals were previously created on the dot net framework.
It was decided last year to replace our entire web infrastructure with Ruby on Rails. I'm still trying to figure out why. It's not that I am a pro-Microsoft guy, but the site worked. There were complaints about it missing this or that functionality, but that's fairly easy to remedy. As far as I know, dot net can support AJAX functionality (which I believe was at the heart of everything).
The demos of the new site were very cool (apparently we paid someone a lot of money to design the site...a LOT of money). It looked all web 2.0ey, big buttons, small text (which is a suprise given we are an HMO managing Medicare). It looked a lot like the 37signals applications in fact. I guess I've read Tom Kyte for far too long and his rant about putting business logic in the database because the front end will always change. Seems true. Java or dot net were all the rage a few years back, now it's Ruby, in a couple of years it will be something else (ApEx!) at which point we will have to rewrite the whole thing.
Next up, MySQL. It's a given that this database has come a long way. Version 5 even has stored procedures. It's a great free database that supports many websites out there. Free is good. Well, mostly free anyway, we do pay support costs right now. Almost every new project is built on top of MySQL. Why? I'm not sure other than free.
I asked the question to the CIO in one of our All-Hands meetings and his response was cost.
Fair enough, Oracle is expensive. The way we use Oracle is even more so. Each project seems to get it's own database. Why they don't get a schema on an existing database I don't quite understand. I've been told that it is a logistical nightmare to pull down a database that affects so many different applications. Each group wants their own version, etc., etc.
I can understand the Data Warehouse having their own database and perhaps our main appplication having it's own database; but every single application? Why not a one off database that houses all the smaller applications in different schemas? That's what Oracle was built for? Plus you can reuse code, reduce the number of instances (thus reducing the cost)...I just don't understand.
I believe my main complaint is that they are still just treating the database (whether Oracle or MySQL) like a bucket. Web people should not be writing SQL; Ruby people should not be writing SQL; just like I shouldn't be writing Ruby code. I don't know it.
I would be willing to bet that I could re-create many of our smaller applications in a much shorter period of time in Oracle and ApEx given the same requirements.
So I rant on. If I truly thought that this was an effort to make IT cheaper and more sustainable, I would be on board. I just don't see that that is the case...
While I have nothing against them, I've used various tools in the past; I just don't see what our obsession is with them.
Let's start with Ruby on Rails. An open source framework built on top of Ruby. It's used for web development. It's supposed to be a much more user intuitive language which makes it so easy. Fair enough. I'm always for something that will make my job easier. Our corporate website and provider portals were previously created on the dot net framework.
It was decided last year to replace our entire web infrastructure with Ruby on Rails. I'm still trying to figure out why. It's not that I am a pro-Microsoft guy, but the site worked. There were complaints about it missing this or that functionality, but that's fairly easy to remedy. As far as I know, dot net can support AJAX functionality (which I believe was at the heart of everything).
The demos of the new site were very cool (apparently we paid someone a lot of money to design the site...a LOT of money). It looked all web 2.0ey, big buttons, small text (which is a suprise given we are an HMO managing Medicare). It looked a lot like the 37signals applications in fact. I guess I've read Tom Kyte for far too long and his rant about putting business logic in the database because the front end will always change. Seems true. Java or dot net were all the rage a few years back, now it's Ruby, in a couple of years it will be something else (ApEx!) at which point we will have to rewrite the whole thing.
Next up, MySQL. It's a given that this database has come a long way. Version 5 even has stored procedures. It's a great free database that supports many websites out there. Free is good. Well, mostly free anyway, we do pay support costs right now. Almost every new project is built on top of MySQL. Why? I'm not sure other than free.
I asked the question to the CIO in one of our All-Hands meetings and his response was cost.
Fair enough, Oracle is expensive. The way we use Oracle is even more so. Each project seems to get it's own database. Why they don't get a schema on an existing database I don't quite understand. I've been told that it is a logistical nightmare to pull down a database that affects so many different applications. Each group wants their own version, etc., etc.
I can understand the Data Warehouse having their own database and perhaps our main appplication having it's own database; but every single application? Why not a one off database that houses all the smaller applications in different schemas? That's what Oracle was built for? Plus you can reuse code, reduce the number of instances (thus reducing the cost)...I just don't understand.
I believe my main complaint is that they are still just treating the database (whether Oracle or MySQL) like a bucket. Web people should not be writing SQL; Ruby people should not be writing SQL; just like I shouldn't be writing Ruby code. I don't know it.
I would be willing to bet that I could re-create many of our smaller applications in a much shorter period of time in Oracle and ApEx given the same requirements.
So I rant on. If I truly thought that this was an effort to make IT cheaper and more sustainable, I would be on board. I just don't see that that is the case...
Labels: database, mysql, open source, oracle, work
¶ 1/27/2008 09:53:00 PM 6 Comments Links to this post
Tuesday, January 15, 2008
Asynchronous Distributed HotLog - CDC Part IV
Part I, II and III.
For the time being I am throwing in the towel. I managed to patch my 10.2.0.1 version up to 10.2.0.3 thinking that might help, but it did not.
I'm still leaning to something on the 9.2.0.6 side, the only reason being is that in the trace file generated by the 10g instance, I see a "Alter9iSource" line in there...but there is nothing in the alert log on the 9i instance.
So I'm packing it up for a little while, hopefully the time away will do me some good and help me regain focus.
I did find a great example of the manual setup by Lewis Cunningham; that may be the route in the future, just a bit more work to maintain.
For the time being I am throwing in the towel. I managed to patch my 10.2.0.1 version up to 10.2.0.3 thinking that might help, but it did not.
I'm still leaning to something on the 9.2.0.6 side, the only reason being is that in the trace file generated by the 10g instance, I see a "Alter9iSource" line in there...but there is nothing in the alert log on the 9i instance.
So I'm packing it up for a little while, hopefully the time away will do me some good and help me regain focus.
I did find a great example of the manual setup by Lewis Cunningham; that may be the route in the future, just a bit more work to maintain.
Labels: cdc, database, datawarehouse, dbms_cdc_publish, oracle
¶ 1/15/2008 12:20:00 AM 0 Comments Links to this post
Thursday, December 27, 2007
Asynchronous Distributed HotLog - Change Data Capture
I am currently researching the feasibility of getting Asynchronous Distributed HotLog CDC working for a Proof of Concept in our organization. The source database is a 9.2.0.7 on Sun Solaris and our target database is a 10gR2 on the same OS.
I have been trying, since I arrived, to get them to use more features provided by Oracle. From what I have found so far, all of our Oracle databases are severely under utilized.
I will post my findings here along with a full How To when I have completed it and hopefully the results of whether it was implemented or not.
I did want to post some of the sources I have used so far though:
Oracle Documentation - This is where I started. Most of the other links reference the docs.
Mark Rittman - 10g to 10g. Great example but from what I can tell so far, it's almost identical to the documentation.
Oracle How To - I just found this one and am very interested in reading through it.
If you have any examples, feel free to post the link.
I have been trying, since I arrived, to get them to use more features provided by Oracle. From what I have found so far, all of our Oracle databases are severely under utilized.
I will post my findings here along with a full How To when I have completed it and hopefully the results of whether it was implemented or not.
I did want to post some of the sources I have used so far though:
Oracle Documentation - This is where I started. Most of the other links reference the docs.
Mark Rittman - 10g to 10g. Great example but from what I can tell so far, it's almost identical to the documentation.
Oracle How To - I just found this one and am very interested in reading through it.
If you have any examples, feel free to post the link.
Labels: cdc, database, development, oracle
¶ 12/27/2007 01:01:00 AM 0 Comments Links to this post
Thursday, November 29, 2007
Keeping it Simple
One of my all time favorite articles is The Complicator's Gloves on Worse Than Failure (formerly the DailyWTF). It identifies the tendency of software developers in particular to come up with overly complex solutions, usually when there is a much simpler one available.
This was the context of my latest rant to my CIO. Actually, this theme seems to play out in all my rants. Funny how that works.
While web services and the like have their place, many times they are used just because they are the cool new thing, not because of a pressing need. I know I am not the first to mention that nor will I be the last.
Whether it was years of reading asktom (for pleasure no less) or the influence of my first boss, I have striven (sp?) to build applications that are scalable yet easy to maintain.
One of my proudest accomplishments as a developer was at my previous job. A small state-contracted agency where I was the lone developer. I, thanks to a very trusting boss, was allowed to install Oracle and soon after found Application Express (APEX). In 18 months I was able to create some 350 pages of forms and reports for the organization. One person, 350 pages. I once found a job ad for a web developer to help maintain a 100 page website on a team of six. What? Six people? Really? Must be java or something. ;)
I continued to work for them on a contract basis for about six months after I left. Mostly until the new guy got comfortable. Unfortunately for me, they didn't require my services a whole lot. Yes, I could be deluding myself, I realize that...but I just don't believe it. They WOULD tell me.
Back to my point. At our organization we seem to have quite a few architects. They talk of Ruby on Rails, Java, JBoss, etc. MySQL gets a brief mention on occasion.
We have a hard enough time writing good SQL or PL/SQL, so now we're going to introduce new languages and a new database platform?
If we were a company that made software, I will probably be [mostly] on board, but we are not. We store and manage data for the business to do their job.
I do hope I am wrong about them and that they do talk about the importance of data in our organization. I just haven't see it yet.
So, put it in the database, use APEX when appropriate (95% of the time) and keep it simple.
This was the context of my latest rant to my CIO. Actually, this theme seems to play out in all my rants. Funny how that works.
While web services and the like have their place, many times they are used just because they are the cool new thing, not because of a pressing need. I know I am not the first to mention that nor will I be the last.
Whether it was years of reading asktom (for pleasure no less) or the influence of my first boss, I have striven (sp?) to build applications that are scalable yet easy to maintain.
One of my proudest accomplishments as a developer was at my previous job. A small state-contracted agency where I was the lone developer. I, thanks to a very trusting boss, was allowed to install Oracle and soon after found Application Express (APEX). In 18 months I was able to create some 350 pages of forms and reports for the organization. One person, 350 pages. I once found a job ad for a web developer to help maintain a 100 page website on a team of six. What? Six people? Really? Must be java or something. ;)
I continued to work for them on a contract basis for about six months after I left. Mostly until the new guy got comfortable. Unfortunately for me, they didn't require my services a whole lot. Yes, I could be deluding myself, I realize that...but I just don't believe it. They WOULD tell me.
Back to my point. At our organization we seem to have quite a few architects. They talk of Ruby on Rails, Java, JBoss, etc. MySQL gets a brief mention on occasion.
We have a hard enough time writing good SQL or PL/SQL, so now we're going to introduce new languages and a new database platform?
If we were a company that made software, I will probably be [mostly] on board, but we are not. We store and manage data for the business to do their job.
I do hope I am wrong about them and that they do talk about the importance of data in our organization. I just haven't see it yet.
So, put it in the database, use APEX when appropriate (95% of the time) and keep it simple.
Labels: apex, database, development, work
¶ 11/29/2007 01:00:00 AM 1 Comments Links to this post
Monday, November 5, 2007
I Want to Be Better Than Tom Kyte
OK, that got your attention. Somehow I knew it would.
I believe Mr. Kyte to be one of the foremost experts at Oracle development. His solutions are usually simple and concise. His philosophy is simple and concise; logic belongs with the data (in the database), don't reinvent the wheel if we've already created it (using supplied packages) and keep it simple. Of course Mr. Kyte may have objections to some of that, but that's the general idea I have gleaned over that past 5 years.
2. I grew up playing baseball, I liked being better than most of the other kids. I still believe that if I hadn't drank away my opportunity in college, I'd still be playing.
3. I'm an only child, I'm used to the attention and crave it. How do I get it now? By being better than everyone else.
Perhaps I should start small...be the best Oracle developer at WellCare, then Tampa, Florida, the U.S, North America, Northern Hemisphere and finally the World!
I am probably not the best Oracle developer at WellCare, so I have a ways to go. That's what drives me though. Trying to be the best. I'm surrounded by a lot of smart people which is a good thing. No...a great thing. I've been the lone wolf developer for too long. Now I have the opportunity to learn directly (as opposed to just reading) from others. There is give and take. Sometimes my solution is the best and sometimes it is not.
I don't believe my competitive nature interferes with my interpersonal relationships (I hope not anyway). It is more of an internal thing to me. Once upon a time I was skinny and in shape and I did triathlons. I wanted to be a pro (laughable). Each time though I tried to outdo my previous performance. Did I want to win? Sure I did, but it was more important for me to improve.
I believe that I am strong enough to take criticism from others. I can admit when I'm wrong (see countdown timer above).
I do want to be the best. I'll probably never have the opportunity (nor the time) to do what Mr. Kyte has done. I'm not going to strive to be mediocre though. Whether I realize that goal or not is mainly irrelevant, but that is my goal...to be better than Mr. Kyte.
I believe Mr. Kyte to be one of the foremost experts at Oracle development. His solutions are usually simple and concise. His philosophy is simple and concise; logic belongs with the data (in the database), don't reinvent the wheel if we've already created it (using supplied packages) and keep it simple. Of course Mr. Kyte may have objections to some of that, but that's the general idea I have gleaned over that past 5 years.
I am very competitive
1. One of the reasons I got into IT in the first place was that I didn't like this whole group of people knowing more than I did.2. I grew up playing baseball, I liked being better than most of the other kids. I still believe that if I hadn't drank away my opportunity in college, I'd still be playing.
3. I'm an only child, I'm used to the attention and crave it. How do I get it now? By being better than everyone else.
My goal is to be the best developer in the world
Will I ever achieve that? Could it even be measured? Is there some sort of Oracle developer competition out there?Perhaps I should start small...be the best Oracle developer at WellCare, then Tampa, Florida, the U.S, North America, Northern Hemisphere and finally the World!
I am probably not the best Oracle developer at WellCare, so I have a ways to go. That's what drives me though. Trying to be the best. I'm surrounded by a lot of smart people which is a good thing. No...a great thing. I've been the lone wolf developer for too long. Now I have the opportunity to learn directly (as opposed to just reading) from others. There is give and take. Sometimes my solution is the best and sometimes it is not.
I don't believe my competitive nature interferes with my interpersonal relationships (I hope not anyway). It is more of an internal thing to me. Once upon a time I was skinny and in shape and I did triathlons. I wanted to be a pro (laughable). Each time though I tried to outdo my previous performance. Did I want to win? Sure I did, but it was more important for me to improve.
I believe that I am strong enough to take criticism from others. I can admit when I'm wrong (see countdown timer above).
I do want to be the best. I'll probably never have the opportunity (nor the time) to do what Mr. Kyte has done. I'm not going to strive to be mediocre though. Whether I realize that goal or not is mainly irrelevant, but that is my goal...to be better than Mr. Kyte.
Labels: database, development, nerd, oracle, work
¶ 11/05/2007 08:48:00 PM 4 Comments Links to this post
Wednesday, September 19, 2007
To CONSTRAINT or Not to CONSTRAINT
I've been having these long (good) drawn out conversations with a colleague of mine recently about constraints in a datawarehouse environment. Since I come from an OLTP environment, I want to put a constraint on everything. He on the other hand wants any constraints enforced through code.
I can understand that constraints my slow down table loads, but with the volume of data we are currently using (100 million row tables), I just don't see that as a huge impact on performance. For me, writing less code is better than any minimal performance gains we might achieve.
For instance, today he told me he wanted to add a new column. The possible values would either be M or G. I asked (I'm lead on the project) him to throw a CHECK constraint on the table limiting the values that can go in that field.
For me, I guess it's a support issue and part database purist issue. Use it for more than a bucket, Oracle's expensive. It forces developers to deal with that and will immediately tell them if they have done something wrong. It also makes support a tad easier as they won't have to wonder what actually goes in the field.
We talked at length about it but never really came to a decision (i.e. I couldn't convince him).
I know that a benchmark test would be the best way to prove either way, but I don't have the time right now to do it. Perhaps when things slow down again I will.
Any suggestions out there?
I can understand that constraints my slow down table loads, but with the volume of data we are currently using (100 million row tables), I just don't see that as a huge impact on performance. For me, writing less code is better than any minimal performance gains we might achieve.
For instance, today he told me he wanted to add a new column. The possible values would either be M or G. I asked (I'm lead on the project) him to throw a CHECK constraint on the table limiting the values that can go in that field.
For me, I guess it's a support issue and part database purist issue. Use it for more than a bucket, Oracle's expensive. It forces developers to deal with that and will immediately tell them if they have done something wrong. It also makes support a tad easier as they won't have to wonder what actually goes in the field.
We talked at length about it but never really came to a decision (i.e. I couldn't convince him).
I know that a benchmark test would be the best way to prove either way, but I don't have the time right now to do it. Perhaps when things slow down again I will.
Any suggestions out there?
Labels: database, datawarehouse, oracle
¶ 9/19/2007 09:52:00 PM 0 Comments Links to this post
Tuesday, September 11, 2007
Inline vs. Out-of-line Constraints?
Today one of my more feisty colleages and I had a discussion about constraints. I had asked him if he wouldn’t mind naming the NOT NULL constraints to a particular table definition. With this guy, nothing is easy. ;-)
So he came over and we talked about inline vs. out-of-line constraints. I asked him the advantage of out-of-line. He asked me the advantage of inline. I’m sure his answer was better than mine as he is much more articulate.
For me, it’s mostly a style thing, except for FOREIGN KEY constraints which, if defined inline, will inherit the data type of the parent column. That makes life easier if you ever need to change the data type of a parent key (not a recommended best practice mind you). I just think it looks prettier (factual based evidence). It’s all in one file and I like to see how many constraints I can put on a single table. I’m shooting for at least one per column to save myself time down the road of coding exceptions. Let the database do it’s job!
Here’s a sample of inline constraints:
And out-of-line Constraints:
So, what are the advantages for either method? I couldn’t find much via google or asktom, but I probably just didn’t search on the right terms. I find it hard to believe that this topic hasn’t come up before.
* Note that I did use 5 constraint types
So he came over and we talked about inline vs. out-of-line constraints. I asked him the advantage of out-of-line. He asked me the advantage of inline. I’m sure his answer was better than mine as he is much more articulate.
For me, it’s mostly a style thing, except for FOREIGN KEY constraints which, if defined inline, will inherit the data type of the parent column. That makes life easier if you ever need to change the data type of a parent key (not a recommended best practice mind you). I just think it looks prettier (factual based evidence). It’s all in one file and I like to see how many constraints I can put on a single table. I’m shooting for at least one per column to save myself time down the road of coding exceptions. Let the database do it’s job!
Here’s a sample of inline constraints:
DROP TABLE s;
DROP TABLE t;
CREATE TABLE t
(
id NUMBER(10)
CONSTRAINT pk_id PRIMARY KEY,
name VARCHAR2(30)
CONSTRAINT nn_name_t NOT NULL,
age NUMBER(2,0)
CONSTRAINT nn_age_t NOT NULL
CONSTRAINT ck_gtzero_age_t CHECK ( age >= 0 )
);
CREATE TABLE s
(
sid NUMBER(10)
CONSTRAINT pk_sid PRIMARY KEY,
id
CONSTRAINT fk_id_s REFERENCES t ( id ),
something_unique VARCHAR2(30)
CONSTRAINT uq_somethingunique_s UNIQUE
);
And out-of-line Constraints:
DROP TABLE s;
DROP TABLE t;
CREATE TABLE t
(
id NUMBER(10),
name VARCHAR2(30),
age NUMBER(2,0)
);
ALTER TABLE t ADD CONSTRAINT pk_id PRIMARY KEY ( id );
ALTER TABLE t ADD CONSTRAINT nn_name_t CHECK ( name IS NOT NULL );
ALTER TABLE t ADD CONSTRAINT nn_age_t CHECK ( age IS NOT NULL );
ALTER TABLE t ADD CONSTRAINT ck_gtzero_age_t CHECK ( age >= 0 );
CREATE TABLE s
(
sid NUMBER(10),
id NUMBER(10),
something_unique VARCHAR2(30)
);
ALTER TABLE s ADD CONSTRAINT pk_sid PRIMARY KEY ( sid );
ALTER TABLE s
ADD CONSTRAINT fk_id_s FOREIGN KEY ( id ) REFERENCES t ( id );
ALTER TABLE s
ADD CONSTRAINT uq_somethingunique_s UNIQUE ( something_unique );
So, what are the advantages for either method? I couldn’t find much via google or asktom, but I probably just didn’t search on the right terms. I find it hard to believe that this topic hasn’t come up before.
* Note that I did use 5 constraint types
Labels: constraints, database
¶ 9/11/2007 10:19:00 PM 3 Comments Links to this post
Monday, September 10, 2007
Oracle Tools I've Used
Tools:
SQL*Plus
APEX - Application Express (formerly HTMLDB)
JDeveloper
SQL Developer
Reports Builder
OC4J
Discoverer
Oracle Server 8i, 9i, 10g (Windows)
Oracle Application Server 9i, 10g (Windows)
rman
oradim
lsnrctl
tkprof
Coming Soon:
Oracle Warehouse Builder
Database Features:
Java
Object Types
Workflow
Advanced Queueing (in conjunction with Workflow)
Heterogenous Services
Features I'd like to use:
Change Data Capture
XML
Spatial
interMedia
Regular Expressions
Database Supplied Packages:
DBMS_OUTPUT
DBMS_LOB
UTL_FILE
DBMS_APPLICATION_INFO
DBMS_CRYPTO
DBMS_UTILITY
DBMS_METADATA
DBMS_EPG
DBMS_HS_PASSTHROUGH
DBMS_JAVA
DBMS_JOB
DBMS_LOCK
DBMS_MVIEW
DBMS_OBFUSCATION_TOOLKIT
DBMS_RANDOM
DBMS_SESSION
DBMS_SQL
DBMS_STATS
DBMS_XDB
DBMS_XPLAN
HTMLDB_APPLICATION
HTMLDB_ITEM
HTMLDB_UTIL
HTP
OWA_COOKIE
UTL_MAIL
UTL_RAW
UTL_SMTP
WPG_DOCLOAD
Packages I'd like learn to use (10g):
UTL_DBWS
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_DATA_MINING
DBMS_DATAPUMP
DBMS_SCHEDULER
DBMS_OLAP
DBMS_PIPE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
DBMS_STREAMS_MESSAGING
SQL*Plus
APEX - Application Express (formerly HTMLDB)
JDeveloper
SQL Developer
Reports Builder
OC4J
Discoverer
Oracle Server 8i, 9i, 10g (Windows)
Oracle Application Server 9i, 10g (Windows)
rman
oradim
lsnrctl
tkprof
Coming Soon:
Oracle Warehouse Builder
Database Features:
Java
Object Types
Workflow
Advanced Queueing (in conjunction with Workflow)
Heterogenous Services
Features I'd like to use:
Change Data Capture
XML
Spatial
interMedia
Regular Expressions
Database Supplied Packages:
DBMS_OUTPUT
DBMS_LOB
UTL_FILE
DBMS_APPLICATION_INFO
DBMS_CRYPTO
DBMS_UTILITY
DBMS_METADATA
DBMS_EPG
DBMS_HS_PASSTHROUGH
DBMS_JAVA
DBMS_JOB
DBMS_LOCK
DBMS_MVIEW
DBMS_OBFUSCATION_TOOLKIT
DBMS_RANDOM
DBMS_SESSION
DBMS_SQL
DBMS_STATS
DBMS_XDB
DBMS_XPLAN
HTMLDB_APPLICATION
HTMLDB_ITEM
HTMLDB_UTIL
HTP
OWA_COOKIE
UTL_MAIL
UTL_RAW
UTL_SMTP
WPG_DOCLOAD
Packages I'd like learn to use (10g):
UTL_DBWS
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_DATA_MINING
DBMS_DATAPUMP
DBMS_SCHEDULER
DBMS_OLAP
DBMS_PIPE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
DBMS_STREAMS_MESSAGING
Labels: database, oracle, tools, utilities
¶ 9/10/2007 12:12:00 AM 0 Comments Links to this post
Wednesday, September 5, 2007
Business Logic: In the Database or in the Application
When I started in the Data Warehouse, I began subscribing to as many BI/Data Warehousing blogs that I could find (there don't seem to be lot of them). Intelligent Enterprise (Roger Kimball) and Bill Inmon seem to be the most popular or well known. I also read Mark Rittman (and Mr. Mead), David Aldridge, Lewis Cunningham and Dratz who all seem to have more of an Oracle lean.
Today I read a post from Dan Linstedt titled "How Data Models can Impact Business." I followed the rather lengthy, but descriptive post, until I got to this part:
Today I read a post from Dan Linstedt titled "How Data Models can Impact Business." I followed the rather lengthy, but descriptive post, until I got to this part:
"Ok, I kind-of buy it, but what about Referential Integrity, when should that be enforced?
In two places:
1) When the data is captured within the application capturing it - it would clean up a LOT of these source systems, and put much more rigorous business logic (and cleaner data) into the source systems to begin with.
2) When the data is "released" for users, to reports, to screens, to output. This is when reusable common services / routines for getting data out are helpful. They implement the referential integrity in the application layer."
Now, I haven't been in IT all that long (5+ years now), but I put as much business logic into the database as I possibly can. I use the front end (APEX, woohoo!), to handle row color or something along those lines.
If you start with a good, flexible model, have a strong database team (DBAs, Developers) and you build a good API to your physical model (no INSERT, UPDATE or DELETE to any users, the only entry point to your tables being your API), flexibility and maintainability should not be a problem. Making changes should not entail a monumental effort. I suppose if the application in Mr. Linstedt's article is the only point of entry into the tables, I probably wouldn't really disagree (he's just moved it from my database API to his application), but then you have a giant bucket. Why not just use what you have paid for and build it in the database? That way, more than one application can use the same API over and over.
Labels: database, development, oracle, work
¶ 9/05/2007 09:43:00 PM 3 Comments Links to this post
Monday, August 27, 2007
APEX Evangelism
I started using APEX, Application Express, formerly HTMLDB, more than two years ago.
Prior to APEX, anything web related I used Java (J2EE).
When I started at my previous job, they had virtually no internal systems. I was hired as a Reports Analyst/Data Analyst because they had decided their 3rd party vendor wasn't working out.
I somehow managed to talk them into purchasing an Oracle database (SE); since they wanted it quick, and that's what I knew, it was a no-brainer for my boss.
Relatively quickly I managed to nail down my first report after downloading all the data into our shiny new Oracle database. I wanted to impress so I began the tedious task of webifying it, and boy were they impressed.
Up to that point, I had been using a modified version of the Struts framework. It was definitely overkill. I created a bean (class) for the report data and a page (jsp) to display that data. It was a time consuming process at best.
I had read about HTMLDB through AskTom at the job before that one and it was intriguing. So I downloaded it at the new job and took it for a spin. Within a month I had a couple of pages up and running (complete with user login and our cool new graphs). It was difficult initially to wrap my head around the terminology, but once I did it was full speed ahead.
I found the forums on OTN for APEX and began researching problems and asking questions. Soon after that I was contributing. I was hooked.
I was now an APEX evangelist. I would extoll the virtues of APEX to anyone that would listen (mostly my wife, but she was just humoring me). By the time I left that job, 18 months later, our internal web site consisted of almost 350 pages.
At one point I read an job description something along the lines of "Developer wanted to help maintain 100 page web site with a team of 6." Six people? What? Why does it take so many people? What the hell are they using, Java?
I still consider myself an APEX evangelist, but I no longer get to work with it on a daily business. I did manage to convince my superiors though that it would be a good basic reporting tool for our data warehouse environment. We've built 2 applications so far that utilize APEX and hopefully there's more to come.
Prior to APEX, anything web related I used Java (J2EE).
When I started at my previous job, they had virtually no internal systems. I was hired as a Reports Analyst/Data Analyst because they had decided their 3rd party vendor wasn't working out.
I somehow managed to talk them into purchasing an Oracle database (SE); since they wanted it quick, and that's what I knew, it was a no-brainer for my boss.
Relatively quickly I managed to nail down my first report after downloading all the data into our shiny new Oracle database. I wanted to impress so I began the tedious task of webifying it, and boy were they impressed.
Up to that point, I had been using a modified version of the Struts framework. It was definitely overkill. I created a bean (class) for the report data and a page (jsp) to display that data. It was a time consuming process at best.
I had read about HTMLDB through AskTom at the job before that one and it was intriguing. So I downloaded it at the new job and took it for a spin. Within a month I had a couple of pages up and running (complete with user login and our cool new graphs). It was difficult initially to wrap my head around the terminology, but once I did it was full speed ahead.
I found the forums on OTN for APEX and began researching problems and asking questions. Soon after that I was contributing. I was hooked.
I was now an APEX evangelist. I would extoll the virtues of APEX to anyone that would listen (mostly my wife, but she was just humoring me). By the time I left that job, 18 months later, our internal web site consisted of almost 350 pages.
At one point I read an job description something along the lines of "Developer wanted to help maintain 100 page web site with a team of 6." Six people? What? Why does it take so many people? What the hell are they using, Java?
I still consider myself an APEX evangelist, but I no longer get to work with it on a daily business. I did manage to convince my superiors though that it would be a good basic reporting tool for our data warehouse environment. We've built 2 applications so far that utilize APEX and hopefully there's more to come.
Labels: apex, database, oracle
¶ 8/27/2007 11:40:00 PM 0 Comments Links to this post
Tuesday, August 21, 2007
SQL Puzzle, Sort of...
My group was once asked, "What's the first number that contains an 'A' in it?"
This has become a once a week exercise for fun.
I of course started counting from one to see where it might happen. Then I decided to use the magic of SQL to help me go through all the numbers.
On 10g:
Pretty cool stuff. ¶ 8/21/2007 02:11:00 AM 0 Comments Links to this post
This has become a once a week exercise for fun.
I of course started counting from one to see where it might happen. Then I decided to use the magic of SQL to help me go through all the numbers.
On 10g:
I found the format model in the documention and moved on from there. I think my original query was a bit more elegant, but this works.
SELECT num
FROM
(
SELECT
TO_CHAR(TO_DATE('01/01/4712 BC', 'MM/DD/YYYY BC') + (rownum - 1), 'JSP' ) num
FROM DUAL
CONNECT BY LEVEL < 1000
)
WHERE INSTR( num, 'A' ) > 0;
NUM
-------------------
ONE THOUSAND
Pretty cool stuff. ¶ 8/21/2007 02:11:00 AM 0 Comments Links to this post
Saturday, August 18, 2007
How I Got Started...
It all started 5 1/2 years ago, my first IT job as a reports developer. I was given a tnsnames file and SQL*Plus. What? What the hell am I supposed to do with this?
I learned about databases through Microsoft's Access. While I would never want to go back to it, I am thankful it was there or I probably would never have learned. I was basically a secretary (administrative assistant for the PC). I got tired of entering in the same thing every month and knew there had to be a better way. So I put it in Access to speed things up a bit. I began to annoy the crap out of the IT department. I now wanted raw feeds of the data so that I wouldn't have to type in anything...
Finally a friend came along and offered me a job.
That's when I met Oracle.
So there I sat on my first day wondering where the tables where. I couldn't "see" them. I needed to "see" them dammit. Within a week I had set up an Access database to be my front end to Oracle; I couldn't stand not "seeing" the tables.
That got old real quick though. The sheer volume of data caused that. Within weeks, I was using SQL*Plus and beggining to learn the data dictionary.
I'd ask my cube mate, "How do I see the source of the view?"
SELECT text FROM user_views WHERE view_name = 'MY_VIEW';
Oh yeah; SET LONG 100000
I was fortunate enough to have a helpful cube mate.
And so began my obsession...
I would say it took a good year before I really started to feel comfortable with SQL*Plus and Oracle, and 5 years later, I'm still using it.
I'm glad I was forced to learn Oracle that way. I believe that I have a far better understanding of how Oracle works because of my experience.
¶ 8/18/2007 11:48:00 PM 2 Comments Links to this post
I learned about databases through Microsoft's Access. While I would never want to go back to it, I am thankful it was there or I probably would never have learned. I was basically a secretary (administrative assistant for the PC). I got tired of entering in the same thing every month and knew there had to be a better way. So I put it in Access to speed things up a bit. I began to annoy the crap out of the IT department. I now wanted raw feeds of the data so that I wouldn't have to type in anything...
Finally a friend came along and offered me a job.
That's when I met Oracle.
So there I sat on my first day wondering where the tables where. I couldn't "see" them. I needed to "see" them dammit. Within a week I had set up an Access database to be my front end to Oracle; I couldn't stand not "seeing" the tables.
That got old real quick though. The sheer volume of data caused that. Within weeks, I was using SQL*Plus and beggining to learn the data dictionary.
I'd ask my cube mate, "How do I see the source of the view?"
SELECT text FROM user_views WHERE view_name = 'MY_VIEW';
Oh yeah; SET LONG 100000
I was fortunate enough to have a helpful cube mate.
And so began my obsession...
I would say it took a good year before I really started to feel comfortable with SQL*Plus and Oracle, and 5 years later, I'm still using it.
I'm glad I was forced to learn Oracle that way. I believe that I have a far better understanding of how Oracle works because of my experience.
¶ 8/18/2007 11:48:00 PM 2 Comments Links to this post
Thursday, August 16, 2007
Swapping Partitions
Recently I learned a pretty cool trick, swapping partitions.
I'm sure I read it originally on AskTom, but I never had a need for it. Now that I am in data warehousing though, moving millions and millions of records around takes time...a lot of time.
On this recent project, I had a process which brought in the data from a different database. There were some minor transformations on that data including the use of an analytic function to de-dup the data (I would assign a 1 to the first record matching my criteria and that's what I would use for everything downstream).
So I have essentially six tables. One which is the target for the source data, four which handle the transformed data and another one which holds all of that transformed data. Initially I would perform a INSERT INTO...SELECT FROM...but it would take upwards of three hours for this one particular set of data (~26 million records), and I was doing that twice (six hours).
Sitting with a colleague trying to figure out a way to speed up the process, we came across an article on AskTom which mentioned swapping (or exchanging) partitions with a regular table. The final table (of the six) is partitioned by line of business, a perfect candidate.
So I issue the statement found there:
ALTER TABLE target_table
EXCHANGE PARTITION gmd_lob
WITH TABLE source_table;
That didn't work because the table structures did not match. So I went about changing the table structure to match that of the target table.
I issued the statement again:
ALTER TABLE target_table
EXCHANGE PARTITION gmd_lob
WITH TABLE source_table;
Success! I then went on to change the other three tables structure to match that of the target table. What had taken three to four hours now took less than five minutes!
I'm sure I read it originally on AskTom, but I never had a need for it. Now that I am in data warehousing though, moving millions and millions of records around takes time...a lot of time.
On this recent project, I had a process which brought in the data from a different database. There were some minor transformations on that data including the use of an analytic function to de-dup the data (I would assign a 1 to the first record matching my criteria and that's what I would use for everything downstream).
So I have essentially six tables. One which is the target for the source data, four which handle the transformed data and another one which holds all of that transformed data. Initially I would perform a INSERT INTO...SELECT FROM...but it would take upwards of three hours for this one particular set of data (~26 million records), and I was doing that twice (six hours).
Sitting with a colleague trying to figure out a way to speed up the process, we came across an article on AskTom which mentioned swapping (or exchanging) partitions with a regular table. The final table (of the six) is partitioned by line of business, a perfect candidate.
So I issue the statement found there:
ALTER TABLE target_table
EXCHANGE PARTITION gmd_lob
WITH TABLE source_table;
That didn't work because the table structures did not match. So I went about changing the table structure to match that of the target table.
I issued the statement again:
ALTER TABLE target_table
EXCHANGE PARTITION gmd_lob
WITH TABLE source_table;
Success! I then went on to change the other three tables structure to match that of the target table. What had taken three to four hours now took less than five minutes!
Labels: database, howto, oracle, partition
¶ 8/16/2007 08:29:00 PM 0 Comments Links to this post
Tuesday, August 14, 2007
My Manifesto
I am an Oracle Nerd.
I am not an Oracle fanatic.
I believe that SQL Server, DB2, MySQL, et. al. are all fine databases, but I don't know them like I know Oracle.
Regardless of your database, use it. Stop treating it like a bucket for your data. If you are going to do so, you might as well use flat files...or better yet, XML! Oracle is an incredibly powerful database and I believe in using what I (well, my employer) paid for.
At my current employer, we have numerous Oracle databases on site: Operational Data Store (ODS - mostly raw data from outside agencies), our production OLTP system and 2 data warehouse databases (we're currently trying to re-architect into one). Out of all those, we use exactly zero built in features. No CDC (Change Data Capture), no Advanced Queueing (Streams), hardly any analytics.
I try to keep things as simple as possible. My experience has been that most people want to overcomplicate things...hopefully I can help to change that at my workplace now. It won't be easy, but I'll certainly try.
I am not an Oracle fanatic.
I believe that SQL Server, DB2, MySQL, et. al. are all fine databases, but I don't know them like I know Oracle.
Regardless of your database, use it. Stop treating it like a bucket for your data. If you are going to do so, you might as well use flat files...or better yet, XML! Oracle is an incredibly powerful database and I believe in using what I (well, my employer) paid for.
At my current employer, we have numerous Oracle databases on site: Operational Data Store (ODS - mostly raw data from outside agencies), our production OLTP system and 2 data warehouse databases (we're currently trying to re-architect into one). Out of all those, we use exactly zero built in features. No CDC (Change Data Capture), no Advanced Queueing (Streams), hardly any analytics.
I try to keep things as simple as possible. My experience has been that most people want to overcomplicate things...hopefully I can help to change that at my workplace now. It won't be easy, but I'll certainly try.
I want to share my experiences; in my new role as a data warehouse developer, things I have learned designing, developing and maintaining OLTP systems, and my experience with the incredibly powerful APEX (formerly HTMLDB).
¶ 8/14/2007 11:39:00 PM 3 Comments Links to this post