Partitoning Headaches - constrained with constraints

Okay, slight change in thought..the follow-up from my Dev-team didnt happen as much as I thought, probably due to the fact the past posts might have been very primitive..but the good feedbacks and suggestions from other circle made me think I should turn this more of my sctrach pad..anything I find interesting gets posted here..and that may have nothing to do with development or tuning..(hopefully that makes a few of my DBA friends happy - who didnt like the tone of my prev posts where I had mentioned "..keep your DBA away" :) ).

Now here is something I learnt something new while troubleshooting a SQL performance at a client site..They have this huge table Range Partitioned by Date..and local indexes etc..but the sql(think its a MERGE statement) was taking forever.


Now before we jump into what the problem..here is the setup script if you would want to validate ( I did this test on 10gR1 and 10gR1)..

-- create partitioned table
-- ------------------------
dmo>ed
Wrote file afiedt.buf

1 Create table test_partition
2 ( trans_date timestamp,
3 trans_id number ,
4 trans_info varchar2(10)
5 )
6 partition by range(trans_date)
7 ( partition p1 values less than (to_date('01-01-2006','DD-MM-YYYY')),
8 partition p2 values less than (to_date('01-01-2007','DD-MM-YYYY')),
9 partition pn values less than (maxvalue)
10* )
dmo>/

Table created.

dmo>ed
Wrote file afiedt.buf

1 Create unique index test_part_id_idx on
2* test_partition(trans_date,trans_id) local
dmo>/

Index created.

dmo>alter table test_partition add (
2 constraint test_partition_pk primary key(trans_date,trans_id)
3 using index test_part_id_idx
4 );

Table altered.

-- load data into partition p1
--
dmo>ed
Wrote file afiedt.buf

1 Insert /*+ APPEND */
2 into test_partition
3 select
4 to_date('04-05-2005','DD-MM-YYYY'),rownum,'junk here'
5 from all_objects a, all_objects b
6* where rownum<99999>/

99998 rows created.

dmo>commit;

Commit complete.

-- load data into partition p2
--
dmo>Insert /*+ APPEND */
2 into test_partition
3 select
4 to_date('04-05-2006','DD-MM-YYYY'),rownum,'junk here'
5 from all_objects a, all_objects b
6 where rownum<99999>commit;

Commit complete.

-- load data into last partition pn
--

dmo>Insert /*+ APPEND */
2 into test_partition
3 select
4 to_date('04-05-2009','DD-MM-YYYY'),rownum,'junk here'
5 from all_objects a, all_objects b
6 where rownum<99999>commit;

Commit complete.

-- Now lets gather stats and make sure they get populated

dmo>ed
Wrote file afiedt.buf

1 begin
2 dbms_stats.gather_table_stats (
3 ownname=>USER,
4 tabname=>'TEST_PARTITION',
5 cascade=>TRUE
6 );
7* end;
dmo>/

PL/SQL procedure successfully completed.

dmo>ed
Wrote file afiedt.buf

1 select partition_name,NUM_ROWS,blocks from user_tab_partitions
2* where table_name='TEST_PARTITION'
dmo>/

PARTITION_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
P1 99998 403
P2 100644 403
PN 99998 403

so far , everything looks good.

Now lets query .. and watch the execution plan output

-- query1: get data from partition p2
-- ----------------------------------

dmo>select * from test_partition
2 where trans_date=to_date('04-05-2006','DD-MM-YYYY')
3 /

99998 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369430197

-------------------------------------------------------------------
| Id | Operation | Name | Rows |Pstart| Pstop |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| | |
| 1 | PARTITION RANGE SINGLE| | 100K| 2 | 2 |
|* 2 | TABLE ACCESS FULL | TEST_PARTITION | 100K| 2 |
-------------------------------------------------------------------


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

2 - filter("TRANS_DATE"=TIMESTAMP'2006-05-04 00:00:00')

-- query2
-- ------

dmo>ed
Wrote file afiedt.buf

1 select * from test_partition
2* where trans_date=to_date('04-05-2005','DD-MM-YYYY')
dmo>/

99998 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369430197

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99998 | | |
| 1 | PARTITION RANGE SINGLE| | 99998 | 1 | 1 |
|* 2 | TABLE ACCESS FULL | TEST_PARTITION | 99998 | 1 |
--------------------------------------------------------------------

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

2 - filter("TRANS_DATE"=TIMESTAMP'2005-05-04 00:00:00')

so Oracle optimizer seems to be picking up the right estimation..Everything good so far..

Now DB analysis team want to add a constraint to make sure you dont enter any date with time remember date column includes time component in Oracle) or subsecond units (if the column is timestamp) ,instead just the date alone to be stored in transdate column..

so what do they do..add a simple check constraint to make sure all data in transdate are simply dates with 00:00:00 for time component.

-- introduce a check constraint
-- ----------------------------

dmo>ed
Wrote file afiedt.buf

1 alter table test_partition add
2 (
3 constraint do_date_check check (trans_date=trunc(trans_date))
4* )
dmo>/

Table altered.

Now rerun the query and check the execution plan (note the cardinality estimated)

-- check the plan again
-- ---------------------
dmo>select * from test_partition newsql
2 where trans_date=to_date('04-05-2006','DD-MM-YYYY')
3 /

99998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1369430197

-------------------------------------------------------------------
| Id | Operation | Name | Rows |Pstart| Pstop |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1006 | | |
| 1 | PARTITION RANGE SINGLE| | 1006 | 2 | 2 |
|* 2 | TABLE ACCESS FULL | TEST_PARTITION | 1006 | 2 |
-------------------------------------------------------------------

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

2 - filter(TRUNC(INTERNAL_FUNCTION("TRANS_DATE"))=TIMESTAMP'2006-05-04 00:00:00' AND
"TRANS_DATE"=TIMESTAMP'2006-05-04 00:00:00')

see how the estimation went wrong? big time? (from 10000 rows to 1000 rows) - which is way way off the mark.


There is no fix yet..(atleast in 10g)..hopefully this will get fixed in version 11.x..but I just happened to discover this odd behavior while I was troubleshooting some other sql performance tuning issue..and I was so glad I learnt something new on that day.
(At client place I added a hint to make it work)

so watch out if you have partitions and check constraints on partitioned key column..You may be getting hit somewhere without your knowledge.

now to summarise..I will run a test sql with and without constraints and let you see the explain plan results..(and assume you have joined to many other tables in one sql, one wrong cardinality estimates might change the whole plan ..change the join orders..good enough to make all sorts of mess)


-------
Test sql:
--------
dmo>ed
Wrote file afiedt.buf

1 select * from test_partition newsql
2 where trans_date=to_date('04-05-2006','DD-MM-YYYY')
3* and trans_id between 1000 and 2000
dmo>

Version 1: With Check constraint Enabled:
-----------------------------------------

-----------------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 |
| 1 | PARTITION RANGE SINGLE | | 10 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_PARTITION | 10 |
|* 3 | INDEX RANGE SCAN | TEST_PART_ID_IDX | 3 |
-----------------------------------------------------------------------

Version 2: With Check constraint Disabled:
-------------------------------------------
dmo>alter table test_partition disable constraint do_date_check;

Table altered.

run the sql here

-----------------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1009 |
| 1 | PARTITION RANGE SINGLE | | 1009 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_PARTITION | 1009 |
|* 3 | INDEX RANGE SCAN | TEST_PART_ID_IDX | 336 |
-----------------------------------------------------------------------

Tomorrow is busy day and so looks thursday and Friday..Hopefully I will find something interesting to blog during the weekend.

Bye until next...:)