create index on creation of table in Oracle

Question

In MySql, one can create an index on a (non-unique) column along with a table, e.g.

create table orders(
   orderid varchar(20) not null unique,
   customerid varchar(20),
   index(customerid)
   );

Having not found a corresponding option in Oracle, i.e. creating the index on table creation rather than as a separate command afterwards, I suspect it is not possible. Is this correct? If so, what is the reason behind this - efficiency, as for example discussed here Insertion of data after creating index on empty table or creating unique index after inserting data on oracle? ? Thanks in advance!

Bob Jarvis - Слава Україні · Accepted Answer · 2020-02-18 13:13:12Z

1

Other than indexes defined as part of a primary or unique constraint there does not appear to be a way to define an index as part of a CREATE TABLE statement in Oracle. Although the USING INDEX clause is part of the constraint-state element of the CREATE TABLE statement, a missing right parenthesis error is issued if you try to include a USING INDEX clause in any constraint definition except a PRIMARY or UNIQUE constraint - see this db<>fiddle for examples.

As to "why" - that's a question only someone on the architecture team at Oracle could answer. From my personal user-oriented point of view, I see no particular value to being able to create an index as part of the CREATE TABLE statement, but then I'm accustomed to how Oracle works and have my thought patterns oriented in that particular direction. YMMV.

answered Feb 18, 2020 at 13:13

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

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

Comments

EdStevens · Accepted Answer · 2020-02-18 13:04:57Z

1

The root reason for the difference is that MySQL and Oracle are two distinctly different products, developed at different times by different software engineering teams. The fact thay MySQL is owned by Oracle means nothing in this case. MySQL was a separate and separetly developed product which was subsequently purchased by Oracle. As for why the two separate and distinct design teams made the decisions they did ... you'd have to ask them. But I'm pretty certain it has nothing to do with operational efficiency as you suggest. Once a table and index are created, there is no difference between having created an index as part of the CREATE TABLE vs. creating the index separately. And so there would be no difference in efficiency of any DML on said table.

answered Feb 18, 2020 at 13:04

EdStevens

3,9203 gold badges12 silver badges19 bronze badges

4 Comments

ctenar Over a year ago

Ok, thanks, but in any case, is it true that in Oracle the index cannot be generated on table creation?

EdStevens Over a year ago

I see noting in the CREATE TABLE syntax to suggest that you can explicitly create and index as part of the CREATE TABLE statement. If, as part of CREATE TABLE, you specify a column as PK or UK, oracle will automatically create an index to enforce that constraint. But no, you cannot just embed a CREATE INDEX into a CREATE TABLE.

Rick James Over a year ago

There are cases where it is significantly more efficient to populate the table first, then build the index. In MySQL, FULLTEXT prefers such and SPATIAL demands such. BTree, on the other hand, does reasonably well being built incrementally.

EdStevens Over a year ago

@RickJames - but again that is after the fact of table creation. Whether the index is created as part of the CREATE TABLE, or as a separate CREATE INDEX, once the table and index are created it doesn't matter which mechanism was used to get there.

Collectives™ on Stack Overflow

create index on creation of table in Oracle

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related