Solved

Best strategy for apply indexes to ORACLE tables

Posted on 2012-03-12
3
373 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 76

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

790 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