| Team Fly | |
So far, we have had a brief look at tables, views, tablespaces, and a handful of stored objects—views, triggers, procedures, packages, and functions. Let's round out this introduction to Oracle Database 10g architecture by covering a few other items commonly encountered from day one. This discussion is a hodge-podge of things that are necessary for a person's understanding of the Oracle Database 10g architecture and operations. We must spend a bit of time first looking at the database administrator, affectionately called the DBA, the gatekeeper of the database and the person responsible for its smooth operation. There is a more detailed look at the DBA in Chapter 3, with more information on how DBAs go about carrying out their administrative chores.
Tables are made up of rows and columns, being the baseline of all objects in the Oracle Database 10g. As applications interact with the database, they often retrieve vast amounts of data. Suppose MyYP, a fictitious Internet company, provided Yellow Pages listings for North America, and the data was stored primarily in a table called YP_MASTER. Each row in the YP_MASTER table is uniquely identified by a combination of company name, municipality, and geographic location (state or province). As words are retrieved from the database to satisfy online queries, indexes would provide a quick access path to the qualifying data. These characteristics about indexes are relevant to the power they deliver in the Oracle Database 10g. For instance:
They are built on one or more columns in a table using simple SQL statements.
They are separate from the tables upon which they are built, and can be dropped without affecting the data in the table itself. On the contrary, when a table is dropped, any indexes it has disappear with the table.
The function they perform can be likened to the index in a book. If one were looking for a specific topic in a textbook, the best place to start would be the index—it provides a shortcut to the information being sought. If one imagined that YP_MASTER were a book rather than a table, finding Y&M Plumbing in Pensacola, Florida would be faster using the index than reading the book from the start into the entries for the 25th letter of the alphabet. The names on the corner of the pages in a phone book are like an index.
Indexes occupy space in the database and, even though there are ways to keep their space to a minimum, extra space is required and must be preallocated.
| Team Fly | |