TimesTen - Performance Optimization of Queries With Complex "Where" Clauses (Doc ID 1381928.1)

Last updated on SEPTEMBER 23, 2016

Applies to:

TimesTen Data Server - Version 7.0.0.0.0 to 11.2.1.8.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***

Goal

Given a table with the following structure:

create table t1
(
c1 int,
c2 int,
c3 int
);

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:

Command> select * from t1 where c1 = 5408;
< 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 ???

Solution

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