Is function index the best solution?

From: Robert Nicholson <robert.nicholson_at_gmail.com>
Date: Sun, 26 Sep 2010 18:46:39 -0700 (PDT)
Message-ID: <9804a980-d9e9-4596-b012-000159e1a7b6_at_i5g2000yqe.googlegroups.com>

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

Received on Sun Sep 26 2010 - 20:46:39 CDT