I'm just trying to do a comparison between Index on a NUMBER field and a DATE field.
But my experiments are giving me a mixed kind of response.
My assumption was that - Index on a NUMBER field would be faster coz it avoids the datatype conversion of DATE fields. (as seen in the 'Predicate section' of the explain plan)
1. Composite index on fields (NUMBER, DATE)
2. Composite index on fields (NUMBER, NUMBER)
CREATE TABLE table_x2
AS
SELECT LEVEL rid, SYSDATE + LEVEL dt, 'aaaaa-bbbb' name
FROM dual
CONNECT BY LEVEL<=1000000;
ALTER TABLE table_x2
ADD CONSTRAINT table_x2_pk PRIMARY KEY(rid, dt);
SQL> exec dbms_stats.gather_table_stats('schema_x','table_x2');
PL/SQL procedure successfully completed.
SQL> SELECT *
2 FROM table_x2
3 WHERE rid >= 500 AND dt <= To_Date('04-08-2020 05:21:00','dd-mm-yyyy hh24:mi:ss');
2001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1079754163
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 59976 | 1121 (2)| 00:00:14 |
|* 1 | TABLE ACCESS FULL| TABLE_X2 | 2499 | 59976 | 1121 (2)| 00:00:14 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DT"<=TO_DATE(' 2020-08-04 05:21:00', 'syyyy-mm-dd
hh24:mi:ss') AND "RID">=500)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4155 consistent gets
0 physical reads
0 redo size
45894 bytes sent via SQL*Net to client
1797 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2001 rows processed
=====================================================================================================================
SCENARIO - 2
CREATE TABLE table_x4
AS
SELECT LEVEL rid, To_Number(To_Char(SYSDATE + LEVEL, 'yyyymmddhh24miss')) dt, 'aaaaa-bbbb' name
FROM dual
CONNECT BY LEVEL<=1000000;
ALTER TABLE table_x4
ADD CONSTRAINT table_x4_pk PRIMARY KEY(rid, dt);
BEGIN
Dbms_Stats.gather_table_stats('schema_x','table_x4');
END;
SQL> SELECT *
2 FROM table_x4
3 WHERE rid >= 500 AND dt <= 20200805060302;
2001 rows selected.
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2547 | 63675 | 1157 (2)| 00:00:14 |
|* 1 | TABLE ACCESS FULL| TABLE_X4 | 2547 | 63675 | 1157 (2)| 00:00:14 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DT"<=20200805060302 AND "RID">=500)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4221 consistent gets
0 physical reads
0 redo size
24143 bytes sent via SQL*Net to client
1060 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1001 rows processed
Also, not sure why it is not selecting the Index here.
I might be completely wrong... Can you please guide me on this?