hi
May I specity which projection to be used in the query? like oracle hint
May I specity which projection to be used in the query?
Moderator: NorbertKrupa
-
- Newbie
- Posts: 1
- Joined: Tue Oct 20, 2015 12:39 pm
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: May I specity which projection to be used in the query?
There is an optimizer hint called "UseOnlyProjections" that may be of value to you.
Example:
Notice that the above EXPLAIN PLAN shows that the public.test_super projection is used.
Notice that the above EXPLAIN PLAN shows that the public.use_this_one projection is being used now.
Example:
Code: Select all
dbadmin=> create table test (c1 int, c2 int) order by c1;
CREATE TABLE
dbadmin=> create projection use_this_one as select * from test order by c2;
CREATE PROJECTION
dbadmin=> explain select * from test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------
explain select * from test;
Access Path:
+-STORAGE ACCESS for test [Cost: 2, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Projection: public.test_super
| Materialize: test.c1, test.c2
Code: Select all
dbadmin=> select set_optimizer_directives('UseOnlyProjections=use_this_one');
set_optimizer_directives
--------------------------------------------------------------------------------------
Optimizer Directives
----------------------
UseOnlyProjections=public.use_this_one
(1 row)
dbadmin=> explain select * from test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------
Optimizer Directives
----------------------
UseOnlyProjections=public.use_this_one
explain select * from test;
Access Path:
+-STORAGE ACCESS for test [Cost: 606, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Projection: public.use_this_one
| Materialize: test.c2, test.c1
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: May I specity which projection to be used in the query?
You can also set an optimizer directive to ignore projections during optimization.
Checkout vertica.tips for more Vertica resources.