Hello Everyone,
I have following query:
WITH
basic AS (...),
purchases AS (...),
...
activity AS (...)
SELECT
...
FROM schema.each_day dr
LEFT JOIN basic ON dr.name = basic.name
LEFT JOIN purchases ON dr.name = purchases.name
...
LEFT JOIN activity ON dr.name = activity.name
WHERE
dr.name BETWEEN '2015-01-01' AND '2015-05-31';
And using Vertica Community Edition v.7.1.1. It has such option:
SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION');
Setting this option really improves my query performance, query works five (5) times faster.
I've checked that from console client vsql.
Now I would like to fetch query results from my PHP script. I'm using PDO and unixODBC.
I was playing with PDO attributes like:
PDO::ATTR_EMULATE_PREPARES
PDO::ATTR_AUTOCOMMIT
I've tried to create single instance of PDO and then call each query one by one but nothing happens:
(Yes, I know that this option can be setup by superuser only and I'm using superuser credentials)
$r = $instance->query("SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION')");
$r = $instance->prepare("SELECT show_current_vertica_options()");
$r->fetchAll();last fetchAll() return empty array like no option set. Here is what I've expected to see:
db => SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION');
add_vertica_options
---------------------
Options Set
(1 row)
=> SELECT show_current_vertica_options();
show_current_vertica_options
--------------------------------------------------------------------------------
Opt Vertica Options
--------------------
ENABLE_WITH_CLAUSE_MATERIALIZATION
Does anyone have any idea hot to enable this option thru ODBC and keep it alive for next query?
Or maybe there is a way to enable this option thru Vertica configuration files or adminTools ?
Let me know!
Best,
Vitaliy.
still:
