Multiple choice

An index needs to be created on the EMP_ID column of the EMPLOYEE table which satisfies the following conditions:

  1. The index will be called EMP_PK.
  2. The index should be sorted in ascending order.
  3. The index should be created in the INDEX01 tablespace, which is a dictionary managed tablespace.
  4. All extents of the index should be 1 MB in size.
  5. The index should be unique.
  6. No redo information should be generated when the index is created.
  7. 20% of each data block should be left free for future index entries.

Which of the following commands create the index and meets all the requirements?

  1. CREATE UNIQUE INDEX emp_pk ON Employee(Emp_id) TABLESPACE index0l PCTFREE 20 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);

  2. CREATE UNIQUE INDEX emp_pk ON employee(emp_id) TABLESPACE index0l PCTFREE 20 STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0) NOLOGGING;

  3. CREATE UNIQUE INDEX emp_pk ON Employee(emp_id) TABLESPACE index0l PCTUSED 80 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0) NOLOGGING;

  4. CREATE UNIQUE INDEX emp_pk ON employee(emp_id) TABLESPACE index0l PCTUSED 80 STORAGE (INITIAL lm NEXT lm PCTINCREASE 0);
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Option B meets all requirements: UNIQUE constraint ensures uniqueness, NOLOGGING suppresses redo generation, PCTFREE 20 reserves 20% of each block, STORAGE clause sets 1M extents, and the index is created in INDEX01 tablespace. Option A uses 'lm' instead of '1m' (typos), C incorrectly uses PCTUSED, and D lacks NOLOGGING.

Keep practicing — related questions