- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- oracle index creation guidelines
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2004 02:21 AM
тАО03-02-2004 02:21 AM
oracle index creation guidelines
T
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2004 03:06 AM
тАО03-02-2004 03:06 AM
Re: oracle index creation guidelines
2) TKPROF : surely when a transaction us running slow you will trace it, analyze and take action (the index creation may not be the only answer to the performance pb, but if you don't have access to the code ...)
Also, sometimes you may have the index but the order of the columns is not the best.
Regards,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2004 03:41 AM
тАО03-02-2004 03:41 AM
Re: oracle index creation guidelines
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2004 03:55 AM
тАО03-02-2004 03:55 AM
Re: oracle index creation guidelines
You will have to know the application (SQL) a way or another. Ih the 3rd party does not give you the list of indexes to create (or this might be an incomplete list !) you will have to obtain the list of SQL that require attention.
Statspack is one way.
Check also this doc from Metalink on SQL tuning (attachment)
Regards,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2004 04:03 AM
тАО03-02-2004 04:03 AM
Re: oracle index creation guidelines
The trace analyzer tool from Oracle can diagnose where an index could be of use.
http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=224270.1
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2004 04:49 AM
тАО03-02-2004 04:49 AM
Re: oracle index creation guidelines
design your queries to contain as much "column =
If your query is expected to deliver less than 30% of the table-content, create an index on those columns in the order specified above.
If your query delivers more ( 40%+), a full table scan may be the best choice (depending on average row length).
If you have a large percentage of data to be selected, and need sorting, an index on the sorting columns may serve you better than the one on WHERE, it depends.
If you have to use ranges, try to use "WHERE column BETWEEN
If you have to use aggregates (sum, count, avg, ... ) partitioning the table on the corresponding group -columns may help, because the aggregates can be calculated by parallel query in the diffrent partitions.
If you use max, min functions, be sure to specify a corresponding ASC or DESC index.
....
Just starters
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2004 03:45 PM
тАО03-02-2004 03:45 PM
Re: oracle index creation guidelines
also consider Function Based Indexes.
Oracle8i introduces a feature virtually every DBA and programmer will be using immediately -- the ability to index functions and use these indexes in query. In a nutshell, this capability allows you to have case insenstive searches or sorts, search on complex equations, and extend the SQL language efficiently by implementing your own functions and operators and then searching on them.
hope this helps too!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2004 03:48 PM
тАО03-02-2004 03:48 PM
Re: oracle index creation guidelines
more on Function Based Indexes.
see attachment.
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2004 02:20 AM
тАО03-03-2004 02:20 AM
Re: oracle index creation guidelines
I recommend using the SQL analyzer in Enterprise Manager. If you haven't already set it up, it's worth doing. All it really does is give you the same info that statspack would, but it's much FASTER and easier to read and understand. You can use it to show you the worst performing SQL statements based on disk I/O, buffer gets, etc. Plus, there is a "virtual index" tool. You can select a SQL statement that takes a long time to run, then create a "virtual index" and see how the explain plan would change IF you created different indexes - it will show you the % improvement in execution time too.
If you're going to do this, use the latest 9i version. Even if you're running 8i database, you can analyze with the 9i Enterprise Manager (you have to setup a small 9i database for the repository).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2004 04:01 PM
тАО03-03-2004 04:01 PM
Re: oracle index creation guidelines
Oracle Expert feature in Oracle Enterprise Manager can also guide you the advise on index that need to create.
It was very easy and power full , they can also give you the script to create indexes.
HTH