| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: What's wrong with this SQL?
ohaya wrote:
> Bent Stigsen wrote:
> Ok, I think I see.
>
> Synopsis:
>
> - By dropping the inline primary key, that means that the original
> "CREATE TABLE" won't cause the "sessionid" to be indexed, then
Pretty much. It is the declaration of the primary key that is causing Oracle to create an index. In the script that the vendor supplies the index will have a user-hostile name.
> - By eliminating the "CREATE INDEX", that will eliminate an index
being
> created, then
>
Absolutely.
> - By adding the "ALTER TABLE" with the add constraint, this will make
> "sessionid" the primary key AND create a CONSTRAINT named
> "XIE1ss_sessionspec5".
It will create the PRIMARY KEY named XIE1SS_SESSIONSPEC5 *and* an index of the same name.
> Questions:
>
> I'm assuming that when the "ALTER TABLE" makes "sessionid" the
primary
> key, that this creates an index. Is this correct?
Yes, but it doesn't have to, bear with me a bit, I'm going to try and expand things a bit below without over-complicating things, but hopefully also not by over-simplyfying.
> Also, in Oracle, is the constraint "XIE1ss_sessionspec5" that results
> from "ALTER TABLE" treated EXACTLY the same as if it were an INDEX
after
> that?
No. An index in and of itself is pretty much only used for accessing data more efficiently. The primary key constraint tells Oracle a lot more about the data in the sessionid column.
A primary key in any relational database is the column (or columns) that uniquely and completely identify a member of a set (a session in this case). That is to say that every session MUST have a session_id AND that session_id MUST EXIST.
When you tell oracle that a column is a primary key, that tells it that every row in the table must have a value (be NOT NULL) and that each value must be unique. Oracle achieves this by default by creating a NOT NULL constraint for the column and by creating a system named UNIQUE index.
You can see this for yourself below.
SQL> CREATE TABLE ss_sessionspec5 (
2 sessionid VARCHAR2(64), 3* expirationtime INTEGER DEFault 0 not null);
Table created.
SQL> desc ss_sessionspec5;
Name Null? Type ----------------------------------------- -------- ---------------------------- SESSIONID VARCHAR2(64) EXPIRATIONTIME NOT NULL NUMBER(38)
SQL> select index_name from user_indexes where table_name='SS_SESSIONSPEC5';
no rows selected
SQL> DROP TABLE SS_SESSIONSPEC5; Table dropped.
SQL> CREATE TABLE ss_sessionspec5 (
2 sessionid VARCHAR2(64), 3 expirationtime INTEGER DEFault 0 not null)4 .
SQL> ED
Wrote file afiedt.buf
1 CREATE TABLE ss_sessionspec5 (
2 sessionid VARCHAR2(64) primary key, 3* expirationtime INTEGER DEFault 0 not null)SQL> .
SQL> desc ss_sessionspec5;
Name Null? Type ----------------------------------------- -------- ---------------------------- SESSIONID NOT NULL VARCHAR2(64) EXPIRATIONTIME NOT NULL NUMBER(38)
SQL> select index_name from user_indexes where table_name='SS_SESSIONSPEC5';
INDEX_NAME
SQL> select index_name,uniqueness
2 from user_indexes
3* where table_name = 'SS_SESSIONSPEC5'
SQL> /
INDEX_NAME UNIQUENES ------------------------------ --------- SYS_C006138 UNIQUE
1 row selected.
Now there is one further possibility and that is to use a non-unique index on the primary key. It is possible that this is what your vendor was trying to achieve with the create index statement, as the create index statement will NOT create a unique index.
SQL> drop table ss_sessionspec5;
Table dropped.
SQL> CREATE TABLE ss_sessionspec5 (
2 sessionid VARCHAR2(64), 3 expirationtime INTEGER DEFAULT 0 NOT NULL);
Table created.
SQL> create index idx_sessionspec5
2* on ss_sessionspec5(sessionid);
Index created.
SQL> alter table ss_sessionspec5
2 add constraint pk_sessionspec5
3* primary key(sessionid);
Table altered.
SQL> select index_name,uniqueness
2 from user_indexes
3* where table_name='SS_SESSIONSPEC5'
SQL> /
INDEX_NAME UNIQUENES ------------------------------ --------- IDX_SESSIONSPEC5 NONUNIQUE
1 row selected.
SQL> DESC SS_SESSIONSPEC5;
Name Null? Type ----------------------------------------- -------- ------------------------ SESSIONID NOT NULL VARCHAR2(64) EXPIRATIONTIME NOT NULL NUMBER(38)
SQL> All of which rather begs the questions
Well you will want to use a non-unique index for a deferrable constraint. This is used to provide updateable constraints see http://www.oracle.com/technology/oramag/oracle/03-nov/o63asktom.html for an example. Oracle also uses them for materialized views or snapshots, but that is probably irrelevant for now.
The answer to the second question can be answered by considering what happens if you disable a constraint - often used to speed data loads.
SQL> ed
Wrote file afiedt.buf
1 CREATE TABLE ss_sessionspec5 (
2 sessionid VARCHAR2(64), 3 expirationtime INTEGER DEFAULT 0 NOT NULL,4* constraint pk_ss_sessionspec5 primary key(sessionid)) SQL> .
SQL> DESC SS_SESSIONSPEC5;
Name Null? Type ----------------------------------------- -------- ---------------------------- SESSIONID NOT NULL VARCHAR2(64) EXPIRATIONTIME NOT NULL NUMBER(38)
SQL> select index_name,uniqueness from user_indexes 2 where table_name='SS_SESSIONSPEC5';
INDEX_NAME UNIQUENES ------------------------------ --------- PK_SS_SESSIONSPEC5 UNIQUE
1 row selected.
SQL> ALTER TABLE SS_SESSIONSPEC5 DISABLE CONSTRAINT PK_SS_SESSIONSPEC5; Table altered.
SQL> select index_name,uniqueness from user_indexes 2 where table_name='SS_SESSIONSPEC5';
no rows selected
SQL> DROP TABLE SS_SESSIONSPEC5; Table dropped.
SQL> CREATE TABLE ss_sessionspec5 (
2 sessionid VARCHAR2(64), 3 expirationtime INTEGER DEFAULT 0 NOT NULL);
Table created.
SQL> CREATE UNIQUE INDEX PK_SESSIONSPEC5 2 ON SS_SESSIONSPEC5(SESSIONID); Index created.
SQL> ALTER TABLE SS_SESSIONSPEC5
2 ADD CONSTRAINT PK_SESSIONSPEC5 PRIMARY KEY (SESSIONID);
Table altered.
SQL> DESC SS_SESSIONSPEC5;
Name Null? Type ----------------------------------------- -------- ---------------------------- SESSIONID NOT NULL VARCHAR2(64) EXPIRATIONTIME NOT NULL NUMBER(38)
SQL> select index_name,uniqueness from user_indexes 2 where table_name='SS_SESSIONSPEC5';
INDEX_NAME UNIQUENES ------------------------------ --------- PK_SESSIONSPEC5 UNIQUE
1 row selected.
SQL> ALTER TABLE SS_SESSIONSPEC5 DISABLE CONSTRAINT PK_SESSIONSPEC5; Table altered.
SQL> select index_name,uniqueness from user_indexes 2 where table_name='SS_SESSIONSPEC5';
INDEX_NAME UNIQUENES ------------------------------ --------- PK_SESSIONSPEC5 UNIQUE
1 row selected.
SQL> so if we create the constraint inline and later disable it we lose the index, if we precreate the index and let the constraint use it we don't lose the index when we disable the constraint.
Hope that all helps rather than confuses
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Received on Tue Jan 18 2005 - 03:47:21 CST
 |
 |