Best strategy for apply indexes to ORACLE tables

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
LVL 1
jxbmaSoftware ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
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
sdstuberCommented:
>>> 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.