PROJS

Specifies one or more projections to use for a queried table.

Syntax

...FROM table-name /*+ 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

The projection to use. You can specify a list of comma-delimited projections.

Description

The PROJS hint can specify multiple projections; the optimizer determines which ones are valid and uses the one that is most cost-effective for the queried table. If no hinted projection is valid, the query returns a warning and ignores projection hints.

Examples

The following query includes a PROJS hint that specifies two projections:

=> EXPLAIN SELECT * FROM Emp_Dimension /*+PROJS('public.Emp_Dimension_Unseg', 'public.Emp_Dimension')*/;

The first projection public.Emp_Dimension_Unseg does not include all columns in the queried table Emp_Dimension, so the optimizer cannot use it. The second projection includes all table columns so the optimizer uses it, as verified by the following query plan:

QUERY PLAN DESCRIPTION:
------------------------------
explain SELECT * FROM Emp_Dimension /*+PROJS('public.Emp_Dimension_Unseg', 'public.Emp_Dimension')*/;
Access Path:
+-STORAGE ACCESS for Emp_Dimension [Cost: 125, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
|  Projection: public.Emp_Dimension_b0