Vertica Blog

Vertica Blog

VSQL

Hand writing the text: Helpful Tips

View the Query Plan for a COPY Statement

James Knicely, Vertica Field Chief Technologist
May 16, 2019

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: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/EXPLAIN.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/QueryPlans/ExplainOutputOptions/EXPLAINOutputOptions.htm Have fun!
Tips and Tricks Orange Button

Close All Sessions for a User with a Single Command

James Knicely, Vertica Field Chief Technologist
May 9, 2019

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

James Knicely, Vertica Field Chief Technologist
May 8, 2019

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

James Knicely, Vertica Field Chief Technologist
May 7, 2019

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

James Knicely, Vertica Field Chief Technologist
April 25, 2019

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...
Hand writing the text: Helpful Tips

Forcing a Merge Join

James Knicely, Vertica Field Chief Technologist
April 23, 2019

The Vertica optimizer implements a join with one of the following algorithms: Merge join is used when projections of the joined tables are sorted on the join columns. Merge joins are faster and use less memory than hash joins. Hash join is used when projections of the joined tables are not already sorted on the...
Quick Tip - blue button

Displaying the Vertica Code Name

James Knicely, Vertica Field Chief Technologist
April 23, 2019

Every major release of Vertica has a code name. To find out the Vertica code name of your database, you can query the DC_STARTUPS Data Collector table. Example: Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/QueryingDataCollectorTables.htm Have fun!
Vintage businessman concept pointing on the wall wearing futuristic helmet at office

Descending Sequences

James Knicely, Vertica Field Chief Technologist
April 17, 2019

The default increment for a Vertica sequence is 1. But you can also create a sequence that has a negative increment so that you can have a count down. Example: Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Sequences/Sequences.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATESEQUENCE.htm Have fun!
Helpful Tips message on post-it note

Display Session Sequence Cached Values Remaining

James Knicely, Vertica Field Chief Technologist
April 17, 2019

To optimize the performance of large INSERT SELECT and COPY operations, Vertica uses sequence caching. To allocate cache among the nodes in a cluster for a given sequence, Vertica uses the following process: By default, when a session begins, the cluster initiator node requests cache for itself and other nodes in the cluster. The initiator...
Calendar February 29 for Leap Year

Is This a Leap Year?

James Knicely, Vertica Field Chief Technologist
April 16, 2019

A Common Year in the Gregorian calendar has 365 days. A Leap Year, which has 366 days, occurs nearly every 4 years. One algorithm to determine if a year is a Common or a Leap Year is: I’d like to create a user-defined SQL function in Vertica that will let me know if the year...
Cupcakes with blue icing and lit birthday candles

How Old Am I (In Months)?

James Knicely, Vertica Field Chief Technologist
April 16, 2019

You are probably aware that Vertica has a built-in function named AGE_IN_YEARS. It returns the difference in years between two dates, expressed as an integer. Example: My son is turning 18 this year! I was curious to know how many months he’s been on the planet, so I figured that I could just multiply his...

Display the Vertica Process Memory Allocated

James Knicely, Vertica Field Chief Technologist
April 10, 2019

For common Vertica cluster configurations, there should be a single running Vertica process on each node. You can use the Linux ps command to determine the process ID of the Vertica process. Once you have the process ID, you can display the amount of memory that has been allocated to the Vertica process using the...