Because primary keys must be unique, a relational (Web hosting rating)
Because primary keys must be unique, a relational database should automatically create internal unique indexes on primary keys. Commands similar to the following commands could be used to create indexes on foreign key fields, for the CREATE TABLE command on the PUBLICATION table shown previously in this chapter: CREATE INDEX XFK_P_Author ON Publication(author_id); CREATE INDEX XFK_P_Publisher ON Publication(subject_id); Types of Indexes It is important to have a brief understanding of different types of indexing available in relational databases. Some of the smaller-scale database engines (such as dBase, Paradox, and MS Access) might offer little or no variation on index types allowed, generally using BTree type indexing. Types of indexes in various relational database engines are as follows: . BTree index BTree means binary tree and, if drawn out on a piece of paper, a BTree index looks like an upside down tree. The tree is called binary because binary implies two options under each branch node: branch left and branch right. The binary counting system of numbers contains two digits, namely 0 and 1. The result is that a binary tree only ever has two options as leafs within each branch at least that is the theory, not being precisely the case in all databases. BTree indexes are sometimes improperly named as they are not actually binary meaning two branches can have more than two leafs contained within them. Naming conventions are largely immaterial in this situation. Essentially, a BTree consists of a root node, branch nodes, and ultimately leaf nodes containing the indexed field values in the ending (or leaf) nodes of the tree. Some BTree construction and searching methods in some databases are highly efficient for both reading and changing of data, automatically changing the structure of the BTree index without any overflow. Overflow is bad for indexing because changes are placed outside of the optimized index structure. Enough changes and overflow can destroy the efficiency of an index, eventually rendering it useless and drastically deteriorating rather than generally improving table access performance. Figure 3-25 shows an example of what a typical relational database BTree index might look like. . Bitmap index A Bitmap index contains binary representations for each record using 0 s and 1 s. Bitmap indexes are often misused and are extremely vulnerable to overflow over long periods of time. Values cannot be slotted into the existing Bitmap index structure as readily as can be done when updating a BTree index. Figure 3-26 shows a graphical type structure of the internal machinations of a Bitmap index where two bitmaps are created for two values of M for male and F for female. When M is encountered, the M bitmap is set to 1 and the F bitmap is set to 0. In general, Bitmap indexes can be disappointing, even in environments where they are supposedly highly beneficial. 66 Chapter 3
Check Tomcat Web Hosting services for best quality webspace to host your web application.