Re: How to query partitioned tables?

rekaben_at_yahoo.com (RK) wrote in message news:<239f1935.0402161029.606b73f8_at_posting.google.com>...
> "Ron" <support_at_dbainfopower.com> wrote in message news:<e5idneLuipULPrDdRVn-uQ_at_comcast.com>...
> > Hello RK,
> >
> > You are correct - small PL/SQL procedure cam be used to generate
> > necessary SQL and "execute immediate" to execute it.
> >
> > Please, let us know if you'd need any additional help on it.
> >
> >
>
> Thanks. I am not granted the right to write a procedure, I won't
> bother to try it out. However I do have another question here:
>
> If I am querying on a date field as follows:
>
> (1) select count(*) from table1 where dt_f1 = to_date('0210','MMDD');
>
> (2) select count(*) from table1 where dt_f1 >= to_date('0210','MMDD')
> and dt_f1 < to_date('0211','MMDD');
>
> (1) may not give me the correct count result, while (2) usually does.
> Why?
>
> Next question, dt_f1 is the field used to partition table1, which is
> one day one partition, in the daily partition, only the same 'MMDD'
> value for dt_f1 may be allowed. If I am using the following:
>
> (3) select count(*) from table1 partition(0210_name);
>
> I got a somehow slightly bigger count number than if I am using (2)
> (result from (2) should be correct as I have the same data count from
> my data files). The daily partition is truncated and loaded every day,
> so it can not contain other date value for dt_f1. Can you explain
> possibly what happens here?
>
> RK

COME ON, this is fundamental datatype concepts.  Have you even looked in the oracle manuals yet?!

A DATE includes YEAR MONTH DAY HOUR MINUTE SECOND. NOTE the actual value used by the DB is a decimal number. When you convert the string '0210' to a date you get the value equivalent to:
2004 February 10 12:00:00 AM
(remember internally it's a number.)

So if you stored dates in that column with full values like 2004 February 10 05:28:37 PM
(internally a different number than before) then of course they don't match.

 so your first query finds few if any rows to count and the second query works appropriately (even to using a range search if indexed properly).

I haven't worked as much with partions to have a suggestion for the last one, but it's obviously counting something different than the first two.

Suggestion: read the manual or at least take a basic ORACLE training class. You'll be much more productive in the long run.

Ed Received on Wed Feb 18 2004 - 16:38:16 CST