Skip to content

Creating/Viewing Scrambles

A scramble is a special table used by VerdictDB to speed up query processing. The information about the created scrambles is stored in its metadata table and is used at query time.

Syntax for Creating Scrambles

CREATE SCRAMBLE newSchema.newTable 
FROM originalSchema.originalTable 
[METHOD (uniform | hash)]
[(ON | HASHCOLUMN) hashcolumn]
[(SIZE | RATIO) sizeOfScramble]

Note:

  1. newSchema may be identical to originalSchema.
  2. newTable must be different from originalTable if newSchema is same as originalSchema.
  3. The user requires the write privilege for the newSchema schema and the read privilege for the originalSchema.originalTable.
  4. METHOD must be either "uniform" or "hash". A uniform scramble is used for count, sum, avg, max, and min. A hash scramble is used for count-distinct.
  5. If a hash scramble is to be built, the hashcolumn must be present. hashcolumn indicates the column that will appear within the count-distinct function (e.g., count(distinct hashcolumn)).
  6. sizeOfScramble (default = 1.0) defines the relative size of the scramble to its original table and must be a float value between 0.0 and 1.0 (e.g., sizeOfScramble=0.1 will create a scramble which size is 10% of the original table).
  7. The schema and table names can be quoted either using the double-quote (") or the backtick (`).

Syntax for Viewing Scrambles

SHOW SCRAMBLES;

This query will print the list of all scrambled tables that have been built.