Re: Is function index the best solution?

"Robert Nicholson" <robert.nicholson_at_gmail.com> a écrit dans le message de news: 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

Use an FBI on REVERSE(ACCOUNT)
and condition on REVERSE(ACCOUNT) LIKE '1111%'.

SQL> select REVERSE('MICHEL') from dual; REVERS