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
3 REPLIES
Enthusiast

Re: SQL optimization help

Hi Aravind,
I came to know from ur previous postings that u r in working in V2R6...
now u can create a perm table with the partition of the Date column...
join the both tables and load into the perm table and in ur bo reports fetch the data from the perm table.....when ever u want the report u can populate the perm table and refresh the report..
Enthusiast

Re: SQL optimization help

Hi,
gogul, thanks for ur reply.
But I want to know,how partitioning helps for queries which try fetch data between two dates?

--Aravind
Enthusiast

Re: SQL optimization help

Use EXPLAIN identifier before your SQL you would see "Partition Elimination" strategy. It is most effective if the range is encompasses fewer partitions.

There are other overheads of PPI though, additional space, create and drop partitions automation.

You may get more information on PPI from manauls.

Vinay