title: The speed of 'extract(year from sysdate)'
body: <p>I have two queries:</p>
  
  <pre><code>with tmp as (
  select asy.aim_student_id, ast.aim_test, asq.response
    from aim_student_test ast
    join aim_student_qst asq on (asq.aps_yr = ast.aps_yr and asq.aim_test = ast.aim_test and asq.aim_id = ast.aim_id)
    join aim_student_yr asy on (asy.aps_yr = ast.aps_yr and asy.aim_student_yr_id = ast.aim_student_yr_id)
      where asq.aps_yr = '2012'
      and asq.qst_num = 1)
  select aim_student_id, aim_test, response  
    from tmp
    where response is null
    -- execution-time: 0.032 seconds
  
  
  define this_year = extract(year from sysdate)
  with tmp as (
  select asy.aim_student_id, ast.aim_test, asq.response
    from aim_student_test ast
    join aim_student_qst asq on (asq.aps_yr = ast.aps_yr and asq.aim_test = ast.aim_test and asq.aim_id = ast.aim_id)
    join aim_student_yr asy on (asy.aps_yr = ast.aps_yr and asy.aim_student_yr_id = ast.aim_student_yr_id)
      where asq.aps_yr = &amp;this_year
      and asq.qst_num = 1)
  select aim_student_id, aim_test, response  
    from tmp
    where response is null
    -- execution-time: 82.202 seconds
  </code></pre>
  
  <p>The only difference is that in one I use '2012' and the other I implement extract(year from sysdate).</p>
  
  <p>I can only imagine that Oracle is computing extract(year from sysdate) for EVERY record it checks, and that I just can't figure out how to make it compute this once and use it as a variable. Searching has not returned me the answers I seek... so I come to the magicians of SO.com. HOW do I properly use </p>
  
  <pre><code>extract(year from sysdate)
  </code></pre>
  
  <p>as a variable?</p>