|
|
找到了是ask tom上的~
n 长慢慢看看吧，最后提到了这个方法
You Asked
Hi, Tom,
I have a table
create table project (project_ID number primary key,
teamid number,
job varchar2(100),
status number(1));
status=1 means it is an active project, otherwise it is archived,
I was told to enforce a unique rule: the job has to be unique in the same teamid
for the active projects, it means teamid and job have to be unique while
status=1, what is the best way to do this?
What happens if the job can be NULL?
Thanks
and we said...
Here is one method. We can use a function based index on a function:
ops$tkyte@ORA8I.WORLD> create or replace function my_unique_function( p_teamid
in number, p_job in varchar2, p_status in number ) return varchar2
2 DETERMINISTIC
3 as
4 begin
5 if ( p_status = 1 )
6 then
7 return p_teamid || '/' || p_job;
8 else
9 return NULL;
10 end if;
11 end;
12 /
Function created.
Now, we have a function that if status = 1, it will return the TEAMID || '/'
|| JOB (if job is null-you'll get TEAMID || '/' and thats what will be uniqued).
If status is NULL or not equal to one, we return NULL.
This is nice since an index entry that is entirely NULL is not in the index (we
don't put fully null entries in our b*trees), we'll only actually index the data
we need to check
Now, we can create our table and index:
ops$tkyte@ORA8I.WORLD> create table project
2 (project_ID number primary key,
3 teamid number,
4 job varchar2(100),
5 status number(1)
6 );
Table created.
ops$tkyte@ORA8I.WORLD> create unique index project_idx on project( substr(
my_unique_function(teamid,job,status), 1, 110 ) )
2 /
Index created.
See
ops$tkyte@ORA8I.WORLD> insert into project values( 1, 10, 'a', 1 );
1 row created.
ops$tkyte@ORA8I.WORLD> insert into project values( 2, 10, 'a', 1 );
insert into project values( 2, 10, 'a', 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.PROJECT_IDX) violated
that shows that we cannot insert the same TEAMID/JOB with a status of 1...
Lets see what happens when we update that status and try again:
ops$tkyte@ORA8I.WORLD> update project set status = 2 where project_id = 1 and
teamid = 10 and status = 1;
1 row updated.
ops$tkyte@ORA8I.WORLD> insert into project values( 2, 10, 'a', 1 );
1 row created.
as expected, it works... Now, lets put some more data in there:
ops$tkyte@ORA8I.WORLD> insert into project values( 3, 10, 'a', 2 );
1 row created.
ops$tkyte@ORA8I.WORLD> insert into project values( 4, 11, 'a', 1 );
1 row created.
ops$tkyte@ORA8I.WORLD> insert into project select rownum+50, 10, 'a', 2 from
all_users where rownum <= 100;
100 rows created.
ops$tkyte@ORA8I.WORLD> select count(*) from project;
COUNT(*)
----------
104
ops$tkyte@ORA8I.WORLD> select count(*) from project where status = 1;
COUNT(*)
----------
2
So, of 104 rows in the table, 2 have a status of 1. Lets see what impact that
has on our index we are using:
ops$tkyte@ORA8I.WORLD> analyze index project_idx validate structure;
Index analyzed.
ops$tkyte@ORA8I.WORLD> select lf_rows from index_stats;
LF_ROWS
----------
2
Our index has but 2 entries -- only the rows that NEED to be validated will be
in our index.
Now, if you do not have the enterprise edition, you do not have function based
indexes. In that case, the easiest thing for you to do would be to add another
column and a trigger to maintain it plus a unique constraint.
Here is an example of that:
we will use a table called project table
ops$tkyte@ORA8I.WORLD> create table project_TABLE
2 (project_ID number primary key,
3 teamid number,
4 job varchar2(100),
5 status number(1),
6 extra number
7 );
Table created.
That extra column will hold the primary key whenever status is NOT equal to 1,
that'll make it unique. It'll be NULL otherwise. We'll create a view that our
applications will use -- don't want them to see this extra column:
ops$tkyte@ORA8I.WORLD> create or replace view project
2 as
3 select project_id, teamid, job, status from project_table
4 /
View created.
ops$tkyte@ORA8I.WORLD> create unique index project_idx on project_TABLE( teamid,
job, extra )
2 /
Index created.
now, our unique index is on teamid, job, extra. When status = 1, extra is
null so teamid, job must be unique. Whan status is not 1, extra will be the
primary key and hence will be unique so that teamid/job don't have to be
ops$tkyte@ORA8I.WORLD> create or replace trigger project_trigger
2 before insert or update of status on project_TABLE
3 for each row
4 begin
5 if :new.status = 1
6 then
7 :new.extra := null;
8 else
9 :new.extra := :new.project_id;
10 end if;
11 end;
12 /
Trigger created.
that trigger does the work for us, making sure that extra is set properly. If
you run the above example -- the results would be the same..http://asktom.oracle.com/~tkyte/article1/index.html
for why I used substr there and to discover more about function based indexes in
generalYou AskedMore on...unique condition on multiple columns August 29, 2001 Reviewer: Padders from UK Tom - thanks for quick answer. I agree that PL/SQL function permits
more documentation. However this rather goes against your general
advice of language choice i.e. sql, plsql, java sp, c. In many cases
I suspect index naming convention would be sufficient to describe
its purpose.
I am now thinking that delimiter is a problem. If two jobs exists
with matching name except that one job has leading character which
happens to match delimiter then either version of index will not
permit combination. Perhaps it would be safer to use two column
version as in...
CREATE UNIQUE INDEX index_name ON table_name (
DECODE (status, 1, teamid),
DECODE (status, 1, job));
CREATE UNIQUE INDEX index_name ON table_name (
CASE WHEN status = 1 THEN teamid END,
CASE WHEN status = 1 THEN job END);
Followup: In hindsight I fully agree that the implementation of:
CREATE UNIQUE INDEX index_name ON table_name (
DECODE (status, 1, teamid, NULL ),
DECODE (status, 1, job, NULL ));
would be the best overall answer in terms of performance. |
|
楼主: xiaodong_1567
IP卡
狗仔卡
发表于 2008-4-14 18:10
显身卡
楼主
