sql - Need help in tuning a query in Oracle11g -

i modifying original question few conditions changed - running following query on oracle11g & takes 27 seconds show results. can please suggest solution improve response time of query? providing relevant details below -

select  column1 , round(count(column2)/10)  se_ca  column3 <= 4855  , column4 > 4490 group column1;  se_ca table has total 123914265 records.  plan_table_output -------------------------------------------------------------------------------- plan hash value: 3324421310  ------------------------------------------------- | id  | operation          | name  | cost (%cpu)| ------------------------------------------------- |   0 | select statement   |       |   211k  (3)| |   1 |  hash group     |       |   211k  (3)| |*  2 |   table access full| se_ca |   208k  (1)| -------------------------------------------------  predicate information (identified operation id):  plan_table_output -------------------------------------------------------------------------------- ---------------------------------------------------     2 - filter("column3"<=4855 , "column4">4490)    sql> desc se_ca; name                                      null?     type column1                                             varchar2(3) column2                                             number column3                                             number column4                                             number column5                                             number column6                                             number column7                                             number column8                                             number column9                                             varchar2(5) column10                                            varchar2(12)   index definition -  create  index idx1_se_ca on se_ca(column3); create  index idx2_se_ca on se_ca(column4); create  index idx4_se_ca on se_ca(column1); 

• with both conditions applied, expected output around 10% of entire table rows (10581643 rows).
• with each condition applied individually output column3 10581643 , column4 12391426.
• min & max values column3 4623 & 4988 respectively.
• whereas,min & max values column4 4624 , 4991. respectively.

can please tell me if index created correct or need different indexes?

the key understanding whether can improve performance on query understand couple of basic statistics on data.

1. what range of values 2 columns have predicates on (max, min)?
2. what percentage of rows expect meet each of conditions individually?
3. what percentage of rows expect meet both of conditions?

if predicate column3 <= 4855 going include 30% of rows of table, full table scan or fast full index scan best choice optimiser, if predicate on table.

now, predicate column4 > 4490 s going include 90% of rows of table -- based on alone optimiser might conclude when 2 predicates both present proportion of rows retrieved 0.3 * 0.9 = 27%. still indicates full scan appropriate.

however there correlation between 2 values, such low values of column1 have high values of column2, , 3% of rows meet condition. in such case might worth trying use index based access method.

this situation optimizer dynamic sampling can detect including optimiser hint:

select /*+ dynamic_sampling(se_ca 4) */  column1 , ... 

so answers questions distribution of values, , try dynamic sampling anyway -- query looks candidate it, imagine there correlation between "exposed_time" , "infectious_time ;)

further

since looking retrieve 10% of rows table unlikely index-based method of accessing table going help. index range scan may not efficient index fast full scan optimiser has chosen. depend on efficiency of i/o subsystem , relative speeds of single , multiblock i/o. (can make sure segment space autoallocated or uniformly allocated in units of @ least 1mb?)

however if have partitioning option available range-partition table on exposed_time in order allow more efficient partition pruning scan select subset of table rows. that's big change make, though, , lot of care have put considering side-effects on other operations.

you might think using low degree of query parallelism, although again there side-effects (a segment-level checkpoint, have negative effect on table lot of changes happening @ time).