CODEWITHSUNDEEP
sqloracle-databasedatabase-performance
osfar
osfar

Reputation: 413

How improve select query performance in 10M+ records table?

I have a table with more than 10 million rows, and I use simple queries with bind variables (in my case I can't search by primary key id).

The table looks like this:

Col1   Col2   Col3   Col4   Col5   Col6

and the queries is like 

select distinct col1  
from table ;

select distinct col2 
where col1 = :bind ;

select distinct col3 
where col1 = :bind1 and col2 = :bind2 ;
.
.

select distinct col6 
where col1 = :bind1 and col2 = :bind2 and col3 = :bind3
  and col4 = :bind4 and col5 = :bind5

The results of all of these queries are not large - less than 100 records at MAX - but the performance is too slow .

How to improve it ?

Upvotes: 1

Views: 796

Answers (2)

Rick James
Rick James

Reputation: 142298

In general, build an index this way:

  1. Include all the WHERE columns that are tested against a constant with =. (All of your examples so far meet this criterium.) The columns can be in any order.
  2. (This is optimal.) Include the SELECT items at the end of the INDEX, any order. This makes the index "covering" (unless it is more complex than described).
  3. Note that extra columns on the end are a slight burden, but no harm.

The "composite" INDEX(col1, col2, col3, col4, col5, col6) happens to match both of these 'rules' If you have some SELECT this is a bit different, show it to us; we may be able to help you build another index for it.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can add an index on (col1, col2, col3, col4, col5). This can be used for all the queries:

create index idx_t_5 on t(col1, col2, col3, col4, col5);

If that is not possible and the columns have the same type, then you can use a lateral join in Oracle 12c+ to combine this into a single query:

select distinct which, col
from t cross apply
     (select 'col1' as which, t.col1 as col from dual union all
      select 'col2', t.col2 from dual where t.col2 = :bind2 union all
      . . .
     ) t
where col1 = :bind1;

This will scan the table only once, which be a performance improvement.

Upvotes: 2

Related Questions