Oracle資料庫日期過濾方法效能比較,oracle效能比較

來源:互聯網
上載者:User

Oracle資料庫日期過濾方法效能比較,oracle效能比較

   在開發SQL時,過濾日期段是經常遇到的情況,如何高效的過濾出日期段?本文通過實驗進行驗證:

方法一、通過to_char將日期轉換成字串進行比較

         to_char(cr.contractenddate, 'YYYY-MM-DD') >= '2014-11-13'
           and to_char(cr.contractenddate, 'YYYY-MM-DD') <= '2015-11-19'

耗時:0.171s


方法二、通過to_date將字串轉換成日期進行比較

            cr.contractenddate >= to_date('2014-11-13', 'YYYY-MM-DD')
           and cr.contractenddate <= to_date('2015-11-19', 'YYYY-MM-DD')

耗時:0.093s


方法三、通過資料庫關鍵字between  and 進行比較

           cr.contractenddate
             between to_date('2014-11-13', 'YYYY-MM-DD') and to_date('2015-11-19', 'YYYY-MM-DD')

耗時:0.078s


總結:方法一耗時體現在需要逐個比較字串的每個字元;

           方法二耗時體現在>= 和<=;

           推薦使用方法三



相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.