A few basic notes on Oracle Database Administration.

Wednesday, September 23, 2009

A trivial index demo

Today I was sitting in a public library, minding my own business when a man who I had never seen before, leaned over to me and asked me, "Is it true that you can drop and index created explicitly, but not one created implicitly as part of a constraint? Initially the reaction was implicit/explicit? Please speak English. When I sorted that out there were 2 further reactions, also unspoken, "Duh, that's pretty obvious", and "Why me?"

"It is an Ancient DBA and he stoppeth one of three."

I demonstrated the matter a bit to him.

Logged on as scott, I create a play table:
SQL> create table tmp(f1 number(4), f2 number(4), f3 number(4));

Table created.

SQL> insert into tmp values (1, 2, 3);

1 row created.

SQL> create index tmp_f2 on tmp(f2);

Index created.

SQL> drop index tmp_f2;

Index dropped.


All straight forward. The index was created 'explicitly' and there is no constraint that it affects if it is dropped, so I can drop it without problem.


Now to add a constraint, and an 'implicit' index.

SQL> alter table tmp modify f2 unique;

Table altered.

SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TMP';

CONSTRAINT_NAME
------------------------------
SYS_C0011482

SQL> select index_name from user_indexes where table_name = 'TMP';

INDEX_NAME
------------------------------
SYS_C0011482

The constraint has been created as has an index


SQL> drop index SYS_C0011482;
drop index SYS_C0011482
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

and as expected, the index cannot be dropped since it is used by the constraint.


It is all very trivial.

What did get a little interesting was that I was able to enter multiple rows of nulls in a field with a 'unique' constraint. I need to review the properties of null.

And the real question that was never answered

"Now wherefore stopp'st thou me?"

8 comments:

Alistair Wall said...

"What did get a little interesting was that I was able to enter multiple rows of nulls in a field with a 'unique' constraint. I need to review the properties of null."

You can do that because none of the null values are equal to each other.

girlgeek said...

OK - it comes vaguely back to me.

Null is the absence of a value
Therefore
Null is not = to anything
Therefore
Null is not = Null
i.e.
The Null in row 1 is not = Null in row 2.

Is that the explanation???

Colin Nicholls said...

Another way of putting it, is that NULLs aren't indexed at all (unless you put them in as part of a compound index ).

Anonymous said...

let me share my experience with regard to the service of how do i reinstall my sql server?. it automatically eliminates data corruption issues in selected databases

Tani said...
This comment has been removed by the author.
Tani said...

Thanks! ur post helped me out a lot just what I need let me share my experience with regard to the Null value.
sap upgrade

Andrew Reid said...

I enjoyed this so I translated it into Spanish so that Spanish speakers could enjoy it too.

Andrew Reid said...

Sorry, but I have moved my Spanish post to a new blog.