Analysis on the query method and efficiency of Oracle about date type

background ： Several positions ETL Link passed sqoop Extract a Oracle Business table , Suddenly on a certain day sqoop Export error , Retrying still failed . After querying the source database, it is found that the current incremental extraction sql It takes a long time , involve oracle database date Query efficiency in type comparison .

• Original incremental query sql

select * from ora_table where to_char(update_time,'yyyymmdd') = '20220222';

notes ： stay Oracle Source library update_time Field is date type , Above sql It means to take 2022-02-22 A day's record

• Problem cause analysis

After testing, it is found that the original incremental query sql It is particularly slow to query the source database , Think for a moment and realize ,oracle database date All types of data storage use Time stamp , That is, the final comparison is still with Numerical comparison . and to_char() After the conversion, the comparison becomes Character comparison 了 , That is, by bytecode comparison , By comparison , It must be more efficient in numerical comparison .

• Optimize the query method

Since the use of date Direct comparison of types is more efficient , Then you need to convert the characters to be compared to date type

select * from ora_table where update_time = to_date('20220222','yyyy-mm-dd');

Use the above sql when , Here's the problem ,to_date() Function parameters can only be transferred to date （ Without time ） Words , The converted date value will be added with time by default 00:00:00, This is used for conditional judgment , I can't get it 2022-02-22 Recorded on this day

We can use to_char() Format the function to_date('20220222','yyyy-mm-dd') This date Type value to verify

select to_char(to_date('20220222','yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss) from ora_table where rownum=1;

You will find that the obtained value is 2022-02-22 00:00:00, If you match by date type , take 2022-02-22 The record of this day , We hope that the value range should be 2022-02-22 00:00:00 to 2022-02-22 23:59:59（ Left and right closed interval ）, So we need to to_date() Add a time limit when formatting functions , below 2 Both schemes can be realized

Scheme 1

-- adopt between..and（ Closed interval ）
select * from ora_table where update_time between to_date('20220222 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('20220222 23:59:59','yyyy-mm-dd hh24:mi:ss');

-- adopt >= and <=
select * from ora_table where update_time >= to_date('20220222 00:00:00','yyyy-mm-dd hh24:mi:ss') and update_time <= to_date('20220222 23:59:59','yyyy-mm-dd hh24:mi:ss');

But it is recommended to use >= Symbol , because between..and 

If you don't want to restrict the time format , You can also add one day to the right range , And then through < To achieve , So the value range is 2022-02-22 00:00:00 to 2022-02-23 00:00:00（ Left closed , Right open ）

Option two

select * from ora_table where update_time >= to_date('20220222','yyyy-mm-dd') and update_time < to_date('20220223','yyyy-mm-dd');