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).


Comments

Post a Comment

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more

node.js - Bad Request - node js ajax post -

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());
Read more