Hi Bryan, Prior to the 4.2 release, if you want to delete rows from a table declared as immutable, you need to drop the table (in which case the index would be dropped as well). With 4.2 and above, the index of an immutable table will be kept in sync when rows are deleted from the data table with some restrictions (see PHOENIX-619 for details). If these restrictions don't work for your use case, you'll need to switch to using mutable secondary indexing, as declaring a table as immutable is an optimization geared toward append-only data. Thanks, James
On Tue, Mar 31, 2015 at 3:05 PM, Gerber, Bryan W <[email protected]> wrote: > After using DELETE FROM TABLE_NAME; to purge data from a table, queries that > “FULL SCAN” TABLE_NAME still return matches against the DELETED data, but > queries that SCAN OVER indexes do not return the values. Basically, after a > DELETE, data is in the main table, but not in the indexes. > > > > Any ideas how we can ensure that DELETE fully deletes the original data, or > detect when this occurs? > > > > Schema is roughly: > > > > CREATE TABLE IF NOT EXISTS table_name > > ( > > file_time VARCHAR NOT NULL, > > file_name VARCHAR NOT NULL, > > rec_num INTEGER NOT NULL, > > m.f1 VARCHAR, > > m.f2 VARCHAR, > > m.f3 VARCHAR, > > m.f4 VARCHAR, > > m.f5 VARCHAR, > > m.f6 VARCHAR, > > m.f7 VARCHAR, > > m.f8 VARCHAR, > > CONSTRAINT pkey PRIMARY KEY (file_time,file_name,rec_num) > > ) > TTL='7776000',IMMUTABLE_ROWS=true,KEEP_DELETED_CELLS='false',COMPRESSION='SNAPPY',SALT_BUCKETS=10,SPLIT_POLICY='org.apache.hadoop.hbase.regionserver.ConstantSizeRegionSplitPolicy'; > > > > -- indexes > > CREATE INDEX IF NOT EXISTS raw_data_idx ON table_name(m.f1) > TTL='7776000',KEEP_DELETED_CELLS='false',COMPRESSION='SNAPPY',MAX_FILESIZE='1000000000',SPLIT_POLICY='org.apache.hadoop.hbase.regionserver.ConstantSizeRegionSplitPolicy'; > > CREATE INDEX IF NOT EXISTS table_name_f2f3_idx ON table_name(m.f2,m.f3) > TTL='7776000',KEEP_DELETED_CELLS='false',COMPRESSION='SNAPPY',MAX_FILESIZE='1000000000',SPLIT_POLICY='org.apache.hadoop.hbase.regionserver.ConstantSizeRegionSplitPolicy'; > > CREATE INDEX IF NOT EXISTS table_name_f4f5_idx ON table_name(m.f4,m.f5) > TTL='7776000',KEEP_DELETED_CELLS='false',COMPRESSION='SNAPPY',MAX_FILESIZE='1000000000',SPLIT_POLICY='org.apache.hadoop.hbase.regionserver.ConstantSizeRegionSplitPolicy'; > > > > > > Query Examples: > > 0: jdbc:phoenix:dn01> SELECT FILE_NAME FROM TABLE_NAME WHERE FILE_NAME = > ‘abcdefg' AND REC_NUM =101; > > +------------------------------------------+ > > | FILE_NAME | > > +------------------------------------------+ > > +------------------------------------------+ > > No rows selected (1.329 seconds) > > > > Full scan (includes non-index fields) > > 0: jdbc:phoenix:dn01> SELECT * FROM TABLE_NAME WHERE FILE_NAME = ‘abcdefg' > AND REC_NUM =101; > > +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+--------------------+ > > | FILE_TIME | FILE_NAME > | REC_NUM | F1 > | F3 | | > > +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+--------------------+ > > | 2015-03-23 06:42:37+00 | abcdefg | 101 > | 49 | 50 > | 15 | > > +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+--------------------+ > > > > Full scan (single non-indexed field) > > 0: jdbc:phoenix:dn01> SELECT F8 FROM TABLE_NAME WHERE FILE_NAME = ‘abcdefg' > AND REC_NUM =101; > > +------------------------------------------+ > > | F8 | > > +------------------------------------------+ > > | ABC | > > +------------------------------------------+ > > > > > > Index scan (single indexed field) > > 0: jdbc:phoenix:dn01> SELECT F1 FROM TABLE_NAME WHERE FILE_NAME = ‘abcdefg' > AND REC_NUM =101; > > > > +------------------------------------------+ > > | F1 | > > +------------------------------------------+ > > +------------------------------------------+ > > > > Thanks, > > Bryan G. > >
