How to create a table with multiple indices with Oracle SQL?-CodePudding

I want to create a new table with (Oracle) SQL with indices for multiple columns, with the indices to be created at the same time the column is created (in one statement). Here (https://www.oracletutorial.com/oracle-index/oracle-create-index/) I found the statement to create a table:

CREATE TABLE members(
    member_id INT GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(100) NOT NULL,
    last_name VARCHAR2(100) NOT NULL,
    gender CHAR(1) NOT NULL,
    dob DATE NOT NULL,
    email VARCHAR2(255) NOT NULL,
    PRIMARY KEY(member_id)
);

And in a second statement an index for last_name is created:

CREATE INDEX members_last_name_i 
ON members(last_name);

I would like to know how the create table statement has to be modified to have an index for columns "last_name" and "dob" for example. Something like:

CREATE TABLE members(
    member_id INT GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(100) NOT NULL,
    last_name VARCHAR2(100) NOT NULL,
    gender CHAR(1) NOT NULL,
    dob DATE NOT NULL,
    email VARCHAR2(255) NOT NULL,
    PRIMARY KEY(member_id),
    INDEX members_last_name_i (last_name),
    INDEX date_i (dob)
);

CodePudding user response：

Shortly, you can not.

CREATE INDEX is a separate statement. Only PRIMARY KEY constraint in the background creates (unique) index which supports it (if such an index doesn't exist yet; if it exists, you can re-use it), but - for the rest of them - you'll have to create them afterwards, one after another.