 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
|
|
|
|
|
|
Location: Blogs
Bert Scalzo's Blog
|
|
| Bert |
Wednesday, August 15, 2007 6:18 AM |
Oracle 11g is out for Linux – and like every new release, there’s tons of cool new stuff. So over the next few weeks, I will write about some of Oracle 11g’s new features. This week, I’ll look at Virtual Columns and Virtual Indexes.
When designing a relational database, analysts often look at prior systems’ screens and reports for insight. So the database analyst designing the business entity of a “product” might see a textual value such as “01AUTO1234567” when reviewing such materials. Upon asking business users what this value represents, they’re told that the first two positions represent the product’s status (active versus inactive), the next four positions represent the department where the product is sold, and the final seven positions are the product number. Review of the legacy system’s file system record layout confirms this information.
So how should the database analyst design the product table? Well if they stick to good relational design, “first normal form” requires each table’s columns to represent a single or atomic concept (which also includes having no arrays or no repeating groups). So the ideal relational structure might look like this:
CREATE TABLE PROD1 (
STAT CHAR(2) DEFAULT ’01’ NOT NULL,
DEPT CHAR(4) NOT NULL,
PNUM CHAR(7) NOT NULL,
CONSTRAINT PROD1_PK PRIMARY KEY (STAT, DEPT, PNUM),
CONSTRAINT PROD1_CK CHECK (STAT IN (’00’, ’01’)),
CONSTRAINT PROD1_FK FOREIGN KEY(DEPT)
REFERENCES DEPT(ID) ON DELETE CASCADE
);
Now there’s a lot of powerful stuff here being enforced by the database: default values, check constraints, foreign key constraints, and a primary key (i.e. unique index). So the database is enforcing all these business rules. Furthermore, the following SQL query can make use of the primary key’s index for an efficient range scan.
SELECT * FROM PROD1 WHERE STAT=’01’ AND DEPT=’AUTO’;
But alas, all the above is not how most people end up designing their table. Instead, they simply create the table mimicking the legacy system’s screen, report or record layout. So the table most often ends up looking like this:
CREATE TABLE PROD2 (
PNUM CHAR(13) NOT NULL,
CONSTRAINT PROD2_PK PRIMARY KEY (PNUM)
);
Thus the SQL query becomes the following (note the liberal need for SUBSTR function references – which eliminates the use of the primary key index).
SELECT * FROM PROD2
WHERE SUBSTR(PNUM,1,2)=’01’
AND SUBSTR(PNUM,3,4)=’AUTO’;
I think the main reason people do this is two fold: first, they hesitate to make changes, and second, all displayed references to “product” are always thirteen character positions long – so why break it up? Of course the prior table definition would work, but that would require more work on the developers’ part to code the concatenation each time J
So now they have lost a ton of capability (possibly quite a lot depending on the Oracle version). The database no longer can do the following: default values, check constraints, and foreign key constraints. Ouch! Plus we may lose the use of our index. Remember that prior to 10g, Oracle could only perform range scans using the leading portion of the index. So had the query only asked for a restriction on the DEPT column, Oracle would have had to perform a full table scan. L
So people tried to work around this by creating a view to always capture the need for SUBSTR function references as follows:
CREATE VIEW PROD3 AS
SELECT SUBSTR(PNUM,1,2) AS STAT,
SUBSTR(PNUM,3,4) AS DEPT,
SUBSTR(PNUM,7,7) AS PNUM
FROM PROD2;
Of course we still need to add the following function based indexes to try to regain some of the indexing qualities we lost. And our index count is now four as we’ve added one!
CREATE INDEX PROD_F1 ON PROD2(SUBSTR(PNUM,1,2));
CREATE INDEX PROD_F2 ON PROD2(SUBSTR(PNUM,3,4));
CREATE INDEX PROD_F3 ON PROD2(SUBSTR(PNUM,7,7));
So to make this bad design practice even easier, Oracle 11g introduces the concept of virtual tables and virtual indexes. Here is the same concept as above using these new techniques:
CREATE TABLE PROD4 (
XPNUM CHAR(13) NOT NULL,
STAT CHAR(2) GENERATED ALWAYS AS ((SUBSTR(XPNUM,1,2))),
DEPT CHAR(4) GENERATED ALWAYS AS ((SUBSTR(XPNUM,3,4))),
PNUM CHAR(7) GENERATED ALWAYS AS ((SUBSTR(XPNUM,7,7))),
CONSTRAINT PROD4_PK PRIMARY KEY (XPNUM)
);
CREATE INDEX PROD_V1 ON PROD4(STAT);
CREATE INDEX PROD_V2 ON PROD4(DEPT);
CREATE INDEX PROD_V3 ON PROD4(PNUM);
As my hero Homer Simpson would say: “woohoo.” But there are still drawbacks to such an implementation. The database still cannot enforce the following: default values, check constraints, and foreign key constraints. Plus we’ve added that fourth index! In fact, virtual columns and indexes serve as nothing more than a “centralized shorthand” notation for the above view and indexes.
So even though Oracle 11g adds many cool new features, make sure they make sense to use before jumping on the band wagon. Just because it’s a new feature does not mean it’s advisable to utilize J
|
| Copyright ©2007 Quest Software Inc. |
| Permalink |
Trackback |
Comments (3)
Add Comment
|
By JCFlack on
Thursday, August 16, 2007 5:19 AM |
| As a "good" database designer, I always normalize, breaking legacy keys into their components as shown in your first example. Of course, then I get from the users, "Where is our Part Number?" At this point, I construct a view, putting the pieces together as a part number rather than the other way around as you show. It will be nice to have the option of not creating the view, yet without breaking good database design. |
|
|
By bscalzo on
Thursday, August 16, 2007 5:40 AM |
Yes - if instead of this:
CREATE TABLE PROD4 (
XPNUM CHAR(13) NOT NULL,
STAT CHAR(2) GENERATED ALWAYS AS ((SUBSTR(XPNUM,1,2))),
DEPT CHAR(4) GENERATED ALWAYS AS ((SUBSTR(XPNUM,3,4))),
PNUM CHAR(7) GENERATED ALWAYS AS ((SUBSTR(XPNUM,7,7))),
CONSTRAINT PROD4_PK PRIMARY KEY (XPNUM)
);
Designers use this:
CREATE TABLE PROD5 (
STAT CHAR(2) NOT NULL,
DEPT CHAR(4) NOT NULL,
PNUM CHAR(7) NOT NULL,
XPNUM CHAR(13) GENERATED ALWAYS AS (STAT||DEPT||PNUM),
CONSTRAINT PROD5_PK PRIMARY KEY (XPNUM)
);
Then you are correct - it would be a very useful shorthand feature to eliminate the view.
But my experience is that most people think the oppsoite of you're good design - and so we most often end up with the former :( |
|
|
By RichardTo on
Thursday, August 16, 2007 6:28 PM |
Bert,
I like this new feature, a "good" database designer will take care his database design first, but a "real" database designer have to consider how many errors may be generated from your "good disign" for a hundreds developers project, or how many effort the team can save from your "bad design". It is a very interesting topic !
^_^
Richard |
|
|
|
|
|
|
|
|
|
|