TimesTen - Performance Optimization of Queries With Complex "Where" Clauses
Last updated on SEPTEMBER 23, 2016
Applies to:TimesTen Data Server - Version 188.8.131.52.0 to 184.108.40.206.6 [Release 7.0 to 11.2]
Information in this document applies to any platform.
This Note will be of interest to all TimesTen DBAs and application developers.
***Checked for relevance on 15-Jul-2013***
Given a table with the following structure:
create unique index ic1 on t1(c1);
create unique index ic2 on t1(c2);
the table has been randomly populated with 500,000 rows. Optimizer statistics have been run against the table specifying 100% sample size.
We then execute the following queries against the table:
< 5408, 5408, 28454 >
1 row found.
Execution time (SQLExecute + Fetch Loop) = 0.000046 seconds.
/**** query uses index on column c1 ****/
Command> select * from t1 where c2 = 19899;
< 19899, 19899, 18877 >
1 row found.
Execution time (SQLExecute + Fetch Loop) = 0.000053 seconds.
/**** query uses index on column c2 ****/
Command> select * from t1 where c1 = 400 or c2 = 211;
< 211, 211, 14573 >
< 400, 400, 29440 >
2 rows found.
Execution time (SQLExecute + Fetch Loop) = 0.054562 seconds.
/**** complex "where" clause takes much longer to execute ****/
How can we reduce the execution time on this query with the complex "where" clause ???
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms