Re: Is function index the best solution?

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Mon, 27 Sep 2010 12:21:35 -0700 (PDT)
Message-ID: <cdda5c53-0469-475c-b3b9-b77b8b489239_at_f25g2000yqc.googlegroups.com>

On Sep 26, 9:46 pm, Robert Nicholson <robert.nichol..._at_gmail.com> wrote:
> We have a query that uses the following style of query
>
> WHERE TYPE in ('A','B') AND (ACCOUNT LIKE '%9999' OR ACCOUNT LIKE
> '%1111) AND TIME >= :1 AND TIME <= :2 order by TIME
>
> ...
>
> right now this query tablescans
>
> Is a function index using ACCOUNT the only way to avoid a tablescan?
>
> There are indexes on account and the time columns but this query still
> performs a full tablescan.
>
> Execution Statistics
>
>         Total   Per Execution   Per Row
>
> executions      163     1       n/a
> Elapsed Time (sec)      12,932.65       79.34   n/a
> CPU Time (sec)  4,615.64        28.32   n/a
> Buffer Gets     198,614,100     1,218,491.41    n/a
> Disk Reads      63,106,576      387,156.91      n/a
> Direct Writes   0       0.00    n/a
> Rows    0       0.00    n/a
> Fetches 162     0.99    n/a

Hi Robert,

I honestly have no clue. How selective is the (assumable) date range of AND TIME >= :1 AND TIME <= :2? How many accounts will end with 9999 or 1111 as a percentage of the table? If that is a large percentage, an index may not matter.

Post some data and table sizes and the feedback will probably be measurably better.

Thanks,

Steve Received on Mon Sep 27 2010 - 14:21:35 CDT