Re: How can I optimize a query on two indexed columns?

From: Michael S. Abbey <masint_at_istar.ca>
Date: Sun, 19 Apr 1998 15:42:03 GMT
Message-ID: <353a1b05.143276811@news.istar.ca>

On Sun, 19 Apr 1998 00:30:30 +0200, "José Lima Suárez" <pplima_at_jet.es> wrote:

>Hi,
>I have a query like this:
>select col1, col2
>from tab1
>where col1 = 'x' and col2 = 'y'
>and both col1 and col2 have differents indexes, col1 has a lot of rows per
>value so if Oracle uses this index is slowly than if uses the col2 index
>(that has a few rows per value).
>
>is there any way to force the index to use in the query execution?
>

You might try placint a composite index (i.e., on both col1 and col2) on the table. Place the col2 column first in the index, check the performance, then try it the other way around. Yourr esults will be different than what one might expect if you are using the rule-based optimizer rather than cost.

Michael

+------------------------------------
+  Michael S. Abbey    Ottawa Canada
+  Co-author of Oracle8: A Beginner's Guide

+ and 5 other works in the Oracle Press Series

+------------------------------------

Received on Sun Apr 19 1998 - 10:42:03 CDT