 | |
not null and indexes
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
not null and indexes -
02-13-2005
, 05:41 AM
#2
| |||
| |||
|
|
Hello Does oracle create automaticly indexes on not null columns ? Thanx Michal |
--
Sybrand Bakker, Senior Oracle DBA
#3
| |||
| |||
|
#4
| |||
| |||
|
|
vertigo (ax178 (AT) wp (DOT) pl) wrote: : Hello : Does oracle create automaticly indexes on not null columns ? No. But if you define constraints on a column then oracle may implement that constraint using an index on the column. Prime example being a primary key - it must be unique and a unique index is used to enforce that - so in that case those columns will be in an index. Also in that case, the columns involved will end up being not null columns. |
Incidentally if the column is already indexed (even with a non-unique key)
then the index won't be created (and more to the point) dropped with the
constraint.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
#5
| |||
| |||
|
|
"Malcolm Dew-Jones" <yf110 (AT) vtn1 (DOT) victoria.tc.ca> wrote in message news:420f964e (AT) news (DOT) victoria.tc.ca... vertigo (ax178 (AT) wp (DOT) pl) wrote: : Hello : Does oracle create automaticly indexes on not null columns ? No. But if you define constraints on a column then oracle may implement that constraint using an index on the column. Prime example being a primary key - it must be unique and a unique index is used to enforce that - so in that case those columns will be in an index. Also in that case, the columns involved will end up being not null columns. Isn't this the *only* such example. As such I'd view it as an exception. Incidentally if the column is already indexed (even with a non-unique key) then the index won't be created (and more to the point) dropped with the constraint. -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com |
Clarification for those not familiar with this -- the behavior described
above is true for both PRIMARY KEY and UNIQUE constraints, but no other
constraints (i.e., not for CHECK, FOREIGN KEY REFERENCES or NOT NULL)
++ mcs
#6
| |||
| |||
|
|
"Malcolm Dew-Jones" <yf110 (AT) vtn1 (DOT) victoria.tc.ca> wrote in message news:420f964e (AT) news (DOT) victoria.tc.ca... vertigo (ax178 (AT) wp (DOT) pl) wrote: : Hello : Does oracle create automaticly indexes on not null columns ? No. But if you define constraints on a column then oracle may implement that constraint using an index on the column. Prime example being a primary key - it must be unique and a unique index is used to enforce that - so in that case those columns will be in an index. Also in that case, the columns involved will end up being not null columns. Isn't this the *only* such example. As such I'd view it as an exception. Incidentally if the column is already indexed (even with a non-unique key) then the index won't be created (and more to the point) dropped with the constraint. |
primary keys
unique constraints
they both will either utilize an existing index or create one -- either a unique
or non-unqiue index as appropriate (for deferrable or nondeferrable
constraints)....
--
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation
#7
| |||
| |||
|
|
Hello Does oracle create automaticly indexes on not null columns ? Thanx Michal |
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.