oracle11g - Oracle 11g - doing analytic functions on millions of rows -


my application allows users collect measurement data part of experiment, , needs have ability report on of measurements ever taken.

below simplified version of tables have:

create table experiments(   expt_id int,   expt_name varchar2(255 char) );  create table users(   user_id int,   expt_id int );  create table samples(   sample_id int,   user_id int );  create table measurements(   measurement_id int,   sample_id int,   measurement_parameter_1 number,   measurement_parameter_2 number );

in database there 2000 experiments, each of has 18 users. each user has 6 samples measure, , 100 measurements per sample.

this means there 2000 * 18 * 6 * 100 = 21600000 measurements stored in database.

i'm trying write query avg() of measurement parameter 1 , 2 each user - return 36,000 rows.

the query have extremely slow - i've left running on 30 minutes , doesn't come anything. question is: there efficient way of getting averages? , possible results amount of data in reasonable time, 2 minutes? or being unrealistic?

here's (again simplified version) query have:

select      e.expt_id,     u.user_id,     avg(measurement_parameter_1) avg_1,     avg(measurement_parameter_2) avg_2      experiments e,      users u,      samples s,     measurements m  u.expt_id = e.expt_id  , s.user_id = u.user_id  , m.sample_id = s.sample_id group e.expt_id, u.user_id

this return row each expt_id/user_id combination , average of 2 measurement parameters.

for query, in case, dbms needs read complete measurements table. far biggest part of data read, , part takes time if query optimized (will come later). means minimum runtime of query time takes read complete measurements table whereever stored. can rough estimate checking how data (in mb or gb) , checking how time take read amount of data harddisk (or table stored). if query runs slower factor of 5 or more, can sure there room optimization.

there vast amount of information (tutorials, individual hints can invaluable, , general practices lists) how optimize oracle queries. not through information quickly. if provide execution plan of query (this oracle's query optimizer thinks best way fulfill query), able spot steps can optimized , suggest solutions.


Comments

Post a Comment

Popular posts from this blog

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

c# - Resource not found error -

i have problem accessing method on homecontroller. show code of method : [httpget] public actionresult decriptidentifiantetredirige(string login_crypter, string mdp_crypter) { string loginacrypter = _globalmanager.protegemotdepasse(login_crypter); string mdpacrypter = _globalmanager.protegemotdepasse(mdp_crypter); user userapp = new models.user(login_crypter, mdp_crypter); if (userapp.authentificationvalidee(userapp.userlogin, userapp.password)) { session["name"] = userapp.userlogin; return redirecttoaction("accueil", "home"); } else { return redirecttoaction("validerauthentification", "home"); } } then in routeconfig.cs wrote route : routes.maproute( name: "authentificationapresdecryptage", url: "{controller}/{action}/{login_crypter}/{mdp_crypter}", defaults: new...
Read more