Oracle SQL Jumpstart with Examples- P11

Oracle SQL Jumpstart with Examples- P11: Review"As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...
Nội dung trích xuất từ tài liệu:
Oracle SQL Jumpstart with Examples- P11 This page intentionally left blankPlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 21 Indexes and Clusters In this chapter: What is an index and what is the purpose of an index? What types of indexes are there, and how do they work? What are the special attributes of indexes? What is a cluster? Recent chapters have discussed various database objects such as tables, views, and constraints. This fourth chapter on database objects covers indexing and clustering. Understanding database objects is essential to a proper understanding of Oracle SQL, particularly with respect to building efficient SQL code; tuning is another subject.1 It is important to under- stand different database objects, indexes and clusters included. 21.1 Indexes Let’s start by briefly discussing what exactly an index is, followed by some salient facts about indexing. 21.1.1 What Is an Index? An index is a database object, similar to a table, that is used to increase read access performance. A reference book, for instance, having an index, allows rapid access to a particular subject area on a specific page within that book. Database indexes serve the same purpose, allowing a process in the database quick access directly to a row in the table. An index contains copies of specific columns in a table where those col- umns make up a very small part of the table row length. The result is an 471Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 472 21.1 Indexes index. An index object is physically much smaller than the table and is therefore faster to search through because less I/O is required. Additionally, special forms of indexes can be created where scanning of the entire index is seldom required, making data retrieval using indexes even faster as a result. Note: A table is located in what is often called the data space and an index in the index space. Attached to each row in an index is an address pointer (ROWID) to the physical location of a row in a table on disk. Reading an index will retrieve one or more table ROWID pointers. The ROWID is then used to find the table row precisely. Figure 21.1 shows a conceptual view of a table with an index on the NAME column. The index stores the indexed column (NAME) and the ROWID of the corresponding row. The index’s rows are stored in sorted order by NAME. The table’s data is not stored in any sorted order. Usually, rows are stored into tables sequentially as they are inserted, regardless of the value of the NAME or any other column. In other words, a table is not ordered, whereas an index is ordered. Figure 21.1 Each Index Entry Points to a Row of Data in the Table.Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 21.1 Indexes 473 Continuing with the example in Figure 21.1, here is a query on the CUSTOMER table: SELECT VOCATION FROM CUSTOMER WHERE NAME = Ned; Because the WHERE clause contains the indexed column (NAME), the Optimizer should opt to use the index. Oracle Database 10g searches the index for the value “Ned”, and then uses the ROWID as an address pointer to read the exact row in the table. The value of the VOCATION column is retrieved (“Pet Store Owner”) and returned as the result of the query. A large table search on a smaller index uses the pointer (ROWID) found in the index to pinpoint the row physical location in the table. This is ...