In this article we will observe how oracle optimizer chooses different access paths based upon available information. Here I am using sh.CUSTOMERS table, which has primary key on CUST_ID column.
Lets get started with below simple query.
SELECT *
FROM customers c
WHERE cust_gender = 'M'
AND cust_postal_code = 37400
AND cust_credit_limit = 7000;
CASE 1:
1) Run the query and check the result.
SQL> set timing on
SQL> set autotrace traceonly
SQL> set linesize 200 pagesize 1000
SQL> SELECT *
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 37400
5 AND cust_credit_limit = 7000;
25 rows selected.
Elapsed: 00:00:00.32
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1086 | 407 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 6 | 1086 | 407 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=37400 AND
"CUST_CREDIT_LIMIT"=7000 AND "CUST_GENDER"='M')
Statistics
----------------------------------------------------------
1135 recursive calls
0 db block gets
1747 consistent gets
1477 physical reads
0 redo size
5265 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
25 rows processed
Observation : Optimizer doing FTS, which is costly operation. This is expected result.
CASE 2:
1) Create three B*-tree indexes on the cust_gender,cust_postal_code,cust_credit_limit of CUSTOMERS table columns:
SQL> CREATE INDEX cust_gender_idx ON customers(cust_gender) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.71
SQL>
SQL> CREATE INDEX cust_postal_code_idx ON customers(cust_postal_code) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.09
SQL> CREATE INDEX cust_credit_limit_idx ON customers(cust_credit_limit) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.09
SQL> ALTER INDEX CUSTOMERS_PK MONITORING USAGE;
Index altered.
Elapsed: 00:00:00.12
2) Start monitoring all the CUSTOMERS indexes.
SQL> ALTER INDEX cust_gender_idx MONITORING USAGE;
Index altered.
Elapsed: 00:00:00.01
SQL> ALTER INDEX cust_postal_code_idx MONITORING USAGE;
Index altered.
Elapsed: 00:00:00.01
SQL> ALTER INDEX cust_credit_limit_idx MONITORING USAGE;
Index altered.
Elapsed: 00:00:00.00
3) Check index usage before running any query.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
CUSTOMERS_PK CUSTOMERS YES NO 09/17/2015 13:16:46
CUST_GENDER_IDX CUSTOMERS YES NO 09/17/2015 13:17:09
CUST_POSTAL_CODE_IDX CUSTOMERS YES NO 09/17/2015 13:17:18
CUST_CREDIT_LIMIT_IDX CUSTOMERS YES NO 09/17/2015 13:17:28
4) Now lets run same query and observe the output.
SQL> SELECT *
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 37400
5 AND cust_credit_limit = 7000;
25 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1086 | 407 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 6 | 1086 | 407 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=37400 AND
"CUST_CREDIT_LIMIT"=7000 AND "CUST_GENDER"='M')
Statistics
----------------------------------------------------------
398 recursive calls
0 db block gets
1607 consistent gets
0 physical reads
0 redo size
5265 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
25 rows processed
Observation: No change, still optimizer using same plan, and that is expected.
CASE 3:
1) Force the query to use the index using INDEX hint. Before that flush the shared pool,buffer cache.
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.46
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.30
SQL> SELECT /*+ INDEX(c) */ *
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 37400
5 AND cust_credit_limit = 7000;
25 rows selected.
Elapsed: 00:00:00.35
Execution Plan
----------------------------------------------------------
Plan hash value: 2449359215
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1086 | 218 (1)| 00:00:03 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 6 | 1086 | 218 (1)| 00:00:03 |
|* 2 | INDEX FULL SCAN | CUST_POSTAL_CODE_IDX | 89 | | 134 (1)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_CREDIT_LIMIT"=7000 AND "CUST_GENDER"='M')
2 - filter(TO_NUMBER("CUST_POSTAL_CODE")=37400)
Statistics
----------------------------------------------------------
1423 recursive calls
3 db block gets
710 consistent gets
1116 physical reads
708 redo size
5265 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
25 rows processed
Observation : Now optimizer started using on one index. This cost is lower than FTS.
2) Check index usage......we can observe one index got used now.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- -----------
CUSTOMERS_PK CUSTOMERS YES NO 09/17/2015 13:16:46
CUST_GENDER_IDX CUSTOMERS YES NO 09/17/2015 13:17:09
CUST_POSTAL_CODE_IDX CUSTOMERS YES YES 09/17/2015 13:17:18
CUST_CREDIT_LIMIT_IDX CUSTOMERS YES NO 09/17/2015 13:17:28
SQL>
CASE 4:
1) Now force the optimizer to use multiple indexes to get the data.
SQL> SELECT /*+ INDEX_COMBINE(c) */ *
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 37400
5 AND cust_credit_limit = 7000;
25 rows selected.
Elapsed: 00:00:00.28
Execution Plan
----------------------------------------------------------
Plan hash value: 962581290
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1086 | 466 (1)| 00:00:06 |
|* 1 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 6 | 1086 | 466 (1)| 00:00:06 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | CUST_CREDIT_LIMIT_IDX | | | 14 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | CUST_GENDER_IDX | | | 51 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=37400)
5 - access("CUST_CREDIT_LIMIT"=7000)
7 - access("CUST_GENDER"='M')
Statistics
----------------------------------------------------------
3 recursive calls
6 db block gets
1104 consistent gets
661 physical reads
1244 redo size
5265 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed
Observation: The cost is higher than that from the previous executions.
2) Check index usage......we can observe all three indexes got used.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- -----------
CUSTOMERS_PK CUSTOMERS YES NO 09/17/2015 13:16:46
CUST_GENDER_IDX CUSTOMERS YES YES 09/17/2015 13:17:09
CUST_POSTAL_CODE_IDX CUSTOMERS YES YES 09/17/2015 13:17:18
CUST_CREDIT_LIMIT_IDX CUSTOMERS YES YES 09/17/2015 13:17:28
CASE 5:
1) Create concatenated index on cust_gender,cust_postal_code,cust_credit_limit of CUSTOMERS table columns:
SQL> CREATE INDEX cust_gender_limit_code_idx
2 ON customers(cust_gender,cust_credit_limit,cust_postal_code)
3 NOLOGGING COMPUTE STATISTICS;
SQL>
2) Run query by forcing to use index.
SQL> SELECT /*+ INDEX(c) */ c.*
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 40804
5 AND cust_credit_limit = 10000;
6 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2871279522
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1267 | 18 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 7 | 1267 | 18 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_GENDER_LIMIT_CODE_IDX | 6 | | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_GENDER"='M' AND "CUST_CREDIT_LIMIT"=10000)
filter(TO_NUMBER("CUST_POSTAL_CODE")=40804)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
22 consistent gets
16 physical reads
0 redo size
3369 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
CASE 6:
Observe the access path what optimizer using against primary key column.....
SQL> SELECT c.*
2 FROM customers c
3 WHERE cust_id IN (88340,104590,44910);
Elapsed: 00:00:00.34
Execution Plan
----------------------------------------------------------
Plan hash value: 293792914
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 543 | 7 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 3 | 543 | 7 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 3 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUST_ID"=44910 OR "CUST_ID"=88340 OR "CUST_ID"=104590)
Statistics
----------------------------------------------------------
1387 recursive calls
0 db block gets
358 consistent gets
48 physical reads
0 redo size
2394 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
CASE 7:
Execute the following query and observe the access path....
SELECT c.cust_last_name
, c.cust_first_name
FROM customers c;
SQL> SELECT c.cust_last_name
2 , c.cust_first_name
3 FROM customers c;
55500 rows selected.
Elapsed: 00:00:00.53
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 812K| 405 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 812K| 405 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
362 recursive calls
0 db block gets
5194 consistent gets
1447 physical reads
0 redo size
1073099 bytes sent via SQL*Net to client
41208 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
55500 rows processed
SQL>
Observation: Doing FTS, as expected.
2) Lets create concatenated B*tree index on cust_last_name,cust_last_name of CUSTOMERS table and run the same query.
SQL> CREATE INDEX cust_last_first_name_idx ON customers(cust_last_name,cust_first_name) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.07
SQL>
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.14
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.20
SQL>
SQL> SELECT c.cust_last_name
2 , c.cust_first_name
3 FROM customers c;
55500 rows selected.
Elapsed: 00:00:00.56
Execution Plan
----------------------------------------------------------
Plan hash value: 445338993
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 812K| 55 (2)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| CUST_LAST_FIRST_NAME_IDX | 55500 | 812K| 55 (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
585 recursive calls
0 db block gets
4065 consistent gets
215 physical reads
0 redo size
999538 bytes sent via SQL*Net to client
41208 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
55500 rows processed
SQL>
Observation : Doing INDEX FAST FULL SCAN, and it is better than FTS. Cost/physical/consistent reads are good.
CASE 8:
Lets drop concatenated index and create separate indexes on both the columns and run the query and observe the output.
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.46
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.17
SQL> CREATE INDEX cust_last_name_idx ON customers(cust_last_name) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:01.74
SQL> CREATE INDEX cust_first_name_idx ON customers(cust_first_name) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.12
SQL>
SQL> SELECT c.cust_last_name
2 , c.cust_first_name
3 FROM customers c;
55500 rows selected.
Elapsed: 00:00:00.56
Execution Plan
----------------------------------------------------------
Plan hash value: 410003550
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 812K| 284 (2)| 00:00:04 |
| 1 | VIEW | index$_join$_001 | 55500 | 812K| 284 (2)| 00:00:04 |
|* 2 | HASH JOIN | | | | | |
| 3 | INDEX FAST FULL SCAN| CUST_FIRST_NAME_IDX | 55500 | 812K| 175 (1)| 00:00:03 |
| 4 | INDEX FAST FULL SCAN| CUST_LAST_NAME_IDX | 55500 | 812K| 179 (1)| 00:00:03 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3989 consistent gets
279 physical reads
0 redo size
1018730 bytes sent via SQL*Net to client
41208 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
55500 rows processed
SQL>
Observation : Even though it is uses both the indexes, still cost is high as compared to previous plan.
CASE 9:
Now drop existing indexes except primary key index and create concatenated bitmap index on cust_last_name,cust_first_name of CUSTOMERS table. And run the same query and observe the output.
SQL> CREATE BITMAP INDEX cust_last_first_idx ON customers(cust_last_name,cust_first_name) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.09
SQL>
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.34
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.18
SQL>
SQL> SELECT c.cust_last_name
2 , c.cust_first_name
3 FROM customers c;
55500 rows selected.
Elapsed: 00:00:00.31
Execution Plan
----------------------------------------------------------
Plan hash value: 72997954
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 812K| 27 (0)| 00:00:01 |
| 1 | BITMAP CONVERSION TO ROWIDS | | 55500 | 812K| 27 (0)| 00:00:01 |
| 2 | BITMAP INDEX FAST FULL SCAN| CUST_LAST_FIRST_IDX | | | | |
----------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
562 recursive calls
0 db block gets
3757 consistent gets
52 physical reads
0 redo size
999538 bytes sent via SQL*Net to client
41208 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
55500 rows processed
SQL>
Observation: It is doing BITMAP INDEX FAST FULL SCAN, and cost is better than any other previous steps.
-Asifkhan
Lets get started with below simple query.
SELECT *
FROM customers c
WHERE cust_gender = 'M'
AND cust_postal_code = 37400
AND cust_credit_limit = 7000;
CASE 1:
1) Run the query and check the result.
SQL> set timing on
SQL> set autotrace traceonly
SQL> set linesize 200 pagesize 1000
SQL> SELECT *
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 37400
5 AND cust_credit_limit = 7000;
25 rows selected.
Elapsed: 00:00:00.32
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1086 | 407 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 6 | 1086 | 407 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=37400 AND
"CUST_CREDIT_LIMIT"=7000 AND "CUST_GENDER"='M')
Statistics
----------------------------------------------------------
1135 recursive calls
0 db block gets
1747 consistent gets
1477 physical reads
0 redo size
5265 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
25 rows processed
Observation : Optimizer doing FTS, which is costly operation. This is expected result.
CASE 2:
1) Create three B*-tree indexes on the cust_gender,cust_postal_code,cust_credit_limit of CUSTOMERS table columns:
SQL> CREATE INDEX cust_gender_idx ON customers(cust_gender) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.71
SQL>
SQL> CREATE INDEX cust_postal_code_idx ON customers(cust_postal_code) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.09
SQL> CREATE INDEX cust_credit_limit_idx ON customers(cust_credit_limit) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.09
SQL> ALTER INDEX CUSTOMERS_PK MONITORING USAGE;
Index altered.
Elapsed: 00:00:00.12
2) Start monitoring all the CUSTOMERS indexes.
SQL> ALTER INDEX cust_gender_idx MONITORING USAGE;
Index altered.
Elapsed: 00:00:00.01
SQL> ALTER INDEX cust_postal_code_idx MONITORING USAGE;
Index altered.
Elapsed: 00:00:00.01
SQL> ALTER INDEX cust_credit_limit_idx MONITORING USAGE;
Index altered.
Elapsed: 00:00:00.00
3) Check index usage before running any query.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
CUSTOMERS_PK CUSTOMERS YES NO 09/17/2015 13:16:46
CUST_GENDER_IDX CUSTOMERS YES NO 09/17/2015 13:17:09
CUST_POSTAL_CODE_IDX CUSTOMERS YES NO 09/17/2015 13:17:18
CUST_CREDIT_LIMIT_IDX CUSTOMERS YES NO 09/17/2015 13:17:28
SQL> SELECT *
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 37400
5 AND cust_credit_limit = 7000;
25 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1086 | 407 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 6 | 1086 | 407 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=37400 AND
"CUST_CREDIT_LIMIT"=7000 AND "CUST_GENDER"='M')
Statistics
----------------------------------------------------------
398 recursive calls
0 db block gets
1607 consistent gets
0 physical reads
0 redo size
5265 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
25 rows processed
Observation: No change, still optimizer using same plan, and that is expected.
CASE 3:
1) Force the query to use the index using INDEX hint. Before that flush the shared pool,buffer cache.
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.46
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.30
SQL> SELECT /*+ INDEX(c) */ *
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 37400
5 AND cust_credit_limit = 7000;
25 rows selected.
Elapsed: 00:00:00.35
Execution Plan
----------------------------------------------------------
Plan hash value: 2449359215
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1086 | 218 (1)| 00:00:03 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 6 | 1086 | 218 (1)| 00:00:03 |
|* 2 | INDEX FULL SCAN | CUST_POSTAL_CODE_IDX | 89 | | 134 (1)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_CREDIT_LIMIT"=7000 AND "CUST_GENDER"='M')
2 - filter(TO_NUMBER("CUST_POSTAL_CODE")=37400)
Statistics
----------------------------------------------------------
1423 recursive calls
3 db block gets
710 consistent gets
1116 physical reads
708 redo size
5265 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
25 rows processed
Observation : Now optimizer started using on one index. This cost is lower than FTS.
2) Check index usage......we can observe one index got used now.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- -----------
CUSTOMERS_PK CUSTOMERS YES NO 09/17/2015 13:16:46
CUST_GENDER_IDX CUSTOMERS YES NO 09/17/2015 13:17:09
CUST_POSTAL_CODE_IDX CUSTOMERS YES YES 09/17/2015 13:17:18
CUST_CREDIT_LIMIT_IDX CUSTOMERS YES NO 09/17/2015 13:17:28
SQL>
CASE 4:
1) Now force the optimizer to use multiple indexes to get the data.
SQL> SELECT /*+ INDEX_COMBINE(c) */ *
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 37400
5 AND cust_credit_limit = 7000;
25 rows selected.
Elapsed: 00:00:00.28
Execution Plan
----------------------------------------------------------
Plan hash value: 962581290
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1086 | 466 (1)| 00:00:06 |
|* 1 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 6 | 1086 | 466 (1)| 00:00:06 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | CUST_CREDIT_LIMIT_IDX | | | 14 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | CUST_GENDER_IDX | | | 51 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=37400)
5 - access("CUST_CREDIT_LIMIT"=7000)
7 - access("CUST_GENDER"='M')
Statistics
----------------------------------------------------------
3 recursive calls
6 db block gets
1104 consistent gets
661 physical reads
1244 redo size
5265 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed
Observation: The cost is higher than that from the previous executions.
2) Check index usage......we can observe all three indexes got used.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- -----------
CUSTOMERS_PK CUSTOMERS YES NO 09/17/2015 13:16:46
CUST_GENDER_IDX CUSTOMERS YES YES 09/17/2015 13:17:09
CUST_POSTAL_CODE_IDX CUSTOMERS YES YES 09/17/2015 13:17:18
CUST_CREDIT_LIMIT_IDX CUSTOMERS YES YES 09/17/2015 13:17:28
CASE 5:
1) Create concatenated index on cust_gender,cust_postal_code,cust_credit_limit of CUSTOMERS table columns:
SQL> CREATE INDEX cust_gender_limit_code_idx
2 ON customers(cust_gender,cust_credit_limit,cust_postal_code)
3 NOLOGGING COMPUTE STATISTICS;
SQL>
2) Run query by forcing to use index.
SQL> SELECT /*+ INDEX(c) */ c.*
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 40804
5 AND cust_credit_limit = 10000;
6 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2871279522
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1267 | 18 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 7 | 1267 | 18 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_GENDER_LIMIT_CODE_IDX | 6 | | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_GENDER"='M' AND "CUST_CREDIT_LIMIT"=10000)
filter(TO_NUMBER("CUST_POSTAL_CODE")=40804)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
22 consistent gets
16 physical reads
0 redo size
3369 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
CASE 6:
Observe the access path what optimizer using against primary key column.....
SQL> SELECT c.*
2 FROM customers c
3 WHERE cust_id IN (88340,104590,44910);
Elapsed: 00:00:00.34
Execution Plan
----------------------------------------------------------
Plan hash value: 293792914
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 543 | 7 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 3 | 543 | 7 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 3 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUST_ID"=44910 OR "CUST_ID"=88340 OR "CUST_ID"=104590)
Statistics
----------------------------------------------------------
1387 recursive calls
0 db block gets
358 consistent gets
48 physical reads
0 redo size
2394 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
CASE 7:
Execute the following query and observe the access path....
SELECT c.cust_last_name
, c.cust_first_name
FROM customers c;
SQL> SELECT c.cust_last_name
2 , c.cust_first_name
3 FROM customers c;
55500 rows selected.
Elapsed: 00:00:00.53
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 812K| 405 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 812K| 405 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
362 recursive calls
0 db block gets
5194 consistent gets
1447 physical reads
0 redo size
1073099 bytes sent via SQL*Net to client
41208 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
55500 rows processed
SQL>
Observation: Doing FTS, as expected.
2) Lets create concatenated B*tree index on cust_last_name,cust_last_name of CUSTOMERS table and run the same query.
SQL> CREATE INDEX cust_last_first_name_idx ON customers(cust_last_name,cust_first_name) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.07
SQL>
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.14
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.20
SQL>
SQL> SELECT c.cust_last_name
2 , c.cust_first_name
3 FROM customers c;
55500 rows selected.
Elapsed: 00:00:00.56
Execution Plan
----------------------------------------------------------
Plan hash value: 445338993
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 812K| 55 (2)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| CUST_LAST_FIRST_NAME_IDX | 55500 | 812K| 55 (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
585 recursive calls
0 db block gets
4065 consistent gets
215 physical reads
0 redo size
999538 bytes sent via SQL*Net to client
41208 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
55500 rows processed
SQL>
Observation : Doing INDEX FAST FULL SCAN, and it is better than FTS. Cost/physical/consistent reads are good.
CASE 8:
Lets drop concatenated index and create separate indexes on both the columns and run the query and observe the output.
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.46
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.17
SQL> CREATE INDEX cust_last_name_idx ON customers(cust_last_name) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:01.74
SQL> CREATE INDEX cust_first_name_idx ON customers(cust_first_name) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.12
SQL>
SQL> SELECT c.cust_last_name
2 , c.cust_first_name
3 FROM customers c;
55500 rows selected.
Elapsed: 00:00:00.56
Execution Plan
----------------------------------------------------------
Plan hash value: 410003550
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 812K| 284 (2)| 00:00:04 |
| 1 | VIEW | index$_join$_001 | 55500 | 812K| 284 (2)| 00:00:04 |
|* 2 | HASH JOIN | | | | | |
| 3 | INDEX FAST FULL SCAN| CUST_FIRST_NAME_IDX | 55500 | 812K| 175 (1)| 00:00:03 |
| 4 | INDEX FAST FULL SCAN| CUST_LAST_NAME_IDX | 55500 | 812K| 179 (1)| 00:00:03 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3989 consistent gets
279 physical reads
0 redo size
1018730 bytes sent via SQL*Net to client
41208 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
55500 rows processed
SQL>
Observation : Even though it is uses both the indexes, still cost is high as compared to previous plan.
CASE 9:
Now drop existing indexes except primary key index and create concatenated bitmap index on cust_last_name,cust_first_name of CUSTOMERS table. And run the same query and observe the output.
SQL> CREATE BITMAP INDEX cust_last_first_idx ON customers(cust_last_name,cust_first_name) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.09
SQL>
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.34
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.18
SQL>
SQL> SELECT c.cust_last_name
2 , c.cust_first_name
3 FROM customers c;
55500 rows selected.
Elapsed: 00:00:00.31
Execution Plan
----------------------------------------------------------
Plan hash value: 72997954
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55500 | 812K| 27 (0)| 00:00:01 |
| 1 | BITMAP CONVERSION TO ROWIDS | | 55500 | 812K| 27 (0)| 00:00:01 |
| 2 | BITMAP INDEX FAST FULL SCAN| CUST_LAST_FIRST_IDX | | | | |
----------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
562 recursive calls
0 db block gets
3757 consistent gets
52 physical reads
0 redo size
999538 bytes sent via SQL*Net to client
41208 bytes received via SQL*Net from client
3701 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
55500 rows processed
SQL>
Observation: It is doing BITMAP INDEX FAST FULL SCAN, and cost is better than any other previous steps.
-Asifkhan
No comments:
Post a Comment