On your Oracle Database, you issue the following commands to create indexes:
SQL > CREATE INDEX oe.ord_customer_ix1 ON oe.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.
F.
Both the indexes are created: however, only ORD_CUSTOMERS_IX2 is used by the optimizer
for queries on the ORDERS table.
Explanation:
Not A: Both indexes are created fine.
B: The invisible index ORD_CUSTOMERS_IX1 and the bitmap index are both updated by DML
operations on the Orders table.
F: Since ORD_CUSTOMERS_IX1 is invisible only ORD_CUSTOMERS_IX2 is used by the query
optimizer.
Not C,Not D,Not E:
* ord_customer_ix1 is an invisible index and is therefore not used by the optimizer.
* 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.
Note: 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.
B – TRUE
D – TRUE
OPTIMIZER_USE_INVISIBLE_INDEXES enables or disables the use of invisible indexes.
Values
true
Invisible indexes are treated as visible (normal) indexes.
false – default
Invisible indexes will not be considered by the optimizer but will still be maintained by DML operations.
http://docs.oracle.com/database/121/REFRN/refrn10301.htm#REFRN10301
D – FALSE:
[oracle@virt12c ~]$ sqlplus sys/kolobok123@virt12c:1521/sphere as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 25 10:46:16 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create table T1 as select * from dba_objects
/
create index t1_i1 on T1 (object_id, object_name) invisible
2 /
create bitmap index t1_i2 on T1 (object_id,object_name)
/
set autot traceonly
select object_id, data_object_id from T1 where object_id=47
/
alter session set optimizer_use_invisible_indexes = true
/
select object_id, data_object_id from T1 where object_id=47
/
Table created.
SQL> 2
Index created.
SQL> 2
Index created.
SQL> SQL> SQL> 2
Execution Plan
———————————————————-
Plan hash value: 2285408266
——————————————————————————–
————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
——————————————————————————–
————-
| 0 | SELECT STATEMENT | | 1 | 26 | 168 (0
)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 26 | 168 (0
)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | |
| |
|* 3 | BITMAP INDEX RANGE SCAN | T1_I2 | | |
| |
——————————————————————————–
————-
Predicate Information (identified by operation id):
—————————————————
3 – access(“OBJECT_ID”=47)
filter(“OBJECT_ID”=47)
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
11 recursive calls
0 db block gets
84 consistent gets
348 physical reads
0 redo size
622 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> 2
Session altered.
SQL> 2
Execution Plan
———————————————————-
Plan hash value: 3320414027
——————————————————————————–
————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
——————————————————————————–
————-
| 0 | SELECT STATEMENT | | 17 | 442 | 3 (0
)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 17 | 442 | 3 (0
)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 17 | | 2 (0
)| 00:00:01 |
——————————————————————————–
————-
Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJECT_ID”=47)
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
5 recursive calls
0 db block gets
78 consistent gets
1 physical reads
0 redo size
622 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
So what is your answer?
Correct, B and F are correct
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>
BF
B and F appears to be correct
B F are true
BF – OK tested
but when is only one invisible then get ;/
BF
An invisible index is an alternative to making an index unusable or even to drop it. An invisible index is maintained for any DML operation but is not used by the optimizer unless you explicitly specify the index with a hint. Applications often have to be modified without being able to bring the complete Application offline. Create invisible indexes temporarily for specialized non-standard operations, such as online application upgrades, without affecting the behavior of any existing application. Furthermore, invisible indexes can be used to test the removal of an index without dropping it right away, thus enabling a grace period for testing in production environments
Note that both the indexes are created on the same set of columns.
ord_sustomet_ix1 is invisible and ord_customet_ix2 is visible.
Even though you can create multiple indexes on the same set of columns, only one index is visible at a given time. The optimizer cannot choose the best among the multiple indexes. The optimizer can only see the visible index and ends up using (or not using) the only visible index, just as in the previous database releases. So, Choice F is correct.
D is incorrect becuase if you set the OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE, the optimizer can consider and use the invisible index.
E is incorrect. C is incorrect. A is also incorrect.
B is correct and F is correct.
Answers: B, F
BF
BF
BF
B,F are correct