Optimizer path

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Mon, 5 Mar 2012 15:44:19 -0500
Message-ID: <304F58144267C5439E733532ABC9A3A1144A1BB9_at_USA0300MS02.na.xerox.net>

Folks,
I have a statement, which produces the following plan, which is based on FTS:   SELECT *   FROM <TABLE_NAME>

WHERE reprocess_flag = 'Y'

   AND processing_phase = 1

   AND processing_status_code2 = 'Running'

   AND processing_mode_code2 = 'Normal'

;  

---

---

| Id  | Operation         | Name                 | Rows  | Bytes | Cost
(%CPU)| Time     |



------------------------------------------------------------------------

------------------


|   0 | SELECT STATEMENT  |                      |   139K|    52M|  5158

(1)| 00:01:02 |

|* 1 | TABLE ACCESS FULL| TXRWI0_WIP_CMPL_INTF | 139K| 52M| 5158 (1)| 00:01:02 |

---

---

 

Predicate Information (identified by operation id):

---

 

   1 - filter("PROCESSING_PHASE"=1 AND "REPROCESS_FLAG"='Y' AND

              "PROCESSING_STATUS_CODE2"='Running' AND "PROCESSING_MODE_CODE2"='Normal')    

When the same statement is run with a little modification in the WHERE clause, it produces a different and more efficient execution plan:

SELECT *   FROM <TABLE_NAME>

WHERE reprocess_flag = 'Y'

   AND processing_phase = 1

   AND upper(processing_status_code2) = 'RUNNING'

   AND upper(processing_mode_code2 )= 'NORMAL'

;  

---

---

| Id  | Operation                   | Name                 | Rows  |
Bytes | Cost (%CPU)| Time     |



------------------------------------------------------------------------

----------------------------


|   0 | SELECT STATEMENT            |                      |     1 |
397 |     4   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| TXRWI0_WIP_CMPL_INTF |     1 |
397 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IXRWI0_WIP_CMPL_IDX5 |     1 |
|     3   (0)| 00:00:01 |



------------------------------------------------------------------------

----------------------------


 

Predicate Information (identified by operation id):

---

 

   1 - filter("REPROCESS_FLAG"='Y')

   2 - access("PROCESSING_PHASE"=1)

       filter(UPPER("PROCESSING_STATUS_CODE2")='RUNNING' AND

              UPPER("PROCESSING_MODE_CODE2")='NORMAL')     Below is the what the index IXRWI0_WIP_CMPL_IDX5 looks like:  

INDEX_NAME                     COLUMN_NAME               COLUMN_POSITION



------------------------------ ------------------------- ---------------


IXRWI0_WIP_CMPL_IDX5           PROCESSING_PHASE                        1

                               PROCESSING_STATUS_CODE2                 2

                               PROCESSING_MODE_CODE2                   3

 

INDEX_NAME                       IND_ROWS DISTINCT_KEYS

CLUSTERING_FACTOR TAB_ROWS TAB_BLOCKS

• ---------- ------------- ----------------- ---------- ----------

IXRWI0_WIP_CMPL_IDX5               281189             1
11220     282400      18882

 

All of the columns of the index have bad selectivity, individually, as well as combined:

select processing_phase, processing_status_code2, processing_mode_code2 , count(*) from txrwi0_wip_cmpl_intf group by

processing_phase, processing_status_code2, processing_mode_code2

;  

PROCESSING_PHASE PROCESSING_STATUS_CODE2 PROCESSING_MODE_CODE2 COUNT(*)

• ------------------------------
  ------------------------------ ----------
  

               4 Completed                      Normal

283934

               1 Completed                      Error

2  

I have the following question:

• When the statement is run with functions in the WHERE clause, the optimizer decided to use the leading column of the index to fetch rows, even though the selectivity of that column was bad. What made the optimizer go that route and why it did not choose it with the first run?

The eventual solution of the problem is to use histograms on column PROCESSING_PHASE, which worked fine.  

Thanks

Amir  

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 05 2012 - 14:44:19 CST