On your Oracle Database, you issue the following commands to create indexes:
SQL > CREATE INDEX oe.ord_customer_ix1 ON or-orders (customer_id, sales_rep_id)
INVISIBLE;
SQL> CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customer_id, sales_rep_id);
Which two statements are true?
A.
Only the ORD_CUSTOMER_IX1 index created.
B.
Both the indexes are updated when a row is inserted, updated, or deleted in the ORDERS
table.
C.
Both the indexes are created: however, only ORD_CUSTOMERS_IX1 is used by the optimizer
for queries on the ORDERS table.
D.
The ORD_CUSTOMER_IX1 index is not used by the optimizer even when the
OPTIMIZER_USE_INVISIBLE_INDEXES parameters is set to true.
E.
Both the indexes are created and used by the optimizer for queries on the ORDERS table.
Explanation:
* Specify BITMAP to indicate that index is to be created with a bitmap for each distinct key, rather
than indexing each row separately. Bitmap indexes store the rowids associated with a key value
as a bitmap. Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means
that the row with the corresponding rowid contains the key value. The internal representation of
bitmaps is best suited for applications with low levels of concurrent transactions, such as data
warehousing.
* VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the
optimizer. An invisible index is maintained by DML operations, but it is not be used by the
optimizer during queries unless you explicitly set the parameter
OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.
So BD?
secret answer F with B 😉
For me only B is right
BF
F. Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for queries on the ORDERS table.
BC
IMHO – Only “B” is correct.
INCORRECT A. Only the ORD_CUSTOMER_IX1 index created.
CORRECT B. Both the indexes are updated when a row is inserted, updated, or deleted in
the ORDERS table.
INCORRECT C. Both the indexes are created: however, only ORD_CUSTOMERS_IX1 is used by the
optimizer for queries on the ORDERS table.
REASON: “ORD_CUSTOMERS_IX1” is invisible to the optimizer. “ORD_CUSTOMERS_IX2′
(with “2”) will be used by optimized (unless a param is explicitly set)
INCORRECT D. The ORD_CUSTOMER_IX1 index is not used by the optimizer even when the
OPTIMIZER_USE_INVISIBLE_INDEXES parameters is set to true.
REASON: When this param is set to TRUE, INVISIBLE indexes ARE used by optimizer.
INCORRECT E. Both the indexes are created and used by the optimizer for queries on the
ORDERS table.
REASON: If so – what is function of “INVISIBLE” clause while creating index.
So I believe the question is wrong – it should have asked for only one correct answer not two.
just saw “fj’s” comments and then re-read “smbd.smth” comments :
SO: if there is any such option as “F” on the exam, then the answer would be BF.
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>
Option F: Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for queries on the ORDERS table.
and Option B.
BF
BF
F –> Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer for queries on the ORDERS table.