Warm tip: This article is reproduced from stackoverflow.com, please click
oracle sql database-performance

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

发布于 2020-03-27 10:28:14

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 ?

Questioner
osfar
Viewed
84
分享
Gordon Linoff 2019-07-04 00:43

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.

osfar 2019-07-03 23:31:41

ok , but if want to select 2 columns how will the syntax be ?

osfar 2019-07-03 23:40:13

its really complicated could you elaborate please ?

Aleks G 2019-07-04 00:23:00

@osfar While it's a bit more complicated that your typical select, it's quite useful. Yet, you really, really need to get the right indexes in place. A correct index can see speed improvements of hundreds of times. In the past I saw creating an index on a 10M+ table reduce query time from over 1 hour to under 1 second.

Gordon Linoff 2019-07-04 00:36:37

@osfar . . . If you wanted to select two columns, you would have a different question. But you can easily add more columns into the lateral join.

osfar 2019-07-04 00:40:22

@ Aleks G how to make the perfect index for my case ?

Related issues

1
Query is possibly slow?
2
Simple Sql Role query issue
3
Group by query with rollup
4
How to include a PHP variable inside a MySQL statement
5
Getting empty rows from "SELECT ALL" query in PostgreSQL
6
MySQL pivot table using java
7
Implement smart search by text field in database
8
How to speed up count(distinct) with Between clause in MySQL
9
how to perform sql query MIN/MAX FILTER
10
Dynamic pivot in oracle sql