How to Make Queries on a DATETIME Column Efficient If My Primary Query Pattern is an Hour?

Question

Context

Here is the DDL that I am intending to use to define the table for a logistics/delivery company.

CREATE TABLE scraping_details (
    id INT IDENTITY(1,1) PRIMARY KEY, -- Identity insert and autoincrement 
    unique_id VARCHAR2(64) NOT NULL,
    ts DATETIME NOT NULL, -- Timezone naive 
    pickup_zip VARCHAR2(6) NOT NULL,
    pickup_long NUMBER NOT NULL,
    pickup_lat NUMBER NOT NULL,
    dest_zip VARCHAR2(6) NOT NULL,
    dest_long NUMBER NOT NULL,
    dest_lat NUMBER NOT NULL,
    UNIQUE (unique_id)
);
SET IDENTITY_INSERT scraping_details OFF;

Query Pattern

The most frequent query pattern that I foresee, will always seek the ts, pickup_zip and dest_zip columns for a specific hour of a specific day. That means, we will want all the rows (and above columns) where ts is between 19th June 2024, 10 am to 10:59:59 am.

Questions

• How to modify the table creation command, especially ts to make this query as efficient as possible? Any kind of clustering or indexing on this row will help? I can trade some insertion latency to make this query efficient.
• About the implications of turning off the identity insert, can I insert the rows from a polars dataframe (using SQLAlchemy) where the original dataframe does not have the id column? Does it mean the database will create the corresponding numbers?

Backend Technology

If important, my company is using an Oracle ADB for this purpose. Mentioning this as I believe different backends have different functionalities.

Answer 1

SQL Server and Oracle are different products.

SET IDENTITY_INSERT table_name OFF is SQL Server syntax which has no equivalent on Oracle , the most similar might be generated by default as identity start with 1 increment by 1.

There is no datetime datatype in Oracle. From Datetime Datatypes and Time Zone Support The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.

---

How to modify the table creation command, especially ts to make this query as efficient as possible? Any kind of clustering or indexing on this row will help? I can trade some insertion latency to make this query efficient

Since you will always seek the ts, pickup_zip and dest_zip columns for a specific hour of a specific day, the following index in the mentioned order will speed up things

CREATE INDEX ts_pzp_dzp ON scraping_details (pickup_zip, dest_zip, ts);

for the following where clause

WHERE pickup_zip = AND dest_zip = AND ts BETWEEN ...

---

About the implications of turning off the identity insert, can I insert the rows from a polars dataframe (using SQLAlchemy) where the original dataframe does not have the id column? Does it mean the database will create the corresponding numbers?

Since there is no identity insert off/on and you go with generated by default as identity start with 1 increment by 1 allows you to either insert or not. See Turn ON OFF IDENTITY INSERT equivalent in Oracle.

Note if you insert id manually there will be no ordering id-s. See example

---

...my database administrator told me it's an essential practice to have a primary key set to auto increment even if another column is unique

Not in your particular case. Primary key uniquely identifies each row of a table, but since you have unique_id VARCHAR2(64) NOT NULL UNIQUE and ts TIMESTAMP NOT NULL id is redundant and in a large dataset it might make a difference.

---

My suggestion

Remove id , use unique_id as a primary key.

CREATE TABLE scraping_details (
    unique_id VARCHAR2(64) NOT NULL,
    ts TIMESTAMP NOT NULL, 
    pickup_zip VARCHAR2(6) NOT NULL,
    pickup_long NUMBER NOT NULL,
    pickup_lat NUMBER NOT NULL,
    dest_zip VARCHAR2(6) NOT NULL,
    dest_long NUMBER NOT NULL,
    dest_lat NUMBER NOT NULL,
    PRIMARY KEY  (unique_id)
);