Oracle Query Day record three methods efficiency comparison

Tag:os    Data    io   for   cti   re    

--Query the data generated by the day in a table-the CDate field in the original table Mobilefrends has an index on it: CREATE INDEX mobilefrends_cdate_idx on mobilefrends (CDate); --------------------------------------------------------------------------------------------------------------- --------method One: Use the To_char () function [email protected]> Select COUNT (*) from Mobilefrends where To_char (CDate, ' yyyy-mm-dd   ') =to_char (sysdate, ' yyyy-mm-dd '); COUNT (*)----------82119 used time: 00:00:17.18 execution plan----------------------------------------------------------plan hash value:3731074549---------------------------------------------------------------------------------------------- --| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |------------------------------------------------------------------------------------------------| 0 |                        SELECT STATEMENT |     |     1 | 8 | 11773 (16) |   00:02:22 | |  1 |                        SORT AGGREGATE |     |     1 |            8 |          | ||* 2 | INDEX FAST Full scan|   Mobilefrends_cdate_idx |  129k| 1012k| 11773 (16) | 00:02:22 |------------------------------------------------------------------------------------------------ predicate information (identified by Operation ID):---------------------------------------------------2-filter (To_c HAR (Internal_function ("CDATE"), ' Yyyy-mm-dd ') =to_char ([Email protected]!-1, ' yyyy-mm-dd ')) statistical information--------- -------------------------------------------------1 Recursive calls 0 db block gets 36896 consis Tent gets 0 physical reads 0 Redo size 345 Bytes sent via sql*net to client 338 bytes Received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sort s (disk) 1 rows processed-------------------------------------------------------------------------------------- ---------------------------------method Two: [email protected]> s with trunc () functionElect Count (*) from Mobilefrends where Trunc (cdate) =trunc (sysdate); COUNT (*)----------82119 used time: 00:00:16.32 execution plan----------------------------------------------------------plan hash value:3731074549---------------------------------------------------------------------------------------------- --| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |------------------------------------------------------------------------------------------------| 0 |                        SELECT STATEMENT |     |     1 | 8 | 12580 (22) |   00:02:31 | |  1 |                        SORT AGGREGATE |     |     1 |            8 |          | ||   * 2 | INDEX FAST Full scan|   Mobilefrends_cdate_idx |  129k| 1012k| 12580 (22) | 00:02:31 |------------------------------------------------------------------------------------------------ predicate information (identified by Operation ID):---------------------------------------------------2-filter (TRUNC (Internal_function ("CDATE")) =trunc ([email protected]!-1)]      Statistical information----------------------------------------------------------0 recursive calls 0 db block gets        36896 consistent gets 0 physical reads 0 Redo size 345 Bytes sent via sql*net to client          338 Bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 Sorts (disk) 1 rows processed------------------------------------------------------------------------- ----------------------------------------------method Three: No function [email protected]> select COUNT (*) from Mobilefrends   where Cdate>=trunc (sysdate) and Cdate<trunc (sysdate) +1; COUNT (*)----------82119 used time: 00:00:00.43 execution plan----------------------------------------------------------plan hash value:2668176725---------------------------------------------------------------------------------------------| Id | Operation | NAme | Rows | Bytes | Cost (%CPU) |   Time |---------------------------------------------------------------------------------------------| 0 |                        SELECT STATEMENT |     |     1 |     8 | 3 (0) |   00:00:01 | |  1 |                        SORT AGGREGATE |     |     1 |            8 |          | ||   * 2 |                        FILTER |       |       |            |          | ||    * 3 | INDEX RANGE scan|    Mobilefrends_cdate_idx |   48 |     384 | 3 (0) | 00:00:01 |--------------------------------------------------------------------------------------------- predicate information (identified by Operation ID):---------------------------------------------------2-filter (Trun   C ([email protected]!-1) <trunc ([email protected]!)]  3-access ("CDATE" >=trunc ([Email protected]!-1) and "CDATE" <trunc ([email protected]!)] Statistical information----------------------------------------------------------0 recursive CALLS 0 db block gets 221 consistent gets 0 physical reads 0 redo size 345 byt Es sent via sql*net to client 338 bytes received via sql*net from client 2 sql*net roundtrips To/from cl Ient 0 Sorts (memory) 0 sorts (disk) 1 rows processed-------------------------------------- ---------------------------------------------------------------------------------is visible from the three methods:--method One: With the To_char () function, It takes 17.18 seconds, 36,896 logical reads, and a full index scan;--method two: Using the trunc () function, it takes 16.32 seconds, the logical reading is 36,896 times, and the full index scan is performed;--method Three: no function, Takes 0.43 seconds, logically reads 221 times, performs an index range scan;