Skip to main content

Duplicate projections created automatically


Rupendra
Forum|alt.badge.img+1
I am using Vertica 6.1.2, and noticed that when the table is created and loaded with data using INSERT statement, Vertica creates multiple projections (even if the table has a single column) and all of the projections are based on same set of columns. I tried following- CREATE TABLE test_table( first_name VARCHAR(10), last_name VARCHAR(25), age INTEGER, gender VARCHAR(1) ); INSERT INTO test_table VALUES('John','Smith',16,'M'); SELECT * FROM projections WHERE upper(anchor_table_name)=upper('test_table') AND projection_schema=; SELECT * FROM projection_columns where table_name='test_table' AND table_schema=; Observations- a. There are 3 projections for the table. All of them are suffixed with "_b". b. All of these projections use all 4 columns. c. "IS_SUPER_PROJECTION" flag is true for all of them, The behavior is similar in Vertica 6.0 but different in Vertica 6.1.1. In 6.1.1, after data insertion, a super projection with suffix "_super" is created. Am I missing something here or is this a known issue in Vertica? Thanks, Rupendra

2 replies

Forum|alt.badge.img+2
  • Participating Frequently
  • August 2, 2013
Hi Rupendra, Vertica will duplicate the projections based on your k-safety. Check in : =>select designed_fault_tolerance from system; If your system fault tolerance is 1 that means that you will have 2 projections per table. You can read in the documentation about k-safety and fault tolerance to understand better why Vertica works like this : https://my.vertica.com/docs/6.1.x/HTML/index.htm#10730.htm https://my.vertica.com/docs/6.1.x/HTML/index.htm#10730.htm Hope this helps, Eugenia

Rupendra
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • August 3, 2013
Thanks a lot Eugenia. This explains why I was getting more than one projections per table. Vertica 6.1.1 cluster I was using had no k-safety. While Vertica 6.0 and 6.1.2 clusters had k-safety 1 and 2 respectively. Regards, Rupendra