Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: less than, index and string

Re: less than, index and string

From: <fitzjarrell_at_cox.net>
Date: 14 Feb 2006 10:11:52 -0800
Message-ID: <1139940712.706580.254900@g43g2000cwa.googlegroups.com>


Comments embedded.
tmp123 wrote:
> Hello,
>
> First of all, sorry if this question is off-topic in this group. In
> this case, I will welcome a sugestion about the correct one.
>
> I'm starting with oracle, and I've a very basic doubt. Could someone
> sugest me an actiion line, or a reference to read, ... . Thanks.
>
> Question:
>
> I'm thinking on define a table with 3 columns. The first two are an
> array of 10 digits (10 numbers from '0' to '9'. It could be taken as an
> ASCII string). The last one is a NUMBER. I will call them CMIN, CMAX
> and CRES. It is a fact that CMIN<=CMAX and that ranges [CMIN,CMAX]
> never overlap.

Yet this doesn't tell the entire story. Are these 10-digit values always 10 digits? Are there leading 0's? If so then varchar2(10) would be your choice. If these values have a maximum length of 10 digits, absent leading 0's, then a number(10,0) would be a better solution, as ASCII 'numbers' don't sort in numerical sequence ('1','10','11','12','13',..'18','19', '2', '20' ...) You could use the to_number() or cast() functions to make them numbers again (so they would sort 'properly') but in that case I'd store them as numbers. Presentation can always be set in the application using lpad().

> The "input/key" of the query is another 10 digits value (X). The
> objective of the query is to select the one with CMIN>=X AND CMAX<=X,
> to known the value of CRES.
>

Again, these comparisons are defined for CMIN and CMAX as numbers, or as ASCII strings? There is a difference.

> My doubt is: which definition of an index for the table is the better
> one?. One using only CMIN? Combine CMIN and CMAX?
>

Will you always be using CMIN and CMAX in a WHERE clause? If so a concatenated index (CMIN, CMAX) might work for you. If not, then individual indexes might be better. You won't really know until you build the table, load some data and run the query, generating an execution plan. And, until you do, we really won't know what to tell you, either. All suggestions at this point are guesswork (educated guesswork, but gueswork nonetheless).

> Thanks a lot for your time.

David Fitzjarrell Received on Tue Feb 14 2006 - 12:11:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US