Using Index versus Create Index in oracle

Question

I am creating a table with following script (I used Using index).

CREATE TABLE TABLE1 (
    C1 VARCHAR2(2 CHAR) NOT NULL ENABLE,
    C2 VARCHAR2(1 CHAR) NOT NULL ENABLE,
    CONSTRAINT TABLE_PK PRIMARY KEY (C1) USING INDEX TABLESPACE SFE_I1
)
TABLESPACE SFE_D1;

In the above query index will create for what column?

CREATE INDEX IDX_TABLE ON TABLE1 (C1) TABLESPACE SFE_I1;

If I am creating index with the above create index query, it will create index for C1 column. But what is the difference between to this two scripts.

As well if I run both this query, what should happen. And what is the suggested way to do this?

If my create table script contains composite primary key and i am using USING INDEX keyword then how index will be created (will it create a single index for all the composite columns)

Mureinik · Accepted Answer · 2016-01-22 07:20:33Z

2

The important part of the create table statement, for this question, is the CONSTRAINT TABLE_PK PRIMARY KEY (C1) USING INDEX TABLESPACE SFE_I1. Let's break it down and understand it:

CONSTRAINT TABLE_PK

You're creating a constraint named table_pk.

PRIMARY KEY

This constraint is a primary key

(C1)

On the column c1

USING INDEX TABLESPACE SFE_I1

A primary key, implicitly needs to create an index so it can efficiently search for duplicates that would violate the constraint. Like with explicitly created indexes (e.g., your second statement), the index would be created on the user's default tablespace, which isn't always the best idea. The using index tablespace syntax allows you do define which tablespace to use, in the same way you can use it when creating an index.

If you attempt to run both statements the second one should fail, since c1 is already indexed by the first one.

answered Jan 22, 2016 at 7:20

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yoga Over a year ago

If USING INDEX is not used in the First query of the above post, will it create index by default? If Yes, how can we see the index? If composite primary create with using index, then will it create index considering all the involved composite columns.

Yoga Over a year ago

If i want to create a index for a column which is not primary key, then what are all the condition should be satisfied (For example, if the column is capable of producing duplicate values)

Mureinik Over a year ago

@Yoga a primary key will always create an index, which is has the same name as the primary key. If you omit the using index tablespace clause, it will be created on the default tablespace. If you have a composite key, it will create a composite index. You can create an index on a column with duplicate values. If you attempt to create a unique index, though, it will fail.

Collectives™ on Stack Overflow

Using Index versus Create Index in oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related