• Tips & Tricks

    Ignore Projections During Optimization

    2

    I just learned about an undocumented function which allows you to tell the optimizer to ignore certain projections. Ignoring projections can be useful for testing or when optimizing queries. This post will show how to use the set_optimizer_directives() function to accomplish this.

  • News

    Vertica 7 Training Webinar

    5

    HP held a 3-part webinar on an overview of Vertica 7 back in January. This webinar discussed installation improvements, fault groups, load improvements, SQL Server improvements, and query optimizer improvements.

  • Optimization, Performance Tuning

    Overview of Pre-join Projections

    3

    This post will give an overview of Vertica’s pre-join projections. Pre-join projections are not frequently used due to the manual set up work and limitations around its usage. However, they can deliver increased performance at query execution by moving the join step into the load.

  • News

    Call For Authors

    0

    I’ve set up this site with the goal of becoming a valuable resource for the analytic database known as Vertica. Thus far, it has been a tremendous immersion process to learn and practice this technology. Resources have been extremely scarce and there’s much to share while learning the technology.

  • Administration, Tips & Tricks

    Skip lazy projection creation

    0

    I received a strange error when attempting to replace a super projection. I haven’t been able to reproduce this behavior (by explicitly defining the projections or by auto super projection creation). However, I’ll walk through scenario with the former projection, the replacement projection, and the resolution.

  • Administration, Best Practices, Development

    Time Series with Last Value

    0

    This post will briefly cover working with time series stock data in Vertica. In my first experience, I use the TIME_SLICE function to aggregate and window the data as described in documentation. However, due to a lack of a window frame, I received incorrect results when paired with LAST_VALUE. After adding a window frame, the…

  • Administration, Database Design, Tips & Tricks

    Clearing Database Designer Workspace

    0

    I recently had an error running Database Designer which left some residual tables and schemas associated with the design. I posted my question on the (unofficial) Vertica forums as to how to remove the design workspace, and was fortunate to have my question answered. However, I was surprised there were few resources around the function…

  • Administration, Monitoring

    Table Size

    0

    This post will show how to get the compressed size of Vertica tables from column_storage and projection_storage system tables in the v_monitor schema. To get an estimated raw or uncompressed data size, see my earlier post on License Utilization.

  • Administration, Monitoring

    License Utilization

    0

    It’s important to remain compliant with license limits and not exceed space utilization in Vertica. Space utilization for licenses are based on the raw data size. This post will briefly cover the mechanisms for getting license usage and the uncompressed database size.

  • Administration

    Using Query Labels

    2

    In an earlier post, I wrote about the importance of using stream names for COPY loads in Vertica. This post continues on that topic with using query labels on DML statements such as SELECT, INSERT, UPDATE and DELETE.

  • Tips & Tricks

    Special Characters

    0

    Vertica uses extended (E) and Unicode (U) escaping for special characters. Some common special characters which need escaping are: Extended String Syntax Unicode Literal String Description ASCII Decimal E’\n’ U&’\000a’ Linefeed 10 E’\f’ U&’\000c’ Formfeed 12 E’\r’ U&’\000d’ Carriage return 13 E’\\’ U&’\005c’ Backslash 92

  • Tips & Tricks

    Request Errors

    0

    To investigate query request errors in Vertica, you can try the query below which joins the query_requests to the error_messages. The error level, code and message will be added to the query_request. The query uses the left 100 characters of request removing any tab, carriage return or line feeds.

  • Best Practices

    Importance of Using Stream Name with Copy

    0

    When using a COPY bulk load in Vertica, it’s important to properly identify the load using the optional STREAM NAME parameter. This parameter makes it possible to easily identify loads when monitoring system load streams since they can generally be resource intensive.

  • Administration

    Identifying Slow Queries

    0

    In this post, I’ll show how to identify slow running query requests in Vertica. The query_requests system table in the v_monitor schema holds information about user-issued query requests.

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.