yes, for partitioned.

unique indexes should be created by UNIQUE or PRIMARY KEY constraints -- for if you have a unique index without one or the other, you've done something wrong.


you can use the "using index" clause of the constraint clause to get the global partitioning scheme you desire (else you get a single partition)

if the unique columns are NOT part of the partition key, you'll be using a GLOBAL index to enforce uniqueness.

nope, never said that. it is not that it will not consider it (it'll cost certain things differently) it is that query rewrite relies on constraints being in place -- not indexes. so it can affect the optimizer, but the optimizer doesn't "not" consider it.

I should never say never...

using function based indexes - using a unique index can definitely make sense (complex integrity constraints)......

in general - if something is unique, use a UNIQUE constraint or PRIMARY KEY constraint. that is used by the optimizer, it understands.

1) neither really. The presumption would be "the indexed column is in fact unique" so one row comes back - both indexes would be pretty close - the unique index might be marginally better since we know "unique scan". In general, if you have a unique column, you will use a unique constraint - and by default we'll create the unique index for you (assuming the column is not indexed). If you ask for a DEFERRABLE unique constraint, we'll create a non-unique index for you.

/*
drop table t;

create table t ( x int, y int );
insert into t select rownum, rownum from all_objects;
create unique index t_idx_x on t(x);
create index t_idx_y on t(y);
alter table t add constraint x_unique unique(x);
alter table t add constraint y_unique unique(y);

*/
column cnt new_val cnt
select count(*) cnt from t;
@trace
begin
for i in 1 .. &cnt
loop
for x in ( select * from t where x = i )
loop
null;
end loop;
for x in ( select * from t where y = i )
loop
null;
end loop;
end loop;
end;
/


SELECT * FROM T WHERE X = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50175 6.54 6.26 0 0 0 0
Fetch 50175 1.44 1.28 0 150525 0 50175
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100351 7.98 7.55 0 150525 0 50175
********************************************************************************
SELECT * FROM T WHERE Y = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50175 6.59 6.28 0 0 0 0
Fetch 50175 1.71 1.61 0 150635 0 50175
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100351 8.30 7.90 0 150635 0 50175


2) because it cannot (implementation of Oracle says "no"), it will not, it doesn't AND if the column were unique, well - would you care to comment on the sensibility of using a bitmap index on it??? :)

think about it - a bitmap index on a unique column, under what circumstances would that be a "good idea"?

b*tree's are not like that at all.

most b*trees - even on millions of rows - take 3 or 4 searches to find the answer.

it is all about the height of the index.

same with the bitmap!!! (bitmaps are just a data structure stored in a b*tree index...)