In addition to the previous notes, indexes containing a column name fragment
"STAT" beg to be considered for considering the "DONE" status value to be
defined as null. Your programs may need to be aware of this change if made
to the existing status, but:
An alternative that seems especially likely in your case, since you are
already using a function based index, is changing the "DONE" status to null
in the function or having a virtual column where "DONE" is converted to null
and using the virtual column in the function definition.
For a multi-column index you'll need to convert the other column values to
null as well IN THE FUNCTIONAL INDEX if you want all the "DONE" status
values to disappear from the index. You'll want to code that up in a way the
null of status converts to null other column before other functions on the
column are made. I'm not sure whether it is easier to code up as virtual
columns or in the index specification; my experiments on using either don't
really differentiate a best solution on that for index efficiency (the big
deal is all null values dropping from the index). Virtual columns do have
the value of sometimes being useful quite apart from the particular
functional index.
None of this is to do with the "likely bug" advice, which might also solve
your problem. Is the "DESC" actually of high value in some context as you
use your system?
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Tuesday, August 25, 2015 2:17 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Function Based Index lower cost then FTS but performs worse
There are a couple of bugs that appear when you have indexes with descending
columns (possibly it's only indexes that START with descending) on a table.
There's an example here
http://jonathanlewis.wordpress.com/2015/01/12/fbi-bug-reprise/
<https://jonathanlewis.wordpress.com/2015/01/12/fbi-bug-reprise/> of how
the wrong index can influence the optimizer's strategy, but your example
looks more like the basis issue of how the optimizer does arithmetic with
the sys_op_descend() column - as described here:
https://jonathanlewis.wordpress.com/2015/07/17/descending-indexes/
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Chris Grabowy [cgrabowy@xxxxxxxxx]
Sent: 24 August 2015 23:43
To: oracle-l@xxxxxxxxxxxxx
Subject: Function Based Index lower cost then FTS but performs worse
A developer identified some SQL that is having a performance issue on an
Oracle 11.2.0.3 database.
I created an explain plan for the SQL.
############################################################################
######################################################
SELECT count(*)
FROM CLAIMS fc
WHERE fc.CLAIM_PAYMENT_STATUS = '02'
AND fc.PLACE_OF_SERVICE IN ('11', '12', '24', '22')
AND fc.SERVICE_FROM_DATE BETWEEN '01-JUN-2013' AND '31-MAY-2014';
Execution Plan
----------------------------------------------------------
Plan hash value: 689374613
----------------------------------------------------------------------------
-------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 14 | 5 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | |
1 | 14 | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| CLAIMS |
3668K| 48M| 5 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | CLAIMS_STAT_ORIG_XBT |
1 | | 4 (0)| 00:00:01 | | |
----------------------------------------------------------------------------
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FC"."SERVICE_FROM_DATE">=TO_DATE(' 2013-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
("FC"."PLACE_OF_SERVICE"='11' OR "FC"."PLACE_OF_SERVICE"='12'
OR "FC"."PLACE_OF_SERVICE"='22' OR
"FC"."PLACE_OF_SERVICE"='24') AND
"FC"."SERVICE_FROM_DATE"<=TO_DATE(' 2014-05-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
3 - access(SYS_OP_DESCEND("CLAIM_PAYMENT_STATUS")=HEXTORAW('CFCDFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CLAIM_PAYMENT_STATUS"))='02')
############################################################################
######################################################
I created an explain plan for the SQL with a full table scan hint.
############################################################################
######################################################
SELECT /*+ FULL(fc) */ count(*)
FROM CLAIMS fc
WHERE fc.CLAIM_PAYMENT_STATUS = '02'
AND fc.PLACE_OF_SERVICE IN ('11', '12', '24', '22')
AND fc.SERVICE_FROM_DATE BETWEEN '01-JUN-2013' AND '31-MAY-2014';
Execution Plan
----------------------------------------------------------
Plan hash value: 321060423
----------------------------------------------------------------------------
--------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop |
----------------------------------------------------------------------------
--------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2762K (1)|
09:12:34 | | |
| 1 | SORT AGGREGATE | | 1 | 14 | |
| | |
| 2 | PARTITION RANGE ALL| | 3668K| 48M| 2762K (1)|
09:12:34 | 1 | 42 |
|* 3 | TABLE ACCESS FULL | CLAIMS | 3668K| 48M| 2762K (1)|
09:12:34 | 1 | 42 |
----------------------------------------------------------------------------
--------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("FC"."SERVICE_FROM_DATE">=TO_DATE(' 2013-06-01 00:00:00',
'syyyy-mm-dd
hh24:mi:ss') AND "FC"."CLAIM_PAYMENT_STATUS"='02' AND
("FC"."PLACE_OF_SERVICE"='11' OR
"FC"."PLACE_OF_SERVICE"='12' OR "FC"."PLACE_OF_SERVICE"='22'
OR "FC"."PLACE_OF_SERVICE"='24')
AND "FC"."SERVICE_FROM_DATE"<=TO_DATE(' 2014-05-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
############################################################################
######################################################
The cost for using the index seems to be pretty low (5) while for the FTS
its 2.7M.
The index is a function based index.
CREATE INDEX TEST.CLAIMS_STAT_ORIG_XBT ON TEST.CLAIMS
(CLAIM_PAYMENT_STATUS DESC,
ORIGINAL_CLAIM_ID,
SUBSTR(CLAIM_ID,1,LENGTH(CLAIM_ID)-2))
PARALLEL 4 TABLESPACE DWHPART2 PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE
(INITIAL 64K BUFFER_POOL DEFAULT);
The query completes with a FTS but does not seem to complete with the index.
When I make the index invisible then the optimizer chooses the FTS.
I did a 10053 and I believe these are the stats for FTS
Access Path: TableScan
Cost: 2762793.85 Resp: 2762793.85 Degree: 0
Cost_io: 2751436.00 Cost_cpu: 281804015238
Resp_io: 2751436.00 Resp_cpu: 281804015238
And these are the stats for the index. Not sure what the logdef part is
about?
Access Path: index (RangeScan)
Index: CLAIMS_STAT_ORIG_XBT
resc_io: 5.00 resc_cpu: 37109
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 50.00
***** End Logdef Adjustment ******
Cost: 5.00 Resp: 5.00 Degree: 1
The table is about 150 million rows and is partitioned.
The function based index SYS named columns have statistics
I'm scratching my head trying to figure out what I am missing that makes the
optimizer favor the FBI over the FTS????
Should I dig into histograms for the FBI's sys named columns? And then that
might help fix the cost?
TIA for any ideas.
Chris
