tailieunhanh - Mastering Data Warehouse DesignRelational and Dimensional Techniques phần 8

thực hiện bằng cách tối ưu hóa của cơ sở dữ liệu, và kết quả là nhìn vào nó sẽ Hoặc là tất cả các hàng cho ngày cụ thể và quét cho khách hàng hoặc xem xét tất cả các hàng cho các khách hàng và quét ngày. Nếu, mặt khác, bạn định nghĩa một hợp chất b-cây chỉ số sử dụng ngày tháng và khách hàng, sẽ sử dụng nó để xác định vị trí các chỉ số đó hàng trực tiếp. | Data Warehouse Optimization 303 performed by the database s optimizer and as a result it would either look at all rows for the specific date and scan for customer or look at all rows for the customer and scan for date. If on the other hand you defined a compound b-tree index using date and customer it would use that index to locate the rows directly. The compound index would perform much better than either of the two simple indexes. Figure Simplified b-tree index structure. 304 Chapter 9 B-Tree Index Advantages B-tree indexes work best in a controlled environment. That is to say you are able to anticipate how the tables will be accessed for both updates and queries. This is certainly attainable in the enterprise data warehouse as both the update and delivery processes are controlled by the data warehouse development team. Careful design of the indexes provides optimal performance with minimal overhead. B-tree indexes are low maintenance indexes. Database vendors have gone to great lengths to optimize their index structures and algorithms to maintain balanced index trees at all times. This means that frequent updating of tables does not significantly degrade index performance. However it is still a good idea to rebuild the indexes periodically as part of a normal maintenance cycle. B-Tree Index Disadvantages As mentioned earlier b-tree indexes cannot be used in combination with each other. This means that you must create sufficient indexes to support the anticipated accesses to the table. This does not necessarily mean that you need to create a lot of indexes. For example if you have a table that is queried by date and by customer and date you need only create a single compound index using date and customer in that order to support both. The significance of column order in a compound index is another disadvantage. You may be required to create multiple compound indexes or accept that some queries will require sequential scans after exhausting the usefulness of .