Greenplum will use a set of default rules. Random distribution is fine for small-dimension tables and lookup tables, but probably not for large fact tables.įor the gruz table there is no distribution clause. Although this will generate a good distribution with almost no skew, it might not be useful when colocated joins will help performance. The foo table will have rows distributed randomly among the segments. In the case of the bar table, Greenplum will compute a hash value on the id column of the table when the row is created and then uses that value to determine in which segment the row should reside. (id INT, even_more_stuff TEXT) DISTRIBUTED REPLICATED

(id INT, still_more_stuff TEXT, zipcode CHAR(5))

(id INT, more_stuff TEXT, size FLOAT8 ) DISTRIBUTED RANDOMLY (id INT, stuff TEXT, dt DATE) DISTRIBUTED BY (id) The other distribution method uses a hash function computed on the values of some columns in the table. In random distribution, each row is randomly assigned a segment when the row is initially inserted. Prior to Greenplum 6, there were two distribution methods. Greenplum adds a distribution clause to the Data Definition Language (DDL) for a CREATE TABLE statement. In Greenplum, the data distribution policy is determined at table creation time. All other things being equal, having roughly the same number of rows in each segment of a database is a huge benefit. One of the most important methods for achieving good query performance from Greenplum is the proper distribution of data. Used to speed lookups of individual rows in a table. Provide a method for accessing data outside Greenplum. Used to enhance performance for data that is rarely changed. Used to minimize data table storage in the disk system. Orientationĭetermines whether the data is stored by rows or by columns. Partitioningĭetermines how the data is stored on each of the segments. Data model aside, Greenplum offers a wide variety of choices in how data is organized, including the following: Distributionĭetermines into which segment table rows are assigned.

Data warehouses generally prefer a data model that is flatter than a normalized transactional model. A simple âlift and shiftâ from a transactional data model is almost always suboptimal. To make effective use of Greenplum, architects, designers, developers, and users must be aware of the various methods by which data can be stored because these will affect performance in loading, querying, and analyzing datasets.

Disk Sorter Ultimate 15.4.16 free