Home > Trouble, Uncategorized > Cardinality estimates on list subpartitioned tables with binds on partition, literals on subpartition

Cardinality estimates on list subpartitioned tables with binds on partition, literals on subpartition

Acutually, I decided blogging is just too much pain especially the formating. Additionally considering the good quality of most of the other blogs, I would not want distribute half knowledge. However as some blog services do not allow direct formated long comments or limit comment to 4K, I will have to do this here (but even this does not format as I want).

While the bugs mentioned in http://oracle-randolf.blogspot.ch/2009/03/partition-oddities-part-1-severe-bug-in.html are according to oracle support fixed, still not all works well in 11.2.0.2. At least for range partitioned, sub list partitioned in combination with partial use of bind variables and add partition operations.

A partitioned table with identical subpartitions seems to work ok, however add one subpartition to the maxvalue partition (or any other or remove one) and you are back to square one, except for the first subpartition.

Unless I made an error or misunderstood something, a plan with cardinality 1 results:

	SQL_ID  a1d6qv0615qjg, child number 0
	-------------------------------------
	SELECT /*+ full(t) 1 off h*/ COUNT(*) FROM TESTSZ WHERE I=:B1 AND J=1
	 
	Plan hash value: 835289084
	 
	--------------------------------------------------------------------------------------------------
	| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
	--------------------------------------------------------------------------------------------------
	|   0 | SELECT STATEMENT        |        |       |       |    14 (100)|          |       |       |
	|   1 |  SORT AGGREGATE         |        |     1 |    10 |            |          |       |       |
	|   2 |   PARTITION RANGE SINGLE|        |     1 |    10 |    14   (0)| 00:00:01 |   KEY |   KEY |
	|   3 |    PARTITION LIST SINGLE|        |     1 |    10 |    14   (0)| 00:00:01 |       |       |
	|*  4 |     TABLE ACCESS FULL   | TESTSZ |     1 |    10 |    14   (0)| 00:00:01 |   KEY |   KEY |
	--------------------------------------------------------------------------------------------------
	 
	Predicate Information (identified by operation id):
	---------------------------------------------------
	 
	   4 - filter(("J"=1 AND "I"=:B1))
	 

following first, abbreviated output of plan of different sql statements, then the script:

WITH added subpartition, note the cardinality estimate of 1 for j=1, j=2 (statement 3 and 4)
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SELECT /*+ full(t) f off h*/ COUNT(*) FROM TESTSZ WHERE
|* 4 | TABLE ACCESS FULL | TESTSZ | 90 | 990 | 14 (0)| 00:00:01 | 2 | 2 |
SELECT /*+ full(t) 0 off h*/ COUNT(*) FROM TESTSZ WHERE I=:B1 AND J=0
|* 4 | TABLE ACCESS FULL | TESTSZ | 30 | 300 | 14 (0)| 00:00:01 | KEY | KEY |
SELECT /*+ full(t) 1 off h*/ COUNT(*) FROM TESTSZ WHERE I=:B1 AND J=1
|* 4 | TABLE ACCESS FULL | TESTSZ | 1 | 10 | 14 (0)| 00:00:01 | KEY | KEY |
SELECT /*+ full(t) 2 off h*/ COUNT(*) FROM TESTSZ WHERE I=:B1 AND J=2
|* 4 | TABLE ACCESS FULL | TESTSZ | 1 | 10 | 14 (0)| 00:00:01 | KEY | KEY |

SELECT /*+ full(t) 3 off h*/ COUNT(*) FROM TESTSZ WHERE I=:B2 AND J=:B1
|* 4 | TABLE ACCESS FULL | TESTSZ | 100 | 1100 | 14 (0)| 00:00:01 | KEY | KEY |

WITHOUT added subpartition resulting in reasonable estimates

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SELECT /*+ full(t) f off h*/ COUNT(*) FROM TESTSZ WHERE
|* 4 | TABLE ACCESS FULL | TESTSZ | 90 | 990 | 14 (0)| 00:00:01 | 2 | 2 |
SELECT /*+ full(t) 0 off h*/ COUNT(*) FROM TESTSZ WHERE I=:B1 AND J=0
|* 4 | TABLE ACCESS FULL | TESTSZ | 30 | 300 | 14 (0)| 00:00:01 | KEY | KEY |
SELECT /*+ full(t) 1 off h*/ COUNT(*) FROM TESTSZ WHERE I=:B1 AND J=1
|* 4 | TABLE ACCESS FULL | TESTSZ | 90 | 990 | 14 (0)| 00:00:01 | KEY | KEY |
SELECT /*+ full(t) 2 off h*/ COUNT(*) FROM TESTSZ WHERE I=:B1 AND J=2
|* 4 | TABLE ACCESS FULL | TESTSZ | 180 | 1980 | 14 (0)| 00:00:01 | KEY | KEY |

SELECT /*+ full(t) 3 off h*/ COUNT(*) FROM TESTSZ WHERE I=:B2 AND J=:B1
|* 4 | TABLE ACCESS FULL | TESTSZ | 100 | 1100 | 14 (0)| 00:00:01 | KEY | KEY |

and the script to generate above output:

set linesize 200
set pagesize 50
set echo on
set feedback off
column plan_table_output format a120

drop table testsz purge;
create table testsz
(i date, j number, v varchar2(100))
partition by range(i)
subpartition by list (j)
subpartition template
(subpartition p0 values (0),
 subpartition p1 values (1),
 subpartition p2 values (2)
)
(
partition p_100 values less than (to_date('3101201201','DDMMYYYYSS')),
partition p_200 values less than (to_date('3103201201','DDMMYYYYSS')),
partition p_max values less than (maxvalue)
);

alter table testsz modify partition p_max add subpartition values(3);


insert into testsz
select to_date('31012012','DDMMYYYY'),
        case when mod(rownum,100)  <10 then 0 when mod(rownum,100) <40 then 1 else 2 end,
       lpad('x',100)
  from dual connect by level <301;

insert into testsz
select to_date('31032012','DDMMYYYY'),
        case when mod(rownum,100)  <10 then 0 when mod(rownum,100) <40 then 1 else 2 end,
       lpad('x',100)
  from dual connect by level <301;
  
commit;

begin
 dbms_stats.gather_table_stats(ownname => user,tabname => 'TESTSZ',
                               method_opt => 'for all columns size 1',no_invalidate => false);
end;
/

-- info
select owner, table_name, partitioning_type, subpartitioning_type, partition_count, def_subpartition_count
 from dba_part_Tables where table_name='TESTSZ' and owner like 'TEST%';
select table_name, partition_name, num_rows,partition_position from user_tab_partitions where table_name='TESTSZ'
order by 1;
select table_name, subpartition_name, num_rows from user_tab_subpartitions where table_name='TESTSZ'
order by 2;

--
declare
 i1 date:=to_date('31012012','DDMMYYYY');
 h number:=1;
 j number;
begin
  select  /*+ full(t) f off h*/ count(*) into j from testsz where  I=to_date('31012012','DDMMYYYY')and j=1;
  select  /*+ full(t) 0 off h*/ count(*) into j from testsz  where I=i1 and j=0;
  select  /*+ full(t) 1 off h*/ count(*) into j from testsz  where I=i1 and j=1;
  select  /*+ full(t) 2 off h*/ count(*) into j from testsz  where I=i1 and j=2;
  select  /*+ full(t) 3 off h*/ count(*) into j from testsz  where I=i1 and j=h;
  --
  select  /*+ full(t) 4a off h*/ count(*) into j from testsz  where I=to_date('31012012','DDMMYYYY');
  select  /*+ full(t) 4b off h*/ count(*) into j from testsz  where I=to_date('31012012','DDMMYYYY') and j=h;
end;
/  

select t.sql_id, o.* from v$sql t ,
      table(dbms_xplan.display_cursor(t.sql_id, t.CHILD_NUMBER,'ADVANCED')) o
 where sql_Text like 'SELECT %full%TESTSZ%%'
 and (o.plan_table_output like '%TESTSZ %')
 and last_active_time > sysdate -1/24/60
 order by t.sql_text, o.plan_table_output desc
;

pause ...
About these ads
Categories: Trouble, Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: