SKIP_PROJS

Specifies which projections to avoid using for a queried table.

Syntax

...FROM table-name /*+ SKIP_PROJS( [[database.]schema.]projection[,...] ) */

Arguments

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.

projection

A projection to skip. You can specify a list of comma-delimited projections.

Description

The SKIP_PROJS specifies one or more projections that the optimizer should avoid using. If the SKIP_PROJS hint excludes all available projections that are valid for the query, the optimizer issues a warning and ignores the projection hints.

Examples

In this example, the EXPLAIN output shows that the optimizer uses the projection public.Emp_Dimension_b0 for a given query:

QUERY PLAN DESCRIPTION:
------------------------------

EXPLAIN SELECT Employee_last_name, Employee_first_name, Employee_city, Employee_position FROM Emp_Dimension;

Access Path:
+-STORAGE ACCESS for Emp_Dimension [Cost: 59, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
|  Projection: public.Emp_Dimension_b0
				

You can use the SKIP_PROJS hint to avoid using this projection. If another projection is available that is valid for this query, the optimizer uses it instead:

QUERY PLAN DESCRIPTION:
------------------------------

EXPLAIN SELECT Employee_last_name, Employee_first_name, Employee_city, Employee_position FROM Emp_Dimension /*+SKIP_PROJS('public.Emp_Dimension')*/;

Access Path:
+-STORAGE ACCESS for Emp_Dimension [Cost: 152, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
| Projection: public.Emp_Dimension_Unseg