ITPub博客

Creating a reverse key index, compared to a standard index, reverses the bytes of eachcolumn indexed (except the rowid) while keeping the column order. Such anarrangement can help avoid performance degradation with Real Application Clusterswhere modifications to the index are concentrated on a small set of leaf blocks. Byreversing the keys of the index, the insertions become distributed across all leaf keys inthe index.Using the reverse key arrangement eliminates the ability to run an index rang

Key compression is useful in many different scenarios, such as:■ In a nonunique regular index, Oracle stores duplicate keys with the rowidappended to the key to break the duplicate rows. If key compression is used,Oracle stores the duplicate key as a prefix entry on the index block without therowid. The rest of the rows are suffix entries that consist of only the rowid.■ This same behavior can be seen in a unique index that has a key of the form (item,time stamp), for example (stock_ticker, tr

Oracle Containers for J2EE 10g Release 3 (10.1.3), or OC4J, provides a complete Java 2 EnterpriseEdition (J2EE) 1.4-compliant environment. OC4J provides all the containers, APIs, and servicesmandated by the J2EE specification.OC4J is distributed in two configurations: *A standalone configuration, in which OC4J is installed as a single, "standalone" instance andis managed, started and stopped directly as a self-contained component. *A managed configuration, in which OC4J is instal

Key compression can lead to a huge saving in space, letting you store more keys ineach index block, which can lead to less I/O and better performance.Although key compression reduces the storage requirements of an index, it canincrease the CPU time required to reconstruct the key column values during an indexscan. It also incurs some additional storage overhead, because every prefix entry hasan overhead of 4 bytes associated with it.性能以及存储上的考虑1. 可以在索引块中存储跟过的索引键 ，减少

Key compression breaks the index key into a prefix entry (the grouping piece) and asuffix entry (the unique piece). Compression is achieved by sharing the prefix entriesamong the suffix entries in an index block. Only keys in the leaf blocks of a B-treeindex are compressed. In the branch blocks the key suffix can be truncated, but the keyis not compressed.Key compression is done within an index block but not across multiple index blocks.Suffix entries form the compressed version of index rows. E

Key compression lets you compress portions of the primary key column values in anindex or index-organized table, which reduces the storage overhead of repeatedvalues.Generally, keys in an index have two pieces, a grouping piece and a unique piece. Ifthe key is not defined to have a unique piece, Oracle provides one in the form of arowid appended to the grouping piece. Key compression is a method of breaking offthe grouping piece and storing it so it can be shared by multiple unique pieces.键压缩

Index range scan is a common operation for accessing selective data. It can bebounded (bounded on both sides) or unbounded (on one or both sides). Data isreturned in the ascending order of index columns. Multiple rows with identical valuesare sorted (in ascending order) by the ROWIDs.索引范围1. 索引范围扫描可以是封闭或者不风不的2. 数据是以按照索引列的升序排列的[@more@]

Index unique scan is one of the most efficient ways of accessing data. This accessmethod is used for returning the data from B-tree indexes. The optimizer chooses aunique scan when all columns of a unique (B-tree) index are specified with equalityconditions.索引唯一扫描当一个唯一索引的某些与查询体条件表达式相等时，优化器将选择使用索引唯一扫描[@more@]

The B-tree structure has the following advantages:■ All leaf blocks of the tree are at the same depth, so retrieval of any record fromanywhere in the index takes approximately the same amount of time.■ B-tree indexes automatically stay balanced.■ All blocks of the B-tree are three-quarters full on the average.■ B-trees provide excellent retrieval performance for a wide range of queries,including exact match and range searches.■ Inserts, updates, and deletes are efficient, maintaining key or

All leaf blocks are at the same depth from the root branch block. Leafblocks store the following:■ The complete key value for every row■ ROWIDs of the table rowsAll key and ROWID pairs are linked to their left and right siblings. They are sorted by(key, ROWID).叶子节点1. 所有的叶子节点相对于其根分支块具有相同的深度2. 叶子节点存储以下信息 ：每行完成的键值 和 数据行的ROWID所有的 键值-ROWID 对都与其左右的兄弟节点链接，并按照（key，ROWID）的顺序排序[@more@]

Branch blocks store the following: ■ The minimum key prefix needed to make a branching decision between two keys ■ The pointer to the child block containing the key If the blocks have n keys then they have n+1 pointers. The number of keys and pointers is limited by the block size. 分支块 分之块存储以下信息 1. 最小的键值前缀 ， 在两个键值之间做出分支选择 2. 指向包含所查找键值的子块的指针 包含n个键值的分支块对应有n+1个指针 。键值和指针数有块大小所限制[@more@]

The two kinds of blocks:■ Branch blocks for searching■ Leaf blocks that store the values索引属性包含两种类型的块 ：分支块用来搜索　，　叶子块则存储值[@more@]

Oracle uses B-trees to store indexes to speed up data access. With no indexes, you haveto do a sequential scan on the data to find a value. For n rows, the average number ofrows searched is n/2. This does not scale very well as data volumes increase.Consider an ordered list of the values divided into block-wide ranges (leaf blocks). Theend points of the ranges along with pointers to the blocks can be stored in a search treeand a value in log(n) time for n entries could be found. This is the basi

Space available for index data is the Oracle block size minus block overhead, entryoverhead, rowid, and one length byte for each value indexed.When you create an index, Oracle fetches and sorts the columns to be indexed andstores the rowid along with the index value for each row. Then Oracle loads the indexfrom the bottom up. For example, consider the statement:CREATE INDEX employees_last_name ON employees(last_name);Oracle sorts the employees table on the last_name column. It then loads the ind

When you create an index, Oracle automatically allocates an index segment to hold theindex’s data in a tablespace. You can control allocation of space for an index’s segmentand use of this reserved space in the following ways:■ Set the storage parameters for the index segment to control the allocation of theindex segment’s extents.■ Set the PCTFREE parameter for the index segment to control the free space in thedata blocks that constitute the index segment’s extents.The tablespace of an in

C:>tnsping ora11gTNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 14-11月-2007 23:24Copyright (c) 1997, 2007, Oracle. All rights reserved.已使用的参数文件:D:appAdministratorproduct11.1.0db_1networkadminsqlnet.ora已使用 TNSNAMES 适配器来解析别名Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.111.112)(PO) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora11g)))TNS-12532: TNS: 无效的参数C:>oerr ora 12532ORA-12532: TNS:invalid argume

Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index. Restrictions on Online Index Building Online index building is subject to the following restrictions:■ Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, then Oracle Database returns an error.■ You cannot specify ONLINE for a bitmap index or a cluster index.■ For a nonunique secondary index on an index-organized table,

A function-based index depends on any function that it is using. If the function or the specification of a package containing the function is redefined (or if the index owner’s EXECUTE privilege is revoked), then the following conditions hold:■ The index is marked as DISABLED.■ Queries on a DISABLED index fail if the optimizer chooses to use the index.■ DML operations on a DISABLED index fail unless the index is also markedUNUSABLE and the initialization parameter SKIP_UNUSABLE_INDEXES is se

The index owner needs the EXECUTE privilege on thefunction used to define a function-based index. If the EXECUTE privilege is revoked,Oracle marks the index DISABLED. The index owner does not need the EXECUTEWITH GRANT OPTION privilege on this function to grant SELECT privileges on theunderlying table.定义函数上的权限1. 对于在函数索引中定义的函数 ， 用户必须具有EXECUTE权限2. 如果EXECUTE权限被回收 ， 函数索引被标记为失效3. 函数索引拥有者可赋予SELECT权限on underlying table 而不需要WITH GR

Any user-written function used in a function-based indexmust have been declared with the DETERMINISTIC keyword to indicate that thefunction will always return the same output return value for any given set of inputargument values, now and in the future.DETERMINISTIC函数函数索引使用的用户自定义函数必须声明为 DETERMINISTIC，此关键字表明对于不一定的输入参数，此函数总会得到相同的输出结果返回值。[@more@]