Oracle 10g index/paritioning design question

Posted on 2011-05-09
Last Modified: 2012-05-11

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.
Question by:MarioC82
    LVL 7

    Accepted Solution

    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.'

    LVL 47

    Assisted Solution

    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'
    LVL 15

    Assisted Solution

    by:Franck Pachot

    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.

    LVL 5

    Assisted Solution

    by:Sanjeev Labh

    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.

    Author Closing Comment

    Good questions raised after much thought i had to award points to all answers as they made me ask the right questions.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now