- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- speed of inserts dependence on number of indices a...
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-25-2002 08:00 AM
тАО03-25-2002 08:00 AM
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-25-2002 08:17 AM
тАО03-25-2002 08:17 AM
SolutionThere is no magic answer to this question. There is always a tradeoff in that more indices will make selects faster (or at least almost all of the time) but that more indices will make inserts, deletes, and updates (if the keys are altered) slower. I certainly have cases where tables have many rows, many indices, and the rows themselves are quite large and yet performance is excellent. I certainly would not say that 5 is the limit.
I can tell you that in most B-trees, the thing to avoid is highly duplicate keys. For example, if you index state, then operations (especially deletes) on 'CA' are going to take a long time. Even CITY, STATE, ZIP could still be highly duplicate. In general, I've found the best compromise to be to create composite indices with the most used parts of the index as the most significcant part of the key. The optimizer is smart enough to use the 'front part' of the key and you can you the 'back parts' of the key to avoid excessive duplicate key generation. In a few cases, I've even tacked on a rowid just to avoid highly duplicate data.
The best way to determine where your problems lie is to plot the performance vs number of rows ,N, or better still plot the performance vs log N. The slope of the log plots can indicate the severity of your problem. For example, if you see performance degrading with a slope of 2, then you have an N-squared problem; a slope of 4 indicates an N^4 problem and you better come up with a new indexing scheme very quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-25-2002 08:38 AM
тАО03-25-2002 08:38 AM
Re: speed of inserts dependence on number of indices and size of the table
If the table data is relatively static, or you are dealing with a data warehouse, then you can have as many indexes as you like.
If the table is write-bound, then the fewer the better, depending on the application.
Avoid indexes on flags, instead partition the table, or create a look-up table containing the keys of the rows you may be interested in.
Whatever you choose, remember to update statistics when the population changes and, if you are using Oracle, clear the SQL statement cache so that the optimizer is updated.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-25-2002 08:41 AM
тАО03-25-2002 08:41 AM
Re: speed of inserts dependence on number of indices and size of the table
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-01-2002 02:50 PM
тАО04-01-2002 02:50 PM
Re: speed of inserts dependence on number of indices and size of the table
In order to provide best update performance use rowid if possible.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-01-2002 07:15 PM
тАО04-01-2002 07:15 PM