BitMap Index access not used when Partition Key is query predicate
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 18 Aug 2010 18:24:02 +0800
I am trying to understand a scenario
where the query :
==============================================================================
select product_id from hemant_sales_tbl where partition_key = 201003310000;
results in this Execution Plan :
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3208477755
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 200K| 2929K| 3438 (1)|
00:00:42 | | |
| 1 | PARTITION LIST
SINGLE| | 200K| 2929K| 3438 (1)| 00:00:42
| 4 | 4 |
| 2 | TABLE ACCESS FULL | HEMANT_SALES_TBL
| 200K| 2929K| 3438 (1)| 00:00:42 | 4 | 4 |
----------------------------------------------------------------------------------------------------------
9 rows selected.
(Note : NO Predicate Section listed , Oracle has already identified
Partition 4 -- i.e. done static partition pruning)
The 10053 trace file has these entries :
Access Path: TableScan
Cost: 3437.93 Resp: 3437.93 Degree: 0
Cost_io: 3429.00 Cost_cpu: 134121823
Resp_io: 3429.00 Resp_cpu: 134121823
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: H_S_T_CID
resc_io: 809.00 resc_cpu: 45621045
ix_sel: 1 ix_sel_with_filters: 1
Cost: 812.04 Resp: 812.04 Degree: 0
Access Path: index (FullScan)
Index: H_S_T_PID
resc_io: 101.00 resc_cpu: 759265
ix_sel: 1 ix_sel_with_filters: 1
Cost: 101.05 Resp: 101.05 Degree: 0
Access Path: index (FullScan)
Index: H_S_T_PID
resc_io: 101.00 resc_cpu: 759265
ix_sel: 1 ix_sel_with_filters: 1
Cost: 101.05 Resp: 101.05 Degree: 0
Access path: Bitmap index - rejected
Cost: 20193.49 Cost_io: 20183.36 Cost_cpu: 152129885 Sel: 1
Not believed to be index-only
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: TableScan
Cost: 3437.93 Degree: 1 Resp: 3437.93 Card: 200000.00 Bytes: 0
==============================================================================
while the query :
==============================================================================
select product_id from hemant_sales_tbl partition (P201003310000);
results in this execution plan :
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 42192253
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 200K| 1757K| 90 (0)|
00:00:02 | | |
| 1 | PARTITION LIST
SINGLE | | 200K| 1757K| 90 (0)| 00:00:02
| 4 | 4 |
| 2 | BITMAP CONVERSION TO ROWIDS
| | 200K| 1757K| 90 (0)| 00:00:02 | | |
| 3 | BITMAP INDEX FAST FULL SCAN| H_S_T_PID
| | | | | 4 | 4 |
-----------------------------------------------------------------------------------------------------------
10 rows selected.
(Note : Oracle is going to Partition 4)
The 10053 Trace File has these entries :
Access Path: TableScan
Cost: 3437.27 Resp: 3437.27 Degree: 0
Cost_io: 3429.00 Cost_cpu: 124121823
Resp_io: 3429.00 Resp_cpu: 124121823
Access Path: index (index (FFS))
Index: H_S_T_PID
resc_io: 90.00 resc_cpu: 664930
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 90.04 Resp: 90.04 Degree: 1
Cost_io: 90.00 Cost_cpu: 664930
Resp_io: 90.00 Resp_cpu: 664930
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: H_S_T_PID
resc_io: 101.00 resc_cpu: 759265
ix_sel: 1 ix_sel_with_filters: 1
Cost: 101.05 Resp: 101.05 Degree: 0
Access Path: index (FullScan)
Index: H_S_T_PID
resc_io: 101.00 resc_cpu: 759265
ix_sel: 1 ix_sel_with_filters: 1
Cost: 101.05 Resp: 101.05 Degree: 0
Access path: Bitmap index - rejected
Cost: 101.05 Cost_io: 101.00 Cost_cpu: 759265 Sel: 1
Believed to be index-only
****** finished trying bitmap/domain indexes ******
******** Begin index join costing ********
******** End index join costing ********
Best:: AccessPath: IndexFFS Index: H_S_T_PID
Cost: 90.04 Degree: 1 Resp: 90.04 Card: 200000.00 Bytes: 0
==============================================================================
Table HEMANT_SALES_TBL is List-Partitioned on the Partition Key
column which is a NUMBER.
(The Partition P201003310000 corresponds to PARTITION_KEY=201003310000)
Index H_S_T_PID is a BitMap index on PRODUCT_ID of which there are
100 distinct values.
The only difference between the first and the second queries is that
I add PARTITION_KEY=201003310000 as a Filter (i.e. in the WHERE
clause) in the first query while the second query explicitly names
the target target partition.
Both queries do get executed with Partition Pruning to Partition 4.
So, it *seems* as if the presence of the WHERE clause in the first
query results in Oracle recomputing the cost of the Index FullScan to
be very high because the query is "Not believed to be index-only".
However, this doesn't appear in the Explain Plan predicates section
after actually having been used to prune the evaluation to Partition 4.
I also notice that if I run
==============================================================================
select /*+ index (s H_S_T_PID) */ product_id from hemant_sales_tbl s
where partition_key = 201003310000;
I get
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1975175230
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 2929K|
20193 (1)| 00:04:03 | | |
| 1 | PARTITION LIST SINGLE | | 200K| 2929K|
20193 (1)| 00:04:03 | 4 | 4 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 200K| 2929K|
20193 (1)| 00:04:03 | | |
| 3 | BITMAP INDEX FULL SCAN | H_S_T_PID
| | | | | 4 | 4 |
----------------------------------------------------------------------------------------------------------
10 rows selected.
Note : NO Table Access !
==============================================================================
Here is the full Test Case :
==============================================================================
SQL> drop table hemant_sales_tbl purge;
Table dropped.
SQL>
SQL>
SQL> create table hemant_sales_tbl
2 (partition_key number not null,
3 product_id varchar2(12),
4 customer_id varchar2(12),
5 sale_quantity number,
6 sale_description varchar2(500))
7 partition by list (partition_key)
8 (
9 partition p200912310000 values (200912310000),
10 partition p201001310000 values (201001310000),
11 partition p201002280000 values (201002280000),
12 partition p201003310000 values (201003310000),
13 partition p201004300000 values (201004300000),
14 partition p201005310000 values (201005310000),
15 partition p201006300000 values (201006300000)
16 )
17 /
Table created.
SQL>
SQL> alter table hemant_sales_tbl nologging;
Table altered.
SQL>
SQL>
SQL> insert /*+ APPEND */ into hemant_sales_tbl
2 select
decode(mod(rownum,5),0,200912310000,1,201001310000,2,201002280000,3,201003310000,4,201003310000,5,201004300000),
3 to_char(trunc(dbms_random.value(12000100,12000200))),
4 to_char(trunc(dbms_random.value(10000000,40000000))),
5 mod(rownum,100),
6 dbms_random.string('X',400)
7 from dual connect by level < 500001
8 /
500000 rows created.
SQL>
SQL>
SQL> create bitmap index h_s_t_pid on hemant_sales_tbl(product_id)
local parallel 4 nologging;
Index created.
SQL> alter index h_s_t_pid noparallel;
Index altered.
SQL>
SQL> create bitmap index h_s_t_cid on hemant_sales_tbl(customer_id)
local parallel 4 nologging;
Index created.
SQL> alter index h_s_t_cid noparallel;
Index altered.
SQL>
SQL>
SQL> exec
dbms_stats.gather_table_stats('','HEMANT_SALES_TBL',estimate_percent=>100,degree=>8,granularity=>'ALL');
PL/SQL procedure successfully completed.
SQL> select count(*) from hemant_sales_tbl;
COUNT(*)
----------
500000
SQL> select count(distinct(product_id)) from hemant_sales_tbl;
COUNT(DISTINCT(PRODUCT_ID))
---------------------------
100
SQL> select count(distinct(product_id)) from hemant_sales_tbl
partition (P201003310000);
COUNT(DISTINCT(PRODUCT_ID))
---------------------------
100
SQL>
SQL>
SQL> REM ###################################################################
SQL> REM Get the Execution Plans
SQL>
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> explain plan for
2 select product_id from hemant_sales_tbl where partition_key =
201003310000;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3208477755
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 200K| 2929K| 3438 (1)|
00:00:42 | | |
| 1 | PARTITION LIST
SINGLE| | 200K| 2929K| 3438 (1)| 00:00:42
| 4 | 4 |
| 2 | TABLE ACCESS FULL | HEMANT_SALES_TBL
| 200K| 2929K| 3438 (1)| 00:00:42 | 4 | 4 |
----------------------------------------------------------------------------------------------------------
9 rows selected.
SQL>
SQL> explain plan for
2 select product_id from hemant_sales_tbl partition (P201003310000);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 42192253
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 200K| 1757K| 90 (0)|
00:00:02 | | |
| 1 | PARTITION LIST
SINGLE | | 200K| 1757K| 90 (0)| 00:00:02
| 4 | 4 |
| 2 | BITMAP CONVERSION TO ROWIDS
| | 200K| 1757K| 90 (0)| 00:00:02 | | |
| 3 | BITMAP INDEX FAST FULL SCAN| H_S_T_PID
| | | | | 4 | 4 |
-----------------------------------------------------------------------------------------------------------
10 rows selected.
SQL>
SQL> explain plan for
2 select product_id from hemant_sales_tbl FULL_TABLE;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2499970667
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 500K| 4394K| 225 (0)|
00:00:03 | | |
| 1 | PARTITION LIST
ALL | | 500K| 4394K| 225 (0)| 00:00:03
| 1 | 7 |
| 2 | BITMAP CONVERSION TO ROWIDS
| | 500K| 4394K| 225 (0)| 00:00:03 | | |
| 3 | BITMAP INDEX FAST FULL SCAN| H_S_T_PID
| | | | | 1 | 7 |
-----------------------------------------------------------------------------------------------------------
10 rows selected.
SQL>
SQL>
SQL> explain plan for
2 select /*+ index (s H_S_T_PID) */ product_id from
hemant_sales_tbl s where partition_key = 201003310000;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1975175230
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 2929K|
20193 (1)| 00:04:03 | | |
| 1 | PARTITION LIST SINGLE | | 200K| 2929K|
20193 (1)| 00:04:03 | 4 | 4 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 200K| 2929K|
20193 (1)| 00:04:03 | | |
| 3 | BITMAP INDEX FULL SCAN | H_S_T_PID
| | | | | 4 | 4 |
----------------------------------------------------------------------------------------------------------
10 rows selected.
SQL>
==============================================================================
Hemant K Chitale
http://hemantoracledba.blogspot.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
