RE: Partitioning by first digit of VARCHAR column data

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 27 Aug 2004 10:39:32 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKAEHBFEAA.mwf@rsiz.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Surendra.Tirumala_at_ky.gov
Sent: Friday, August 27, 2004 7:46 AM
To: oracle-l_at_freelists.org
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_at_yahoo.co.uk] Sent: Thursday, August 26, 2004 10:51 PM To: oracle-l_at_freelists.org
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_at_ky.gov 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_at_freelists.org
  > put 'unsubscribe' in the subject line.
  > --
  > Archives are at http://www.freelists.org/archives/oracle-l/
  > FAQ is at http://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_at_yahoo.com

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Aug 27 2004 - 10:39:46 CDT