On your Oracle Database, you issue the following commands to create indexes: Which two
statements are true?
A.
Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the
optimizer for queries on the ORDERS table.
B.
Only the ORD_CUSTOMER_IX1 index created.
C.
Both the indexes are updated when a row is inserted, updated, or deleted in the
ORDERS table.
D.
Both the indexes are created: however, only ORD_CUSTOMERS_IX1 is used by the
optimizer for queries on the ORDERS table.
E.
The ORD_CUSTOMER_IX1 index is not used by the optimizer even when the
OPTIMIZER_USE_INVISIBLE_INDEXES parameters is set to true.
F.
Both the indexes are created and used by the optimizer for queries on the ORDERS
table.
B / F
SQL> CREATE TABLE SIDNEY.employees_demo
2 ( employee_id NUMBER(6)
3 , first_name VARCHAR2(20)
4 , last_name VARCHAR2(25)
5 CONSTRAINT emp_last_name_nn_demo NOT NULL
6 , email VARCHAR2(25)
7 CONSTRAINT emp_email_nn_demo NOT NULL
8 , phone_number VARCHAR2(20)
9 , hire_date DATE DEFAULT SYSDATE
10 CONSTRAINT emp_hire_date_nn_demo NOT NULL
11 , job_id VARCHAR2(10)
12 CONSTRAINT emp_job_nn_demo NOT NULL
13 , salary NUMBER(8,2)
14 CONSTRAINT emp_salary_nn_demo NOT NULL
15 , commission_pct NUMBER(2,2)
16 , manager_id NUMBER(6)
17 , department_id NUMBER(4)
18 , dn VARCHAR2(300)
19 , CONSTRAINT emp_salary_min_demo
20 CHECK (salary > 0)
21 , CONSTRAINT emp_email_uk_demo
22 UNIQUE (email)
23 );
Tabla creada.
SQL> CREATE INDEX SIDNEY.CUSTOMER_IX1 ON SIDNEY.EMPLOYEES_DEMO (employee_id, sa
lary) INVISIBLE;
═ndice creado.
SQL> CREATE BITMAP INDEX SIDNEY.CUSTOMER_IX2 ON SIDNEY.EMPLOYEES_DEMO (employee_
id, salary);
═ndice creado.
SQL> select employee_id, salary from SIDNEY.EMPLOYEES_DEMO;
ninguna fila seleccionada
Plan de Ejecuci¾n
———————————————————-
Plan hash value: 2590550183
——————————————————————————–
————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
——————————————————————————–
————-
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0
)| 00:00:01 |
| 1 | BITMAP CONVERSION TO ROWIDS | | 1 | 26 | 1 (0
)| 00:00:01 |
| 2 | BITMAP INDEX FAST FULL SCAN| CUSTOMER_IX2 | | |
| |
——————————————————————————–
————-
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
EstadÝsticas
———————————————————-
14 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
433 bytes sent via SQL*Net to client
540 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter session set optimizer_use_invisible_indexes = true
2 ;
Sesi¾n modificada.
SQL> select employee_id, salary from SIDNEY.EMPLOYEES_DEMO;
ninguna fila seleccionada
Plan de Ejecuci¾n
———————————————————-
Plan hash value: 2137003275
——————————————————————————–
–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
——————————————————————————–
–
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01
|
| 1 | INDEX FULL SCAN | CUSTOMER_IX1 | 1 | 26 | 0 (0)| 00:00:01
|
——————————————————————————–
–
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
EstadÝsticas
———————————————————-
11 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
433 bytes sent via SQL*Net to client
540 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
A,C