Re: Is function index the best solution?

From: ddf <oratune_at_msn.com>
Date: Sun, 26 Sep 2010 20:39:22 -0700 (PDT)
Message-ID: <746e6bf8-93be-4022-852a-7da097fc3fee_at_k1g2000prl.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

I see no function in use against the ACCOUNT column; I do see leading wildcards in the WHERE clause which guarantees you a full tablescan.

David Fitzjarrell Received on Sun Sep 26 2010 - 22:39:22 CDT