kranov: (Default)
[personal profile] kranov
Переписал запрос:
select max(dt) 
from t
where a = :a
  and b = 'К'
  and dt <= :dt


индекс (a,dt), max-мум из него не найти, из-за b = 'К'
индекс (a,b,dt) построить нельзя.

А если идти назад по индексу от (a = :a and dt <= :dt), до первой b = 'К' ?

родилось такое:
select max(dt)
from (
  select /*+first_rows*/ dt 
  from (  
    select /*+index_desc(t ix)*/ dt,b
    from t
    where a = :a and dt <= :dt and rownum >0
    order by dt desc
    ) 
  where rownum =1
    and b = 'К'
 )


Время выполнения упало в два раза (20 сек, против 40) на закешированных данных, consistent gets в 5 раз.
Запрос как обычно, вызывался из процедуры, которая вызывалась 2 млн. раз, при выполнении другого запроса.
From:
Anonymous( )Anonymous This account has disabled anonymous posting.
OpenID( )OpenID You can comment on this post while signed in with an account from many other sites, once you have confirmed your email address. Sign in using OpenID.
User
Account name:
Password:
If you don't have an account you can create one now.
Subject:
HTML doesn't work in the subject.

Message:

 
Notice: This account is set to log the IP addresses of everyone who comments.
Links will be displayed as unclickable URLs to help prevent spam.

Profile

kranov: (Default)
kranov

April 2017

S M T W T F S
      1
2345678
9101112 131415
16171819202122
23242526272829
30      

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Jul. 25th, 2017 10:41 am
Powered by Dreamwidth Studios