ITPub博客

http://wiki.oracle.com/[@more@]

The records_per_block_clause lets you specify whether Oracle Database restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible.Restrictions on Records in a Block ■ You cannot specify either MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table. You must first drop the bitmap index.■ You cannot specify this clause for an index-organized table or a nested tab

http://www.orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl[@more@]

orafaqA deadlock occurs when two or more users are waiting for data locked by each other. When this happens, these users are stuck (deadly embraced) and cannot continue processing. Oracle automatically detects deadlocks and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also write ou

V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors. Each row in the view contains information for one bind variable defined in a cursor. This includes:■ Reference to the cursor defining the bind variable(hash_value, address) for the parent cursor and (hash_value, child_address) for the child cursor.■ Bind metadataName, position, datatype, character set ID, precision, scale, and maximum length of the bind variable.■ Bind dataOne of the bind values used for the bind v

If an error occurs while reading the server parameter file (during startup or an export operation), or while writing the server parameter file during its creation, the operation terminates with an error reported to the user.If an error occurs while reading or writing the server parameter file during a parameter update, the error is reported in the alert log and all subsequent parameter updates to the server parameter file are ignored. At this point, you can take one of the following actions:■ S

You can use the CREATE PFILE statement to export a server parameter file to a text initialization parameter file. Doing so might be necessary for several reasons:■ For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2 views.■ To modify the server parameter file by first exporting it, editing the resulting text file, and then re-creating it using the

SCOPE = SPFILE The change is applied in the server parameter file only. The effect is as follows:■ For dynamic parameters, the change is effective at the next startup and is persistent. ■ For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.SCOPE = MEMORY The change is applied in memory only. The effect is as follows:■ For dynamic parameters, the effect is immediate, but it is not persistent because the

The SPFILE initialization parameter contains the name of the current server parameter file. When the default server parameter file is used by the server (that is, you issue a STARTUP command and do not specify a PFILE), the value of SPFILE is internally set by the server. The SQL*Plus command SHOW PARAMETERS SPFILE (or any other method of querying the value of a parameter) displays the name of the server parameter file that is currently in use.[@more@]

If you are currently using a text initialization parameter file, use the following steps to migrate to a server parameter file.1. If the initialization parameter file is located on a client machine, transfer the file (for example, FTP) from the client machine to the server machine. 2. Create a server parameter file using the CREATE SPFILE statement. This statement reads the initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE

A server parameter file can be thought of as a repository for initialization parameters that is maintained on the machine running the Oracle Database server. It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This arrangement eliminates the need to manually update initialization parameters

Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision support systems and data warehouses. In addition to conventional tables, you can use parallel query and parallel DML with range- and hash-partitioned tables. By doing so, you can enhance scalability and performance for batch operations.The semantics and restrictions for parallel DML sessions are the same whether you are using index-organized tables or not.[@mo

A partition-wise join is a join optimization for joining two tables that are both partitioned along the join column(s). With partition-wise joins, the join operation is broken into smaller joins that are performed sequentially or in parallel. Another way of looking at partition-wise joins is that they minimize the amount of data exchanged among parallel slaves during the execution of parallel joins by taking into account data distribution.[@more@]

The Oracle database server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements. In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.For each SQL statement, depending on the selection criteria specified, unneeded partitions or subpartit

索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。　　一个索引的选择性越接近于1，这个索引的效率就越高。　　如果是使用基于cost的最优化，优化器不应该使用选择性不好的索引。如果是使用基于rule的最优化，优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。　　确定索引的选择性，可以有两种方法：手工测量和自动测量。　　（1）手工测量索引的选择性 　　如果

Here are a few points to remember when using partitioned indexes on composite partitions:■ Subpartitioned indexes are always local and stored with the table subpartition by default.■ Tablespaces can be specified at either index or index subpartition levels.当在复合分区上使用分区索引时，需要记录以下几点：1. 子分区索引通常情况下为本地索引，并存储于表的子分区2. 表空间可以在索引或索引子分区级别指定[@more@]

Here are a few guidelines for data warehousing and DSS applications:■ Local indexes are preferable because they are easier to manage during data loads and during partition-maintenance operations.■ Local indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.在数据载入和分区维护操作情况较多的情况下，本地索引更加合适在索引键的范围内查询时，本地索引可改进性能，因为许多索引分区能被并行扫描[@more@]

Here are a few guidelines for OLTP applications:■ Global indexes and unique, local indexes provide better performance than nonunique local indexes because they minimize the number of index partition probes.■ Local indexes offer better availability when there are partition or subpartition maintenance operations on the table.■ Hash-partitioned global indexes offer better performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions

You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.可在分区表上创建位图索引，但必须是Local类型[@more@]

Global nonpartitioned indexes behave just like a nonpartitioned index. They are commonly used in OLTP environments and offer efficient access to any individual record.全局非分区索引普遍用于OLTP环境[@more@]