+2How sensitive is the query parser to token position? We have a CTAS query where the /*+DIRECT*/ is in the wrong place but we don't see lots of moveout or WOS_SPILL events so we're wondering if it is being picked up anyways.
The query is currently written as CREATE TABLE foo AS /*+DIRECT*/ SELECT stuff FROM tables;
They execute several of these an hour. The "SELECT " returns several GB so I'd expect to see a spill or moveout somewhere.
+2Hi,
I think your hint is in the correct place if you are trying to perform the current COPY as a DIRECT load.
The doc link below shows the DIRECT hint appearing just after the AS clause:
While the doc link below shows the hint appearing after the SELECT clause:
I think you can put the hint in either spot.
I quick test shows that you can generate a warning if the hint is not specified correctly in either location!
Both work:
dbadmin=> CREATE TABLE some_test AS /*+ DIRECT */ SELECT * FROM big_fact LIMIT 100000; CREATE TABLE dbadmin=> CREATE TABLE some_test2 AS SELECT /*+ DIRECT */ * FROM big_fact LIMIT 100000; CREATE TABLE
These generate warnings:
dbadmin=> CREATE TABLE some_test_bad2 AS /*+ DI RECT */ SELECT * FROM big_fact LIMIT 100000; WARNING 4857: Syntax error in HINT at or near "RECT" at character 39 CREATE TABLE dbadmin=> CREATE TABLE some_test_bad AS SELECT /*+ DI RECT */ * FROM big_fact LIMIT 100000; WARNING 4857: Syntax error in HINT at or near "RECT" at character 45 CREATE TABLE
And if we try the hint in a spot where it shouldn't be, we get an error:
dbadmin=> CREATE TABLE some_test_bad3 AS SELECT * FROM /*+ DIRECT */ big_fact LIMIT 100000;
ERROR 4856: Syntax error at or near "/*+" at character 46
LINE 1: CREATE TABLE some_test_bad3 AS SELECT * FROM /*+ DIRECT */ b...
^
If you really, really want to make sure you are using DIRECT, put the hint in twice 
dbadmin=> CREATE TABLE some_test_ha_hah AS /*+ DIRECT */ SELECT /*+ DIRECT */ * FROM big_fact LIMIT 100000; CREATE TABLE
+2Also, note that a CTAS in Vertica is actually a 2 step process. First Vertica creates the table then it performs an INSERT. If you want to make sure your DIRECT hint is being used, just check out the SQL Vertica generates!
Example:
dbadmin=> CREATE TABLE some_test AS /*+ DIRECT */ SELECT c1 FROM big_fact LIMIT 100000;
CREATE TABLE
dbadmin=> SELECT start_timestamp, request FROM query_requests WHERE session_id = current_session() LIMIT 2;
start_timestamp | request
-------------------------------+-----------------------------------------------------------------------------------------------
2018-12-28 09:29:39.74899-05 | CREATE TABLE some_test AS /*+ DIRECT */ SELECT c1 FROM big_fact LIMIT 100000;
2018-12-28 09:29:39.780561-05 | INSERT /*+DIRECT*/ INTO public.some_test SELECT big_fact.c1 FROM public.big_fact LIMIT 100000
(2 rows)
dbadmin=> \q
[dbadmin@s18384357 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
dbadmin=> CREATE TABLE some_test2 AS SELECT /*+ DIRECT */ c1 FROM big_fact LIMIT 100000;
CREATE TABLE
dbadmin=> SELECT start_timestamp, request FROM query_requests WHERE session_id = current_session() LIMIT 2;
start_timestamp | request
-------------------------------+------------------------------------------------------------------------------------------------
2018-12-28 09:30:42.871112-05 | CREATE TABLE some_test2 AS SELECT /*+ DIRECT */ c1 FROM big_fact LIMIT 100000;
2018-12-28 09:30:42.903237-05 | INSERT /*+DIRECT*/ INTO public.some_test2 SELECT big_fact.c1 FROM public.big_fact LIMIT 100000
(2 rows)
Vertica applied the DIRECT hint to the INSERT statement whether if the hint was put just after the AS clause or just after the SELECT clause in the CTAS!
Already have an account? Login
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
