Groups > comp.lang.java.databases > #510

Re: Searching for ranges

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!usenet.ukfsn.org!not-for-mail
From	Martin Gregorie <martin@address-in-sig.invalid>
Newsgroups	comp.lang.java.databases
Subject	Re: Searching for ranges
Date	Mon, 21 Nov 2011 21:18:53 +0000 (UTC)
Organization	UK Free Software Network
Lines	29
Message-ID	<jaef7t$r47$1@localhost.localdomain> (permalink)
References	<nqukc7ta1mrd128h8tqbabo9k9e76eq7ak@4ax.com>
NNTP-Posting-Host	84.45.235.129
Mime-Version	1.0
Content-Type	text/plain; charset=UTF-8
Content-Transfer-Encoding	8bit
X-Trace	localhost.localdomain 1321910333 27783 84.45.235.129 (21 Nov 2011 21:18:53 GMT)
X-Complaints-To	usenet@localhost.localdomain
NNTP-Posting-Date	Mon, 21 Nov 2011 21:18:53 +0000 (UTC)
User-Agent	Pan/0.135 (Tomorrow I'll Wake Up and Scald Myself with Tea; GIT 30dc37b master)
Xref	x330-a1.tempe.blueboxinc.net comp.lang.java.databases:510

Show key headers only | View raw

On Mon, 21 Nov 2011 08:56:45 -0800, Roedy Green wrote:

> I was wondering about techniques for using SQL to find records with a
> low-high associated range, especially when the ranges are contiguous, or
> when the range sizes are a multiple of some integer.
> 
> I am familiar with in-RAM technique such as binary search and dividing
> by the range size/atomicity to get an index.
> 
> I suspect just asking for  table.low <= wanted < table.high won't be
> that clever, even if low and high are indexed.
>
Au contraire, that should be pretty good given a decent RDBMS, provided 
that:

- a frequency analysis is done to ensure that the costs of maintaining
  the index don't damage the performance of other queries.

- the query using the new index isn't so complex that the effect of the
  index becomes irrelevant or even harmful. EXPLAIN is your friend here
  for PostgreSQL and some other DBMS. Those that don't implement 
  EXPLAIN should provide a similar tool. If the DBMS you're using
  doesn't, consider carefully if you should be using it for anything.
  

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

Back to comp.lang.java.databases | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Searching for ranges Roedy Green <see_website@mindprod.com.invalid> - 2011-11-21 08:56 -0800
  Re: Searching for ranges Martin Gregorie <martin@address-in-sig.invalid> - 2011-11-21 21:18 +0000
  Re: Searching for ranges David Lee Lambert <davidl@lmert.com> - 2012-01-17 11:59 -0800
    Re: Searching for ranges Roedy Green <see_website@mindprod.com.invalid> - 2012-01-19 09:32 -0800

csiph-web