Wednesday, July 22, 2009

How to get the name of the Partition within a table that a ROWID comes from...

Oracle's partitioning feature is extremely handy for allowing you to break up a huge table into smaller, more manageable pieces.

I use it extensively for Datawarehousing - where it allows one to add and remove fact data partitioned by time/date very efficiently.

What is hard, however - is to find out which partition a particular row comes from within a table. Well - not any more. Just create this handy little function to get the name of the partition that any row comes from (using the "ROWID" pseudocolumn).

Here goes:

CREATE OR REPLACE FUNCTION get_partition_name_for_rowid (
p_rowid IN ROWID)
RETURN VARCHAR2 AS
-- Variables
l_all_objects_rec all_objects%ROWTYPE;
l_partition_name all_tab_partitions.partition_name%TYPE;
-- Custom Exceptions
rowid_not_from_a_partition EXCEPTION;
BEGIN
SELECT /*+ FIRST_ROWS */
*
INTO l_all_objects_rec
FROM all_objects
WHERE data_object_id = dbms_mview.pmarker (p_rowid);

IF l_all_objects_rec.object_type = 'TABLE PARTITION' THEN
l_partition_name := l_all_objects_rec.subobject_name;
ELSE
RAISE rowid_not_from_a_partition;
END IF;

RETURN l_partition_name;
EXCEPTION
WHEN rowid_not_from_a_partition THEN
raise_application_error (-20101
, build_string (p_input_string => 'ROWID: %s is in table: %s - which is not partitioned.'
, str1 => p_rowid
, str2 => l_all_objects_rec.object_name));
WHEN OTHERS THEN
RAISE;
END get_partition_name_for_rowid;
/




Just run this SQL to test it:

CREATE TABLE part_table
(partition_key INTEGER
, some_other_column VARCHAR2 (30)
)
PARTITION BY RANGE (partition_key)
( PARTITION p1 VALUES LESS THAN (50)
, PARTITION p2 VALUES LESS THAN (MAXVALUE)
)
;

INSERT INTO part_table
(partition_key, some_other_column)
SELECT ROWNUM
, TO_CHAR (ROWNUM)
FROM all_objects
WHERE ROWNUM <= 100;

SELECT get_partition_name_for_rowid (ROWID) AS partition_name
, partition_key
, some_other_column
FROM part_table
WHERE partition_key BETWEEN 47 AND 52;



Semper Fidelis

Friday, July 10, 2009

Handy Function for parsing strings in PL/SQL!

One common function needed for PL/SQL is some sort of string parsing solution. For instance, if you want to parse out the "3rd" position of a string delimited by an "/" character.

My buddy Todd had written a handy function in Visual Basic for Applications, and even converted it for use in Oracle. After I saw Tom Kyte use a combination of INSTR and SUBSTR to parse strings, I rewrote Todd's approach to perform a little better.

Here's the code in case you are interested in this handy little string function:

CREATE OR REPLACE FUNCTION parsestring (
p_string IN VARCHAR2
, p_delimiter IN VARCHAR2
, p_position IN INTEGER
)
/**************************************************************
Function: parsestring

Version: 2.0

Author: Philip Moore - (version 2.0)

Original Author: Todd Ward - (version 1.0)

Date: 23-JAN-2006

Description: This function's purpose is to return
the "p_position"th portion of the string
"p_string", delimited by "p_delimiter".

Example call: parsestring('a/b/c', '/', 2) Returns: 'b'
**************************************************************/
RETURN VARCHAR2
AUTHID CURRENT_USER
PARALLEL_ENABLE
DETERMINISTIC
AS
-- Variables
v_txt VARCHAR2 (32767);
BEGIN
v_txt := p_delimiter || p_string || p_delimiter;
RETURN SUBSTR (v_txt
, INSTR (v_txt
, p_delimiter
, 1
, p_position
) + LENGTH (p_delimiter)
, INSTR (v_txt
, p_delimiter
, 1
, p_position + 1
) - INSTR (v_txt
, p_delimiter
, 1
, p_position
) - LENGTH (p_delimiter)
);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END parsestring;
/

SHOW ERRORS;



Now - note that this will not perform as well as using the SUBSTR and INSTR combination of functions shown in the source code itself - because of the PL/SQL context switches when run in the SQL engine. But I personally think that it is MUCH easier to remember the syntax for calling "PARSESTRING" than for calling the complex SUBSTR/INSTR combination.

I've used this function many thousands of times over the years - and I believe that you will find it handy as well.

Good luck!