ITPub博客

Function-based indexes provide an efficient mechanism for evaluating statements thatcontain functions in their WHERE clauses. The value of the expression is computed andstored in the index. When it processes INSERT and UPDATE statements, however,Oracle must still evaluate the function to process the statement.Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. A function-based index can also be used for a globalization support sort

You can create indexes on functions and expressions that involve one or more columnsin the table being indexed. A function-based index computes the value of the functionor expression and stores it in the index. You can create a function-based index as eithera B-tree or a bitmap index.The function used for building the index can be an arithmetic expression or anexpression that contains a PL/SQL function, package function, C callout, or SQLfunction. The expression cannot contain any aggregate func

NULL values in indexes are considered to be distinct except when all the non-NULLvalues in two or more rows of an index are identical, in which case the rows areconsidered to be identical. Therefore, UNIQUE indexes prevent rows containing NULLvalues from being treated as identical. This does not apply if there are no non-NULLvalues—in other words, if the rows are entirely NULL. Oracle does not index table rows in which all key columns are NULL, except in the caseof bitmap indexes or when the cl

Although the terms are often used interchangeably, indexes and keys are different.Indexes are structures actually stored in the database, which users create, alter, anddrop using SQL statements. You create an index to provide a fast access path to tabledata. Keys are strictly a logical concept. Keys correspond to another feature of Oraclecalled integrity constraints, which enforce the business rules of a database.Because Oracle uses indexes to enforce some integrity constraints, the terms key an

A composite index (also called a concatenated index) is an index that you create onmultiple columns in a table. Columns in a composite index can appear in any orderand need not be adjacent in the table.Composite indexes can speed retrieval of data for SELECT statements in which theWHERE clause references all or the leading portion of the columns in the compositeindex. Therefore, the order of the columns used in the definition is important.Generally, the most commonly accessed or most selective c

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of atable have duplicate values in the key column (or columns). Nonunique indexes donot impose this restriction on the column values.Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUEINDEX. Creating unique indexes through a primary key or unique constraint is notguaranteed to create a new index, and the index they create is not guaranteed to be aunique index.唯一索引和非唯一索引1. 在一个

Indexes are optional structures associated with tables and clusters. You can createindexes on one or more columns of a table to speed SQL statement execution on thattable. Just as the index in this manual helps you locate information faster than if therewere no index, an Oracle index provides a faster access path to table data. Indexes arethe primary means of reducing disk I/O when properly used.You can create many indexes for a table as long as the combination of columns differsfor each index.

A synonym is an alias for any table, view, materialized view, sequence, procedure,function, package, type, Java class schema object, user-defined object type, or anothersynonym. Because a synonym is simply an alias, it requires no storage other than itsdefinition in the data dictionary.Synonyms are often used for security and convenience. For example, they can do thefollowing:■ Mask the name and owner of an object■ Provide location transparency for remote objects of a distributed database■ Si

The sequence generator provides a sequential series of numbers. The sequencegenerator is especially useful in multiuser environments for generating uniquesequential numbers without the overhead of disk I/O or transaction locking. Forexample, assume two users are simultaneously inserting new employee rows into theemployees table. By using a sequence to generate unique employee numbers for theemployee_id column, neither user has to wait for the other to enter the nextavailable employee number. The

A dimension defines hierarchical (parent/child) relationships between pairs ofcolumns or column sets. Each value at the child level is associated with one and onlyone value at the parent level. A hierarchical relationship is a functional dependencyfrom one level of a hierarchy to the next level in the hierarchy. A dimension is acontainer of logical relationships between columns, and it does not have any datastorage assigned to it.The CREATE DIMENSION statement specifies:■ Multiple LEVEL clauses

A materialized view log is a schema object that records changes to a master table’sdata so that a materialized view defined on the master table can be refreshedincrementally.Each materialized view log is associated with a single master table. The materializedview log resides in the same database and schema as its master table.物化视图日志1. 记录主表数据变化的方案对象 , 使得定义在主表上的物化视图能够增量刷新2. 每个物化视图日志与一个主表相关 , 与主表位于同一数据库同一方案[@more@]

Oracle maintains the data in materialized views by refreshing them after changes aremade to their master tables. The refresh method can be incremental (fast refresh) orcomplete. For materialized views that use the fast refresh method, a materialized viewlog or direct loader log keeps a record of changes to the master tables.Materialized views can be refreshed either on demand or at regular time intervals.Alternatively, materialized views in the same database as their master tables can berefreshe

Data warehousing applications recognize multidimensional data in the Oracledatabase by identifying Referential Integrity (RI) constraints in the relational schema.RI constraints represent primary and foreign key relationships among tables. Byquerying the Oracle data dictionary, applications can recognize RI constraints andtherefore recognize the multidimensional data in the database. In some environments,database administrators, for schema complexity or security reasons, define views onfact and

In mobile computing environments, materialized views are used to download asubset of data from central servers to mobile clients, with periodic refreshes fromthe central servers and propagation of updates by clients back to the centralservers.数据计算环境下的物化视图在移动计算环境中,移动客户端可以使用物化视图从中央服务器下载一个数据的子集,还可以定期地从中央服务器取得最新数据,并将移动客户端的数据修改传回中央服务器.[@more@]

In distributed environments, materialized views are used to replicate data atdistributed sites and synchronize updates done at several sites with conflictresolution methods. The materialized views as replicas provide local access to datathat otherwise has to be accessed from remote sites.分布式环境下的物化视图在分布式环境中,用户可以使用物化视图提供的功能在各个分布的节点间复制数据, 同步各个节点修改的数据 物化视图可以将远程节点访问的数据复制到本地节点[@more@]

In data warehouses, materialized views are used to compute and store aggregateddata such as sums and averages. Materialized views in these environments aretypically referred to as summaries because they store summarized data. They canalso be used to compute joins with or without aggregations. If compatibility is setto Oracle9i or higher, then materialized views can be used for queries that includefilter selections.The optimizer can use materialized views to improve query performance byautomatica

Materialized views are schema objects that can be used to summarize, compute,replicate, and distribute data. They are suitable in various computing environmentssuch as data warehousing, decision support, and distributed or mobile computing.Materialized views are similar to indexes in several ways:■ They consume storage space.■ They must be refreshed when the data in their master tables changes.■ They improve the performance of SQL execution when they are used for queryrewrites.■ Their existe

An inline view is not a schema object. It is a subquery with an alias (correlation name)that you can use like a view within a SQL statement.内联视图1. 不是方案对象2. 是一个拥有别名的子查询 , 用户可以在 SQL 语句中将她作为一个视图来使用[@more@]

In the Oracle object-relational database, an object view let you retrieve, update, insert,and delete relational data as if it was stored as an object type. You can also defineviews with columns that are object datatypes, such as objects, REFs, and collections(nested tables and VARRAYs).对象视图1. 在 Oracle 的对象-关系型数据库中,利用对象视图就可以像操作对象类型一样对关系型数据进行DML操作2. 用户定义的视图中也可以包含对象数据类型的列,常用的对象数据类型有对象 , REFs等[@more@]

A join view is defined as a view that has more than one table or view in its FROMclause (a join) and that does not use any of these clauses: DISTINCT, aggregation,GROUP BY, START WITH, CONNECT BY, ROWNUM, and set operations (UNION ALL,INTERSECT, and so on).An updatable join view is a join view that involves two or more base tables or views,where UPDATE, INSERT, and DELETE operations are permitted. The data dictionaryviews ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS c