 | |
Is function index the best solution?
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Is function index the best solution? -
09-26-2010
, 08:46 PM
#2
| |||
| |||
|
|
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 |
wildcards in the WHERE clause which guarantees you a full tablescan.
David Fitzjarrell
#3
| |||
| |||
|
|
On Sep 26, 9:46*pm, Robert Nicholson <robert.nichol... (AT) gmail (DOT) 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 |
ie. wrapping the likes in a case block is an appropriate remedy for
this
type of problem?
Quote:
|
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 |
#4
| |||
| |||
|
|
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 |
and condition on REVERSE(ACCOUNT) LIKE '1111%'.
SQL> select REVERSE('MICHEL') from dual;
REVERS
------
LEHCIM
Regards
Michel
#5
| |||
| |||
|
|
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.