jump to navigation

Ensuring Correlated Partition Exclusion December 7, 2009

Posted by mwidlake in performance, Uncategorized, VLDB.
Tags: , ,
trackback

<Previous Post…Next Pos >
I’ve posted a couple of times recently about a tuning method with partition exclusion where you infer a relationship between the value you want to limit a query on and the partition key. It takes a while to explain the theory so I am going to give it a name, in the vain hope it catches on {forgive me if someone has already done this – in fact, just flame me with pointers if you already know a name for this}. At the very least, for my own postings, I can use this name from now on and link back to a single posting explaining it.

I’m going to call it Correlated Partition Exclusion. In essence, you have partitioned the table on a key, in my case the primary key ID, which is an ascending numeric probably sourced from a sequence.
You have a second column, in my case CRE_DATETIME, which increases in line with the PK. If you limit your query on the CRE_DATETIME partition exclusion is not possible as there is no guarantee which CRE_DATETIME values appear in which partition. But, as a human, you understand that if you create 10,000 records a day, if you want to look at the last week’s date you can use:

WHERE ID > MAX_ID-(7*10000)

to exclude partitions with an id more than 7 days worth ago.

So, you have your Correlated Partition Exclusion.

How can you be sure that going back 70,000 IDs is going to safely cover one week of data and how can you maximise your efficiency of including only partitions that cover the date range? {note, I am using a numeric ID as my partition range key and a datetime as my correlated column, this principle works just as well if you partition on datetime and want to correlate to a (generally) ascending numeric key}

Here is my test table :-

create table test_p3
(id number(10) not null
,cre_datetime date not null
,status number(1) not null
,num_1 number(4) not null -- random 20
,num_2 number(4) -- random 500
,num_3 number(5) -- cycle smoothly
,num_4 number(5) -- Random 10000
,vc_1 varchar2(10)
,vc_2 varchar2(10)
,vc_pad varchar2(2000))
tablespace users
partition by range (id)
(partition id_01k values less than (1000)
tablespace users
,partition id_02k values less than (2000)
tablespace users
,partition id_03k values less than (3000)
tablespace users
,partition id_04k values less than (4000)
tablespace users
...
,partition id_45k values less than (45000)
tablespace users
--
,partition id_max values less than (maxvalue)
tablespace users
)
/
--@ind_cols
IND_NAME TAB_NAME PSN COL_NAME
------------------ ------------------ --------- --------------------
TP3_PK TEST_P3 1 ID

TP_CRE_DT TEST_P3 1 CRE_DATETIME

If I want to look for all records between two dates I could use code like the below (based in that suggested by Bernard Polarski, any mistakes are mine).

with get_min_id as
(select max(id) min_id from test_p3
where cre_datetime >= TO_DATE('18-OCT_2009','DD-MON-YYYY') )
,get_max_id as
(select min(id) max_id from test_p3
where cre_datetime <= TO_DATE('20-OCT_2009','DD-MON-YYYY') )
select count(*)
from test_p3
,get_min_id
,get_max_id
where id >get_min_id.min_id
and id < get_max_id.max_id
and cre_datetime between TO_DATE('18-OCT_2009','DD-MON-YYYY')
and TO_DATE('20-OCT_2009','DD-MON-YYYY')
/
COUNT(*)
----------
721

1 row selected.

ie find the minimum ID for the start date and the maximum ID for the end date and query between them.

This works fine. Unfortunately, you get a plan like the below

-------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 39 | 887 (4)| 00:00:04 | | |
| 1 | SORT AGGREGATE | |
1 | 39 | | | | |
| 2 | NESTED LOOPS | |
2 | 78 | 887 (4)| 00:00:04 | | |
| 3 | NESTED LOOPS | |
1 | 26 | 717 (5)| 00:00:03 | | |
| 4 | VIEW | |
1 | 13 | 505 (5)| 00:00:02 | | |
|* 5 | FILTER | |
| | | | | |
| 6 | SORT AGGREGATE | |
1 | 26 | | | | |
|* 7 | VIEW | index$_join$_001 |
38393 | 974K| 505 (5)| 00:00:02 | | |
|* 8 | HASH JOIN | |
| | | | | |
| 9 | PARTITION RANGE ALL | |
38393 | 974K| 418 (6)| 00:00:02 | 1 | 46 |
|* 10 | INDEX RANGE SCAN | TP_CRE_DT |
38393 | 974K| 418 (6)| 00:00:02 | 1 | 46 |
| 11 | PARTITION RANGE ALL | |
38393 | 974K| 152 (4)| 00:00:01 | 1 | 46 |
| 12 | INDEX FAST FULL SCAN | TP3_PK |
38393 | 974K| 152 (4)| 00:00:01 | 1 | 46 |
| 13 | VIEW | |
1 | 13 | 212 (5)| 00:00:01 | | |
| 14 | SORT AGGREGATE | |
1 | 26 | | | | |
|* 15 | VIEW | index$_join$_002 |
2972 | 77272 | 212 (5)| 00:00:01 | | |
|* 16 | HASH JOIN | |
| | | | | |
| 17 | PARTITION RANGE ALL | |
2972 | 77272 | 59 (6)| 00:00:01 | 1 | 46 |
|* 18 | INDEX RANGE SCAN | TP_CRE_DT |
2972 | 77272 | 59 (6)| 00:00:01 | 1 | 46 |
| 19 | PARTITION RANGE ALL | |
2972 | 77272 | 152 (4)| 00:00:01 | 1 | 46 |
| 20 | INDEX FAST FULL SCAN | TP3_PK |
2972 | 77272 | 152 (4)| 00:00:01 | 1 | 46 |
| 21 | PARTITION RANGE ITERATOR | |
2 | 26 | 170 (1)| 00:00:01 | KEY | KEY |
|* 22 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_P3 |
2 | 26 | 170 (1)| 00:00:01 | KEY | KEY |
|* 23 | INDEX RANGE SCAN | TP_CRE_DT |
707 | | 48 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
892 consistent gets
0 physical reads

If you look at the plan {towards the end, and sorry about the naff layout, my blog is not wide enough for this sort of printout} there are two checks on the TP_CRE_DT indexes that scan all partitions of the index - Pstart/Pstop are 1-46. This is the CBO looking for the partitions where the stated CRE_DATETIME records occur in the whole table. Cost is 892 consistent gets, much of which is the checking of local index partitions that will hold no relevant entries.

Bernard also spotted that the code was considering all partitions and was not as efficient as it could be but did not know how to get around it except with a Global index, which brings it's own issues.

One way is to just say "well, I know how many records per day I get so I will fake up the ID limits based on this". The problem is, and this is why the CBO cannot make the decision for you, is that there is no guarantee, no fixed rule, saying that CRE_DATETIME will always increment with the ID. In fact, there is nothing stopping you altering a record which has an ID from yesterday having a CRE_DATETIME from 10 years ago {but forget I said that until tomorrow's post}. Now, in my example the CRE_DATETIME is going to increment with ID. We will use this special case for now, I know it is flawed and will address that flaw {tomorrow}.

So to ensure yo do not miss data you end up making your ID window pretty large to endure you do not miss records. Say you want all records for the last day, you process 1500 records a day, so you consider a window covering all samples with an ID within the last 10,000. It will still be more efficient than scanning all partitions and should be safe. Fairly safe.

The way out of this is to have a ranges table. Something that tells you, for each partition, which is the maximum and minimum CRE_DATE and the IDs covered by that range. You can then use that to identify the partitions that cover the date range you are interested in.

Here is an example. I will create a simple table to hold the ranges:

create table tp3_range
(min_cre_dati date
,max_cre_dati date
,min_id number
,max_id number)

Now you have to populate it.
The below code will create the first record for the first partition.

insert into tp3_range
SELECT MIN(CRE_DATETIME)
,MAX(CRE_DATETIME)
,MIN(ID)
,MAX(ID) FROM TEST_P3 PARTITION (ID_01K)
/
Execution Plan
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 12 | 102
(1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 12 |
| | | |
| 2 | PARTITION RANGE SINGLE| | 999 | 11988 | 102
(1)| 00:00:01 | 1 | 1 |
| 3 | TABLE ACCESS FULL | TEST_P3 | 999 | 11988 | 102
(1)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
3 db block gets
195 consistent gets
0 physical reads

As you can see from the plan and cost, this is not very efficient as it has to scan the whole partition. Maybe not a problem if you do this once, but there are indexes on both these columns, can't this be done more efficiently? Yes, if you split up the code into four in-line selects (if you want more details about it being more performant to do MIN and MAX on their own than in one statement then see this post on the topic ):

insert into tp3_range
SELECT (SELECT MIN(CRE_DATETIME) FROM TEST_P3 PARTITION (ID_04K))
,(SELECT MAX(CRE_DATETIME) FROM TEST_P3 PARTITION (ID_04K))
,(SELECT MIN(id ) FROM TEST_P3 PARTITION (ID_04K))
,(SELECT MAX(ID ) FROM TEST_P3 PARTITION (ID_04K))
FROM DUAL

PLAN
---------------------------------------------------------
SORT AGGREGATE
PARTITION RANGE SINGLE cst:2 rws:1000
INDEX FULL SCAN (MIN/MAX) TP_CRE_DT cst:2 rws:1000
Statistics
----------------------------------------------------------
1 recursive calls
5 db block gets
9 consistent gets
0 physical reads

{You may wonder why the Explain Plan section above has a different look. This is because there seems to be a bug in 10.2.0.3 where the autotrace plan for the insert statement comes out wrong, as a FAST DUAL access, so I had to Explain the statement in a different way}

You would run one of the above statements against each partition. Probably, the "Best Practice" way would be to generate a SQL script from a query against DBA_TAB_PARTITIONS.

To populate my table I cheated - I just assumed all my partitions are of the same size (1000 records) and used:-
insert into tp3_range
select min(cre_datetime),max(cre_datetime),min(id),max(id)
from test_p3
-- where id between 10000 and 20000
group by trunc(id/1000)
/

Note I left in a commented line. You could run the above against the whole table and then limit it to just new partitions as you add them. This is a tad risky though, you are relying on the partitioning being perfect and it would not scale to hundreds of very large partitions. You would be better off with the partition-by-partition methods above.

You end up with a table like the below:-

select * from tp3_range order by min_cre_dati


MIN_CRE_DATI MAX_CRE_DATI MIN_ID MAX_ID
----------------- ----------------- ---------- ----------
01-JUL-2009 00:04 03-JUL-2009 18:36 1 999
03-JUL-2009 18:40 06-JUL-2009 13:16 1000 1999
06-JUL-2009 13:20 09-JUL-2009 07:56 2000 2999
09-JUL-2009 08:00 12-JUL-2009 02:36 3000 3999
12-JUL-2009 02:40 14-JUL-2009 21:16 4000 4999
14-JUL-2009 21:20 17-JUL-2009 15:56 5000 5999
17-JUL-2009 16:00 20-JUL-2009 10:36 6000 6999
20-JUL-2009 10:40 23-JUL-2009 05:16 7000 7999
23-JUL-2009 05:20 25-JUL-2009 23:56 8000 8999
26-JUL-2009 00:00 28-JUL-2009 18:36 9000 9999
28-JUL-2009 18:40 31-JUL-2009 13:16 10000 10999
31-JUL-2009 13:20 03-AUG-2009 07:56 11000 11999
...
14-OCT-2009 13:20 17-OCT-2009 07:56 38000 38999
17-OCT-2009 08:00 20-OCT-2009 02:36 39000 39999
20-OCT-2009 02:40 22-OCT-2009 21:16 40000 40999
22-OCT-2009 21:20 25-OCT-2009 15:56 41000 41999
25-OCT-2009 16:00 28-OCT-2009 10:36 42000 42999

add the two below indexes:
create index tp3r_micd_miid on tp3_range(min_cre_dati,min_id);
create index tp3r_macd_miad on tp3_range(max_cre_dati,max_id);

And now you can find the upper and lower ID bounds for a date range with the following code:

select min_id from tp3_range
where min_cre_dati = (select max(min_cre_dati)
from tp3_range
where min_cre_dati <TO_DATE('18-OCT_2009','DD-MON-YYYY')
)
MIN_ID
----------
39000

Execution Plan
---------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost 
---------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    22 |     2
|*  1 |  INDEX RANGE SCAN             | TP3R_MICD_MIID |     1 |    22 |     1
|   2 |   SORT AGGREGATE              |                |     1 |     9 |        
|   3 |    FIRST ROW                  |                |    40 |   360 |     1
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| TP3R_MICD_MIID |    40 |   360 |     1
---------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads

select max_id from tp3_range
where max_cre_dati = (select min(max_cre_dati)
from tp3_range
where max_cre_dati >to_date('20-OCT_2009','DD-MON-YYYY')
)

MAX_ID
----------
39999

Execution Plan
----------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost 
----------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    22 |     2
|*  1 |  INDEX RANGE SCAN             | TP3R_MACD_MIAD |     1 |    22 |     1
|   2 |   SORT AGGREGATE              |                |     1 |     9 |        
|   3 |    FIRST ROW                  |                |     4 |    36 |     1
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| TP3R_MACD_MIAD |     4 |    36 |     1
----------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads

Put it all together into one statement and let's see how much it costs:

with get_min_id as
(select min_id from tp3_range
where min_cre_dati = (select max(min_cre_dati)
from tp3_range
where min_cre_dati <TO_DATE('18-OCT_2009','DD-MON-YYYY')
) )
,get_max_id as
(select max_id from tp3_range
where max_cre_dati = (select min(max_cre_dati)
from tp3_range
where max_cre_dati >to_date('20-OCT_2009','DD-MON-YYYY')
) )
select count(*)
from test_p3
,get_min_id
,get_max_id
where id >get_min_id.min_id
and id < get_max_id.max_id
and cre_datetime between TO_DATE('18-OCT_2009','DD-MON-YYYY')
and TO_DATE('20-OCT_2009','DD-MON-YYYY')

COUNT(*)
----------
721
1 row selected.

Execution Plan
----------------------------------------------------------
| Id | Operation | Name | Row
s | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------
----------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 57 | 67 (5)| 00:00:01 | | |
| 1 | SORT AGGREGATE | |
1 | 57 | | | | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST_P3 |
2 | 26 | 65 (5)| 00:00:01 | | |
| 3 | NESTED LOOPS | |
2 | 114 | 65 (5)| 00:00:01 | | |
| 4 | MERGE JOIN CARTESIAN | |
1 | 44 | 2 (0)| 00:00:01 | | |
|* 5 | INDEX RANGE SCAN | TP3R_MICD_MIID |
1 | 22 | 1 (0)| 00:00:01 | | |
| 6 | SORT AGGREGATE | |
1 | 9 | | | | |
| 7 | FIRST ROW | |
40 | 360 | 1 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN (MIN/MAX) | TP3R_MICD_MIID |
40 | 360 | 1 (0)| 00:00:01 | | |
| 9 | BUFFER SORT | |
1 | 22 | 1 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | TP3R_MACD_MIAD |
1 | 22 | 1 (0)| 00:00:01 | | |
| 11 | SORT AGGREGATE | |
1 | 9 | | | | |
| 12 | FIRST ROW | |
4 | 36 | 1 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN (MIN/MAX) | TP3R_MACD_MIAD |
4 | 36 | 1 (0)| 00:00:01 | | |
| 14 | PARTITION RANGE ITERATOR | |
| | | | KEY | KEY |
| 15 | BITMAP CONVERSION TO ROWIDS | |
| | | | | |
| 16 | BITMAP AND | |
| | | | | |
| 17 | BITMAP CONVERSION FROM ROWIDS| |
| | | | | |
| 18 | SORT ORDER BY | |
| | | | | |
|* 19 | INDEX RANGE SCAN | TP3_PK | 7
07 | | 6 (0)| 00:00:01 | KEY | KEY |
| 20 | BITMAP CONVERSION FROM ROWIDS| |
| | | | | |
| 21 | SORT ORDER BY | |
| | | | | |
|* 22 | INDEX RANGE SCAN | TP_CRE_DT | 7
07 | | 48 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
133 consistent gets
0 physical reads

If you look way back up this post, you will see that the number of records selected by the above is the same as from the code indicated by Bernard (721 records) and for a lot less cost - 133 consistent gets compared to 892.

So I have hopefully explained the issue (having to visit all the index partitions to check the date range), shown how a ranges table can help, given some simple and a less-simple-but-more-efficient examples of code to populate the table and finally shown that using the range table can be more efficient.

And I arrogantly gave it a name - Correlated Partition Exclusion :-)

Some of you may remember tha this example has been a special case, as there is no overlap between the dates; the relationship between the CRE_DATETIME and ID is perfect. You are unlikely to have that in real life. Also, dome of you may also be tired of reading this post. So I will cover the general case in the NEXT post.

About these ads

Comments»

1. Assisting Partition Exclusion – Partitions for Performance « Martin Widlake’s Yet Another Oracle Blog - December 7, 2009

[...] VLDB, performance. Tags: explain plan, partitions, performance trackback <Previous Post…Next Post> Partitions are often used to help increase the performance of SQL select activity via the concept [...]

2. Bernard Polarski - December 8, 2009

Clever trick, but I am afraid it is not recommendable on OLTP system. The problems lies in the integrity of the returned boundaries in the last partition and this suppose providing past partitions are not modified.

The version with inline view over target partitioned table has bad performance but guaranteed that get_min_id/get_max_id returns the real current min and max id for any range period. Though the proposed solution solves the performance issue, it introduces the risk of discrepancy if the range boundaries data contained into the range table (tp3_range) is not in sync with the partitioned data : the final query will be wrong.

I already looked to a singled query with its inline view access restricted to the single partition but the code ‘ select * from table (partition X) does not accept a sub query on X for parsing reason I suspect.

Don’t work :

With get_max_id as (
select max(id)
from my_tab_partition partition (select partition_name from all_table_partitions where ….. ) ), ……

Parser is not happy for the access path, I presume.

There is also problem for the partition condition, in all_tab_partitions, is stored as long (col high_value) making a pain to use comparisons over it, and this may be overcome with a customized function.

The best I could achieve to reach my single query is to fetch first the partition name in a separate SQL and use it under the form:

create or replace funct_cvt_logn_to_date {

code varies following the hash values is a timestamp of date
}

col partition_name new_value my_partition noprint;

select partition_name from all_tab_partitions where funct_cvt_long_to_date(high_value) between &my_date1 and &my_date2 and owner = … and table_name=….;

– My single query becomes. It is parable at last moment when &my_partition gets its value:

with get_min_id as (select min(id) from my_part_table partition (&my_partition) ),
with get_max_id as (select min(id) from my_part_table partition (&my_partition) )
select ……. from my_part_table where …..

Alas, on OLTP system, with heavy concurrent access, I fear the load on data dictionary. So at the end I do prefer to pay the gets to each root block local index. At worse, this is a known and predictable load.

mwidlake - December 8, 2009

Wow! Your reply is as long as one of my posts.

I find Partitions less useful in OLTP (but they do have their place) than DW. The issue you highlight about things being beyond those bounds I will address today (err, maybe tomorrow). But I don’t really address things being changed… I might tweak the next post for that. It is not restricted to OLTP of course, but with a DW, for most queryies lacking one record is sometimes acceptable, eg for reporting stats on monthly growth changes. So long as the financial system catches everything that’s fine.

If you want to alter the partiton you look at, try dynamic SQL from within PL/SQL. {I no longer claim any skill in PL/SQL but I have done similar in the past}. You function for extracting the end ranges from the data dictionary is nice. PL/SQL works around Oracle’s Very Vexing decision to use a LONG for the HIGH_VALUE (it is a LONG in the underlying sys.tabpartv$ and sys.tabcompartv$ objects too, so you can’t side-step it.). You could work it out once and put the data into a range table though :-)

You are right to be wary of heavy access on the data dictionary for OLTP. I am fighting slow performance of a DW data dictionary at the moment. Gathered all fixed object and dictionary stats but there are so many segements and the DBA_ views are so complex that performance for some statements is very bad.

I suspect my next post will not be good enough for your situatuation Bernard, but maybe it might.

3. Bernard Polarski - December 8, 2009

The functions to convert any date stored into a long and used into SQL are not easy. This is my implementation. If somebody has something simpler then I will gladly adopt:

Providing ‘system’ will use these functions, he needs direct grants:

grant select on dba_part_key_columns to system ;
grant select on dba_tab_columns to system ;

create or replace function long_to_str( P_OWNER varchar2, P_TABLE varchar2, P_PART_NAME varchar2 ) return varchar2
is
var all_tab_partitions.high_value%type;
begin
select high_value into var from all_tab_partitions
where
TABLE_OWNER = P_OWNER
and TABLE_NAME = P_TABLE
and PARTITION_NAME = P_PART_NAME ;
return var;
end;

create or replace function get_col_type ( P_OWNER varchar2, P_TABLE varchar2 ) return varchar2 is
var varchar2(20);
begin
select data_type into var from dba_part_key_columns a, dba_tab_columns b
where a.owner = P_OWNER and a.name = P_TABLE and
b.OWNER = a.OWNER and
b.table_name = a.name and a.COLUMN_POSITION = 1 and a.COLUMN_NAME = b.COLUMN_NAME ;
if instr(var,’(‘) > 0 then
return substr(var,1,instr(var,’(‘)-1) ;
else
return var ;
end if;
end;

create or replace function extract_hv_date(P_OWNER in varchar2,P_TABLE in varchar2, P_PARTITION_NAME in varchar2) return varchar2
is
v_hv varchar2(10) ;
v_col_type varchar2(10) ; — Possible Value are : Date (date) Number (Julian date)
— varchar2 (date in char) Integer (Another form of Julian date)
v_hv_num number ;
sqlcmd varchar2(512);
ret varchar2(30);

begin

v_col_type:=get_col_type(P_OWNER,P_TABLE);
if v_col_type = ‘DATE’ or v_col_type = ‘TIMESTAMP’ then
sqlcmd:=’select regexp_replace(
long_to_str(table_owner, table_name, partition_name),
”.*([[:digit:]][[:digit:]][[:digit:]][[:digit:]].[[:digit:]][[:digit:]].[[:digit:]][[:digit:]]).*”, ”\1”)
from all_tab_partitions
where TABLE_OWNER = ”’||P_OWNER||”’ and
TABLE_NAME = ”’||P_TABLE || ”’ and
PARTITION_NAME = ”’||P_PARTITION_NAME || ””;
execute immediate sqlcmd into v_hv ;
return substr(v_hv,1,4)||substr(v_hv,6,2)||substr(v_hv,9,2); — return YYYYMMDD

elsif v_col_type = ‘NUMBER’ or v_col_type = ‘INTEGER’ then
select high_value into v_hv_num
from all_tab_partitions
where TABLE_OWNER = P_OWNER and
TABLE_NAME = P_TABLE and
PARTITION_NAME = P_PARTITION_NAME;
return to_char(to_date(v_hv_num,’J’),’YYYYMMDD’);
end if;
— For varchar2 type column, you need to provide your own code as the date format is unknown, if it is a date :p
end ;

– how to use it:

1 select TABLE_OWNER, TABLE_NAME, PARTITION_NAME,
2 extract_hv_date(table_owner,table_name,partition_name) date_in_str_format
3 from all_tab_partitions
4* where table_owner = ‘CUSTOMER’ and table_name = ‘TXN’

–output :

TABLE_OWNER TABLE_NAME PARTITION_NAME DATE_IN_STR_FORMAT
———— ———- —————- ——————
CUSTOMER TXN TXN20091210_12 20091210
CUSTOMER TXN TXN20091210_00 20091210
CUSTOMER TXN TXN20091209_12 20091209
CUSTOMER TXN TXN20091209_00 20091209
CUSTOMER TXN TXN20091208_12 20091208
CUSTOMER TXN TXN20091208_00 20091208
CUSTOMER TXN TXN20091207_12 20091207
CUSTOMER TXN TXN20091207_00 20091207
CUSTOMER TXN TXN20091206_12 20091206

mwidlake - December 8, 2009

Thanks for sharing that Bernard, code examples are always good to have.

4. Log Buffer #172: a Carnival of the Vanities for DBAs | The Pythian Blog - December 11, 2009

[...] Martin Widlake introduces, “ . . . a tuning method with partition exclusion where you infer a relationship between the value you want to limit a query on and the partition key. It takes a while to explain the theory so I am going to give it a name, in the vain hope it catches on . . .  I’m going to call it Correlated Partition Exclusion.” [...]

5. Ensuring Correlated Partition Exclusion #2 « Martin Widlake’s Yet Another Oracle Blog - December 20, 2009

[...] Ensuring Correlated Partition Exclusion #2 December 20, 2009 Posted by mwidlake in VLDB, performance. Tags: partitions, performance, VLDB trackback <Previous Post… [...]

6. coskan - December 22, 2009

Martin,

Nice trick,

For the explain plan layout maybe “pre” tag can work instead of source tag

Regards

7. Blogroll Report 04/12/2009-11/12/2009 « Coskan’s Approach to Oracle - December 23, 2009

[...] Martin Widlake -Ensuring Correlated Partition Exclusion [...]


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.

Join 156 other followers

%d bloggers like this: