RE: Partitioning by first digit of VARCHAR column data
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Aug 2004 10:39:32 -0400
Connor's way is probably faster and smaller, but since varchar lexical sort
is left to right, I'm not sure what the problem was with using the whole
varchar as part of the composite range key when you want the trailing bit to
be governed by the first char. Or did you mean searching through the varchar
until it came to a digit? Clearly you've got a good solution, but I'm
curious what you were trying to accomplish.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
Surendra.Tirumala@xxxxxx
Sent: Friday, August 27, 2004 7:46 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Partitioning by first digit of VARCHAR column data
Thank you Connor!!
We have just decided to go for range on year and hash on varchar2.
Thanks again.
Surendra
-----Original Message-----
From: Connor McDonald [mailto:hamcdc@xxxxxxxxxxx]
Sent: Thursday, August 26, 2004 10:51 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Partitioning by first digit of VARCHAR column data
Bang a trigger on to populate a new column and partition on that.
Alternatively, use dbms_utility.get_hash_value to see how your varchar2 map
out across 4
partitions. You might be able to get away with just a range on year and
hash on varchar2.
Connor
--- Surendra.Tirumala@xxxxxx wrote:
> Hi,
>
> I have a table with several columns which include a NUMBER(year) and a
> VARCHAR(some code) columns. The VARCHAR column has values that start with
> either 1 or 2 or 3 or 4. I would like to have composite range partition
key
> based on YEAR and the first digit of the VARCHAR column. Is there any way
I
> can accomplish this? Can I use some function like SUBSTR to partition the
> table based on first digit of VARCHAR column data?
>
> Thanks for your help.
> Surendra
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
___________________________________________________________ALL-NEW Yahoo!
Messenger - all new features - even more fun! http://uk.messenger.yahoo.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
