Query On XXX_TAB_PARTITIONS Does Full Table Scan On TABPART$

(Doc ID 1134660.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

Any query on the views XXX_TAB_PARTITIONS and XXX_IND_PARTITIONS does a full table scan
and full sort on the respective partitioning base tables tabpart$ and tabind$,
as it can be seen at the following part of a typical execution plan:

-----------------------------------------------------------------------
| Id | Operation |Name |Rows |Bytes |TempSpc|Cost
-----------------------------------------------------------------------
|* 8|HASH JOIN | | 18| 7686 | | 252
| 9| TABLE ACCESS BY INDEX ROWID|OBJ$ | 1| 28 | | 4
|* 10| INDEX RANGE SCAN |I_OBJ2 | 1| | | 3
| 11| VIEW |TABPARTV$|13266| 5169K| | 247
| 12| WINDOW SORT | |13266| 829K| 2968K| 247
| 13| TABLE ACCESS FULL |TABPART$ |13266| 829K| | 40

In this way, using a lot of partitions, then these views are extremely slow.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms