| Author |
Message |
DA Morgan
*nix forums Guru
Joined: 06 Mar 2005
Posts: 1042
|
 Posted: Thu Feb 17, 2005 3:46 pm Post subject:
Re: not null and indexes
|
|
|
vertigo wrote:
| Quote: | Hello
Does oracle create automaticly indexes on not null columns ?
Thanx
Michal
|
No as Sybrand said but also Oracle does not index NULLs.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond) |
|
| Back to top |
|
 |
Thomas Kyte
*nix forums addict
Joined: 03 May 2005
Posts: 62
|
| Posted: Sun Feb 13, 2005 8:51 pm Post subject:
Re: not null and indexes
|
|
|
In article <420f9b25$0$16585$cc9e4d1f@news-text.dial.pipex.com>, Niall
Litchfield says...
| Quote: |
"Malcolm Dew-Jones" <yf110@vtn1.victoria.tc.ca> wrote in message
news:420f964e@news.victoria.tc.ca...
vertigo (ax178@wp.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.
|
well, there is that "unique" constraint as well :)
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 |
|
| Back to top |
|
|
Mark C. Stock
*nix forums Guru
Joined: 05 May 2005
Posts: 730
|
| Posted: Sun Feb 13, 2005 8:50 pm Post subject:
Re: not null and indexes
|
|
|
"Niall Litchfield" <niall.litchfield@dial.pipex.com> wrote in message
news:420f9b25$0$16585$cc9e4d1f@news-text.dial.pipex.com...
| Quote: | "Malcolm Dew-Jones" <yf110@vtn1.victoria.tc.ca> wrote in message
news:420f964e@news.victoria.tc.ca...
vertigo (ax178@wp.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
|
Good addtional regarding the pre-existing index.
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 |
|
| Back to top |
|
|
Niall Litchfield
*nix forums Guru Wannabe
Joined: 29 Apr 2005
Posts: 128
|
| Posted: Sun Feb 13, 2005 5:23 pm Post subject:
Re: not null and indexes
|
|
|
"Malcolm Dew-Jones" <yf110@vtn1.victoria.tc.ca> wrote in message
news:420f964e@news.victoria.tc.ca...
| Quote: | vertigo (ax178@wp.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 |
|
| Back to top |
|
|
Malcolm Dew-Jones
*nix forums Guru
Joined: 04 Mar 2005
Posts: 418
|
| Posted: Sun Feb 13, 2005 5:02 pm Post subject:
Re: not null and indexes
|
|
|
vertigo (ax178@wp.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.
--
This space not for rent. |
|
| Back to top |
|
|
Sybrand Bakker
*nix forums Guru
Joined: 03 Apr 2005
Posts: 1766
|
| Posted: Sun Feb 13, 2005 10:58 am Post subject:
Re: not null and indexes
|
|
|
On Sun, 13 Feb 2005 12:41:07 +0100, vertigo <ax178@wp.pl> wrote:
| Quote: | Hello
Does oracle create automaticly indexes on not null columns ?
Thanx
Michal
|
No
--
Sybrand Bakker, Senior Oracle DBA |
|
| Back to top |
|
|
vertigo
*nix forums addict
Joined: 23 Mar 2005
Posts: 71
|
| Posted: Sun Feb 13, 2005 10:41 am Post subject:
not null and indexes
|
|
|
Hello
Does oracle create automaticly indexes on not null columns ?
Thanx
Michal |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
