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 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