Re: Partition Views on Oracle 7.3.2

From: Andy Cowling <arc_at_sequent.com>
Date: 1997/05/23
Message-ID: <85aflmz280.fsf@lapwing.uk.sequent.com>#1/1

I has similar problems with partitoned views recently and Oracle tech. support sent me the following checklist which cured my problem.

HTH

Subject:            CHECKLIST FOR GETTING OPTIMIZER TO USE FILTERS WITH 
                    PARTITION VIEWS
Circulation:         ** PUBLIC

Affected-Products: Oracle7 Server

Solution Description:

CHECKLIST FOR GETTING OPTIMIZER TO RECOGNIZE PARTITION VIEWS AND USE FILTERING WHEN APPROPRIATE

Set PARTITION_VIEW_ENABLED=TRUE in the config.ora (ifile).
Set OPTIMIZER_MODE=CHOOSE in the config.ora.
Set COMPATIBLE=7.3.2 in the config.ora
Make sure all the tables have correctly defined CHECK constraints.
Make sure all columns and all indexes of all tables in the partition view are identically defined. Columns must be of the same size and type; indexes must be on the same columns and be of the same type (regular, binary, bitmapped or reverse).
Make sure each UNION ALL branch of the partition view definition is either a SELECT * or is an explicit expansion of SELECT *. A documentation bug (#417614) has been filed to better document this restriction, but it is expected behavior.
ANALYZE all tables and indexes.
Make sure the query uses only a simple predicate, such as an equality or BETWEEN against literals. The optimizer won't recognize partition views if WHERE IN, OR or functions are used. This may change in future releases.
If a Nested Loops Join is performed, this could be bug 388621, fixed in 7.3.3. On most platforms, the fix is also included in patch release 7.3.2.2, available from support.
If the partitioning column is a CHAR type, this could be bug 366589, fixed in 7.3.3. On most platforms, the fix is also inlcuded in patch release 7.3.2.2, available from support. The workaround is to make the column a VARCHAR2 type and trim off any null padding in the data.