Tracking Save Points: Quick Tip

Jim Knicely authored this tip. The SAVEPOINT SQL command creates a special mark, called a savepoint, inside a transaction. A savepoint allows all commands that are executed after it was established to be rolled back, restoring the transaction to the state it was in at the point in which the savepoint was established. Savepoints can […]

Keep Data Collector Information for a Set Interval: Quick Tip

Jim Knicely authored this tip. The Data Collector retains history of important system activities and records essential performance and resource utilization counters. You probably know that you can set a size restraint (memory and disk space in kilobytes) for the specified Data Collector table on all nodes. But if you are looking to keep records […]

Turn Off Row Counts in VSQL: Quick Tip

Jim Knicely authored this tip. The SET NOCOUNT command in SQL Server will stop the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results. You can accomplish the same thing in Vertica by shutting off the footer. Example: [dbadmin@s18384357 ~]$ vsql -c “SELECT * FROM […]

Generate a Notice Not an Error When Adding a Duplicate Column to a Table: Quick Tip

Jim Knicely authored this tip. When adding a column to a table in Vertica 9.2, the optional clause IF NOT EXISTS generates an informational message if the column already exists under the specified name. If you omit this option and the column exists, Vertica generates a ROLLBACK error message. Example: dbadmin=> CREATE TABLE test (c1 […]

What You Never Knew About Vertica Could Surprise You

Paige Roberts authored this post. I just started working on the Vertica team. As the “new guy,” my first few weeks of work have been largely about cramming as much Vertica information into my brain as possible in the shortest time possible. I’ve been aware of the Vertica Analytics Platform for a while. I used […]

Swapping Schemas: Quick Tip

Jim Knicely authored this tip. Renaming schemas is useful for swapping schemas without actually moving data. To facilitate the swap, enter a non-existent, temporary placeholder schema. But be careful with hardcoded schema names in SQL code like views! They won’t be swapped. Example: dbadmin=> create schema schema1; CREATE SCHEMA dbadmin=> create schema schema2; CREATE SCHEMA […]

Vertica Logos

Confirming if a Data Load was Apportioned: Quick Tip

Jim Knicely authored this tip. When running a COPY command, a parser can use more than one database node to load a single input source in parallel. This approach is referred to as apportioned load and is often much faster than loading from a single node. One way of confirming that a data load took […]

Sort Data from Within a View: Quick Tip

Jim Knicely authored this tip. If a view definition includes an ORDER BY clause, Vertica ignores it. But you can force Vertica to sort the view data on the ORDER BY columns by adding a LIMIT clause. If you don’t know how many records to limit, simply limit by some arbitrary large value! Example: dbadmin=> […]

Copy Table History: Quick Tip

Jim Knicely authored this tip. The COPY_TABLE function copies one table to another… FAST! Down the road I might want to know if a table was created as a copy using the COPY_TABLE function. To find out, I can query the data collector table DC_COPY_TABLES. Example: dbadmin=> SELECT COUNT(*) FROM big_fact; COUNT ———– 200000000 (1 […]