Re: Is function index the best solution?

From: Robert Nicholson <robert.nicholson_at_gmail.com>
Date: Sun, 26 Sep 2010 21:42:11 -0700 (PDT)
Message-ID: <5c23a21d-0bbc-4f67-9207-5a21986455a2_at_26g2000yqv.googlegroups.com>

On Sep 26, 10:39 pm, ddf <orat..._at_msn.com> wrote:
> 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
>

Yes that is correct. The question is whether a function index in account
ie. wrapping the likes in a case block is an appropriate remedy for this
type of problem?

> > 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 - 23:42:11 CDT