A reader, July 04, 2017 - 9:46 am UTC
Different behaviour between 11g and 12c
Stuart Smith, October 04, 2017 - 3:44 am UTC
Hi Connor,
I have a partitioned table with a unique global index. Select count(*) from the full table provokes a fast index scan in 11.2.0.4, as expected, but a index full scan in 12.1.0.2. Adding a index_ffs hint fixes this.
Furthermore, select count from a partition shows this in 11.2.0.4:
explain plan for select count(*) from dwstglm.fct_lm_requests partition (requests_201710);
select * from table(dbms_xplan.display);
Plan hash value: 1476719074
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 129K (2)| 00:03:56 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE SINGLE| | 30M| 129K (2)| 00:03:56 | 18 | 18 |
| 3 | TABLE ACCESS FULL | FCT_LM_REQUESTS | 30M| 129K (2)| 00:03:56 | 18 | 18 |
---------------------------------------------------------------------------------------------------
and in 12.1.0.2:
explain plan for select count(*) from dwstglm.fct_lm_requests partition (requests_201710);
select * from table(dbms_xplan.display);
Plan hash value: 711915442
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1274K (4)| 00:01:40 |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | INDEX FULL SCAN| FCT_LM_REQUESTS_PK | 30M| 1274K (4)| 00:01:40 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TBL$OR$IDX$PART$NUM("DWSTGLM"."FCT_LM_REQUESTS",0,1,0,ROWI
D)=18)
Obviously, the index full scan takes much longer.
October 05, 2017 - 4:16 pm UTC
We'd need to see the entire DDL - please post as a new question on AskTOM
Please answer how we avoid FTS
Tony Fernandez, June 22, 2023 - 6:45 pm UTC
Is there q way to avoid the FTS while we are instructing to only go one partition?
June 23, 2023 - 9:27 am UTC
Same as for non-partitioned tables, the optimizer will use an index if:
- There's one available that matches the where clause
- The optimizer determines using the index is cheaper than a full table scan
If there's a suitable index in place and you're still getting a full partition scan, then either:
- The FTS is faster
- The optimizer has made a miscalculation; this could be for many reasons - a big one is if the underlying table stats are missing/out-of-date/incorrect