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 COUNT(*) FROM CUSTOMERS WHERE CUST_CREDIT_LIMIT=11000;
CASE 1:
Before running the query, lets check what kind of data distribution having CUST_CREDIT_LIMIT column.
SQL> SELECT CUST_CREDIT_LIMIT, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CREDIT_LIMIT;
CUST_CREDIT_LIMIT COUNT(*)
----------------- ----------
11000 2935
9000 9093
5000 7724
10000 5935
15000 1870
3000 7975
1500 11334
7000 8634
8 rows selected.
SQL>
Run the query and check the result. There is no index on CUSTOMERS.CUST_CREDIT_LIMIT column.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.39
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.70
SQL>
SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_CREDIT_LIMIT=11000;
Elapsed: 00:00:00.95
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 407 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 6938 | 27752 | 407 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_CREDIT_LIMIT"=11000)
Statistics
----------------------------------------------------------
480 recursive calls
0 db block gets
1614 consistent gets
1473 physical reads
0 redo size
527 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Observation : Doing FTS as expected. COST is high.
Lets add a B*tree index on CUSTOMERS.CUST_CREDIT_LIMIT and run the same query and observe.
SQL> CREATE INDEX CUST_CREDIT_LIMIT_IDX ON CUSTOMERS(CUST_CREDIT_LIMIT) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:01.77
SQL>
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.96
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.65
SQL>
SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_CREDIT_LIMIT=11000;
Elapsed: 00:00:00.74
Execution Plan
----------------------------------------------------------
Plan hash value: 511918857
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 14 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| CUST_CREDIT_LIMIT_IDX | 6938 | 27752 | 14 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_CREDIT_LIMIT"=11000)
Statistics
----------------------------------------------------------
541 recursive calls
0 db block gets
176 consistent gets
42 physical reads
0 redo size
527 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Observation : Now optimizer using new index, and doing INDEX RANGE SCAN, which is better than FTS. Look at the COST/Physical/Consistent reads etc....
Now drop B*tree index, and create a Bitmap index on same column and run the same query...
SQL> DROP INDEX CUST_CREDIT_LIMIT_IDX;
Index dropped.
Elapsed: 00:00:02.68
SQL>
SQL> CREATE BITMAP INDEX CUST_CREDIT_LIMIT_IDX ON CUSTOMERS(CUST_CREDIT_LIMIT) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:01.04
SQL>
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.74
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.59
SQL>
SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_CREDIT_LIMIT=11000;
Elapsed: 00:00:00.54
Execution Plan
----------------------------------------------------------
Plan hash value: 1136234836
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | BITMAP CONVERSION COUNT | | 6938 | 27752 | 1 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| CUST_CREDIT_LIMIT_IDX | | | | |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUST_CREDIT_LIMIT"=11000)
Statistics
----------------------------------------------------------
541 recursive calls
0 db block gets
170 consistent gets
31 physical reads
0 redo size
527 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Observation : Now optimizer started using new index and doing BITMAP INDEX SINGLE VALUE, which is better than all above paths. Which is idle in case of repeated data column.
CASE 2 :
Lets run below query and observer the plans....
SELECT cust_id,cust_last_name FROM CUSTOMERS WHERE LOWER(CUST_FIRST_NAME) LIKE 'abigail';
SQL> SELECT cust_id,cust_last_name FROM CUSTOMERS WHERE LOWER(CUST_FIRST_NAME) LIKE 'abigail';
65 rows selected.
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 555 | 11100 | 406 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 555 | 11100 | 406 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LOWER("CUST_FIRST_NAME")='abigail')
Statistics
----------------------------------------------------------
520 recursive calls
0 db block gets
1625 consistent gets
1461 physical reads
0 redo size
2059 bytes sent via SQL*Net to client
563 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
65 rows processed
Observation: As expected, Optimizer doing FTS as there is no index on predicate. COST is high.
Lets create B*tree index and rerun the same query, observer the result.
SQL> CREATE INDEX CUST_FIRST_NAME_IDX ON CUSTOMERS(CUST_FIRST_NAME) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.45
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.51
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:13.77
SQL> SELECT cust_id,cust_last_name FROM CUSTOMERS WHERE LOWER(CUST_FIRST_NAME) LIKE 'abigail';
65 rows selected.
Elapsed: 00:00:01.23
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 555 | 11100 | 406 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 555 | 11100 | 406 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LOWER("CUST_FIRST_NAME")='abigail')
Statistics
----------------------------------------------------------
581 recursive calls
0 db block gets
1638 consistent gets
1474 physical reads
0 redo size
2059 bytes sent via SQL*Net to client
563 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
65 rows processed
Observation : Optimizer not using new index, because predicate having function around it.
Lets drop existing B*tree index, and create new function based index like below and rerun the query.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.14
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:08.74
SQL> CREATE INDEX CUST_FIRST_NAME_IDX ON CUSTOMERS(LOWER(CUST_FIRST_NAME)) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.74
SQL> SELECT cust_id,cust_last_name FROM CUSTOMERS WHERE LOWER(CUST_FIRST_NAME) LIKE 'abigail';
65 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 78262457
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 555 | 13875 | 29 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 555 | 13875 | 29 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_FIRST_NAME_IDX | 222 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("CUST_FIRST_NAME")='abigail')
Statistics
----------------------------------------------------------
44 recursive calls
0 db block gets
22 consistent gets
4 physical reads
0 redo size
2059 bytes sent via SQL*Net to client
563 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
65 rows processed
SQL>
Observation: Optimizer started using new function based index, now COST is very low as compared to all above runs.
CASE 3:
Table T1 having only primary key (PROD_ID) index, no index on CUST_ID column. Execute following query and observe....
SQL> SELECT COUNT(*) FROM T1 WHERE CUST_ID=2865;
Elapsed: 00:00:01.82
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6155 (2)| 00:01:14 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T1 | 651 | 3255 | 6155 (2)| 00:01:14 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_ID"=2865)
Statistics
----------------------------------------------------------
372 recursive calls
0 db block gets
22183 consistent gets
22103 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
Observation : Doing FTS, which is expected. COST is high.
Create concatenated index on T1(PROD_ID,CUST_ID), and run the same query...
SQL> CREATE INDEX T1_COMB_IDX ON T1(PROD_ID,CUST_ID) NOLOGGING COMPUTE STATISTIC
Index created.
Elapsed: 00:00:18.36
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.45
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.49
SQL> SELECT COUNT(*) FROM T1 WHERE CUST_ID=2865;
Elapsed: 00:00:01.71
Execution Plan
----------------------------------------------------------
Plan hash value: 572263824
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 75 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX SKIP SCAN| T1_COMB_IDX | 651 | 3255 | 75 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_ID"=2865)
filter("CUST_ID"=2865)
Statistics
----------------------------------------------------------
454 recursive calls
0 db block gets
283 consistent gets
215 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Observation : Doing INDEX SKIP SCAN as leading column (PROD_ID) not using in predicate.
-Asifkhan
Lets get started with below simple query.
SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_CREDIT_LIMIT=11000;
CASE 1:
Before running the query, lets check what kind of data distribution having CUST_CREDIT_LIMIT column.
SQL> SELECT CUST_CREDIT_LIMIT, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CREDIT_LIMIT;
CUST_CREDIT_LIMIT COUNT(*)
----------------- ----------
11000 2935
9000 9093
5000 7724
10000 5935
15000 1870
3000 7975
1500 11334
7000 8634
8 rows selected.
SQL>
Run the query and check the result. There is no index on CUSTOMERS.CUST_CREDIT_LIMIT column.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.39
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.70
SQL>
SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_CREDIT_LIMIT=11000;
Elapsed: 00:00:00.95
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 407 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 6938 | 27752 | 407 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_CREDIT_LIMIT"=11000)
Statistics
----------------------------------------------------------
480 recursive calls
0 db block gets
1614 consistent gets
1473 physical reads
0 redo size
527 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Observation : Doing FTS as expected. COST is high.
Lets add a B*tree index on CUSTOMERS.CUST_CREDIT_LIMIT and run the same query and observe.
SQL> CREATE INDEX CUST_CREDIT_LIMIT_IDX ON CUSTOMERS(CUST_CREDIT_LIMIT) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:01.77
SQL>
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.96
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.65
SQL>
SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_CREDIT_LIMIT=11000;
Elapsed: 00:00:00.74
Execution Plan
----------------------------------------------------------
Plan hash value: 511918857
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 14 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| CUST_CREDIT_LIMIT_IDX | 6938 | 27752 | 14 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_CREDIT_LIMIT"=11000)
Statistics
----------------------------------------------------------
541 recursive calls
0 db block gets
176 consistent gets
42 physical reads
0 redo size
527 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Observation : Now optimizer using new index, and doing INDEX RANGE SCAN, which is better than FTS. Look at the COST/Physical/Consistent reads etc....
Now drop B*tree index, and create a Bitmap index on same column and run the same query...
SQL> DROP INDEX CUST_CREDIT_LIMIT_IDX;
Index dropped.
Elapsed: 00:00:02.68
SQL>
SQL> CREATE BITMAP INDEX CUST_CREDIT_LIMIT_IDX ON CUSTOMERS(CUST_CREDIT_LIMIT) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:01.04
SQL>
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.74
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.59
SQL>
SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_CREDIT_LIMIT=11000;
Elapsed: 00:00:00.54
Execution Plan
----------------------------------------------------------
Plan hash value: 1136234836
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | BITMAP CONVERSION COUNT | | 6938 | 27752 | 1 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| CUST_CREDIT_LIMIT_IDX | | | | |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUST_CREDIT_LIMIT"=11000)
Statistics
----------------------------------------------------------
541 recursive calls
0 db block gets
170 consistent gets
31 physical reads
0 redo size
527 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Observation : Now optimizer started using new index and doing BITMAP INDEX SINGLE VALUE, which is better than all above paths. Which is idle in case of repeated data column.
CASE 2 :
Lets run below query and observer the plans....
SELECT cust_id,cust_last_name FROM CUSTOMERS WHERE LOWER(CUST_FIRST_NAME) LIKE 'abigail';
SQL> SELECT cust_id,cust_last_name FROM CUSTOMERS WHERE LOWER(CUST_FIRST_NAME) LIKE 'abigail';
65 rows selected.
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 555 | 11100 | 406 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 555 | 11100 | 406 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LOWER("CUST_FIRST_NAME")='abigail')
Statistics
----------------------------------------------------------
520 recursive calls
0 db block gets
1625 consistent gets
1461 physical reads
0 redo size
2059 bytes sent via SQL*Net to client
563 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
65 rows processed
Observation: As expected, Optimizer doing FTS as there is no index on predicate. COST is high.
Lets create B*tree index and rerun the same query, observer the result.
SQL> CREATE INDEX CUST_FIRST_NAME_IDX ON CUSTOMERS(CUST_FIRST_NAME) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.45
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.51
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:13.77
SQL> SELECT cust_id,cust_last_name FROM CUSTOMERS WHERE LOWER(CUST_FIRST_NAME) LIKE 'abigail';
65 rows selected.
Elapsed: 00:00:01.23
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 555 | 11100 | 406 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 555 | 11100 | 406 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LOWER("CUST_FIRST_NAME")='abigail')
Statistics
----------------------------------------------------------
581 recursive calls
0 db block gets
1638 consistent gets
1474 physical reads
0 redo size
2059 bytes sent via SQL*Net to client
563 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
65 rows processed
Observation : Optimizer not using new index, because predicate having function around it.
Lets drop existing B*tree index, and create new function based index like below and rerun the query.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.14
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:08.74
SQL> CREATE INDEX CUST_FIRST_NAME_IDX ON CUSTOMERS(LOWER(CUST_FIRST_NAME)) NOLOGGING COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:00.74
SQL> SELECT cust_id,cust_last_name FROM CUSTOMERS WHERE LOWER(CUST_FIRST_NAME) LIKE 'abigail';
65 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 78262457
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 555 | 13875 | 29 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 555 | 13875 | 29 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_FIRST_NAME_IDX | 222 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("CUST_FIRST_NAME")='abigail')
Statistics
----------------------------------------------------------
44 recursive calls
0 db block gets
22 consistent gets
4 physical reads
0 redo size
2059 bytes sent via SQL*Net to client
563 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
65 rows processed
SQL>
Observation: Optimizer started using new function based index, now COST is very low as compared to all above runs.
CASE 3:
Table T1 having only primary key (PROD_ID) index, no index on CUST_ID column. Execute following query and observe....
SQL> SELECT COUNT(*) FROM T1 WHERE CUST_ID=2865;
Elapsed: 00:00:01.82
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6155 (2)| 00:01:14 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T1 | 651 | 3255 | 6155 (2)| 00:01:14 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_ID"=2865)
Statistics
----------------------------------------------------------
372 recursive calls
0 db block gets
22183 consistent gets
22103 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
Observation : Doing FTS, which is expected. COST is high.
Create concatenated index on T1(PROD_ID,CUST_ID), and run the same query...
SQL> CREATE INDEX T1_COMB_IDX ON T1(PROD_ID,CUST_ID) NOLOGGING COMPUTE STATISTIC
Index created.
Elapsed: 00:00:18.36
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.45
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.49
SQL> SELECT COUNT(*) FROM T1 WHERE CUST_ID=2865;
Elapsed: 00:00:01.71
Execution Plan
----------------------------------------------------------
Plan hash value: 572263824
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 75 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX SKIP SCAN| T1_COMB_IDX | 651 | 3255 | 75 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_ID"=2865)
filter("CUST_ID"=2865)
Statistics
----------------------------------------------------------
454 recursive calls
0 db block gets
283 consistent gets
215 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Observation : Doing INDEX SKIP SCAN as leading column (PROD_ID) not using in predicate.
-Asifkhan
No comments:
Post a Comment