今天看ocp题库，发现了一个新知识点，原来索引还可以这样创建；

CREATE TABLE order_item (order_id NUMBER(3),item_id NUMBER(2),qty NUMBER(4),

CONSTRAINT ord_itm_id_pk  PRIMARY KEY (order_id,item_id)

USING INDEX(CREATE INDEXord_itm_idx ON order_item(order_id,item_id)));

表创建成功以后，在列order_id和item_id上只有一个普通索引ord_itm_idx;如果不用using index 子句，那么只会创建一个与主键约束一样名字的唯一索引；

当然也可以先创建表，然后创建一个索引，再添加一个主键约束并用using index子句使用哪个已有的索引；

下面两道题目说明了这两个知识点：

QUESTION 48
Evaluate the following CREATE TABLE command:
CREATE TABLE order_item
(order_id NUMBER(3),
item_id NUMBER(2),
qty NUMBER(4),
CONSTRAINT ord_itm_id_pk
PRIMARY KEY (order_id,item_id)
USING INDEX
(CREATE INDEX ord_itm_idx
ON order_item(order_id,item_id)));
Which statement is true regarding the above SQL statement?
A. It would execute successfully and only ORD_ITM_IDX index would be created.
B. It would give an error because the USING INDEX clause cannot be used on a composite primary key.
C. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.
D. It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.

Answer: A

QUESTION 41

View the Exhibit and examine the structure of the EMP table.

You executed the following command to add a primary key to the EMP table:
ALTER TABLE emp
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
USING INDEX emp_id_idx;

Which statement is true regarding the effect of the command?

A. The PRIMARY KEY is created along with a new index.
B. The PRIMARY KEY is created and it would use an existing unique index.
C. The PRIMARY KEY would be created in a disabled state because it is using an existing index.
D. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.

Answer: B