CODEWITHSUNDEEP
oracle-databaseoracle11gsql-execution-plan
Peter
Peter

Reputation: 1196

selecting whole partitions uses index - why?

I am selecting all the data from some partitions of a partitioned table (Oracle 11g - actually updating in the real case, but for the example a select shows the same behaviour that I would like to understand). Can you explain to me why Oracle decides to use the index instead of a full scan? In my understanding a full scan would be the smarter access method. Why does Oracle think an index range scan + table access by index rowid batched is smarter than a full scan of the partitions?

Test table:

CREATE TABLE t_test
    (ID                NUMBER                 NOT NULL ENABLE, 
    PARTITION_NUMBER   NUMBER                 NOT NULL ENABLE, 
    CREATION_TIMESTAMP DATE   DEFAULT SYSDATE NOT NULL ENABLE, 
    CONSTRAINT PK_t_test PRIMARY KEY (PARTITION_NUMBER, ID) USING INDEX LOCAL
    ) 
PARTITION BY LIST (PARTITION_NUMBER) 
    (
    PARTITION P1  VALUES (1) SEGMENT CREATION IMMEDIATE,
    PARTITION P2  VALUES (2) SEGMENT CREATION IMMEDIATE,
    PARTITION P3  VALUES (3) SEGMENT CREATION IMMEDIATE,
    PARTITION P4  VALUES (4) SEGMENT CREATION IMMEDIATE,
    PARTITION P5  VALUES (5) SEGMENT CREATION IMMEDIATE,
    PARTITION P6  VALUES (6) SEGMENT CREATION IMMEDIATE,
    PARTITION P7  VALUES (7) SEGMENT CREATION IMMEDIATE,
    PARTITION P8  VALUES (8) SEGMENT CREATION IMMEDIATE,
    PARTITION P9  VALUES (9) SEGMENT CREATION IMMEDIATE
    );

Selecting the data from some partitions (nothing was inserted for this example, but behaviour is the same with data in the partitions): 

explain plan for select * from t_test where PARTITION_NUMBER in (2,3,4,5,6,7);
SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3284178661

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |           |     1 |    35 |     1   (0)| 00:00:01 |       |       |
|   1 |  INLIST ITERATOR                            |           |       |       |            |          |       |       |
|   2 |   PARTITION LIST ITERATOR                   |           |     1 |    35 |     1   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T_TEST    |     1 |    35 |     1   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  4 |     INDEX RANGE SCAN                        | PK_T_TEST |     1 |       |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("PARTITION_NUMBER"=2 OR "PARTITION_NUMBER"=3 OR "PARTITION_NUMBER"=4 OR "PARTITION_NUMBER"=5 OR 
              "PARTITION_NUMBER"=6 OR "PARTITION_NUMBER"=7)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

I don't understand why it does the range scan... Why not just like this (same query but with FULL-hint)? Why does it ever think using the index is the better approach here? (Even an ALL_ROWS hint does not change the behavior) :

explain plan for select /*+ full(t_test) */ * from t_test where PARTITION_NUMBER in (2,3,4,5,6,7);
SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3335595461

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    35 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST INLIST|        |     1 |    35 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   2 |   TABLE ACCESS FULL   | T_TEST |     1 |    35 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Upvotes: 3

Views: 788

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

The example uses an index instead of a full table scan because of how the segment space is allocated. By default, Oracle tends to allocate a non-trivial amount of space for table partitions. For indexes, Oracle tends to allocate a much smaller amount of space for index partitions.

When I run your sample code, the empty table contains 72 megabytes but the empty index contains 0.5 megabytes:

select segment_name, sum(bytes)/1024/1024 mb
from user_segments
where segment_name in ('T_TEST', 'PK_T_TEST')
group by segment_name
order by 1 desc;

SEGMENT_NAME   MB
------------   -------
T_TEST         72
PK_T_TEST       0.5625

Full table scans have to read the entire segment. The index range scan still has to read from the table, but it can do so using the ROWIDs, and therefore reads less data from disk.

Oracle's automatic segment space allocation is almost always better than configuring it manually. But with a trivial amount of data, it might make sense to optimize the segments. If you change each of the 9 SEGMENT CREATION IMMEDIATE to STORAGE (INITIAL 64K NEXT 64K) then the table partitions will be smaller and the execution plan will use a full table scan. But you probably don't want to do that. This problem is likely only related to using an unrealistically small sample data set.

Oracle's space algorithms are understandably optimized for storing large amounts of data in partitions.

Unfortunately, this likely doesn't help you with your real problem. You mentioned a 300 million row table, and that kind of size should work better with partitioning.

But the real problem might also be related to a segment space issue. Perhaps the table used to have 3 billion rows, and was deleted, but never shrunk? Or maybe the table was created with hundreds of partitions, most of which are empty. Or maybe the table was created with some ridiculous manual space setting.

When dealing with large table performance we often have to think in terms of segments and bytes instead of the number of rows.

Upvotes: 2

Mark Stewart
Mark Stewart

Reputation: 2098

Because the index tells oracle what partitions your data are in. When you say "full scan" do you mean full scan of the table, or read all the rows in the specified partitions? The "TABLE ACCESS FULL" row in second explain plan has KEY(I) in the Pstart and Pstop columns, so Oracle is only going to scan all rows in those partitions, not the whole table.

So the partitioning is limiting reading just the partitions in the where clause, and it is reading all rows in the specific partitions and using the index to figure out what partitions to read. It is using a range scan to look for the leading column value in your primary key. In either explain plan, it is using the index to look just at the partitions that contain the specified PARTITION_NUMBER -- in the first explain plan you see 4 = access meaning it is using the index.

Upvotes: 0

Related Questions