[prev in list] [next in list] [prev in thread] [next in thread] 

List:       odtug-sqlplus-l
Subject:    SV: Q about selecting date ranges ... am I missing something? Is
From:       Kim Berg Hansen <kbh () thansen ! dk>
Date:       2010-08-16 10:51:27
Message-ID: DDF46044E4720E4F8B522F434160814705E0DC644135 () EXCH01SVR ! thansen ! dk
[Download RAW message or body]

Hi Suzanne

Don't know if you already have an answer to this, but here's a little input anyway \
:-)


Depending on the amount of data I would guess that your idea of a table with PKey and \
Date for every date between DBegin and DEnd would result in the fastest lookup \
possible. If DBegin/DEnd columns in your main table does not change often (that is \
the PKey/Date table will have mostly inserts and few updates), then an Index \
Organized Table (rather than a composite index of Date,PKey) could be an idea.

But the idea does cost quite a bit in the form of lots of extra data and work to do \
to maintain that table. If very fast lookup is crucial, it might be worth it - but I \
guess in most cases it could be overkill.


Alternative would be to make do with the Main table as is with a bit of extra \
indexing. For doing that you probably should change your selection clause.

This clause cannot use index on DBegin or DEnd (AFAIK):

	where :Date_Of_Interest between DBegin and DEnd

This clause can make use of indexes:

	where DBegin <= :Date_Of_Interest
	and   DEnd   >= :Date_Of_Interest

Now you could have an index on DBegin and another index on DEnd.
(The optimizer could then perhaps choose 2 index scans and hash the bits together.)
Or you could have a composite index with both dates.

What works best would depend on the actual data and use cases.

I could guess, that Main table grows over time leaving historical records.
Such that over time most records would have both DBegin and DEnd in the past.
And that queries most often would be with :Date_Of_Interest = SysDate (or at least a \
relatively "recent" date.) If that is the use case, then "DEnd >= :Date_Of_Interest" \
would be a very discriminating where clause - few percent of the data would match \
this. And so a composite index of "DEnd, DBegin" could probably work "fast enough".


That's my 2 cents on this - but do test it thoroughly.
There can be no "one correct answer" to this question - it all depends on the \
application :-) (If for example your selects always have an optimal path to let the \
Details table be driving (perfect index on X1 selecting only fractions of the table), \
then the discussion is moot as Main is always hit via the primary key...)


Med venlig hilsen / Best regards
 
T. Hansen Gruppen A/S
 
 
Kim Berg Hansen
Chef-udvikler / Senior System Developer
 
web: www.thansen.dk
e-mail: kbh@thansen.dk
 
Tlf: +45 63 41 69 00
Fax: +45 63 41 69 90


-----Oprindelig meddelelse-----
Fra: odtug-sqlplus-l-bounce@fatcity.com [mailto:odtug-sqlplus-l-bounce@fatcity.com] \
                På vegne af Michelle, Suzanne
Sendt: 29. juli 2010 22:21
Til: ODTUG-DEVSUITE-L@fatcity.com; ODTUG-SQLPLUS-L@fatcity.com
Emne: Q about selecting date ranges ... am I missing something? Is there a better \
way?

I have a table with a primary key column, and two date columns (among
other cols for various reasons, but Q relates to dates). Let's call this
Main, with PKey and DBegin, DEnd, etc.
I want to select all the records that will be in effect on a certain
date ... 
something along the lines of "Select .... from Main where
Date_Of_Interest between DBegin and DEnd"

Better still ... I have a child table against Main ... let's call it
Details, same Parent Key and an arbitrary "LineNo" that users assign to
rank the details (and please ignore your misgivings about this design,
unless this has relevance to the answer) ...

I want to select from Details based on some criteria AND whether each
parent Main record DBegin/DEnd are relevant ...
something along the lines of ....
"Select X1, X2, X3 ... XN from Details D, Main M
 where D.X1 = Val1 and D.X2 = Val2 and M.PKey = D.PKeyPart1
and Date_of_Interest between M.DBegin and M.DEnd"

What is the best way to index / not index / design the "Main" table to
deal with selection by date?

The only way I can think of is to have another Child Table (of sorts)
against Main that has 2 fields, PKey and Date, where Date is every Date
between the DBegin and DEnd of Main, with an index on both fields
individually (as well as both fields being part of a composite primary
key).

Is there another way to do this? Am I completely nuts? Did I miss some
spectacular Oracle widget or design configuration?

And, yes, I know I can create a view to do this (marrying the Main table
with a generic calendar), but ... the view seems slow ... thus my
question.

Please advise, thanks in advance.

Suzanne Michelle
TIS - Applications, UGOS
2 Broadway A13.32
desk: 646-252-8663
cell: 347-907-1125


[prev in list] [next in list] [prev in thread] [next in thread]