[ Pobierz całość w formacie PDF ]
.A key value cannot exceedroughly one-half (minus some overhead) the available data space in a datablock.Indexes and KeysAlthough the terms are often used interchangeably, you should understandthe distinction between  indexes and  keys.Indexes are structures actuallystored in the database, which users create, alter, and drop using SQL statements.Youcreate an index to provide a fast access path to table data.Keys are strictly a logicalconcept.Keys correspond to another feature of Oracle called integrity constraints.Integrity constraints enforce the business rules of a database; see Chapter 23, Data Integrity.Because Oracle uses indexes to enforce some integrityconstraints, the terms key and index are often are used interchangeably;however, they should not be confused with each other.How Indexes Are StoredWhen you create an index, Oracle automatically allocates an index segment tohold the index s data in a tablespace.You control allocation of space for anindex s segment and use of this reserved space in the following ways:Schema Objects 7-21 " Set the storage parameters for the index segment to control theallocation of the index segment s extents." Set the PCTFREE parameter for the index segment to control the freespace in the data blocks that constitute the index segment s extents.The tablespace of an index s segment is either the owner s default tablespaceor a tablespace specifically named in the CREATE INDEX statement.You donot have to place an index in the same tablespace as its associated table.Furthermore, you can improve performance of queries that use an index bystoring an index and its table in different tablespaces located on different diskdrives because Oracle can retrieve both index and table data in parallel.See User Tablespace Settings and Quotas on page 24-8.Format of Index BlocksSpace available for index data is the Oracle block size minus block overhead,entry overhead, ROWID, and one length byte per value indexed.The numberof bytes required for the overhead of an index block is operating systemdependent.Additional Information: See your Oracle operating system-specificdocumentation for more information about the overhead of an index block.When you create an index, Oracle fetches and sorts the columns to be indexed,and stores the ROWID along with the index value for each row.Then Oracleloads the index from the bottom up.For example, consider the statement:CREATE INDEX emp_ename ON emp(ename);Oracle sorts the EMP table on the ENAME column.It then loads the index withthe ENAME and corresponding ROWID values in this sorted order.When ituses the index, Oracle does a quick search through the sorted ENAME valuesand then uses the associated ROWID values to locate the rows having thesought ENAME value.Though Oracle accepts the keywords ASC, DESC, COMPRESS, andNOCOMPRESS in the CREATE INDEX command, they have no effect onindex data, which is stored using rear compression in the branch nodes but notin the leaf nodes.The Internal Structure of IndexesOracle uses B*-tree indexes that are balanced to equalize access times to anyrow.The theory of B*-tree indexes is beyond the scope of this manual; for moreinformation you can refer to computer science texts dealing with data7-22 Oracle8 Server Concepts structures.Figure 7-7,  Internal Structure of a B*-Tree Index , illustrates thestructure of a B*-tree index.Figure 7-7: Internal Structure of a B*-Tree Index [ Pobierz całość w formacie PDF ]

  • zanotowane.pl
  • doc.pisz.pl
  • pdf.pisz.pl
  • funlifepok.htw.pl

    •