SQL optimization help

Database
Enthusiast

SQL optimization help

Hi all,
I have to optimize some queries.
In a typical reporting scenarion in BO,the user will enter the dates thru calendar and these dates coming as the column cdate from sys_calendar.caldates are compared with the tables in our warehouse.
The queries will be fetching data between two dates.

what should be the DDL of the tables in these scenario with tables having records 100 million on average.
How can I partition the table?
Else How can I have Secondary Indices?

Please help.

Regards
--Aravind