Solved

Best strategy for apply indexes to ORACLE tables

Posted on 2012-03-12
3
371 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 73

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 71
Exchange 2010 - Best practice MDB Data size 8 82
Help on model clause 5 29
Oracle Insert not working 10 23
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Read about achieving the basic levels of HRIS security in the workplace.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

806 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