Comments

• Jim_Knicely - Select Field - Administrator

  November 2017 edited November 2017

  Number 1:

  For TABLE_DAILY, what do you mean by DATE? Like 11/13/2017, 11/14/2017, 11/15/2017, etc.? Or do you mean DAY (i.e. 0, 1, 2, 3, 4, 5, 6, 7)? If its by date than I'm sure you'd exceed the 1,024 ROS container limit as each partition in stored in its own container.

  Vertica 9 has a great feature called "Hierarchical Partitioning" that could help you reduced the number of ROS containers used.

  See:
  https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AdministratorsGuide/Partitions/HierarchicalPartitioning.htm

  Number 2:

  You can create a storage policies for less often queried data partitions (i.e. older dates).

  See:
  https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AdministratorsGuide/StorageLocations/CreatingStoragePoliciesForOldData.htm

  0
• Vertica_User

  November 2017

  Thanks Jim.

  **Number 1 **
  TABLE_DAILY is partitioned by - The day of the year (1–365/366).
  Could you please let me know whether can we have below 3 partitions in one table,
  1. DOY
  2. Year53 + Week
  3. Year12 + Month

  0
• Jim_Knicely - Select Field - Administrator

  November 2017

  Certainly you can create those partitions, but I'd recommend against it. Take a peek at the following links:

  https://my.vertica.com/kb/HPE-Vertica-Partitions-The-FAQs/Content/FAQs/HPE-Vertica-Partitions-The-FAQs.htm

  https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Partitions/BestPractices.htm

  I'd highly recommend looking at "Hierarchical Partitioning" if you can!

  https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Partitions/BestPractices.htm

  0
• Vertica_User

  November 2017

  Thanks Jim. I will take a look.

  0
• emoreno Employee

  November 2017

  You should check if the projections have a right encoding. Maybe just changing the encoding will reduce the storage footprint. You can see encoding in the projection_column table.

  As Jim mentions take a look at Hierarchical partition and that can help to reduce the storage print taking advantage of the encoding.

  https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AdministratorsGuide/Partitions/HierarchicalPartitioning.htm?Highlight=hierarchy

  0
• Vertica_User

  November 2017

  Hi Emoreno,
  1.Columns are encoded as RLE or AUTO. Would like to use DESIGNER_DESIGN_PROJECTION_ENCODINGS and take a look at the script for the suggested column encodings in the biggest table.

  2. We are using Vertica Analytic Database v7.2.3-4. I think hierarchical partitioning is not available. Could you please confirm if its not available and let me know alternatives.

  0
• Vertica_User

  November 2017

  I ran the DESIGNER_DESIGN_PROJECTION_ENCODINGS, and looks like the table columns are encoded as per the recommendations. I don't see much difference in the encoding type between existing & recommended.

  Is it possible to try BZIP / GZIP but it is not recommended(by designer) though in any of the columns.

  0
• Vertica_User

  November 2017

  Do you have any update on above post

  0