Parallelism and partitioning are two excellent features of Oracle. What is the relation between CBO and these two guys? Well I catch some issues. First of all lets create a table with range partitioning:
SQL> select version from v$instance;
VERSION
-----------------
10.2.0.1.0
CREATE TABLE part_table
PARTITION BY RANGE (c1)(
PARTITION p1 VALUES LESS THAN (100000),
PARTITION p2 VALUES LESS THAN (200000),
PARTITION p3 VALUES LESS THAN (MAXVALUE))
nologging
as
select
level c1, cast(' ' as char(2000)) filler
from dual
connect by level < 300000;
CREATE INDEX part_table_c1_idx
ON part_table (c1)
LOCAL (
PARTITION p1,
PARTITION p2,
PARTITION p3);
gather stats...
Just a table with three partitions and each partition has a local index. C1 column has strictly continuous data. Lets do some exercises.
select * from part_table where c1 = 10000; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2005 | | 1 | PARTITION RANGE SINGLE | | 1 | 2005 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PART_TABLE | 1 | 2005 | |* 3 | INDEX RANGE SCAN | PART_TABLE_C1_IDX | 1 | | --------------------------------------------------------------------------------
Here we see partition pruning, only partition P1 and its index considered as source data. Of course index path is used, because it is a singe-row access, no need for a FTS. below there is an example for 2 partition usage with index path.
select * from part_table where c1 in (10000, 190000); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 4010 | 1 | INLIST ITERATOR | | | | 2 | PARTITION RANGE ITERATOR | | 2 | 4010 | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| PART_TABLE | 2 | 4010 |* 4 | INDEX RANGE SCAN | PART_TABLE_C1_IDX | 2 | --------------------------------------------------------------------------------
Now, I want to see an index access with a FTS, indeed. What I meant is:
select * from part_table where c1 between 0 and 100000-1 or c1 = 150000; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 191M | 1 | CONCATENATION | | | | 2 | PARTITION RANGE SINGLE | | 1 | 2005 | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| PART_TABLE | 1 | 2005 |* 4 | INDEX RANGE SCAN | PART_TABLE_C1_IDX | 1 | | 5 | PARTITION RANGE SINGLE | | 99999 | 191M |* 6 | TABLE ACCESS FULL | PART_TABLE | 99999 | 191M -------------------------------------------------------------------------------- cost: 7393
Well done. I wanted to read all of P1 and one row from P2, FTS for P1 and index access for P2. But now, I want it to be parallel, some processes will read from index of p2 others will make an FTS on P1
select /*+ PARALLEL(part_table,2) */ * from part_table where c1 between 0 and 100000-1 or c1 = 150000; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 191M | 1 | CONCATENATION | | | | 2 | PARTITION RANGE SINGLE | | 1 | 2005 | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| PART_TABLE | 1 | 2005 |* 4 | INDEX RANGE SCAN | PART_TABLE_C1_IDX | 1 | | 5 | PARTITION RANGE SINGLE | | 99999 | 191M |* 6 | TABLE ACCESS FULL | PART_TABLE | 99999 | 191M -------------------------------------------------------------------------------- cost:4085 select /*+ PARALLEL(part_table,4) */ * from part_table where c1 between 0 and 100000-1 or c1 = 150000; [same exec plan] cost:2044
CBO’s formula for costing concatenation is cost(INDEX RANGE SCAN) + cost(TABLE ACCESS FULL)/degree of parallelism. It is realistic to not dividing cost of INDEX RANGE SCAN with DOG (degree of parallelism). But I do not find it realistic to just divide the cost of TABLE ACCESS FULL with DOG. If one programmer writes a basic ERP program in 2 months, one cannot say 2 programmers write the same program in 1 month. As I said before, I want it to be parallel, some of process will read from index of p2 others will make an FTS on P1
begin update part_table set c1 = 10000 where c1 < 100000; dbms_stats.gather_table_stats( ownname => USER,tabname => 'PART_TABLE',cascade => TRUE); commit; end;
I updated all of P1, there is only one value for C1 column.
select * from part_table where c1 in (10000); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 191M| 7389 (1)| 00:01 | 1 | PARTITION RANGE SINGLE| | 100K| 191M| 7389 (1)| 00:01 |* 2 | TABLE ACCESS FULL | PART_TABLE | 100K| 191M| 7389 (1)| 00:01 -------------------------------------------------------------------------------- select * from part_table where c1 in (190000); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2005 | | 1 | PARTITION RANGE SINGLE | | 1 | 2005 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PART_TABLE | 1 | 2005 | |* 3 | INDEX RANGE SCAN | PART_TABLE_C1_IDX | 1 | | --------------------------------------------------------------------------------
Alrigth, but if I give a predicate that includes both of them, CBO does not use index range scan for P2:
select * from part_table where c1 in (10000, 190000); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 98966 | 189M| 14778 (1)| 00:02 | 1 | PARTITION RANGE INLIST| | 98966 | 189M| 14778 (1)| 00:02 |* 2 | TABLE ACCESS FULL | PART_TABLE | 98966 | 189M| 14778 (1)| 00:02 -------------------------------------------------------------------------------- select /*+ PARALLEL(part_table,4) */ * from part_table where c1 in (10000, 190000); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 98966 | 189M| 4082 (1)| 00:00:4 | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 98966 | 189M| 4082 (1)| 00:00:4 | 3 | PX BLOCK ITERATOR | | 98966 | 189M| 4082 (1)| 00:00:4 |* 4 | TABLE ACCESS FULL| PART_TABLE | 98966 | 189M| 4082 (1)| 00:00:4 --------------------------------------------------------------------------------
What I expect from Oracle to hire one process for reading P2 with index and hire other processes to FTS P1. Forcing oracle to do parallelism on this partitioned table causes a parallel FTS on P2, which is higher than a non-parallel index access. Anyway, best case was concatenation, which looks like below, also nearly the same cost of concatenation with 4 parallelism
select /*+ PARALLEL(p1,4) */ * from part_table partition (p1) p1 where c1 = 10000 union all select /*+ PARALLEL(p2,1) */ * from part_table partition (p2) p2 where c1 = 190000; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 191M | 1 | UNION-ALL | | | | 2 | PX COORDINATOR | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 100K| 191M | 4 | PX BLOCK ITERATOR | | 100K| 191M |* 5 | TABLE ACCESS FULL | PART_TABLE | 100K| 191M | 6 | PARTITION RANGE SINGLE | | 1 | 2005 | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| PART_TABLE | 1 | 2005 |* 8 | INDEX RANGE SCAN | PART_TABLE_C1_IDX | 1 | -------------------------------------------------------------------------------- cost: 2043
I want it to be parallel, some processes should read from index of p2 others should make an FTS on P1 at the same time. Each partition should have its own process group and each group should have their very own execution plan. May be one group using bitmap, one group making rowid separated parallelism, one group making index-skip-scan. “Buradan yetkililere sesleniyorum”
