multiple hints in query, how to pass ?

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. 



  • Options
    Try this :
     insert /*+direct*/ into test1 select /*+label(test2)*/ * from test1;
  • Options
    Great. That worked.  Documentation as stated on the link above is however incorrect. 
  • Options
    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. 
  • Options

    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 :)
  • Options
    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. 
  • Options

    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.

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file