View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are
composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)
A.
CREATE INDEX ord_idx1
ON ord(ord_no);
B.
CREATE INDEX ord_idx2
ON ord_items(ord_no);
C.
CREATE INDEX ord_idx3
ON ord_items(item_no);
D.
CREATE INDEX ord_idx4
ON ord, ord_items(ord_no, ord_date, qty);
Explanation:
How Are Indexes Created?
You can create two types of indexes.
Unique index: The Oracle server automatically creates this index when you define a column in a table to have
a PRIMARY KEY or a UNIQUE constraint. The name of the index is the name that is given to the constraint.
Nonunique index: This is an index that a user can create. For example, you can create the FOREIGN KEY
column index for a join in a query to improve the speed of retrieval.
Note: You can manually create a unique index, but it is recommended that you create a unique constraint,
which implicitly creates a unique index.
CREATE TABLE ord
(
ord_no NUMBER(2) NOT NULL,
ord_date DATE,
cust_id NUMBER(4)
);—Table ORD créé(e).
CREATE TABLE ord_items
(
ord_no NUMBER(2) NOT NULL,
item_no NUMBER(3) NOT NULL,
qty NUMBER(8,2)
); —Table ORD_ITEMS créé(e).
ALTER TABLE ord ADD CONSTRAINT pk_ord PRIMARY KEY(ord_no); —Table ORD modifié(e).
ALTER TABLE ord_items ADD CONSTRAINT pk_ord_item PRIMARY KEY(ord_no,item_no);—Table ORD_ITEMS modifié(e).
CREATE INDEX ord_idx1 ON ord
(ord_no
); —“such column list already indexed”
CREATE INDEX ord_idx2 ON ord_items
(ord_no
); —Index ORD_IDX2 créé(e).
CREATE INDEX ord_idx3 ON ord_items
(item_no
); —Index ORD_IDX3 créé(e).
CREATE INDEX ord_idx4 ON ord,ord_items
(ord_no, ord_date,qty
); —“missing left parenthesis”
DROP TABLE ord;
DROP TABLE ord_items;
CREATE TABLE ord
(
ord_no NUMBER(2) NOT NULL,
ord_date DATE,
cust_id NUMBER(4)
);—Table ORD créé(e).
——————————————————————
CREATE TABLE ord_items
(
ord_no NUMBER(2) NOT NULL,
item_no NUMBER(3) NOT NULL,
qty NUMBER(8,2)
); —Table ORD_ITEMS créé(e).
——————————————————————
ALTER TABLE ord ADD CONSTRAINT pk_ord PRIMARY KEY(ord_no); —Table ORD modifié(e).
——————————————————————
ALTER TABLE ord_items ADD CONSTRAINT pk_ord_item PRIMARY KEY(ord_no,item_no);—Table ORD_ITEMS modifié(e).
——————————————————————
CREATE INDEX ord_idx1 ON ord
(ord_no
); —“such column list already indexed”
——————————————————————
CREATE INDEX ord_idx2 ON ord_items
(ord_no
); —Index ORD_IDX2 créé(e).
——————————————————————
CREATE INDEX ord_idx3 ON ord_items
(item_no
); —Index ORD_IDX3 créé(e).
——————————————————————
CREATE INDEX ord_idx4 ON ord,ord_items
(ord_no, ord_date,qty
); —“missing left parenthesis”
——————————————————————
DROP TABLE ord;
DROP TABLE ord_items;
B,C