Problem solve Get help with specific problems with your technologies, process and projects.

Questions about partitions, indexes, and parallelism

In a true data warehouse environment:
  1. What is the role of the partition? What are different types of partitions, and how, when and where do you use a partition (both primary and secondary)?
  2. What are the different types of indexes?
  3. What is paralelism?

Your questions are very broad and would take a long time to answer fully. I'll give a brief answer to each question, and then I'll refer you to the relevant Oracle documentation for further investigation.

Partitions work by breaking a table down into smaller pieces. This accomplishes two tasks. One, if you only need data in one partition, then the entire table does not need to be read, only the partition(s) that contains the data you are interested in. This helps performance.

Two, if you spread your paritions on multiple disk volumes and you lose one disk volume, your application only loses some data, not all of it. This helps with availability.

There are three basic partitioning schemes. You can partition by range, by a list, or by a hashing algorithm. An example of partitioning by range would be to store this year's data in one partition, last year's data in another and so on. When I look for data that is in the range of Jan 1, 2003 to Dec 31, 2003 then I know that the data is in last year's partition. List partition lets you define a list of values to denote the data in each partition. For instance, I might partition my data based on the COUNTRY. The NORTH_AMERICA partition would contain UNITED STATES, CANADA, and MEXICO data. The EUROPE partition would contain UNITED KINGDOM, FRANCE, SPAIN, etc. Hash parition evenly distributes all of your data using a hash function.

For more information on partition, refer to the Oracle Concepts guide, particularly Chapter 18, Partitioned Tables and Indexes. You can find this chapter here:


There are a number of index types available in Oracle. You have B-tree indexes, Bitmap indexes, Join Indexes, Index-Organized tables, just to name a few. For more information on index, I refer you to the Oracle Concepts guide again, particularly this topic:


Parallelism mean using more than one CPU to complete a CPU-intensive operation quicker. The work is broken down into multiple pieces and then run on multiple CPUs. When all of the pieces have completed, a coordinator puts all of the pieces back together again. Again, the Oracle Concepts guide contains more information:


Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.