Skip to main content

Posts

Showing posts from September, 2008

Unique and non-unique indexes

An interesting questions is: what is in fact the difference between unique and non-unique indexes? For a long discussion, see Richard Foote's blog. Here, we look at the on-disk differences.

Let's start with environment setup and block dump creation:
connect system

create user itest identified by itest;

grant dba to itest;

create tablespace ITEST;

alter user itest default tablespace itest;

connect itest/itest

create table TDATA (pk varchar2(20));

begin
for i in 1..10000 loop
insert into TDATA values ('VAL'||i);
end loop;
commit;
end;

create index TIDX1 on TDATA(pk);

(I chose varchar2 type so that the actual characters are clearly seen in the dump. Also, I created a fresh new tablespace so the block numbers are small and probably consecutive.)
Now, find the extents involved:
select * from dba_extents where owner=user;And dump the blocks (take numbers from the query above - relative_fno, block_id):
alter system dump datafile 14 block min 33 block max 62;Save the trace, create a unique inde…