Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: July 03, 2017 - 7:03 am UTC

Last updated: June 23, 2023 - 9:27 am UTC

Version: 11G

Viewed 10K+ times! This question is

You Asked

Hi,

My situation is this:

1.) I have a table that is partitioned on a given column (we'll call the partition p1).
2.) After loading the p1 partition, stats are run for that partition only (not the whole table).
3.) Once stats are run, a select statement is executed against that table, which is basically this:

select *
from table partition (p1)
where column_name = criteria associated with p1


So, in step 3, I'm trying to tell Oracle to only look within a given partition. However, my DBA is telling me that in some instances the select statement is still conducting a full table scan. Is there a better way of telling Oracle to only look within the p1 partition?

Thanks!
Brian

and Connor said...

If you do:

select *
from table partition (p1)

then is there *no* way that query is scanning any blocks from a *different* partition.

But that doesn't mean that you can't do

- a full scan *on that partition*
- a local index lookup (ie, index partition p1 to table partition p1)
- a global index lookup (ie, index spans entire table to get rows that exist in partition p1)

"TABLE ACCESS FULL" in the explain plan is not the entire story. You need to look at the partition information as well, eg

SQL> create table t ( x int, y int )
  2  partition by range ( x )
  3  (
  4    partition p1 values less than (1000),
  5    partition p2 values less than (2000),
  6    partition p3 values less than (3000)
  7  );

Table created.

SQL> set autotrace traceonly explain
SQL> select * from t partition (p2);

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    26 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |    26 |     2   (0)| 00:00:01 |     2 |     2 |  <<===
|   2 |   TABLE ACCESS FULL    | T    |     1 |    26 |     2   (0)| 00:00:01 |     2 |     2 |  <<===
-----------------------------------------------------------------------------------------------


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.

Connor McDonald
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?
Chris Saxon
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

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.