[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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
0
jxbma
Asked:
jxbma
1 Solution
 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now