Vertica Blog

Vertica Blog

Quick Tips

Spread Daemon Timeout

Min and Max Values for Spread Daemon Timeout

When you know your network or nodes may be unable to respond for a specific amount of time due to network delays or temporary pauses of a VM, you can increase the spread timeout period to longer than this time using the SET_SPREAD_OPTION function (available starting in Vertica 9.2.1-0). You might be wondering what the...
Spread Daemon Timeout

Adjusting Spread Daemon Timeouts for Virtual Environments

If you are running Vertica in a virtual environment, you’ve probably seen nodes leave the cluster even though they are still running. If network delays or temporary pauses of a VM last longer than the spread timeout period, you may see up nodes leave the database. In these cases, you can increase the spread timeout...
Helpful Tips in blue text with magnifying glass

Include the ALTER and DROP Privileges in a GRANT ALL Statement

When you issue a GRANT ALL statement on a database object such as a table, the ALTER and DROP privileges are excluded. Staring in Vertica 9.2.1, you can include the ALTER and DROP privileges as part of a GRANT ALL statement by specifying the EXTEND clause. Helpful Link: Have fun!
Helpful Tips text with hand and marker over data center background

Watch Out for Data Induced “Cross” Joins

I recently worked with a client who reported that a query in Vertica would not return data. That is, the query ran forever. The query was pretty simple, joining two 100 billion+ tables together. After some investigation, we realized that the query could potentially return trillions and trillions of records! Why? Because the join keys...
Quick Tip - blue button

Direct Output to /dev/null in VSQL

When tuning a query, you often need to run it many times as you make adjustments. If the query returns a lot of rows, then this can make for a messy screen and long waits as VSQL formats the data. All you really want to know is how long it took to run the query....
Vintage businessman concept pointing on the wall wearing futuristic helmet at office

NULL Values Do Not Count Against Your Vertica License!

I am often asked if NULL values stored in table columns are counted against the raw data size of a Vertica license. The answer is NO. Helpful Links: Have fun!
Compass rose with true north pointing to "Helpful Tips" text

Add a Column to All of a Table’s Projections

Starting in Vertica 9.2.1, when you add a column to a table you can now tell Vertica to add that column to all of the table’s underlying projections. To do that, use the ALL PROJECTIONS clause of the ALTER TABLE … ADD COLUMN command. By default when you add a column to a table, Vertica...
Hand writing the text: Helpful Tips

View the Query Plan for a COPY Statement

Vertica 9.2.1 now supports the ability to see the query plan produced by the EXPLAIN command for a COPY command. Wait, where is the plan? Oh, I need at least one table projection to get a plan! Helpful Links: Have fun!
Tips and Tricks Orange Button

Close All Sessions for a User with a Single Command

The Vertica CLOSE_SESSION function can be used to close a user session one session at a time. If you have a user that has opened a bunch of sessions, closing them one at a time would be a very long and tedious process. That’s where the CLOSE_USER_SESSIONS function comes in handy. It will stop all...
Vintage businessman concept wearing futuristic helmet at office

Table Schemas for Global and Local Temp Tables

By default, Vertica stores Global Temporary Tables in the Public schema and Local Temporary Tables in the V_TEMP_SCHEMA schema. Vertica uses the default schemas if a schema name is not provided when creating temporary tables: I can specify a different schema name for a Global Temporary Table: But not for a Local Temporary Table: Helpful...
Helpful Tips message on post-it note

Make Comparisons on Subqueries that Return Multiple Rows

You typically use comparison operators (such as =, >, < ) only on subqueries that return one row. With ANY and ALL operators, you can make comparisons on subqueries that return multiple rows. I want to check if an integer is greater than any of the integers in a set of rows. If I try...
Business card that says Expert Tips,

Create a Python UDx to Order a List of Values

User-Defined Extensions (UDxs) are functions contained in external shared libraries that are developed in C++, Python, Java, or R using the Vertica SDK. The external libraries are defined in the Vertica catalog using the CREATE LIBRARY statement. They are best suited for analytic operations that are difficult to perform in SQL, or need to be...