unique vs. non unique index
Ishaque Hussain, February 04, 2003 - 10:10 pm UTC
Thanks Tom, this was very helpful. Could you explain then how does Oracle identify the rows for a unique index quickly. I thought that rowid is the fastest way of identifying a row in an Oracle table. Bear with me I am studying for my 4th (Perf tuning) Oracle 9i DBA test.
February 05, 2003 - 7:48 am UTC
It is a chicken and egg problem here.
Yes, if you give me a rowid, I can get the row very fast for the rowid is the address of the row on disk.
But -- how did you get that rowid? typically from an index!
The index is just an efficient data structure that can hold many millions of entries in a data structure that requires 3 maybe 4 block reads to find an entry on. We start at the root and navigate a very complex tree to find the rowid of the row in question.
Mike, February 05, 2003 - 9:40 am UTC
"
well, the optimizer can look at an index that is unique and say "ah-hah, if you
use "where x =:x and y = :y and ...." I'm going to get ONE row back, I can cost
that much better"
If the index is non-unique, the optimizer will say -- "ah-hah, index range scan,
I'm going to get 0..N rows back" and it'll cost it differently.
"
Do you mean for the same table, and "where x =:x and y = :y and ....", the unique index adopted will be faster than non-unique count-part?
February 05, 2003 - 11:55 am UTC
No, I mean it will be treated with a different cost, the plan KNOWS that you will get at most one row -- it can do things differently then if it will get 0..N rows where N is unbounded.
A merge join cartesian is much more appropriate for a single row source then for a N row source for example.
So, the extra knowledge gained -- that there is ONE row -- can influence the plans that fall out for a given query.
a very complex tree
A reader, February 05, 2003 - 12:08 pm UTC
what is the data structure for that
B or B+ tree ? or anything else ?
Unique constraint?
Doug, February 05, 2003 - 11:30 pm UTC
This might tie in with a workspace manager follow up I have floating out here too. I have a problem with non primary-key unique indexes that are being used to enforce uniqueness. This prevents me from using versioning on tables. From what you are saying I could use a defferable unique constraint and get around that? because it wouldn't require a unique index? I.e, can I really add a unique constraint without using a unique index? What are the ramifications?
Unique Constraint? Never mind found the answer -
Doug C, February 06, 2003 - 12:03 am UTC
I read the documentation (the what?) and did a little experiment - deferrable and non-deferrable
ORA-20109: a table with unique constraints cannot be version enabled
Boy that is really bumming me out.
& I get the impression hardly anyone is using Workspace Manager because there are no hits on this. Even had a tar about it and they didn't know either - hence the enhancement request.
index
mo, February 25, 2003 - 2:46 pm UTC
Tom:
If you have a query like this:
select t1.col1,
t2.col1,t2.col2,t2.col3,t2.col4,t2.col5,
t3.col1,t3.col2,
t4.col1,t4.col2,
t5,col1
from t1,t2,t3,t4,t5 where
t1.col1 = t2.col1 and
t2.col1 = t3.col1 and
t3.col1 = t4.col1 and
t4.col1 = t5.col1 and
t1.col2 > 0 and t2.col3 in ('AC','DE','DD') and
t3.col2 in ('T','D') and
t4.col3 in ('A','C','R') and t4.col5 = :var;
Would you be creating three indexes on t1.col2,
another on t2.col3 , t3.col2, t4.col3.col5?
Then you would compare a run without an index and one with an index to see which is faster?
February 25, 2003 - 8:32 pm UTC
well, you need to look at more then A query to determine what indexes you might or might not have.
My answer -- maybe I would have zero indexes. Maybe more.
If trying to tune JUST this query and don't care about anything else
probably col1 in all tables
pick the most selective predicate out of the non join conditions -- and index that column.
Say t4.col5 is unique -- that one.
index
mo, February 25, 2003 - 9:27 pm UTC
Tom:
1. If col1 in all/some tables is Primary key then it should be automatically indexed. Correct?
2. Can you explain what you mean by <do not care for anyhting else> like what?
3. <pick the most selective predicate out of the non join conditions > Do you mean the predicate on the table with highest count of records or the column that has unique values?
4. When I set autotrace traceonly, the query takes about 45 seconds. However when I run it without it retrieves 20,000 records and keeps outputing data for a while. If the query is supposed to insert into another table or return results to a powerbuilder client screen, then would the 45 seconds make any sense in timing it since it will probably take much longer to do insert or send results to a client.
Thank you,
February 25, 2003 - 10:21 pm UTC
1) "then it will be" would be more accurate. creating a primary key constraint will either kidnap an index or create on if one does not exist
2) "If trying to tune JUST this query and don't care about anything else"
meaning the rest of a system. You cannot tune a query in isolation with indexes -- you must look at the entire system and index appropriately
3) the predicate that returns the least number of rows
4) yes, of course the 45 seconds makes sense. That is the time it takes for the database to nominally return the 20,000 records to the client. If the client takes 5 hours to display it, so be it. If the client takes 15 seconds to display it, so be it. The 45 seconds is the amount of time it takes to get the data back to the client.
does it apply to an insert as select? no, not nearly as much. apples and toaster oven comparision there.
index range scan on non-unique indexes
John, February 26, 2003 - 1:18 am UTC
Tom,
We have a order entry system and overnight (everyday) we would like to process the orders (which are not processed).
I have order_create_date and my query looks like
update OrdersTable
set orderProcessedStatus='Inprocess'
where order_create_date <= trunc(sysdate)
and orderStatus='Not Processed';
we want to convert this query to look for one week orders (as we process orders everyday and any order missed becasue of business conditions usually picked up in a week)
and so we are planning to write like
update OrdersTable
set orderProcessedStatus='Inprocess'
where order_create_date between trunc(sysdate-7) and trunc(sysdate)
and orderStatus='Not Processed';
Out of the above two queries which will be faster (orders table volume is 6 million and per day processing will be about 200,000).
I looked at the execution plan and both are using range based index scans.
Expecting a convincing answer.
thanks
February 26, 2003 - 8:46 am UTC
convincingly I will say:
o if you use the same plan
o to get the same rows
o from the same place
o they will execute -- well -- obviously "the same"
NOW --
o if you use the same plan
o to get DIFFERENT ROWS (as your query does)
o from the same place
o then no one on this planet can answer the question accurately
but in your case, since I see the queries, the second one may ran faster ONLY SINCE it might return 10 rows where as that other could return those same 10 rows PLUS 10000000 more.
index range scan
John, February 26, 2003 - 11:30 am UTC
Tom,
Before I post the question, I was sure the between will execute fast, but after your responsed
***** then no one on this planet can answer the question accurately*****
AND
**but in your case, since I see the queries, the second one ####may#### ran faster ONLY ****
I'm confused.
Can you pl. explain me in which case the above will be false(my assumption with the kind of data I'm processing.)
pl.provide examples
thanks again
February 27, 2003 - 7:06 am UTC
Look at the queries:
update OrdersTable
set orderProcessedStatus='Inprocess'
where order_create_date <= trunc(sysdate)
and orderStatus='Not Processed';
update OrdersTable
set orderProcessedStatus='Inprocess'
where order_create_date between trunc(sysdate-7) and trunc(sysdate)
and orderStatus='Not Processed';
that second one is just
update OrdersTable
set orderProcessedStatus='Inprocess'
where order_create_date <= trunc(sysdate)
and orderStatus='Not Processed';
AND order_create_date >= trunc(sysdate-7)
that is - the first and second query are basically the same except the second query stops processing data after a while (hence making it potentially faster, does less work)
My point was about the plans -- if you have two queries with the same plan -- you cannot tell which will be faster.
the queries:
update OrdersTable
set orderProcessedStatus='Inprocess'
where order_create_date <= trunc(sysdate)
and orderStatus='Not Processed';
update OrdersTable
set orderProcessedStatus='Inprocess'
where order_create_date <= trunc(sysdate-3000)
and orderStatus='Not Processed';
will have the same plan -- the second will probably run faster (less data to process)
How to define a good index?
Marcio, February 26, 2003 - 2:36 pm UTC
Could you explain and demonstrate (if possible) how to define a good index -- kind of rule of thumb.
Thanks,
Marcio
February 27, 2003 - 7:12 am UTC
well, you should always use the syntax as defined in the SQL Reference manual -- that'll be the only way to create "good" indexes.
I don't really know how to answer this -- it is so broad. seems you are asking "how do i set about indexing my system".
you use indexes to speed things up.
you want as few as possible BUT
as many as you need
Ram, February 27, 2003 - 5:53 am UTC
<quote 1>
It is just that in a unique index, the rowid is not considered "part of the key" and in a non-unique index "the rowid is considered part of the key".
well, the optimizer can look at an index that is unique and say "ah-hah, if you use "where x =:x and y = :y and ...." I'm going to get ONE row back, I can cost
that much better"
<quote 1>
<quote 2>
Yes, if you give me a rowid, I can get the row very fast for the rowid is the address of the row on disk.
But -- how did you get that rowid? typically from an index!
<quote 2>
these two quotes gives the impression that for a 'unique index' the rowid 'should be' considered "part of the key"
rather than not.
what am i missing here ? was it a typo ?
February 27, 2003 - 7:01 pm UTC
In a b*tree index, there is no such thing as a non-unique index.
Therefore, when you create a "non-unique index", we really have to make it unique. Hence, we add the rowid to your key. if your index is on (a,b,c) -- it is really on (a,b,c,rowid)
If you create a unique index, then the rowid is just stored in the index structure but is not part of the key -- the index key is really (a,b,c) in that case, the rowid is just along for the rid.
Physically, the data is stored differently in the leaf nodes for a unique vs non-unique index on (a,b,c) but you don't really care about that in the real world.
The rowid is always there.
In a non-unique index it is actually part of the index key.
In a unique index, it is just hanging out with the key but is not part of it
Great explanation!
Ram, February 28, 2003 - 1:00 am UTC
Very good explanation between unique and non-unique index
Sami, November 05, 2003 - 2:02 pm UTC
Index Unique Scan
Vivek Sharma, April 12, 2004 - 1:41 pm UTC
Dear Tom,
I have a table with 2 rows in a single block. The table is analyzed and there is a unique index on this table. The Index is analyzed as well. There are 2 blocks in this table which I have checked from dba_tables and dba_segments. 1 Block has two rows and another block is used for segment header. Now when I query on a Unique column, rather than doing a full table scan, the query is doing a Unique Index Scan and then a table scan by rowid. The optimizer_mode is choose and since the table and index is analyed, it is CBO.
Don;t you think for such a small table, it should have opted for full table scan rather than Index scan. What is your opinion ?
Regards
Vivek Sharma
April 12, 2004 - 6:55 pm UTC
force it full and do it 10,000 times and see which is better. You'll be convinced I believe that the index -> table is better than the full scan even in this case.
Something like:
set echo on
set linesize 121
drop table t;
create table t ( x int constraint t_pk primary key, y int );
insert into t values ( 1, 1 );
insert into t values ( 2, 2 );
analyze table t compute statistics;
set autotrace traceonly explain
select * from t where x = 1;
select /*+ FULL(t) */ * from t where x = 1;
set autotrace off
create or replace procedure p1
as
begin
for x in ( select * from t where x = 1 )
loop
null;
end loop;
end;
/
create or replace procedure p2
as
begin
for x in ( select /*+ FULL(t) */ * from t where x = 1 )
loop
null;
end loop;
end;
/
@trace
exec p1
exec p2
@connect /
!tk
exec runStats_pkg.rs_start;
begin for i in 1 .. 10000 loop p1; end loop; end;
/
exec runStats_pkg.rs_middle;
begin for i in 1 .. 10000 loop p2; end loop; end;
/
exec runStats_pkg.rs_stop(1000)
(see asktom.oracle.com/~tkyte for runstats if you are not familar with it). the tkprof just was to verify index for p1, full scan for p2 (and it was). the results are:
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_stop(1000);
Run1 ran in 54 hsecs
Run2 ran in 74 hsecs
run 1 ran in 72.97% of the time
Name Run1 Run2 Diff
STAT...index fetch by key 10,000 0 -10,000
STAT...rows fetched via callba 10,000 0 -10,000
STAT...table scans (short tabl 0 10,000 10,000
STAT...table fetch by rowid 10,000 0 -10,000
STAT...session logical reads 20,540 40,535 19,995
STAT...calls to get snapshot s 10,002 30,001 19,999
STAT...consistent gets 20,002 40,001 19,999
STAT...no work - consistent re 0 20,000 20,000
STAT...table scan rows gotten 0 20,000 20,000
STAT...table scan blocks gotte 0 20,000 20,000
STAT...consistent gets - exami 20,001 0 -20,001
LATCH.cache buffers chains 22,626 82,623 59,997
STAT...session pga memory 0 65,536 65,536
STAT...session pga memory max 65,536 0 -65,536
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
73,663 133,812 60,149 55.05%
PL/SQL procedure successfully completed.
<b>index access in this case = better, faster, and less latching
Table needs to be small, but not teeny tiny, for the full scan to become "superior"</b>
(it's not about opinions!)
clarification
amit poddar, April 12, 2004 - 11:26 pm UTC
why,
If the table has two blocks then full scan would incur only 2 lios while index would incur depthe+1 io (atleast 2)
Hence it looks like they would pity much be the same.
Could you please explain the results of the test
thanks
amit
April 13, 2004 - 7:23 am UTC
it is just the cost of the full scan -- what you are seeing is the initial, unavoidable startup overhead of doing a full scan.
The results of the test show that if you have a single block table, full scanning it can be more expensive then index accessing it.
RE : Unique / Non-Unique index
A reader, April 01, 2005 - 9:24 am UTC
Tom,
After reading the discussions on enforcing a primary key constraint using a unique / non-unique index, I have the following understanding. Please confirm my understanding and also please clarify on certain things that I am not clear about.
Understanding
=============
1. When a primary key constraint is created without deferrable clause, then Oracle will automatically create a unique index on the columns that comprise the primary key if the index does not already exist.
2. When a primary key constraint is created and a unique index already exists on the column/columns that compise the leading part of the index, then Oracle will snatch and use that index to enforce the primary key constraint.
3. When a primary key constraint is created with a deferrable clause, then Oracle will automatically create a non-unique index on the columns that comprise the primary key if the index does not already exist
4. When a non-unique index already exists on the columns and a primary key constraint with the leading column of the primary key the same as the leading column of the non-unique index, then Oracle will snatch and use that index to enforce the primary key constraint.
5. The disadvantage of using unique indexes (whether already existing or created by primary key constraint) is that the index gets dropped whenever the primary key constraint is disabled or dropped.
6. The advantage of using non-unique indexes (to precreate non-unique index and create a primary key constraint so Oracle can snatch it or create a primary key constraint with deferrable clause) is that the index does not get dropped whenever the primary key constraint is disabled or dropped. This is useful in large DW applications where we would normally disable constraints before the load and re-enable the constraints after the load and rebuild any unusable indexes.
Please confirm my above understanding on this issue.
Questions
=========
1. When the primary key constraint is based more than 1 column (ie. composite primary key), should a composite unique/non-unique index exist with all of the columns in the primary key? ie. if the primary key is on 3 columns, say Col1, Col2 and Col3 then is it sufficient for example to have a unique/non-unique index whose leading column is (Col1,Col2) for Oracle to snatch that index or should we have a unique/non-unique index with all of the columns (Col1,Col2,Col3)
2. What are the performance implications in terms of response times between using a non-unique vs unique index to enforce primary key constraint.
3. Also on query rewrite capabilities. ie. can Oracle use query rewrite feature even if we enforce a primary key constraint using a non-unique index or should we use only unique-index to enforce primary key constraint for Oracle to use query rewrite capability.
4. I understand that you already mentioned that the unique/non-unique index will primarily be used by CBO to determine its execution plan. ie. using unique index will make the CBO aware that it will utmost get 1 row and so can use index-unique scan vs using non-unique index that will make CBO think that it will retreive 1..N rows and so must make index range scan (even if query is going to get only 1 row). Will there be any performance penalty because of the change in execution plan or is this information be used only for estimating the cost of the plan?
I would appreciate if you could confirm and clarify my doubts on this. Also, please apologize for shooting you such a lengthy question.
Thanks much in advance
April 01, 2005 - 10:03 am UTC
<b>Understanding</b> (hey, I like that heading)
<quote>
1. When a primary key constraint is created without deferrable clause, then
Oracle will automatically create a unique index on the columns that comprise the
primary key if the index does not already exist.
</quote>
I would change the last bit to "if the primary key columns are not on the leading edge of some existing index"
ops$tkyte@ORA9IR2> create table t ( x int, y int, z int );
Table created.
ops$tkyte@ORA9IR2> create index t_idx on t(x,y);
Index created.
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(x);
Table altered.
ops$tkyte@ORA9IR2> @dbls
ops$tkyte@ORA9IR2> @sset
ops$tkyte@ORA9IR2> store set &SETTINGS rep
Wrote file /tmp/xtmpx.sql
ops$tkyte@ORA9IR2> set linesize 100
ops$tkyte@ORA9IR2> column object_name format a30
ops$tkyte@ORA9IR2> column tablespace_name format a30
ops$tkyte@ORA9IR2> column object_type format a12
ops$tkyte@ORA9IR2> column status format a1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_type, object_name,
2 decode( status, 'INVALID', '*', '' ) status,
3 decode( object_type,
4 'TABLE', (select tablespace_name from user_tables where table_name = object_name),
5 'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
6 'LOB', (select tablespace_name from user_segments where segment_name = object_name),
7 null ) tablespace_name
8 from user_objects a
9 order by object_type, object_name
10 /
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX T_IDX USERS
TABLE T USERS
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> @rset
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> drop index t_idx;
drop index t_idx
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
2)
<quote>
2. When a primary key constraint is created and a unique index already exists on
the column/columns that compise the leading part of the index, then Oracle will
snatch and use that index to enforce the primary key constraint.
</quote>
See above, unique, non-unique, more columns than necessary.....
3) and further...
ops$tkyte@ORA9IR2> create table t ( x int, y int, z int );
Table created.
ops$tkyte@ORA9IR2> create UNIQUE index t_idx on t(x);
Index created.
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(x) DEFERRABLE;
alter table t add constraint t_pk primary key(x) DEFERRABLE
*
ERROR at line 1:
ORA-01408: such column list already indexed
it'll fail if a unique index exists.
4) as shown in the clarification for 1
5) unless you specify KEEP INDEX
ops$tkyte@ORA9IR2> create table t ( x int, y int, z int );
Table created.
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(x) DEFERRABLE;
Table altered.
ops$tkyte@ORA9IR2> alter table t disable constraint t_pk KEEP INDEX;
Table altered.
ops$tkyte@ORA9IR2> @dbls
ops$tkyte@ORA9IR2> @sset
ops$tkyte@ORA9IR2> store set &SETTINGS rep
Wrote file /tmp/xtmpx.sql
ops$tkyte@ORA9IR2> set linesize 100
ops$tkyte@ORA9IR2> column object_name format a30
ops$tkyte@ORA9IR2> column tablespace_name format a30
ops$tkyte@ORA9IR2> column object_type format a12
ops$tkyte@ORA9IR2> column status format a1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_type, object_name,
2 decode( status, 'INVALID', '*', '' ) status,
3 decode( object_type,
4 'TABLE', (select tablespace_name from user_tables where table_name = object_name),
5 'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
6 'LOB', (select tablespace_name from user_segments where segment_name = object_name),
7 null ) tablespace_name
8 from user_objects a
9 order by object_type, object_name
10 /
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX T_PK USERS
TABLE T USERS
6) see 5
Questions
1) needs to cover all columns in the primary key (at least)
2) Never measured if there were any. I know that a unique/non-unique index will be costed differently in some circumstances, but I would not *expect* any massive difference at runtime for primary key enforcement. Be a neat simulation to set up some day.
However, be careful of making all PK constraints deferrable:
ops$tkyte@ORA10G> create table t
2 as
3 select object_id, rpad('*',80,'*') data from all_objects;
Table created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter table t add constraint t_pk primary key(object_id) DEFERRABLE;
Table altered.
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set autotrace traceonly explain
ops$tkyte@ORA10G> select count(*) from t;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=141 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=141 Card=48352)
ops$tkyte@ORA10G> set autotrace off
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter table t drop constraint t_pk;
Table altered.
ops$tkyte@ORA10G> alter table t add constraint t_pk primary key(object_id);
Table altered.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set autotrace traceonly explain
ops$tkyte@ORA10G> select count(*) from t;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'T_PK' (INDEX) (Cost=25 Card=48312)
ops$tkyte@ORA10G> set autotrace off
<b>see the difference (and if this doesn't reproduce, it is "OK", it is a change in behavior in the current release -- the PK constraint should allow NULLS into OBJECT_ID when deferred but didn't in the past. But it is one that can be worked around if you also add not null constraints to at least one of the primary key columns again)</b>
3) Oracle is looking at the constraints, the indexes are side effects, the existence of a constraint (even without any index! rely) is useful.
4) the CBO knows a unique index on (a,b) with a predicate like "where a=:x and b=:y" will return 0 or 1 rows, the CBO with a non-unique index will rely on statistics to figure out how many in most cases. In general, as lost as the CARD= is right, it'll be right.
From Oracle docs..
Manjunath Kottur, April 01, 2005 - 12:54 pm UTC
Hi Tom,
I found this from the Oracle Datawarehousing documentation for 9i:
**********************************************
B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys. In many cases, it may not be necessary to index these columns in a data warehouse, because unique constraints can be maintained without an index, and because typical data warehouse queries may not work better with such indexes.
***********************************************
Is it just a bug about the line "because unique constraints can be maintained without an index" or I am understanding it(concepts) wrongly?
Thanks for the excellent material that can be found on this site.
Manjunath
April 01, 2005 - 1:09 pm UTC
that looks like a bug in the doc, can you give me a URL into OTN for it?
URL
Manjunath, April 01, 2005 - 1:31 pm UTC
April 01, 2005 - 1:52 pm UTC
hows about into the html? is that possible?
RE : KEEP INDEX
A reader, April 01, 2005 - 2:03 pm UTC
Tom,
In the recent discussion, you mentioned to use the KEEP INDEX clause when we disable a primary key constraint declared as deferrable. However, I tried putting the primary key constraint as DEFERRABLE. This created a non-unique index on the column(s). Also, later disabling the primary key constraint without KEEP INDEX did not drop the index. Perhaps I am not understanding correctly what you try to mean in the above discussion.
My understanding is that the KEEP INDEX is not required whenever the constraint is created as DEFERRABLE. It is only required when a unique index is used to enforce the constraint.
Pls. clarify
April 01, 2005 - 2:18 pm UTC
I was showing there are other ways to crack that nut, I would use KEEP INDEX if my goal was to KEEP the index, anything else could be risky.
The index is used to enforce the constraint in all cases! Whether we have to drop it or not is another thing.
URL
Manjunath, April 01, 2005 - 2:36 pm UTC
April 01, 2005 - 2:55 pm UTC
thanks, bug filed
Unique Indexes
Kiran, April 02, 2005 - 2:01 am UTC
Tom,
I am working on Oracle Applications I am new that tech. too,I worked on Oracle/d2k most of the time, currently we are using Oracle 8i DB, I observed that for all the stnd. table in all the modules I could not able to find out the Primary Key or Unique, on most of the tables I found only Unique Index, how does it allow us to enter only Unique values and is it good in performance and how far reliable.
Please
April 02, 2005 - 9:22 am UTC
you do not touch the Oracle applications tables, you use their API's and interfaces only.
Unique Indexes
Kiran, April 02, 2005 - 2:03 am UTC
Tom,
I am working on Oracle Applications I am new to that tech. too,I worked on Oracle/d2k most of the time, currently we are using Oracle 8i DB, I observed that for all the stnd. table in all the modules I could not able to find out the Primary Key or Unique, on most of the tables I found only Unique Index, how does it allow us to enter only Unique values and is it good in performance and how far reliable.
Regards
Kiran
what an excellent explanation.
raaghid, April 03, 2005 - 12:46 am UTC
Superb explanationf for the main query. Simply superb!!!
Want to switch unique to non-unique
A reader, April 03, 2005 - 1:37 am UTC
Is it possible to use ALTER INDEX to change the unique index to non-unique index? Or the only way to do this is to drop the unique index and then re-create the non-unique index?
Thanks
David
April 03, 2005 - 9:28 am UTC
the index structures are different. (meaning, short answer is you have to rebuild it)
A b*tree indexes (which are always balanced, regardless of what you might read on other sites), are always unique.
When you create a non-unique index on t(a,b), Oracle really takes your key AND the rowid as the key. So, the index is:
KEY(a,b,ROWID), DATA(null)
when you create a unique index on t(a,b), Oracle considers your key to be unique and the rowid is data (sort of like an Index Organized Table (IOT) before an IOT existed)
KEY(a,b), DATA(null)
In order to become unique, this index must be rewritten, restructured. Every key changes, every data changes
Unique index is smaller than non unique index?
A reader, April 03, 2005 - 9:52 am UTC
Hi Tom,
Does this mean that the size of non-unique index is always larger than unique index (on same columns)?
And what is DATA(null)? Does this mean the pointer to the data?
Thanks,
David
April 03, 2005 - 10:13 am UTC
in the b*tree there is a "data" section and a "key" section.
In a unique index the rowid is in the "data" section.
In a 'non'-unique index, the rowid is in the "key" section.
in both cases, the rowid is there, the same amount of data is there, it just is either part of the key or not.
excellnet
aman, April 05, 2005 - 11:00 am UTC
hi sir
this is really very informative just as like as always i fnd the information is from the MASTER.sir i have a doubt with the oracle courseware that is of 9iDBAFundamentals-1 chapter constraints page no 13-19
<quote>
Enable validate
1.no lock on the table
2.Primary and Unique keys must use the nonunique index.<--i didnt understand this part sir
Pagen0 13-21
Enable novalidate
1.locks the table
2.Can use unique or non-unique indexes <--Didnt get this statement
</quote>
sir what does we mean that when we have to use the ENABLE VALIDATE statemtn then we have MUST the NONUNIQUE indexes for PRIMARY and UNIQUE keys but for the ENABLE NOVALIDATE its not a must but anyone can work?
Please clarify sir
thanx and best regards
aman
April 05, 2005 - 12:20 pm UTC
out of context, not sure what they meant
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> create unique index t_idx on t(x);
Index created.
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(x) enable validate;
Table altered.
ops$tkyte@ORA9IR2> @dbls
Wrote file /tmp/xtmpx.sql
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX T_IDX USERS
TABLE T USERS
ops$tkyte@ORA9IR2> drop index t_idx;
drop index t_idx
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
i didnt get this sir
aman, April 06, 2005 - 7:21 am UTC
sir you are right that there is no link and both the Unique and Non-unique indexes can be used with the Primary and Unique constraints.But in the book they mentioned this stipulation sort of thing with the Enable Validate and Enable novalidate clauses that with the EnableValidate the PK,UK must use the non-unique indexes while in the EnableNovalidate anything will work.So do these caluses have something linked with the different type of indexes sir?I am really confused.I have tried to figure out what can be the correlation within but nothing is comng to my mind.please clarify sir
with regards
aman
April 06, 2005 - 9:12 am UTC
you'd have to contact the people who wrote the material?
I showed that a non-unique index may go with an "enabled validate" primary key, don't know what else to say.
differenct execution plans for unique and non-unique indexes
k., April 21, 2005 - 10:11 am UTC
hi tom,
please could you explain the following to me?
---------------------------------------------------------
SQL> create table test as select * from tcp_stat_run_head;
Tabulka vytvoøena.
SQL> select count(*) from test;
COUNT(*)
----------
44864
SQL> create table ids as select row_id from test where rownum<3000;
Tabulka vytvoøena.
SQL> set autotrace on;
SQL> set timing on;
SQL> desc test;
Název Nezadáno? Typ
----------------------------------------- -------- ----------------------------
ROW_ID NOT NULL NUMBER(12)
INAC_IND NOT NULL CHAR(2)
STRT_TM NOT NULL DATE
END_TM NOT NULL DATE
EXPRESSION_ID NOT NULL NUMBER
SQL> create index test_idx on test(row_id);
Index vytvoøen.
Uplynulo: 00:00:00.08
SQL> select min(strt_tm) from test where row_id in (select row_id from ids);
MIN(STRT
--------
24.02.05
Uplynulo: 00:00:00.02
Plán provedenÃ
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'IDS'
5 3 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE)
Statistiky
----------------------------------------------------------
0 recursive calls
0 db block gets
3027 consistent gets
25 physical reads
0 redo size
396 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> drop index test_idx;
Index zrušen.
Uplynulo: 00:00:00.00
SQL> create unique index test_unq on test(row_id);
Index vytvoøen.
Uplynulo: 00:00:00.07
SQL> select min(strt_tm) from test where row_id in (select row_id from ids);
MIN(STRT
--------
24.02.05
Uplynulo: 00:00:00.01
Plán provedenÃ
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'IDS'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
5 4 INDEX (UNIQUE SCAN) OF 'TEST_UNQ' (UNIQUE)
Statistiky
----------------------------------------------------------
0 recursive calls
0 db block gets
6007 consistent gets
6 physical reads
0 redo size
396 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-----------------------------------------------------------
why is the execution plan generated for non unique index scan better than the one for unique index scan?
it's 3027 consistent gets with non unique vs
6007 consistent gets with unique index.
thanks
April 22, 2005 - 8:42 am UTC
use the cbo please, the 13 rules of the rbo are documented in the performance guide but it is very primitive.
the plans are different because there are different indexes. It does look like the unique index is getting a block more often but you do not give any versions or anything like that so it is hard to say anything more.
ops$tkyte@ORA9IR2> create table t1 as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> create table t2 as select object_id from t1 where rownum <3000;
Table created.
ops$tkyte@ORA9IR2> create index test_idx on t1(object_id);
Index created.
ops$tkyte@ORA9IR2> set autotrace on
ops$tkyte@ORA9IR2> select min(created) from t1 a where object_id in ( select object_id from t2 );
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'T2'
5 3 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
3057 consistent gets
1 rows processed
ops$tkyte@ORA9IR2> drop index test_idx;
Index dropped.
ops$tkyte@ORA9IR2> create unique index test_idx on t1(object_id);
Index created.
ops$tkyte@ORA9IR2> select min(created) from t1 b where object_id in ( select object_id from t2 );
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'T2'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
5 4 INDEX (UNIQUE SCAN) OF 'TEST_IDX' (UNIQUE)
Statistics
----------------------------------------------------------
6007 consistent gets
1 rows processed
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> drop index test_idx;
Index dropped.
ops$tkyte@ORA9IR2> create unique index test_idx on t1(object_id);
Index created.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T1', cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T2', cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> set autotrace on
ops$tkyte@ORA9IR2> select min(created) from t1 a where object_id in ( select object_id from t2 );
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=66 Card=2999 Bytes=50983)
3 2 TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=2999 Bytes=11996)
4 2 TABLE ACCESS (FULL) OF 'T1' (Cost=62 Card=27803 Bytes=361439)
Statistics
----------------------------------------------------------
392 consistent gets
1 rows processed
ops$tkyte@ORA9IR2> drop index test_idx;
Index dropped.
ops$tkyte@ORA9IR2> create unique index test_idx on t1(object_id);
Index created.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_index_stats( user, 'TEST_IDX' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select min(created) from t1 b where object_id in ( select object_id from t2 );
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=66 Card=2999 Bytes=50983)
3 2 TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=2999 Bytes=11996)
4 2 TABLE ACCESS (FULL) OF 'T1' (Cost=62 Card=27803 Bytes=361439)
Statistics
----------------------------------------------------------
392 consistent gets
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> spool off
This probably shouldn't have been using an index at all.
How to define this ?
A reader, May 05, 2005 - 4:24 pm UTC
Hello Sir,
I need to have this constraint.
A dept can help multiple employees but an employee can only
belong to one dept.
Say I have table
dept_emp
(deptno number, dname varchar2(20) ,empno number ,ename varchar2(20) );
So I create a unique index on
(deptno,empno)
But this doesnot stop from emp getting into more than one dept.
How to do this ?
Thanx
May 05, 2005 - 6:05 pm UTC
you fix this with your data model
DEPT( deptno primary key, ....
EMP( empno primary key, deptno fkey to dept, .... )
done.
Any soln with the current model
A reader, May 05, 2005 - 9:04 pm UTC
Thank you sir.
Any soln with the current model of dept_emp ?.
Will I have to create some sort of before insert trigger.
Or some kind of check constraint,But Chk constraint cannot access other table rows ,right ?
May 06, 2005 - 7:16 am UTC
What you have is the EMP table with dname denormalized into it.
So basically, a unique index on EMPNO will ensure there is one record per emp, hence they are in one deptno
but it is just wrong.
How about this soln ?
A reader, May 05, 2005 - 9:25 pm UTC
Unique Index on
(deptno,empno)
And
Unique Index on
(empno).
Then in this case looks like the composite
Unique index on (deptno,empno) is redundant.
Please correct me if I am wrong
Q : Unique constraint or Unique Index
A reader, May 06, 2005 - 10:46 pm UTC
What is more appropriate to make a column unique in a Table
1) Create a Unique constraint .
Uniqueness may/may not be imposed by a non-unique index
Or
2) Create a Unique Index : A Unique Index will be created.
Whats the difference between an Index and Constraint anyway, looks like a Constraint relies on the underlying Index for its existence /function.
Thanx
May 07, 2005 - 8:01 am UTC
A unique constraint is the only proper way to create a rule that says this column(s) is(are) unique.
You want a constraint to say that. An index is just an index, a constraint is meta-data, value add. Tools pick up on a unique constraint, the database knows about a unique constraint, the optimizer recognizes a unique constraint.
A constraint may create an index, but an index never creates a constraint.
"the optimizer recognizes a unique constraint"
A reader, May 07, 2005 - 10:58 am UTC
Thanx for your answer.
Does this mean an Optimizer doesnot recognize a unique Index ?
May 07, 2005 - 11:04 am UTC
it recognizes them, but it further recognizes UNIQUE/PRIMARY KEY for things like query rewrites.
And you cannot have a foreign key to a unique index, you need a constraint
Index asc ,Index desc
A reader, May 07, 2005 - 4:52 pm UTC
Hello Sir.
1)What is use of creating a composite index with individual columns specified some specified as asc and some as desc.
When do we create an index with colname desc.
e.g.
create unique index uque_idx on dept_emp( deptno desc ,empno asc).
2)By default a Unique constraint will always create a Unique Index if needs arises to enforce that constraint( If one doesnot exist or it cant share existing Index)
This Index created will always be of asc type for each columns in the index if it is composite unique.
Correct me please.
3) So if there is a need to have composite index asc and desc then we will need to create an extra composite index apart from the one created by the Unique constraint.
Correct me please.
Thoughts ?
May 07, 2005 - 5:14 pm UTC
1)
select * from t where x = 5 order by x asc, y desc;
for example, create index t_idx on t(x,y desc); could be used to find the x=5 and order the data.
2) if the unique constraint is NOT DEFERRABLE that is true, if the unique constraint is deferrable, it will create a non-unique one if none exists.
3) yes.
Thanx
A reader, May 08, 2005 - 10:45 pm UTC
You have become the Indespensible Person for the Oracle World.
Full table update
Mita, May 09, 2005 - 11:50 am UTC
If I am updating all the records in temporary table, will creating an index make any difference.
table does not have any primary key. but if needed we can create a composite primary key
update statement is updating value using select from another query.
Thanks
Mita
May 09, 2005 - 12:23 pm UTC
if you update all records, creating an index will either
a) have no effect (we should not use it to find the rows, full scan)
b) make it run slower (because you indexed the column you are updating)
Full Table Update
Mita, May 10, 2005 - 1:42 pm UTC
in addition, what would be the impact of same on regular table (not temp table ) ??
May 10, 2005 - 2:28 pm UTC
same
Could you clarivfy?
Bakunian, May 11, 2005 - 11:56 am UTC
Tom,
Oracle 9i Database Concepts states following
"Oracle recommends that unique indexes be created explicitly, and not through enabling a unique constraint on a table."
So if purpose of "Unique indexes is to guarantee that no two rows of a table have duplicate values in the key column (or columns)." then when it would be appropriate to have unique index instead of unique constraint which will provide with the same functionality.
And last line from the same topic kind of contradicts above statement.
"Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. However, it is advisable that any index that exists for
query performance, including unique indexes, be created explicitly."
So if unique index created during creation of unique constraint then why we need to worry about explicitly creating unique index.
Thanks in advance for your time.
May 11, 2005 - 12:27 pm UTC
non-unique index
Jim, May 11, 2005 - 1:56 pm UTC
From, </code>
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/data_acc.htm#3527 <code>
<quote>Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column already is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint <quote>
prefix meaning leading edge of the index? Thanks.
May 11, 2005 - 7:23 pm UTC
prefix means leading edge:
create index t_idx on t(x,y,z)
can be used for enforce:
unique(x)
unique(x,y)
unique(x,y,z)
Size Of a table
A reader, June 16, 2005 - 3:04 pm UTC
Tom,
According the link provided by you,
</code>
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c08schem.htm#5766 <code>
One of listed advantages of the B-tree index is that B-tree performance is good for both small and large tables, and does not degrade as the size of a table grows.
Is this true for both UNIQUE and NON-UNIQUE indexes?? My thinking is that as the number of rows, grows in a table, so does the number of the leaf-entries in the indexes. Search using a UNIQUE index should not be affected because the
there is only one key entry per row, but whereas for the NON-UNIQUE indexes it should get affected because there are multiple key entries; this is especially true of queries which use unselective indexes now have to select more index leaf entries, which inturn, transalates to more I/O.. so the performance SHOULD get affected. Please correct me if I am wrong.
Thanks,
Neeraj
June 16, 2005 - 3:24 pm UTC
but that has nothing to do with the size of the table and everything to do with the selectivity of the indexed column.
retrieve 100,000 rows from a
a) 100,000 row table
b) 1,000,000 row table
c) 100,000,000 row table
d) 100,000,000,000 row table
via a range scan -- which will "perform better"
answer: assuming similar clustering factors and such -- they will all perform the same.
your issue is "as the size of the table grows, the number of rows we retrieve grow", but that isn't really relevant to the "if you get N rows out of a table with M rows in it, getting N rows out of a table with M*10000 rows in it will take about the same amount of work" statement.
Thanks
A reader, June 16, 2005 - 5:59 pm UTC
OK, you are right in pointing out that they are two different things; one thing is the PERFORMANCE of a B-TREE index structure and the other is SELECTIVITY of an index on a table. It is good to know that size of table has no bearing on the performance of the index whether it is through a unique scan or a range scan.
Then what I would also like to know is, that assuming the table grows astronomically, index blocks split heavily, and the data grows in such a manner that clustering factor of the index changes; then I expect that it would take a performance hit???
Thanks for Your Help,
Neeraj
June 16, 2005 - 10:08 pm UTC
that can happen to a 10000 row table as easily as a big table.
depends entirely on how the data is USED, not how big the data is.
Thanks
Neeraj Nagpal, June 16, 2005 - 9:11 pm UTC
OK, you are right in pointing out that they are two different things; one thing is the PERFORMANCE of a B-TREE index structure and the other is SELECTIVITY of an index on a table. It is good to know that size of table has no bearing on the performance of the index whether it is through a unique scan or a range scan.
Then what I would also like to know is, that assuming the table grows astronomically, index blocks split heavily, and the data grows in such a manner that clustering factor of the index changes; at that point, I expect that it would take a performance hit??? or may be it just time to think of different indexing strategy and create a more selective index ???
Thanks for Your Help,
Neeraj
Thanks
Neeraj Nagpal, June 17, 2005 - 12:41 am UTC
Thanks So Much!
Neeraj
Unique vs Primary
NR, January 02, 2007 - 2:10 pm UTC
Following is excerpts from "Oracle database concepts 10g"
=========================================================
"Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE INDEX. Creating unique indexes through a primary key or unique constraint is not
guaranteed to create a new index, and the index they create is not guaranteed to be a unique index".
Does that mean in certain cases using primary key is not a good option? E.g. in partitioned table (Data warehouse).
or how true is this statement? and what will be the impact on the performance?
"Local unique index is prefreable over global primary key (index) in a partitioned table"
January 05, 2007 - 7:27 am UTC
It is poorly worded. It should say:
If you want a unique index for some reason, please do not rely on a unique constraint or primary key constraint to create one. Unique and primary key constraints do not necessarily create a new index, nor is the index they create necessarily going to be "unique". So, if your ultimate goal is to have a unique index - there is only one sure way to have that happen - to create it yourself.
Unique vs Primary
NR, January 02, 2007 - 2:29 pm UTC
Please ignore previous query, I found the explanation.
Advantage of using unique index for primary key constraint instead of nonunique index
Ross, February 02, 2007 - 5:34 pm UTC
In Oracle, a primary key constraint can be created using either a unique or a nonunique index.
The primary key constraint enforces uniqueness even if the index does not (i.e. even if the index is nonunique).
Are there any benefits in a primary key constraint using a unique index instead of using a nonunique index?
PS: Sorry if this should be a new question, but I couldn't post a new question, so I posted here.
February 03, 2007 - 7:28 pm UTC
If you do not need a non-unique (eg: you do not need deferrable constraints primarily), a unique index might have a small advantage (in that we know for sure a range scan is NOT necessary at runtime)
Benefit of unique indexes
Jonathan Lewis, February 04, 2007 - 2:01 pm UTC
Another point to consider is that the optimizer has an "index sanity check" where it will use the number of distinct keys in a unique index to improve an estimate of join cardinality when you have a multi-column join - even when it doesn't use the index in the execution plan. So if you change a unique index to non-unique, you may see a "random" selection of execution plans change, even when that index doesn't appear in the plans before or after the change.
RE: Benefit of unique indexes
Ross, February 05, 2007 - 1:15 am UTC
Jonathan,
Thanks for the info about index sanity checks.
I don't know how the optimizer is implemented, but shouldn't the optimizer be able to use a nonunique index that is being used by an enabled primary key constraint just like a unique index for index sanity checks, since the primary key constraint will also enforce uniqueness?
Should the optimizer be updated to take this into account?
Or am I missing something?
Thanks,
Ross
February 05, 2007 - 7:45 am UTC
but a unique constraint enforced with a non-unique index permits duplicates for some period of time
eg: the data isn't necessarily unique all of the time, only upon commit will it be.
Sanity Check
Jonathan Lewis, February 05, 2007 - 6:10 am UTC
Ross, I can't think of any good reason why the sanity check is restricted to unique indexes. But that's the way it is - and even unique constraints enforced by non-unique indexes really do fall outside the test.
Just guessing
Harrison Picot, February 05, 2007 - 8:44 am UTC
Hi Johnathan,
I only understand about 25% of this stuff, so I am just guessing, but if the unique index is used to guage cardinality then the measure would seem to be equal to the number of rows in a unique index, but unknown if index were not. Of course, I am still mystified by the second response, that indexes provide rowids when I thought they were pointers to blocks, but I still use the word "inodes" (Sys3) instead of "files" (Sys4) so I am struggling to catch up. Assuming that I might be wrong in thinking that I will never need a non-unique index maintaining a unique column (so I won't care what the optimizer does in that case) why would one want that combination beyond (say)cleaning bad data, or some short term condition? I think the optimizer designers might be thinking like I do; if you are doing that, maybe you should stop it? Besides if this is not going to be in "Practical 10g", I am not sure I want it in my head, I'm saving room.
ooops
Harrison Picot, February 05, 2007 - 9:49 am UTC
Checking with "Effective Oracle By Design" I found out that rowid is more information than the name implied to me, including the "file, block, and row on that block", and is not a "rownum" (query related) like object. So the second response makes sense now. This being able to read is starting to pay off.
Duplicates in nonunique index before commit
Ross, February 05, 2007 - 11:52 am UTC
It seems to me that Oracle should keep track on a session & deferrable constraint basis if any rows that violate the constraint have been inserted but not committed in the session.
The optimizer would then only use the nonunique index for the "index sanity check" if there are no such rows for the session, thus obviating the advantage of backing a primary key constraint with a unique index (with the only disadvantage that I know of for the nonunique index of having to store a single bit per session & constraint pair, plus the need to check the bit every once in a while).
Regarding the range scan at runtime required by a nonunique index backing a primary key constraint: when would that range scan need to be run?
Thanks,
Ross
February 05, 2007 - 12:22 pm UTC
problem is, there could be billions of them, in general.
The index may have duplicates - that has to be taken into consideration. Even if the optimizer "knew" for your session - it would not "know" for another session (and plans are developed once)....
A little clarificiation
Mark Brady, April 04, 2007 - 5:12 pm UTC
"but a unique constraint enforced with a non-unique index permits duplicates for some period of time"
I can't seem to make this happen. Create a non-unique index, create a unique constraint, insert 1 row, insert the same data a second time, ORA-00001 right away.
did you mean to say:
but a deferrable unique constraint enforced with a non-unique index permits duplicates while deferred?
If so, shouldn't the CBO be able to read the constraint and it's deferrabilty as to whether or not you can do a index unique scan? I know I've missed something and I'm awaiting a Tom and John tag team double slam.
April 04, 2007 - 6:09 pm UTC
you can add the "deferrable" adjective to the sentence, but the first one is correct - yes, the constraint would have to be in a deferred state.
if you have a unique constraint that is deferrable, the index will be non-unique, and that is what permits this to happen.
The CBO cannot just read the state - since the CBO creates a plan like maybe yesterday and uses it over and over again.
You're missing a case
Mark Brady, April 05, 2007 - 3:08 pm UTC
You're talking about the times when a non-unique index is created because you've created a deferrable constraint. I'm talking about a NOT deferrable unique constraint with a non-unique index by creating the index in advance. I'm not suggesting that oracle would know if a deferrable constraint had been deferred or not - a lot to keep track of. But, shouldn't it be able to make
ID = :val
queries a index unique scan against a non-unique index based on the NOT DEFERRABLE unique constraint?
April 05, 2007 - 3:38 pm UTC
in theory it could, but it doesn't and it probably need not to - since the card=1 should be right in any case.
But will the CBO do the cool thing it can when unique
Mark Brady, April 05, 2007 - 6:44 pm UTC
Above you said,
"A merge join cartesian is much more appropriate for a single row source then for a N row source for example."
IOW, If the CBO knows you have one and only one row for each value it can make better choices as to how to get that data. In the trivial case I tried I got an Index Range Scan for ID = :val for a non-unique index with a unique constraint when I would expect and Index Unique Scan.
Maybe the difference between an IRS and IUS is not a big deal in this case, but could there be other cases, as you mentioned, where the CBO would give me a better plan with a unique index than it would with a non-unique index and a not deferrable unique constraint even if the card=1. Wouldn't it be more likely to give me the best plan if it truly treated not deferrable unique contraints like unique indexes?
Anyway, I'm just trying to wrap my head around it, so I know what the trade offs are for one technique over another.
April 05, 2007 - 7:45 pm UTC
but it doesn't work that way, and I cannot make it change.
the non-unique index cannot be in general considered to be unique.
Alexander, May 27, 2008 - 12:07 pm UTC
Hi Tom,
I'm in the process of tuning a query involving a 27 million row table being joined on columns with no constraints, only indexes.
My question to is, could you please explain the difference to the optimizer between joining when only indexes are present, vs when an actual relationship exists.
I'm uncertain how significant the difference is on what Oracle can do with that information.
May 27, 2008 - 3:14 pm UTC
Mostly has to do with rewrite opportunities:
http://asktom.oracle.com/Misc/stuck-in-rut.html but can be helpful in other areas as well, for example in 10g and up:
ops$tkyte%ORA11GR1> create table emp
2 as
3 select *
4 from scott.emp;
Table created.
ops$tkyte%ORA11GR1> create table dept
2 as
3 select *
4 from scott.dept;
Table created.
ops$tkyte%ORA11GR1> begin
2 dbms_stats.set_table_stats
3 ( user, 'EMP', numrows=>1000000, numblks=>100000 );
4 dbms_stats.set_table_stats
5 ( user, 'DEPT', numrows=>100000, numblks=>10000 );
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> set autotrace traceonly explain
ops$tkyte%ORA11GR1> select ename
2 from emp, dept
3 where emp.deptno = dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 31M| | 31515 (1)| 00:06:19 |
|* 1 | HASH JOIN | | 1000K| 31M| 2448K| 31515 (1)| 00:06:19 |
| 2 | TABLE ACCESS FULL| DEPT | 100K| 1269K| | 2716 (1)| 00:00:33 |
| 3 | TABLE ACCESS FULL| EMP | 1000K| 19M| | 27151 (1)| 00:05:26 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
ops$tkyte%ORA11GR1> set autotrace off
ops$tkyte%ORA11GR1> alter table dept add constraint dept_pk primary key(deptno);
Table altered.
ops$tkyte%ORA11GR1> alter table emp add constraint emp_fk_dept foreign key(deptno)
2 references dept(deptno);
Table altered.
ops$tkyte%ORA11GR1> set autotrace traceonly explain
ops$tkyte%ORA11GR1> select ename
2 from emp, dept
3 where emp.deptno = dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 976K| 27152 (1)| 00:05:26 |
|* 1 | TABLE ACCESS FULL| EMP | 50000 | 976K| 27152 (1)| 00:05:26 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMP"."DEPTNO" IS NOT NULL)
ops$tkyte%ORA11GR1> set autotrace off
see how the optimizer was smart enough to drop a table from the plan altogether - since it wasn't really needed - but only when it KNEW that it could.
you want null/not null, unique, primary key, foreign key constraints in place (read that first link, you can have them there without validating them as well)
Confused
Christer, May 28, 2008 - 9:13 am UTC
Hi Tom,
In the first post you answered:
--
you know what the structural difference between a unique and non-unique index in Oracle
is?
Nothing. bit for bit, byte for byte, they would be the same.
It is just that in a unique index, the rowid is not considered "part of the key" and in a
non-unique index "the rowid is considered part of the key".
--
but somwhere in the middle you said the opposite (at least it sounds like that to me).
--
the index structures are different. (meaning, short answer is you have to rebuild it)
A b*tree indexes (which are always balanced, regardless of what you might read on other sites), are
always unique.
When you create a non-unique index on t(a,b), Oracle really takes your key AND the rowid as the
key. So, the index is:
KEY(a,b,ROWID), DATA(null)
when you create a unique index on t(a,b), Oracle considers your key to be unique and the rowid is
data (sort of like an Index Organized Table (IOT) before an IOT existed)
KEY(a,b), DATA(null)
In order to become unique, this index must be rewritten, restructured. Every key changes, every
data changes
--
I guese you ment "KEY(a,b), DATA(ROWID)" instead of
"KEY(a,b), DATA(null)
"
This makes me somewhat confused.
If they where "bit for bit, byte for byte, they would be the same."
then the only difference would be you point of view (knowledge about unique or not unique),
and in that case it ought to be easy to change an index from unique to non unique by a change to Oracle data dictionary.
But then you state "the index structures are different. (meaning, short answer is you have to rebuild it)"
Please explain it to me.
Regards Christer
May 28, 2008 - 10:06 am UTC
in order for a non-unique index to become unique - or vice versa - the index must be rebuilt because Oracle needs to consider different bits as being "unique", that is all. They would be the same - but we would have considered different bits of them to be unique. We consider different ranges of bytes as the "key".
Still a little confused
Christer, May 29, 2008 - 1:17 am UTC
So the first statement was incorrect?
--
you know what the structural difference between a unique and non-unique index in Oracle
is?
Nothing. bit for bit, byte for byte, they would be the same.
It is just that in a unique index, the rowid is not considered "part of the key" and in a
non-unique index "the rowid is considered part of the key".
--
Regards Christer
May 29, 2008 - 8:00 am UTC
they are the same - they contain the same data - but in one case (the unique index case) the rowid is not included in the set of fields that must be unique. In the non-unique index - it is.
Difference
Christer, May 30, 2008 - 2:05 am UTC
I do understand that they contain the same data,
but they can't be "bit for bit, byte for byte" the same,
as stated in the top.
Regards Christer
May 30, 2008 - 7:04 am UTC
ok, i give up.
they are the same, but different.
they are the same size
they contain the same information
they are navigated the same
they provide the same information
Got it
Christer, May 30, 2008 - 7:49 am UTC
Ok!
Now it's perfectly clear to me.
Regards Christer
Diff between unique and non-unique index
Richard Foote, June 01, 2008 - 9:08 am UTC
Hi Christer
There's a subtle physical difference between a unique and a non-unique index. As Tom has stated, in a non-unique index, Oracle includes the rowid has being an indexed column entry whereas in a unique index, the rowid is referenced separately. Therefore, a non-unique index is actually effectively unique as well.
However, by including the rowid as an indexed column entry, Oracle needs to allocate an additional byte to record the length of the rowid. Therefore, a non-unique index entry is one byte longer than an equivalent unique index entry.
Like I said, it's subtle but if the index column entries are small, an extra byte for each and every indexed row can add up.
I discussed this physical difference on my blog late last year:
http://richardfoote.wordpress.com/2007/12/18/differences-between-unique-and-non-unique-indexes-part-i/ Cheers
Richard Foote
http://richardfoote.wordpress.com/
Rowid is not considered to be part of unique index
Vijay, December 10, 2008 - 5:37 am UTC
Dear Tom,
In one of your comments above, at the very top, you said that rowid is not considered to be part of a unique index structure. I read the same perspective in one of the chapters of your famous work : Effective Oracle by Design
If it is the case, then entries in leaf blocks of such a unique index would be of the form : <key1> <key2> <key3> <key4>....as against a non-unique index wherein it
would be : <rowid1,key1> <rowid1,key1> <rowid1,key2> <rowid1,key3>.
So, in such a scenario, if CBO opts for a index unique scan, using unique index then how will it going to get the associated rowid and therefore the actual row from the table block ? It's not at all present in the leaf blocks unlike in case of a non-unique index.
Please put your thoughts on the above.
Thanks in advance.
Best regards,
Vijay
December 10, 2008 - 9:54 am UTC
no, it would be
<key1><key2><key3><rowid>
rowid would become a 4th attribute of the unique key.
Typo...
Vijay, December 10, 2008 - 5:41 am UTC
Tom,
There's slight typo in my comment above. It's been corrected here. The index structure, for a non-unique index, would be :
<rowid1,key1> <rowid2,key1> <rowid3,key2> <rowid4,key3>....
Apologies for the mistake.
Looking forward to your feedback....
Regards,
Vijay
December 10, 2008 - 9:56 am UTC
ok, now I see.
but still - no - it would be:
KEY DATA FUNCTIONALLY DEPENDENT DATA
--------------- ---------------------------
<key1><rowid>
<key2><rowid>
<key3><rowid>
for a nonunique index on a single column and:
KEY DATA FUNCTIONALLY DEPENDENT DATA
--------------- ---------------------------
<key1> <rowid>
<key2> <rowid>
<key3> <rowid>
the rowid would be part of the "data", not the key - and if the rowid is part of the key - it would be tagged onto the END of the key, not the front as that would obviously destroy the sort order otherwise.
rowid is still a component
Vijay, December 11, 2008 - 7:50 am UTC
Dear Tom,
Thanks for the reply.
Though, many of the concepts/doubts are now clear. But, still the main one remains.
You said that, in case of Unique Index, rowid is part of "data" component and not of "key" component.
But nevertheless, it's still be there and exists in index leaf block entries for each key value.
Am I right ?
Best Regards,
Vijay
December 11, 2008 - 8:15 am UTC
yes, it has to.
the btree index is used to find rows in the table.
the rowid is the 'address' of the row
without it, the btree index would be "not useful for finding the rows in the table"
the rowid is there in a normal btree index on a normal table (not an index organized table...)
Unique vs non-Unique
DK, February 02, 2009 - 5:07 am UTC
Hi Tom,
Can a Unique Index be applied to only the columns which have no duplicates,what will happen if we create a unique index on the columns which have duplicates?
Thanks,
DK
February 02, 2009 - 11:11 am UTC
it will fail of course?
why wouldn't you just test it out and see?
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> insert into t select 1 from all_users;
45 rows created.
ops$tkyte%ORA10GR2> create unique index t_idx on t(x);
create unique index t_idx on t(x)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
DK, February 06, 2009 - 6:34 am UTC
Hi Tom,
Sorry for asking the Silly question.
If the column is already having the Unique values,how will it impact if I create unique or Non-Unique index,as all the values will anyhow get accounted in the Index.?
Thanks,
DK
February 06, 2009 - 3:38 pm UTC
I don't know what you are asking.
how will it impact -- what? what are you thinking this will 'impact'
index
A reader, February 08, 2009 - 9:21 am UTC
Tom:
1. Is there functionally any difference between a unique index and primary ley other than a unique index can have null values and PK can't.
2. If i have a query that takes 45 seconds to run in sql*plus or when data is displayed to the a PB client, does that mean it takes the server 45 seconds to run it or it is taking the client 45 seconds to display it.
how do you know if the query has problems or table design is missing indexes or etc? Do you run it using sql*plus on server to see if it takes 45 seconds to eliminate the client possibility.
3. Is it normal somtimes for a query to take 45 seconds and nothing can be done about it.
February 09, 2009 - 6:44 pm UTC
1) well, there is the fact that a primary key constraint is metadata, is more information, is used by the optimizer to optimize, conveys information to future developers, means something.
If you have a primary key (and you do, or you have done it wrong) use the constraint - period.
2) could be either or both or neither. You give insufficient information.
If the result set is 1 record, probably, the query takes 45 seconds in the server.
if the result set is 10,000,000 records, probably, much of the time is the client.
That a query takes 45 seconds doesn't mean you are missing anything. You have to understand the question being asked, the nature of the data in the database, and envision the work that would need to be done.
And if you cannot do that, you will have to rely on software (enterprise manager packs) to analyze your workload and recommend indexes, materialized views, query rewrites an in 11g partitioning schemes.
3) ummm, of course. 45 minutes even. 45 hours maybe, 0.45 seconds - everything between 0 and a long long time is "normal"
DK, February 09, 2009 - 3:22 am UTC
Tom,
<Reviewer: DK from India>
From the question above,what I want to ask is...
I want to know what is the benefit we get if we create the unique index (as unique index can only be created on columns with unique values),even the non-unique index created on this column(column with unique values) will have same number of records and hence the performance of the 2 indexes on such column will be same.
February 09, 2009 - 7:13 pm UTC
one of them will implicitly enforce uniqueness, the other will not. That is an advantage if your data is to be unique. That is a disadvantage otherwise.
one of them will be known by the optimizer to return a single value when used with "=" and the other will not.
In short, you should be using unique constraints, primary key constraints and have your underlying indexes implicitly created.
If the field is to be UNIQUE - use a UNIQUE constraint
If the field is to be a primary key - use a primary key constraint.
use "create index" for 'tuning' after that. (if you find yourself using create UNIQUE index - that just means you forgot an UNIQUE constraint...)
Unique index vs. Unique constraint
Kim Berg Hansen, February 27, 2009 - 4:26 am UTC
Hi, Tom
You have almost answered my questions in this thread, but I'm still slightly in doubt, so if you have time to verify my thoughts?
I replicate tables using readonly materialized views (snapshots in the old days.) On the snapshot site (11g EE) I have created unique indexes - not to enforce uniqueness (they are unique from the master site), but to let the optimizer know.
A few times I have had problems with the refresh due to these unique indexes (metalink note 67424.1)
When letting the optimizer know about the uniqueness I can choose between:
1) create unique index
2) create unique constraint
3) create unique constraint deferrable
Option 1) is what I do now (not knowing any better :-)
Option 2) would be a nicer/better way of letting the optimizer know about the uniqueness - even though it understands that a unique index is practically the same as unique constraint?
Option 3) would be a way to work around my problem with the refresh of the materialized view.
Question is in what way the optimizer will change behaviour, if I replace my unique indexes on the materialized views with deferrable unique constraints?
Thanks in advance
Kim Berg Hansen
March 03, 2009 - 9:42 am UTC
... I have created unique indexes - not to enforce uniqueness (they are unique from the master site), but to let the optimizer know. ...
that is so backwards. To let the optimizer "know", why wouldn't you simply say "this is unique, thanks".
You have to use deferrable constraints for materialized views to refresh properly, they (the constraints) can and will be violated during the refresh process. The first step of a refresh is "set constraints all deferred"
So, a unique defferable, initially immediate constraint would be called for.
And the fact is - the data is not unique at times - during the refresh, the refresh process will be seeing data that can be duplicated. The optimizer will see the constraint is unique, but will also understand that "there can be more than one sometimes"
Thanks :-)
Kim Berg Hansen, March 03, 2009 - 10:22 am UTC
Thanks Tom
I'll use deferrable unique constraints - certainly.
You said:
...that is so backwards. To let the optimizer "know", why wouldn't you simply say "this is unique, thanks". ...
The answer is simple - I didn't know any better :-)
Now I know better, but I was just a little bit concerned about changing behaviour of the optimizer, since deferrable unique constraints are supported by a non-unique index, so some of my application might possibly change execution plan for the worse.
But I'll test it for sure.
Thanks for your time :-)
March 03, 2009 - 8:55 pm UTC
... so some of my application might possibly change execution plan for the worse.
...
well, when you make any change, one of three things will happen:
1) it'll be really good
2) it won't change a thing
3) it'll be really bad
There are possible plan changes - yes. But - materialized view refreshes need deferrable constraints....
Sharing an index between two constraints on an IOT
Henrik, September 07, 2009 - 5:09 am UTC
This is the background for me asking:
I am designing two new tables, which have a parent/child relationship.
I would like the children of an owner to be stored close to each other, as they will mostly be retrieved by owner id.
Parent has these columns (besides data):
id surrogate key, unique
type number
Child has these columns (besides data):
id surrogate key, unique
type number
owner foreign key to parent.
Finally, it should not be possible to change parent type, if there are any children.
I was thinking of enforcing this using a foreign key on (owner,type), so I need a unique key on parent for that.
(Only alternative I can think of is to prevent any update of type).
So now for the question:
If parent is an IOT, is it possible to have primary key(id) and unique key (id,type) using just one index?
create table TestIOT (id number(9), type number(2),
constraint TestIOTPK primary key(id),
constraint TestIOTUK unique(id, type)
) organization index including type overflow;
select segment_name from user_segments where segment_name like 'TESTIOT%' order by segment_name;
SEGMENT_NAME
------------------
TESTIOTPK
TESTIOTUK
2 rows selected.
For heap tables, it looks to me as if the index can't be unique (currious about why that might be):
create table TestHeap1 (id number(9), type number(2));
create table TestHeap2 (id number(9), type number(2));
create index TestHeap1IDX on TestHeap1(id, type);
create unique index TestHeap2IDX on TestHeap2(id, type);
alter table TestHeap1 add constraint TestHeap1PK primary key(id);
alter table TestHeap1 add constraint TestHeap1UK unique(id, type);
alter table TestHeap2 add constraint TestHeap2PK primary key(id);
alter table TestHeap2 add constraint TestHeap2UK unique(id, type);
select segment_name from user_segments where segment_name like 'TESTHEAP%' order by segment_name;
SEGMENT_NAME
-------------------
TESTHEAP1
TESTHEAP1IDX
TESTHEAP2
TESTHEAP2IDX
TESTHEAP2PK
5 rows selected.
So perhaps I could also ask, can I have the indexes created for an IOT be non-unique?
September 07, 2009 - 7:15 am UTC
another approach, instead of the 'sort of a data model hack' of id,type as a foreign key would be to encapsulate the logic of update in a stored procedure and make it be delete returning the row followed by an insert of the old data with the new type.
The delete would be prevented if there are any child records to worry about.
what about that approach?
... I would like the children of an owner to be stored close to each other, as they
will mostly be retrieved by owner id.
...
that sounds like you want the CHILD to be an IOT, not the parent? the parent is singleton records - no clustering by primary key makes sense.
and the child table would have a key of:
create table child
( parent_id references parent,
surrogate_to_make_unique_key number,
.....
primary key(parent_id,surrogate_to_make_unique_key)
)
organization index;
now, all of the records with the same parent_id would be stored "together"
Sharing an index between two constraints on an IOT
Henrik, September 08, 2009 - 8:05 am UTC
Yes, I wanted the child to be an IOT, sorry about the confusion.
But I am still wondering about why I can't get two constraints to use the same index if the index is unique.
In general I like the stored procedure approach, but that would require changes beyound my control (time etc.)
September 08, 2009 - 9:32 am UTC
sometimes I have to laugh out loud at ourselves...
see Note 577253.1
here is the reason provided:
...
Solution
We cannot use a prefix of a unique index to enforce a unique constraint.
We can use a whole unique index or a prefix of a non-unique index to do that.
This is the way Oracle was designed.
.....
I do not know the logical reason why this is so, I could not think of a reason for it being so.
Pascal Nouma
A reader, September 13, 2009 - 5:05 am UTC
Hi Tom;
I have some doubts about the space usage after index deletion.
1-)
Assume I delete one value from the index.
Does the space oppupied by this index can be used again for future inserts?
or Does it only used again if the deleted value inserted again?
or never used again?
2-)Is there a freelist or bitmaps for indexes (like tables)?
September 14, 2009 - 1:17 pm UTC
1) or updates
or if the entire leaf block becomes empty - we can move the block elsewhere in the index where it is needed.
and it does not have to be the SAME EXACT value to reuse a slot in an index, it just has to be a row that would like to be on that leaf block. Eg: if you insert
1, 2, 999999999999999999999
and index that and then delete 2, insert 3, commit. delete 3, insert 4, commit. etc - the index will never grow, we'll just keep reusing the space nicely.
2) sure, we need a place to manage empty blocks
Pascal Nouma
A reader, September 19, 2009 - 2:14 am UTC
Thanks Sir;
You said that "we can move the block elsewhere in the index where it is needed."
Do we have to move the block explictly by a sql command
or
Does oracle perfom this action automatically?
September 28, 2009 - 8:20 am UTC
we move it, all by ourselves, there is no need to do anything.
A reader, October 10, 2009 - 2:59 am UTC
Sir I am in dilemma;
In ur book, you mentioned that:
If index is deleted (value 2),the space in the index was reused.
You also said that,
The space used by initial number 2 would remain index block forever.
So is the value occupied by value 2 can be reused or not?
create table primary key missing?
Eric Peterson, October 22, 2009 - 1:20 pm UTC
I created this test case where I create the primary key in a different manner then I have seen before.
But looking at the index/constraints, I don't see a UNIQUE PK constraint. yet it is being enforced. Am I looking for it wrong?
Thanks
Eric
SET AUTOCOMMIT ON TIMING OFF FEEDBACK OFF LINESIZE 150
DROP TABLE new_emp PURGE;
COLUMN search_condition FORMAT A30 WORD
COLUMN degree FORMAT A6
CREATE TABLE new_emp
(
employee_id NUMBER(6)
CONSTRAINT new_emp_nn1 NOT NULL
CONSTRAINT new_emp_pk PRIMARY KEY USING INDEX
(
CREATE INDEX new_emp_pk
ON new_emp
(
employee_id
)
TABLESPACE users
PCTFREE 1
PARALLEL 2
),
first_name VARCHAR2(20),
last_name VARCHAR2(25)
)
TABLESPACE users
PARALLEL 2
LOGGING
PCTFREE 2
PCTUSED 90;
SELECT table_name, tablespace_name, status, pct_free, pct_used, blocks, degree FROM user_tables;
SELECT index_name, index_type, uniqueness, pct_free, degree FROM user_indexes;
SELECT constraint_name, search_condition, index_name FROM user_constraints;
CLEAR COLUMNS
INSERT INTO new_emp VALUES ( 1, 'a', 'a' );
INSERT INTO new_emp VALUES ( 1, 'b', 'b' );
INSERT INTO new_emp VALUES ( 2, 'c', 'c' );
SELECT * FROM new_emp ORDER BY employee_id ASC;
SET AUTOCOMMIT OFF TIMING ON FEEDBACK ON
Here you can see that index in nonunique, yet there is a constraint pointing to it.
> SELECT table_name, tablespace_name, status, pct_free, pct_used, blocks, degree FROM user_tables;
TABLE_NAME TABLESPACE_NAME STATUS PCT_FREE PCT_USED BLOCKS DEGREE
------------------------------ ------------------------------ -------- ---------- ---------- ---------- ------
NEW_EMP USERS VALID 2
2
> SELECT index_name, index_type, uniqueness, pct_free, degree FROM user_indexes;
INDEX_NAME INDEX_TYPE UNIQUENES PCT_FREE DEGREE
------------------------------ --------------------------- --------- ---------- ------
NEW_EMP_PK NORMAL NONUNIQUE 1 2
> SELECT constraint_name, search_condition, index_name FROM user_constraints;
CONSTRAINT_NAME SEARCH_CONDITION INDEX_NAME
------------------------------ ------------------------------ ------------------------------
NEW_EMP_NN1 "EMPLOYEE_ID" IS NOT NULL
NEW_EMP_PK NEW_EMP_PK
> CLEAR COLUMNS
> INSERT INTO new_emp VALUES ( 1, 'a', 'a' );
> INSERT INTO new_emp VALUES ( 1, 'b', 'b' );
INSERT INTO new_emp VALUES ( 1, 'b', 'b' )
*
ERROR at line 1:
ORA-00001: unique constraint (EPETERSON.NEW_EMP_PK) violated
> INSERT INTO new_emp VALUES ( 2, 'c', 'c' );
> SELECT * FROM new_emp ORDER BY employee_id ASC;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
1 a a
2 c c
October 23, 2009 - 1:43 pm UTC
you do not need (and have not needed for many many many releases) a unique index
just an index.
all we need is an index with the unique columns on the leading edge of it - does not have to be unique.
ops$tkyte%ORA10GR2> create table t ( x int constraint t_pk primary key deferrable );
Table created.
ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_PK NONUNIQUE
SQL index
Kirthika, September 05, 2012 - 1:25 am UTC
Hi,
Please let me know the exact difference between :
1. Alter and Modify,
2. Index and Unique Index.
Although I know these concepts, I am a bit confused.Please help me.
September 10, 2012 - 7:28 pm UTC
1) alter is a DDL command. Sometimes and ALTER statement can include the word 'modify' - like when you ALTER table and MODIFY a column.
2) an index allows duplicate keys. a unique index does not allow duplicate keys.
Diff between unique and non-unique index
asktom fan, May 22, 2013 - 5:57 pm UTC
Hi Tom,
Regarding the difference between unique and non-unique indexes in this thread, you said on May 30, 2008 - 7am UTC:
<quote>
they are the same, but different.
they are the same size
they contain the same information
they are navigated the same
they provide the same information
</quote>
And on June 1, 2008, Richard Foote added:
<quote>
However, by including the rowid as an indexed column entry, Oracle needs to allocate an additional
byte to record the length of the rowid. Therefore, a non-unique index entry is one byte longer than an equivalent unique index entry.
</quote>
So the size of a non-unique index and an equivalent unique index will NOT be the same?
Thanks!
May 22, 2013 - 7:47 pm UTC
they are pretty much the same size, yes, a non-unique index is trivially larger than a unique one.
ops$tkyte%ORA11GR2> create table t as select * from all_objects;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t_idx_non_unique on t(object_id,object_name,owner,object_type);
Index created.
ops$tkyte%ORA11GR2> analyze index t_idx_non_unique validate structure;
Index analyzed.
ops$tkyte%ORA11GR2> create table istats as select * from index_stats;
Table created.
ops$tkyte%ORA11GR2> drop index t_idx_non_unique ;
Index dropped.
ops$tkyte%ORA11GR2> create unique index t_idx_unique on t(object_id,object_name,owner,object_type);
Index created.
ops$tkyte%ORA11GR2> analyze index t_idx_unique validate structure;
Index analyzed.
ops$tkyte%ORA11GR2> insert into istats select * from index_stats;
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select name, blocks, lf_rows_len, br_rows_len, btree_space
2 from istats;
NAME BLOCKS LF_ROWS_LEN BR_ROWS_LEN BTREE_SPACE
------------------------------ ---------- ----------- ----------- -----------
T_IDX_NON_UNIQUE 640 4288273 7120 4805628
T_IDX_UNIQUE 640 4211337 6990 4717672
pretty much the same - and with inserts/updates/deletes taking place over time - they'll just be "the same".
why is constraint, not an index
Lonion, October 14, 2013 - 4:06 am UTC
Hi,Tom. As you mentioned above this point - If the data must be UNIQUE, you should use a UNIQUE constraint - not an index.
Why you recommended us to use a UNIQUE constraint - not an index?
Thank you advance for replying.
November 01, 2013 - 6:09 pm UTC
the constraint is meta data, it says something. it very clearly says "this is unique".
you go to user_constraints to find the meta data that describes the rules that constraint the table. If you use a unique index - this meta data will not be there.
Use a constraint to enforce a constraint. It will be very rare for you to code a unique index - you'll use it either
a) when you want to use the "using index" clause in the create constraint to specify very specific index attributes.
b) when you want to implement a unique constraint in very 10 and before (before virtual columns) to do something like "selective uniqueness". for example if you have to make sure an employee has at most ONE preferred address in an address table - you might:
create unique index on emp_address( case when preferred = 'Y' then empno end );
in 11g you would create a virtual column - emp_has_preferred_address maybe - equal to the empno and then put a unique constraint on that instead.
Constraint over index vs docs
Maciej, May 26, 2017 - 9:18 am UTC
Hi TOM,
In the post above you said that we should "Use a constraint to enforce a constraint" and that we should rarely be in need to use UNIQUE INDEX. However, in docs we can read: "Alternatively, you can define UNIQUE integrity constraints on the desired columns. The database enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. [...]
However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly."
http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11725 You say that it's better to define unique constraint and rely on automatically defined index.
Docs says we should define unique indexes by ourselves.
Am I missing something?
Thanks in advance.
May 27, 2017 - 6:33 am UTC
I'll agree, it is only a subtle difference. But what if (one day) we come up with an amazing internal algorithm that enforces uniqueness on a table *without* a unique index.
Then you would want to be able to know what indexes *you* wanted for performance, versus what indexes *we* used to implement a data correctness rule.
Enforcing unique constraints by unique, non-unique indexes
Pedro, May 26, 2017 - 2:33 pm UTC
Tom,
As always you are my last resort.
Is there a query performance difference between:
- a non-deferrable unique constraint enforced by unique index
- a non-deferrable unique constraint enforced by non-unique index
The question stems, of course, from the fact that one can make a non-unique index unusable and perform a DML and one cannot make an unique index unusable and perform a DML. Any suggestions aside from rebuild to overcome the unique index/unusable/DML obstacle?
Thank you in advance,
Pedro
May 27, 2017 - 6:45 am UTC