multiple hints in query, how to pass ?

colin_loghin

What is the correct syntax for passing both a label and the direct hint in an insert select query ? 
More generic, is it possible to pass multiple hints ( or directives) in the same query? 

I have tried variations of 
dbadmin=> insert /*+direct*/ /*+label(test2)*/ into test1 select 1;ERROR 4856:  Syntax error at or near "/*+" at character 20
LINE 1: insert /*+direct*/ /*+label(test2)*/ into test1 select 1;
                           ^
but with no success ( v 6.1.3-10)
Documentation shows them as functional. 

https://my.vertica.com/docs/6.1.x/HTML/index.htm#1342.htm

eli_revach

Try this :
 insert /*+direct*/ into test1 select /*+label(test2)*/ * from test1;

colin_loghin

Great. That worked.  Documentation as stated on the link above is however incorrect. 

colin_loghin

Coming back to this topic of multiple hints after a few months. Version 7 has been released and patched a few times, but documentation still remains incorrect.  I will notify vertica support in case they don't notice this post. 

The issue with the eli's solution is that the label is not getting logged even though the statement parses and executes nicely. We might as well skip the label if its not getting logged. 

Hopefully there is a solution to pass the 2 hints in vertica 7, i just don't know what that is. 

id10t

Hi!

Sometimes Im really wonder from question. Yes, its not documented, but does no one trying to learn from his/her experience?

Example of DBD output:

CREATE PROJECTION public.inventory_fact_super /*+basename(inventory_fact),createtype(L)*/ 
(
...
)

Should I explain it?


Cheers

colin_loghin

Yes, we should try to learn from past experience. And yes, the proposed syntax above works great. Thanks. Do you also happen to know the meaning of the createType(L) hint. This is also not documented to my knowledge. 

id10t

Hi!

L = Load (created during load data).
This post outlines the different type of create types available for projections.

This is also not documented to my knowledge.

yep, not documented and Vertica hints its "the dark side of the Moon"
Sorry for English.

Regards.

[Deleted User]

We are looking into the on the Documentation side to correctly document this behavior.