Re: long message list sorted by time

Hi Lothar,

The second query can still take advantage of the simple index, it should be able to apply the Top N against just the index - you might need to write the query in a way so that it gets top N rowids and then joins back to the table.

The third work similarly but when you get to a sufficiently high selectivity for your account_name, it might be worth just reading everything in descending date order (so the date column as the first column in your index).

Hopefully that helps you get started.
Andy

On Fri, 21 Jun 2019 at 07:04, l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> wrote:

> Hi,
>
> i got a special kind of issue today. There is a big list of logs and we
> have to return the first x (e.g. 1000) entries sorted on time, newest first.
> In order to simplify the issue we just discuss one search criteria and
> this will be account name.
>
> There are three scenarios:
>
> 1.) account name = 'XXX' (simple)
> 2.) account_name like 'ABE%' (not simple)
> 3.) account_name in (..…) potentially a log list
>
> Rather than just by rowcount we could potentially limit additionally by a
> number of days in the past. One day has between 1.5 million up tp 8
> million rows.
> Response time should be below 10 seconds.
> There are 180 days worth of data kept online. Altogether 800 million rows.
> Account_name has 2758 distinct values.
> As you can see even the result for one account can be very big.
> Of course for case 1.) an index (account_name, create_time desc) avoids
> sorting and the answer is instand.
> case 2 and 3 are not that easy.
> I think I would need a 2 dimensional index like a spatial index… or can
> even a text index do the trick?
> (This is standard Edition.)
> I guess somebody has already worked on such case.
>
> Regards
>
> Lothar
>
>

--
http://www.freelists.org/webpage/oracle-l