Re: RE: cheapest way to access every 15th row in table ordered by timestamp column
Date: Tue, 1 Feb 2011 21:46:47 +0800
Message-ID: <AANLkTik8Os0jXZOsWtC4M1g2KkcRuQf4hY5Td-1oas+x_at_mail.gmail.com>
On Thu, Jan 20, 2011 at 6:29 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> I�ll not touch a hair on your head. Since it was asked what is cheapest,
> your solution would prevail for some possible row length characteristics.
>
>
>
> If, by the way, there is an index (single column or relatively narrow set
> of columns) on the timestamp AND each row is very long (what I believe Niall
> means by a large dataset, although he could mean simply a big total size for
> the table), THEN it could well be cheapest to force the desired rowids out
> of the index in the inner part of the query and only bring back the required
> columns in the outer query.
>
>
>
>
>
How about indexing (approximately) every 15th row ?
create index BLAH on MY_TABLE (
case when mod(to_number(to_char(timestamp_col,'FF')),15) = 0 then pk_col
end
)
-- Connor McDonald =========================== email: connor_mcdonald_at_yahoo.com web: http://www.oracledba.co.uk "Semper in excremento, sole profundum qui variat" -- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 01 2011 - 07:46:47 CST