• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

Oracle 10g index/paritioning design question


I will describe my question as two examples, firstly if we have a table(PERSON assume normalized and all data is relevant) of 100 fields and 90 of those fields are used to query on to answer questions like "how many people are from london" or "how many are from london have 2 children and are male and born between 1980 and 1985". In respect to Oracle how would i go about speeding up such query's where so many fields can be included. What kind of indexing can be used.

The second question is in regards to partitioning, if i have a table where data is inserted repeatedly every minute, i have 10 processes working on this data but all processes need all the data, does this mean this table is not eligible for partitioning?

Thanks in advance.
4 Solutions
Hi Mario,

1) Ideally you would have one index on each of those 90 columns that a user might use for search. Of course this is theory I won't ever suggest implementing such a thing.

You should find a way to make some of your criteria mandatory. Let's say those that might have an even distribution over your table. In fact, you are searching here for a manner to split the amount of data the query has to read in order to get an answer.

If you are able to find such criteria, those might be the right index entry points (first key) for you. Of course such indexes might be also completed by some other columns in the relevant order.

2) What would be the purpose of your partitionning ? Partitionning is not that easy as it might appear at first sight. What are those processes doing ? How are the inserts done ? etc...

Partitionning is used to split the work. What kind of work do you need to split ? Why ? Are you thinking about parallel executions ?

We really need more info on the idea you hide behind your question in order to answer this ;)

As an alternative answer, I can say 'It depends.'

Apparantly you have to make index over 'City' (Who is from London?)
The second index could be the number of the children (this index could be bitmapped)
The third index could be sex (also bitmapped)
The fourth index is under question.

There are two solutions:
1. to create index over the birthdate
2. to partition over the bithday period and also to make data over the birthdate.

About your second question:

After you define partitioning the distribution of the new data will be done automatically by Oracle.


The fact that your processes (I imagine them as applications) need all data at first glance prevents from partitionning

But you have to analyze the queries the 'processes' issue against the Oracle DB. If the WHERE clause use parts of data (for instance quarterly, regions, cities, etc.)
1. The table is eligible for partitioning
2. The whole table (nevertheles is partitioned) is available for every 'process'
Franck PachotCommented:

For the first question, the answer is bitmap indexes for each column. So that several non selective predicates can be combined.
Note that bitmap indexes are not suitable if the table is heavily updated by small transactions.

For the second question, that does not mean that the table is not eligible for partitioning.
If they need all the data (such as a full scan) then partitioning can help (parallel query, partition-wise joins,...)
If they do an index access, you can choose to partition the index or not, and partition on same partitioning key as the table or not.

You can for example partition the table and have global indexes.

Sanjeev LabhCommented:

Its really difficult to add indexes on a table having so many columns and that too when these columns can be used interdependently for querying purposes. Bitmap indexes are fine but they cannot be used on tables with heavy transactions. Generally they are used on master tables where frequency of change is relatively less.

If table size is relatively less then probably full scans might be still better. You would probably have to think of something very different. One option could be splitting up data for similar columns which are generally fetched together and maybe group them into single materialized views. These individual views will have their proper indexes in place so that you can reap their benefits.
MarioC82Author Commented:
Good questions raised after much thought i had to award points to all answers as they made me ask the right questions.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now