New Materialized View, Query Rewrite, and Summary Management DDL Features

Page 146

For example, if your loader process defines a column as a number (3) but the actual number in the dump file is a 9999.99, then this would have killed a query against the external table in Oracle9i Database, since the data in the external table violates the format that the data is limited to. Oracle Database 10g offers new functionality that allows you to determine how the access driver will validate rows in the external table. You can decide whether to have it error out or to continue processing, skipping the invalid column.

By default, in Oracle Database 10g, Oracle will only process and validate columns on the select list of the query against the external table. You can also define this action by using the new project column clause in the alter table command. If you want the access driver to process and validate all column values, use the project column all clause of the alter table command. If you use the project column all clause, any column that is incorrectly defined will generate an error, regardless of whether or not it is selected.

New Materialized View, Query Rewrite, and Summary Management DDL Features

Oracle Database 10g offers a number of new features related to materialized views (Mviews) and query rewrite. These improvements include

Partition change tracking (PCT) enhancements

Query rewrite enhancements

Mview fast refresh enhancements

Summary management DDL enhancements

Partition Change Tracking Enhancements

Oracle9i Database introduced PCT, which allows you to track changes in partitions of detail tables related to an Mview. PCT also is used to support fast refresh after partition maintenance operations, such as partition truncate. PCT also allows for a wider variety of query rewrites.

In Oracle9i Database, PCT was only available on a table partitioned using either range or composite partitioning. Oracle Database 10g improves PCT by making it available for list-partitioned tables. The PCT requirements for list-partitioned tables are the same as for range- and composite-partitioned tables.

Another new feature related to PCT is that an Mview now only needs to be based on the pseudocolumn ROWID to support PCT. Previously, the Mview must have been based either on the partition key column or a partition marker or on a join-dependent expression of the detail table.