Instead of the from clause specifying the table name you can also specify a projection name. E.g. the example below. I have an online_sales.online_sales_fact table with a projection online_sales_fact_sq_sda_gpda. I can specify the projection vs the table in the from clause if I want to force Vertica to use that projection vs whatever one the optimizer might choose.
dbadmin=> select sale_date_key,product_key,customer_key,sum(sales_quantity),sum(sales_dollar_amount),sum(gross_profit_dollar_amount) from online_sales.online_sales_fact_sq_sda_gpda group by sale_date_key,product_key,customer_key order by sale_date_key,product_key,customer_key limit 1;
sale_date_key | product_key | customer_key | sum | sum | sum
---------------+-------------+--------------+-----+-----+-----
1 | 23 | 7031 | 9 | 320 | 181
(1 row)
Note you want to make sure the projection satisfies the requirements for the query, such as it has all columns. Also note if you force the projection, and it's not the one the optimizer would have chosen, there may be performance implications. Always better to design and tune using Database Designer so the optimizer chooses the best projection based on resources and performance.
Comments
Instead of the from clause specifying the table name you can also specify a projection name. E.g. the example below. I have an online_sales.online_sales_fact table with a projection online_sales_fact_sq_sda_gpda. I can specify the projection vs the table in the from clause if I want to force Vertica to use that projection vs whatever one the optimizer might choose.
dbadmin=> select sale_date_key,product_key,customer_key,sum(sales_quantity),sum(sales_dollar_amount),sum(gross_profit_dollar_amount) from online_sales.online_sales_fact_sq_sda_gpda group by sale_date_key,product_key,customer_key order by sale_date_key,product_key,customer_key limit 1;
sale_date_key | product_key | customer_key | sum | sum | sum
---------------+-------------+--------------+-----+-----+-----
1 | 23 | 7031 | 9 | 320 | 181
(1 row)
Note you want to make sure the projection satisfies the requirements for the query, such as it has all columns. Also note if you force the projection, and it's not the one the optimizer would have chosen, there may be performance implications. Always better to design and tune using Database Designer so the optimizer chooses the best projection based on resources and performance.
I hope it helps.
Or use the PROJS hint...
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Hints/Projs.htm
Example:
dbadmin=> explain select * from test; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ explain select * from test; Access Path: +-STORAGE ACCESS for test [Cost: 10, Rows: 1 (NO STATISTICS)] (PATH ID: 1) | Projection: public.test_super | Materialize: test.c1, test.c2 ------------------------------ dbadmin=> explain select * from test /*+ PROJS('public.test_my_pr') */; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------- - ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ explain select * from test /*+ PROJS('public.test_my_pr') */; Access Path: +-STORAGE ACCESS for test [Cost: 10, Rows: 1 (NO STATISTICS)] (PATH ID: 1) | Projection: public.test_my_pr | Materialize: test.c2, test.c1 ------------------------------Great, thanks for your great support!
