You can add partitions to increase the speed of the query. Usually, each partition on the reader side represents a subset of the data to be processed.
Complete the following tasks to optimize the source database for partitioning,
Tune the database.
If the database is not tuned properly, creating partitions may not make sessions quicker.
Enable parallel queries.
Some databases may have options that must be set to enable parallel queries. Check the database documentation for these options. If these options are off, the Integration Service runs multiple partition SELECT statements serially.
Separate data into different tables spaces.
Each database provides an option to separate the data into different tablespaces. If the database allows it, use the PowerCenter SQL override feature to provide a query that extracts data from a single partition.
Group the sorted data.
You can partition and group source data to increase performance for a sorted Joiner transformation.