Solved

Best strategy for apply indexes to ORACLE tables

Posted on 2012-03-12
3
374 Views
Last Modified: 2012-03-16
Hi:

I'm pretty new to the world of ORACLE schema design.
We're starting to see our queries slow down as we are
increasing the number of records in our tables (1 million +).
Currently we've got no indices applied to the tables.

I'm trying to figure out the best indexing scheme to apply to this schema.
It should be pretty straight forward.

I've got a basic schema (which I've attached below).

Thanks,
JohnB
ORACLE-Indexing-Question.png
0
Comment
Question by:jxbma
3 Comments
 
LVL 23

Accepted Solution

by:
David earned 500 total points
ID: 37712578
Remember that the Zen of Tuning these days is to find and minimize your wait events.  That is, be careful when you change one thing because it will possibly impact more than you think.

At a basic level, your entity diagram calls for primary keys to be indexed, with foreign keys to maintain referential integrity between parent and child tables.  The lookup table can be index organized or bitmapped if it is static (low-volatility).  Specific columns may be indexed if your queries are selecting those columns.

Are the tables being updated (rows added) while the queries are running?  Consider adding materialized views for your queries.  Are the table inserts done in bulk, such as appending rows at the table high-water mark, or are you updating one row at a time?  If most queries select only a portion of the data, such as current month transactions, then use partitioning to organize the data into months or quarters.

We can branch into appropriately sized tablespace blocks, to reduce I/O.  Are the queries using appropriate hints to aid the cost-based optimizer?

All these points are simply to show that tuning cannot be just thought of as indexing -- and that they are all documented in other E-E questions, on the Oracle site, and in 3rd party sites or books.  HTH
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37712594
I agree with everything said above.

You cannot index based on a design.  You need to index on use.  Keep in mind that just because an index exists doesn't mean using it is the 'best' performance.

This is why Oracle came up the the Cost Based Optimizer (CBO).  This takes table statistics and, most of the time, comes up with the 'most optimized', therefore 'best' execution plan.

It does get things wrong at times and you can over-ride it with hints.  Most of the time it does get it right.

You need to monitor performance and tune based on what you observe.

If you are licensed for the diagnostics or performance packs check out AWR and ADDM.

If not, check out StatsPack.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37714668
>>> We're starting to see our queries slow down

There's your answer. Tune the queries that are causing your customers pain. Prioritize by the cost ($) associated with that pain vs cost ($) to address it.

If you don't know what to index.  Look at some of those slow queries.  At the most basic level, start with the where clauses.

If this query is slow:

select col1,col2,col3 from some_table where colx=1,coly=2, colz=3;

I would suggest an index on colx,coly,colz  
(one index on 3 columns,  not 3 indexes of one column each)

As mentioned above, indexes aren't guaranteed to  help; but if you really do have no indexes at all in your schema, then you'll probably see some marked improvement with a few simple targeted additions for the biggest pain points.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

733 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