[ACCEPTED]-Can Multiple Indexes Work Together?-indexing

Oracle will almost certainly use the most 31 selective index to drive the query, and 30 you can check that with the explain plan.

Furthermore, Oracle 29 can combine the use of both indexes in a 28 couple of ways -- it can convert btree indexes 27 to bitmaps and perform a bitmap ANd operation 26 on them, or it can perform a hash join on 25 the rowid's returned by the two indexes.

One 24 important consideration here might be any 23 correlation between the values being queried. If 22 foo='hello' accounts for 80% of values in 21 the table and bar='world' accounts for 10%, then 20 Oracle is going to estimate that the query 19 will return 0.8*0.1= 8% of the table rows. However 18 this may not be correct - the query may 17 actually return 10% of the rwos or even 16 0% of the rows depending on how correlated 15 the values are. Now, depending on the distribution 14 of those rows throughout the table it may 13 not be efficient to use an index to find 12 them. You may still need to access (say) 70% or 11 the table blocks to retrieve the required 10 rows (google for "clustering factor"), in 9 which case Oracle is going to perform a 8 ful table scan if it gets the estimation 7 correct.

In 11g you can collect multicolumn 6 statistics to help with this situation I 5 believe. In 9i and 10g you can use dynamic 4 sampling to get a very good estimation of 3 the number of rows to be retrieved.

To get 2 the execution plan do this:

explain plan for
SELECT *
FROM   sometable
WHERE  foo='hello' AND bar='world'
/
select * from table(dbms_xplan.display)
/

Contrast that 1 with:

explain plan for
SELECT /*+ dynamic_sampling(4) */
       *
FROM   sometable
WHERE  foo='hello' AND bar='world'
/
select * from table(dbms_xplan.display)
/

Eli,

In a comment you wrote:

Unfortunately, I 22 have a table with lots of columns each with 21 their own index. Users can query any combination 20 of fields, so I can't efficiently create 19 indexes on each field combination. But if 18 I did only have two fields needing indexes, I'd 17 completely agree with your suggestion to 16 use two indexes. – Eli Courtwright (Sep 15 29 at 15:51)

This is actually rather crucial 14 information. Sometimes programmers outsmart 13 themselves when asking questions. They try 12 to distill the question down to the seminal 11 points but quite often over simplify and 10 miss getting the best answer.

This scenario 9 is precisely why bitmap indexes were invented 8 -- to handle the times when unknown groups 7 of columns would be used in a where clause.

Just 6 in case someone says that BMIs are for low 5 cardinality columns only and may not apply 4 to your case. Low is probably not as small 3 as you think. The only real issue is concurrency 2 of DML to the table. Must be single threaded 1 or rare for this to work.

Yes, you can give "hints" with 14 the query to Oracle. These hints are disguised 13 as comments ("/* HINT */") to 12 the database and are mainly vendor specific. So 11 one hint for one database will not work 10 on an other database.

I would use index hints 9 here, the first hint for the small table. See 8 here.

On the other hand, if you often search 7 over these two fields, why not create an 6 index on these two? I do not have the right 5 syntax, but it would be something like

CREATE INDEX IX_BAR_AND_FOO on sometable(bar,foo);

This 4 way data retrieval should be pretty fast. And 3 in case the concatenation is unique hten 2 you simply create a unique index which should 1 be lightning fast.

First off, I'll assume that you are talking 25 about nice, normal, standard b*-tree indexes. The 24 answer for bitmap indexes is radically different. And 23 there are lots of options for various types 22 of indexes in Oracle that may or may not 21 change the answer.

At a minimum, if the optimizer 20 is able to determine the selectivity of 19 a particular condition, it will use the 18 more selective index (i.e. the index on 17 bar). But if you have skewed data (there 16 are N values in the column bar but the selectivity 15 of any particular value is substantially 14 more or less than 1/N of the data), you 13 would need to have a histogram on the column 12 in order to tell the optimizer which values 11 are more or less likely. And if you are 10 using bind variables (as all good OLTP developers 9 should), depending on the Oracle version, you 8 may have issues with bind variable peeking.

Potentially, Oracle 7 could even do an on the fly conversion of 6 the two b*-tree indexes to bitmaps and combine 5 the bitmaps in order to use both indexes 4 to find the rows it needs to retrieve. But 3 this is a rather unusual query plan, particularly 2 if there are only two columns where one 1 column is highly selective.

So is Oracle smart enough to search efficiently 7 here?

The simple answer is "probably". There 6 are lots'o' very bright people at each of 5 the database vendors working on optimizing 4 the query optimizer, so it's probably doing 3 things that you haven't even thought of. And 2 if you update the statistics, it'll probably 1 do even more.

I'm sure you can also have Oracle display 2 a query plan so you can see exactly which 1 index is used first.

The best approach would be to add foo to 10 bar's index, or add bar to foo's index (or 9 both). If foo's index also contains an index 8 on bar, that additional indexing level will 7 not affect the utility of the foo index 6 in any current uses of that index, nor will 5 it appreciably affect the performance of 4 maintaining that index, but it will give 3 the database additional information to work 2 with in optimizing queries such as in the 1 example.

It's better than that.

Index Seeks are always 8 quicker than full table scans. So behind 7 the scenes Oracle (and SQL server for that 6 matter) will first locate the range of rows 5 on both indices. It will then look at which 4 range is shorter (seeing that it's an inner 3 join), and it will iterate the shorter range 2 to find the matches with the larger of the 1 two.

You can provide hints as to which index 4 to use. I'm not familiar with Oracle, but 3 in Mysql you can use USE|IGNORE|FORCE_INDEX 2 (see here for more details). For best performance 1 though you should use a combined index.