create col_type
create
col_type
<end_user
|col_type_name> as
<number
|text
>
create
col_type
t_site_id as
text
;
create
col_type
t_dept_id as
text
;
create
col_type
t_item_id as
text
;
create
col_type
t_customer_id as
text
;
create
col_type
t_date as
text
;
create
col_type
t_customer_info as
text
;
create
col_type
end_user
as
text
;
create table
create
table
<|merge|big> table_name ( column_name1 <number
|text
|col_type_name>, ...)
create
merge table
items( item_id t_item_id, art_label text
, dept t_dept_id, avg_week_sales number
, sales_price number
);
create
merge table
customers( customer_id t_customer_id, customer_name text
);
create
table
item_tags( item_id t_item_id, tag text
);
create
table
fidelity_cards( customer_id t_customer_id, card_label t_customer_info, valid_from t_date, valid_until t_date);
create
table
item_customer_infos( customer_id t_customer_id, item_id t_item_id, info t_customer_info, valid_from t_date, valid_until t_date);
create
big table
sales( item_id t_item_id, customer_id t_customer_id, sales_date t_date, sales_qty number
, line_id text
, packaging_id t_item_id);
create
big table
inventory( item_id t_item_id, inv_qty number
);
create view
create
view
view_name as
select
* from
table_name1, table_name2, ... where
table_name1.column_nameA = table_name2.column_nameB ...
create
view
v_items as
select
* from
items;
create
view
v_item_tags as
select
* from
item_tags;
create
view
v_fidelity_cards as
select
* from
fidelity_cards;
create
view
v_item_customer_infos as
select
* from
item_customer_infos;
create
view
v_sales as
select
* from
sales, items, customers where
items.item_id=sales.item_id and
customers.customer_id=sales.customer_id;
create
view
v_inventory as
select
* from
inventory, items where
items.item_id=inventory.item_id;
insert values
insert
into
table_name values
('col1_value'
,'col2_value'
,...)
insert
into
items values
('artA'
,'the article A'
,'dept #1'
,10,1.5);
insert
into
items values
('artB'
,'the article B'
,'dept #2'
,10,3.2);
insert
into
items values
('box1'
,'a box'
,'packaging'
,10,0);
insert
into
customers values
('C1'
,'customer #1'
)('C2'
,'customer #2'
);
insert
into
item_tags values
('artA'
,'tag #1'
);
insert
into
item_tags values
('artA'
,'tag #2'
);
insert
into
fidelity_cards values
('C1'
,'SILVER'
,'20191201'
,'20191231'
);
insert
into
fidelity_cards values
('C1'
,'GOLD'
,'20201201'
,'20201231'
);
insert
into
item_customer_infos values
('C1'
,'artA'
,'FREQUENT BUYER of artA in 2019'
,'20190101'
,'20191231'
);
insert
into
item_customer_infos values
('C1'
,'artB'
,'FREQUENT BUYER of artB in 2020'
,'20200101'
,'20201231'
);
insert
into
item_customer_infos values
('C2'
,'artB'
,'FREQUENT BUYER of artB in 2020'
,'20200101'
,'20201231'
);
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID01'
,'box1'
);
insert
into
sales values
('artB'
,'C2'
,'20200102'
,6,'ID02'
,''
);
insert
into
sales values
('artB'
,'C1'
,'20191231'
,4,'ID03'
,''
);
insert
into
sales values
('artB'
,'C2'
,'20200102'
,7,'ID04'
,'box1'
);
insert
into
sales values
('artC'
,'C1'
,'20200102'
,8,'ID05'
,''
);
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID06'
,'box1'
);
insert
into
sales values
('artA'
,'C2'
,'20191231'
,5,'ID07'
,'box1'
);
insert
into
inventory values
('artA'
,32);
insert
into
inventory values
('artC'
,12);
refresh dirty view
Refresh the views after insert
Dirty means that data has been inserted in the first table of the view since last refresh
refresh
dirty
view
view data model & parameters
Desc (describe) command
desc
<|col_type
|table
|view
|context|computed_columns|parameter>
desc
col_type
<|verbose
>
desc
table
the_table <|verbose
>
desc
view
the_view <|verbose
>
set
CACHE
='n'
;
desc
;
object_name | object_type |
t_site_id | col_type |
t_dept_id | col_type |
t_item_id | col_type |
t_customer_id | col_type |
t_date | col_type |
t_customer_info | col_type |
end_user | col_type |
items | table |
customers | table |
item_tags | table |
fidelity_cards | table |
item_customer_infos | table |
sales | table |
inventory | table |
v_items | view |
v_item_tags | view |
v_fidelity_cards | view |
v_item_customer_infos | view |
v_sales | view |
v_sales_#partition#_00001 | view |
v_sales_#partition#_00002 | view |
sales_#partition#_00001 | table |
v_inventory | view |
sales_#partition#_00002 | table |
TCP_PORT_TRANSAC | parameter |
LOG_VERBOSE | parameter |
CPU_COUNT | parameter |
SLEEP_AFTER_SQL | parameter |
SB_LICENSE_PATH | parameter |
ARRAY_BLOCK_SIZE_BIG | parameter |
ARRAY_BLOCK_SIZE_SMALL | parameter |
PROD_SERVER | parameter |
MAX_PERF_USE_COMPRESSION | parameter |
PARETO_LIMIT | parameter |
PARTITION_LINE_COUNT | parameter |
CACHE | parameter |
MAX_PERF_CHANGE_COUNT | parameter |
ALLOW_WHERE_ERROR | parameter |
ALLOW_GROUP_BY_ERROR | parameter |
INIT_FILE_PATH | parameter |
desc
col_type
;
col_type_name | data_type | ival_count |
t_site_id | text | 0 |
t_dept_id | text | 3 |
t_item_id | text | 5 |
t_customer_id | text | 3 |
t_date | text | 7 |
t_customer_info | text | 4 |
end_user | text | 0 |
desc
col_type
verbose
;
col_type_name | data_type | ival_count |
t_site_id | text | 0 |
t_dept_id | text | 3 |
t_item_id | text | 5 |
t_customer_id | text | 3 |
t_date | text | 7 |
t_customer_info | text | 4 |
end_user | text | 0 |
sys#type#items#art_label | text | 3 |
sys#type#items#avg_week_sales | number | 1 |
sys#type#items#sales_price | number | 3 |
sys#type#customers#customer_name | text | 2 |
sys#type#item_tags#tag | text | 2 |
sys#type#sales#sales_qty | number | 5 |
sys#type#sales#line_id | text | 7 |
sys#type#inventory#inv_qty | number | 2 |
desc
table
;
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
items | 5 | 3 | n | n | n |
customers | 2 | 2 | n | n | n |
item_tags | 2 | 2 | n | n | n |
fidelity_cards | 4 | 2 | n | n | n |
item_customer_infos | 5 | 3 | n | n | n |
sales | 6 | 3 | n | n | n |
inventory | 2 | 2 | n | n | n |
sales_#partition#_00001 | 6 | 3 | n | n | n |
sales_#partition#_00002 | 6 | 1 | n | n | n |
desc
table
items;
table_name | column_name | column_type | col_type_name |
items | item_id | text | t_item_id |
items | art_label | text | sys#type#items#art_label |
items | dept | text | t_dept_id |
items | avg_week_sales | number | sys#type#items#avg_week_sales |
items | sales_price | number | sys#type#items#sales_price |
desc
table
item_tags;
table_name | column_name | column_type | col_type_name |
item_tags | item_id | text | t_item_id |
item_tags | tag | text | sys#type#item_tags#tag |
desc
table
item_tags verbose
;
table_name | column_name | column_type | col_type_name |
item_tags | item_id | text | t_item_id |
item_tags | tag | text | sys#type#item_tags#tag |
desc
view
;
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_items | 1 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_item_tags | 1 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_fidelity_cards | 1 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_item_customer_infos | 1 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_inventory | 2 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
desc
view
v_items;
view_name | table_name | column_names |
v_items | items | item_id,art_label,dept,avg_week_sales,sales_price |
desc
view
v_item_tags;
view_name | table_name | column_names |
v_item_tags | item_tags | item_id,tag |
desc
view
v_sales;
view_name | table_name | column_names |
v_sales | sales | item_id,customer_id,sales_date,sales_qty,line_id,packaging_id |
v_sales | items | item_id,art_label,dept,avg_week_sales,sales_price |
v_sales | customers | customer_id,customer_name |
desc
view
v_sales verbose
;
view_name | table_name | column_name | column_type | col_type_name |
v_sales | sales | item_id | text | t_item_id |
v_sales | sales | customer_id | text | t_customer_id |
v_sales | sales | sales_date | text | t_date |
v_sales | sales | sales_qty | number | sys#type#sales#sales_qty |
v_sales | sales | line_id | text | sys#type#sales#line_id |
v_sales | sales | packaging_id | text | t_item_id |
v_sales | items | item_id | text | t_item_id |
v_sales | items | art_label | text | sys#type#items#art_label |
v_sales | items | dept | text | t_dept_id |
v_sales | items | avg_week_sales | number | sys#type#items#avg_week_sales |
v_sales | items | sales_price | number | sys#type#items#sales_price |
v_sales | customers | customer_id | text | t_customer_id |
v_sales | customers | customer_name | text | sys#type#customers#customer_name |
upsert (merge table)
Insert into a merge table will upsert (update if PK already exits, insert otherwise)
select
* from
v_items;
item_id | art_label | dept | avg_week_sales | sales_price |
artA | the article A | dept #1 | 10 | 1.500 |
artB | the article B | dept #2 | 10 | 3.200 |
box1 | a box | packaging | 10 | 0 |
insert
into
items values
('artA'
,'### the article A ###'
,'dept #1'
,10,12.123);
refresh
dirty
view
;
select
* from
v_items;
item_id | art_label | dept | avg_week_sales | sales_price |
artA | ### the article A ### | dept #1 | 10 | 12.123 |
artB | the article B | dept #2 | 10 | 3.200 |
box1 | a box | packaging | 10 | 0 |
save
Save data on disk (persistence)
save
save
;
insert
into
sales values
('artA'
,'C2'
,'20191231'
,5,'ID07'
,'box1'
);
desc
table
callback
where
(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 3 | n | n | n |
bounce;
desc
table
callback
where
(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 3 | n | n | n |
insert
into
sales values
('artA'
,'C2'
,'20191231'
,5,'ID07'
,'box1'
);
save
;
bounce;
desc
table
callback
where
(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 3 | n | n | n |
insert
into
sales values
('artA'
,'C2'
,'20191231'
,5,'ID07_'
,'box1'
);
desc
table
callback
where
(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 3 | n | n | n |
select
count(*) from
v_sales;
desc
view
callback
where
(1,'v_sales'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
save
;
bounce;
desc
view
callback
where
(1,'v_sales'
)
;
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
refresh
dirty
view
;
desc
view
callback
where
(1,'v_sales'
)
;
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
select
count(*) from
v_sales
;
insert from file
insert
into
table_name select
* from
file(<'path/to/file'
|'file_name under CSV_FILES_DIR/table_name'
>)
desc
parameter verbose
callback
where
(1,'FILE_SEPARATOR'
);
param_name | current_value | comment | is_default | default_value |
FILE_SEPARATOR | , | one character, CSV file separator | y | , |
desc
parameter verbose
callback
where
(1,'HEADER'
);
param_name | current_value | comment | is_default | default_value |
HEADER | y | y/n, y: CSV files have a header line | y | y |
system '(echo '
'item_id,customer_id,sales_date,sales_qty,line_id'
'>/tmp/foo.csv)'
;
system '(echo '
'item_id_01,customer_id_01,sales_date_01,1,line_id_01'
'>>/tmp/foo.csv)'
;
insert
into
sales select
* from
file('/tmp/foo.csv'
);
desc
parameter verbose
callback
where
(1,'CSV_FILES_DIR'
);
param_name | current_value | comment | is_default | default_value |
CSV_FILES_DIR | ../STORAGE/CSV | string, CSV file location used in "insert from file" when file path does not start with / | y | ../STORAGE/CSV |
system 'mkdir -p ../STORAGE/CSV/sales'
;
system '(echo '
'item_id,customer_id,sales_date,sales_qty,line_id'
'>../STORAGE/CSV/sales/foo.csv)'
;
system '(echo '
'item_id_02,customer_id_03,sales_date_04,1,line_id_05'
'>>../STORAGE/CSV/sales/foo.csv)'
;
insert
into
sales select
* from
file('foo.csv'
);
desc
parameter verbose
callback
where
(1,'NEW_COLUMN_NULL_STRING'
);
param_name | current_value | comment | is_default | default_value |
NEW_COLUMN_NULL_STRING | NO_DATA | string, default value when a column is added to a table or when a column is not provided in a CSV file | y | NO_DATA |
system '(echo '
'sales_DATE,item_id,sales_qty_new,line_id'
'>/tmp/foo.csv)'
;
system '(echo '
'sales_date_03,item_id_03,3,line_id_03'
'>>/tmp/foo.csv)'
;
system '(echo '
'sales_date_03,item_id_04,4,line_id_04'
'>>/tmp/foo.csv)'
;
insert
into
sales select
* from
file('/tmp/foo.csv'
);
system '(echo '
'___art_label,dept,item_id'
'>/tmp/foo.csv)'
;
system '(echo '
'art_label_01,dept_01,item_id_01'
'>>/tmp/foo.csv)'
;
system '(echo '
'art_label_02,dept_02,artA'
'>>/tmp/foo.csv)'
;
insert
into
items select
* from
file('/tmp/foo.csv'
);
set
HEADER='n'
;
continue_on_error(63);
insert
into
items select
* from
file('/tmp/foo.csv'
);
reponse |
error 63 (continue): end of line found where non expected |
stop_on_error;
set
HEADER='y'
;
refresh
dirty
view
;
select
sales#item_id,sales#customer_id,sales_date,sales_qty,line_id from
v_sales callback
sort(5,'asc'
);
sales#item_id | sales#customer_id | sales_date | sales_qty | line_id |
artA | C1 | 20191231 | 5 | ID01 |
artB | C2 | 20200102 | 6 | ID02 |
artB | C1 | 20191231 | 4 | ID03 |
artB | C2 | 20200102 | 7 | ID04 |
artC | C1 | 20200102 | 8 | ID05 |
artA | C1 | 20191231 | 5 | ID06 |
artA | C2 | 20191231 | 5 | ID07 |
artA | C2 | 20191231 | 5 | ID07 |
artA | C2 | 20191231 | 5 | ID07_ |
item_id_01 | customer_id_01 | sales_date_01 | 1 | line_id_01 |
item_id_03 | NO_DATA | sales_date_03 | 0 | line_id_03 |
item_id_04 | NO_DATA | sales_date_03 | 0 | line_id_04 |
item_id_02 | customer_id_03 | sales_date_04 | 1 | line_id_05 |
select
* from
v_items callback
sort(1,'asc'
);
item_id | art_label | dept | avg_week_sales | sales_price |
artA | NO_DATA | dept_02 | 0 | 0 |
artB | the article B | dept #2 | 10 | 3.200 |
box1 | a box | packaging | 10 | 0 |
item_id_01 | NO_DATA | dept_01 | 0 | 0 |
desc
parameter verbose
callback
where
(1,'ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE'
);
param_name | current_value | comment | is_default | default_value |
ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE | n | y/n, y: ,"abc,def", in a csv file with , separator is interpreted as one value: abc,def | y | n |
system '(echo '
'art_label,dept,item_id'
'>/tmp/foo.csv)'
;
system '(echo '
'"art_label_03,art_label_03",dept_03,"item_id_03,item_id_03"'
'>>/tmp/foo.csv)'
;
continue_on_error(63);
insert
into
items select
* from
file('/tmp/foo.csv'
);
reponse |
error 63 (continue): end of line found where non expected |
stop_on_error;
set
escape_separator_inside_double_quote='y'
;
insert
into
items select
* from
file('/tmp/foo.csv'
);
set
file_load_without_buffer='n'
;
system '(echo '
'item_id,art_label,dept,avg_week_sales,sales_price'
'>/tmp/foo.csv)'
;
system '(echo '
'"item_id_04,item_id_04","art_label_04,art_label_04",dept_04,99,12.123'
'>>/tmp/foo.csv)'
;
insert
into
items select
* from
file('/tmp/foo.csv'
);
refresh
dirty
view
;
select
sales#item_id,sales#customer_id,sales_date,sales_qty,line_id from
v_sales;
sales#item_id | sales#customer_id | sales_date | sales_qty | line_id |
artA | C1 | 20191231 | 5 | ID01 |
artB | C2 | 20200102 | 6 | ID02 |
artB | C1 | 20191231 | 4 | ID03 |
artB | C2 | 20200102 | 7 | ID04 |
artC | C1 | 20200102 | 8 | ID05 |
artA | C1 | 20191231 | 5 | ID06 |
artA | C2 | 20191231 | 5 | ID07 |
artA | C2 | 20191231 | 5 | ID07 |
artA | C2 | 20191231 | 5 | ID07_ |
item_id_01 | customer_id_01 | sales_date_01 | 1 | line_id_01 |
item_id_02 | customer_id_03 | sales_date_04 | 1 | line_id_05 |
item_id_03 | NO_DATA | sales_date_03 | 0 | line_id_03 |
item_id_04 | NO_DATA | sales_date_03 | 0 | line_id_04 |
select
* from
v_items;
item_id | art_label | dept | avg_week_sales | sales_price |
artA | NO_DATA | dept_02 | 0 | 0 |
artB | the article B | dept #2 | 10 | 3.200 |
box1 | a box | packaging | 10 | 0 |
item_id_01 | NO_DATA | dept_01 | 0 | 0 |
item_id_03,item_id_03 | art_label_03,art_label_03 | dept_03 | 0 | 0 |
item_id_04,item_id_04 | art_label_04,art_label_04 | dept_04 | 99 | 12.123 |
bounce;
delete
select
customer_id, count(*) from
v_sales group by
customer_id;
customer_id | count(*) |
C1 | 4 |
C2 | 3 |
desc
table
callback
where
(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 3 | n | n | n |
delete
sales where
customer_id='C1'
;
desc
table
callback
where
(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 3 | y | n | n |
refresh
dirty
view
;
select
customer_id, count(*) from
v_sales group by
customer_id;
desc
table
callback
where
(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 1 | n | n | n |
delete
sales where
customer_id='C1'
;
desc
table
callback
where
(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 1 | n | n | n |
bounce;
delete
sales where
customer_id='C1'
;
bounce;
refresh
dirty
view
;
select
customer_id, count(*) from
v_sales group by
customer_id;
customer_id | count(*) |
C1 | 4 |
C2 | 5 |
delete
sales where
customer_id='C1'
;
refresh
dirty
table
;
desc
view
callback
where
(1,'v_sales'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 3 | y | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
continue_on_error(207);
select
customer_id, count(*) from
v_sales group by
customer_id;
customer_id | count(*) |
C1 | 4 |
C2 | 5 |
desc
table
callback
like(1,'sales'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 1 | n | n | n |
sales_#partition#_00001 | 6 | 1 | n | n | n |
sales_#partition#_00002 | 6 | 3 | n | n | n |
save
;
bounce;
refresh
dirty
view
;
select
customer_id, count(*) from
v_sales group by
customer_id;
refresh_online
Unlike refresh, refresh_online won't generate downtime
refresh_online
dirty
view
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID11'
,'box1'
);
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID11'
,'box1'
);
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 1 | n | n | n |
sales_#partition#_00001 | 6 | 1 | n | n | n |
sales_#partition#_00002 | 6 | 3 | n | n | n |
sales_#partition#_00003 | 6 | 2 | n | n | n |
refresh_online
dirty
view
;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 0 | y | y | y | 10 | 0 | 0 | 0 | 1 | 1 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
select
count(*) from
v_sales;
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID11'
,'box1'
);
refresh_online
dirty
view
;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 0 | y | y | y | 10 | 0 | 0 | 0 | 2 | 2 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
select
count(*) from
v_sales;
refresh
dirty
view
;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
select
count(*) from
v_sales;
refresh_force
dirty
view
;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
select
count(*) from
v_sales;
partitions
Size of table is limited to 4G lines, hence need for partition
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 1 | n | n | n |
sales_#partition#_00001 | 6 | 1 | n | n | n |
sales_#partition#_00002 | 6 | 3 | n | n | n |
sales_#partition#_00003 | 6 | 3 | n | n | n |
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID11'
,'box1'
);
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 1 | n | n | n |
sales_#partition#_00001 | 6 | 1 | n | n | n |
sales_#partition#_00002 | 6 | 3 | n | n | n |
sales_#partition#_00003 | 6 | 3 | n | n | n |
sales_#partition#_00004 | 6 | 1 | n | n | n |
set
partition_line_count=1;
desc
parameter callback
where
(1,'PARTITION_LINE_COUNT'
);
param_name | current_value | comment | is_default | default_value |
PARTITION_LINE_COUNT | 1 | integer, maximum #line in a table partition (SB will created a new partition during insert if needed), 0 means no partition | n | 3000000000 |
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID11'
,'box1'
);
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 1 | n | n | n |
sales_#partition#_00001 | 6 | 1 | n | n | n |
sales_#partition#_00002 | 6 | 3 | n | n | n |
sales_#partition#_00003 | 6 | 3 | n | n | n |
sales_#partition#_00004 | 6 | 1 | n | n | n |
sales_#partition#_00005 | 6 | 1 | n | n | n |
refresh
dirty
view
;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00005 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
select
count(*) from
v_sales;
select
count(*) from
v_sales
callback
like(1,'partition'
);
bounce;
refresh
dirty
view
;
save
;
refresh_online & partitions
A partition can have its refresh_online views attached
select
count(*) from
v_sales;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID11'
,'box1'
);
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 0 | y | y | y | 10 | 0 | 0 | 0 | 0 | 0 |
refresh_online
dirty
view
;
select
count(*) from
v_sales;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 0 | y | y | y | 10 | 0 | 0 | 0 | 1 | 1 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID11'
,'box1'
);
set
PARTITION_LINE_COUNT=1;
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID11'
,'box1'
);
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 1 | n | n | n |
sales_#partition#_00001 | 6 | 1 | n | n | n |
sales_#partition#_00002 | 6 | 3 | n | n | n |
sales_#partition#_00003 | 6 | 2 | n | n | n |
sales_#partition#_00004 | 6 | 1 | n | n | n |
refresh_online
dirty
view
;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 0 | y | y | y | 10 | 0 | 0 | 0 | 2 | 2 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00002 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 0 | y | y | y | 10 | 0 | 0 | 0 | 1 | 1 |
v_sales_#partition#_00004_#refresh_online#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
select
count(*) from
v_sales;
select
count(*) from
v_sales callback
like(1,'running select'
);
refresh
dirty
view
;
select
count(*) from
v_sales;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00002 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00004_#refresh_online#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
refresh_force
dirty
view
;
select
count(*) from
v_sales;
desc
view
callback
like(1,'sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
refresh advanced
Applies deletes + updates + data model changes on tables and views)
refresh
dirty
table
Recompute view but not max_perf
refresh
view
view_name
Recompute permissions if changes and drop cache
refresh
permission
Recompute computed columns if changes and create a new version of stormbase.so under _SO_LINUX
refresh
computed_column
Drop cache
refresh
cache
Does all above and recomputes max_perf and re execute init file
refresh
dirty
view
deletes & refresh_online
Some big tables have a "delete before insert" logic along with refresh_online needs
In previous version of Stormbase such tables were defined as big_online, now all big tables support this functionality
Big_online keyword is kept for backward compatibility
refresh_force
dirty
view
;
select
list(line_id) from
v_sales;
list(line_id) |
ID02,ID04,ID07,ID07_,ID11 |
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 1 | n | n | n |
sales_#partition#_00001 | 6 | 1 | n | n | n |
sales_#partition#_00002 | 6 | 3 | n | n | n |
sales_#partition#_00003 | 6 | 2 | n | n | n |
sales_#partition#_00004 | 6 | 1 | n | n | n |
desc
view
callback
like(1,'v_sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID12'
,'box1'
)('artA'
,'C1'
,'20191231'
,5,'ID13'
,'box1'
);
delete
sales where
line_id='ID12'
;
continue_on_error(141);
refresh_online
dirty
view
;
reponse |
error 141 (continue): "refresh_online dirty views" is not allowed |
stop_on_error;
refresh_force
dirty
view
;
select
list(line_id) from
v_sales;
list(line_id) |
ID02,ID04,ID07,ID07_,ID11,ID13 |
desc
view
callback
like(1,'v_sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | n | n | n | 10 | 0 | 13 | 13 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00005 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
delete
sales where
regex(item_id,'.*'
);
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID14'
,'box1'
);
refresh_online
dirty
view
;
select
list(line_id) from
v_sales;
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 0 | n | n | y |
sales_#partition#_00001 | 6 | 0 | n | n | y |
sales_#partition#_00002 | 6 | 0 | n | n | y |
sales_#partition#_00003 | 6 | 0 | n | n | y |
sales_#partition#_00004 | 6 | 0 | n | n | y |
sales_#partition#_00005 | 6 | 1 | n | n | y |
desc
view
callback
like(1,'v_sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 1 | 0 |
v_sales_#partition#_00001 | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 1 | 0 |
v_sales_#partition#_00001_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | y | n | n | 10 | 0 | 13 | 13 | 1 | 0 |
v_sales_#partition#_00002_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 2 | y | n | n | 10 | 0 | 12 | 12 | 1 | 0 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 1 | 0 |
v_sales_#partition#_00004_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00005 | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 1 | 1 |
v_sales_#partition#_00005_#refresh_online#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
delete
sales where
regex(item_id,'.*'
);
insert
into
sales values
('artA'
,'C1'
,'20191231'
,5,'ID15'
,'box1'
)('artA'
,'C1'
,'20191231'
,5,'ID16'
,'box1'
);
refresh_online
dirty
view
;
select
list(line_id) from
v_sales;
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 0 | n | n | y |
sales_#partition#_00001 | 6 | 0 | n | n | y |
sales_#partition#_00002 | 6 | 0 | n | n | y |
sales_#partition#_00003 | 6 | 0 | n | n | y |
sales_#partition#_00004 | 6 | 0 | n | n | y |
sales_#partition#_00005 | 6 | 2 | n | n | y |
desc
view
callback
like(1,'v_sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 2 | 0 |
v_sales_#partition#_00001 | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 2 | 0 |
v_sales_#partition#_00001_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00001_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | y | n | n | 10 | 0 | 13 | 13 | 2 | 0 |
v_sales_#partition#_00002_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00002_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 2 | y | n | n | 10 | 0 | 12 | 12 | 2 | 0 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 2 | 0 |
v_sales_#partition#_00004_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00004_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00005 | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 2 | 2 |
v_sales_#partition#_00005_#refresh_online#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00005_#refresh_online#_00002 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
save
;
bounce;
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 0 | n | n | y |
sales_#partition#_00001 | 6 | 0 | n | n | y |
sales_#partition#_00002 | 6 | 0 | n | n | y |
sales_#partition#_00003 | 6 | 0 | n | n | y |
sales_#partition#_00004 | 6 | 0 | n | n | y |
sales_#partition#_00005 | 6 | 2 | n | n | y |
desc
view
callback
like(1,'v_sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 2 | 0 |
v_sales_#partition#_00001 | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 2 | 0 |
v_sales_#partition#_00001_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00001_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | y | n | n | 10 | 0 | 13 | 13 | 2 | 0 |
v_sales_#partition#_00002_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00002_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 2 | y | n | n | 10 | 0 | 12 | 12 | 2 | 0 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 2 | 0 |
v_sales_#partition#_00004_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00004_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00005 | 3 | 1 | y | n | n | 10 | 0 | 11 | 11 | 2 | 2 |
v_sales_#partition#_00005_#refresh_online#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00005_#refresh_online#_00002 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
select
list(line_id) from
v_sales;
save
;
system './clear_max_perf.sh sure'
;
reponse |
sure mode, no confirmation prompt |
clearing directory ../STORAGE/MAX_PERF... |
done |
|
|
bounce;
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 0 | n | n | y |
sales_#partition#_00001 | 6 | 0 | n | n | y |
sales_#partition#_00002 | 6 | 0 | n | n | y |
sales_#partition#_00003 | 6 | 0 | n | n | y |
sales_#partition#_00004 | 6 | 0 | n | n | y |
sales_#partition#_00005 | 6 | 2 | n | n | y |
desc
view
callback
like(1,'v_sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 1 | y | y | y | 10 | 0 | 0 | 0 | 2 | 0 |
v_sales_#partition#_00001 | 3 | 1 | y | y | y | 10 | 0 | 0 | 0 | 2 | 0 |
v_sales_#partition#_00001_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00001_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 3 | y | y | y | 10 | 0 | 0 | 0 | 2 | 0 |
v_sales_#partition#_00002_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00002_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 2 | y | y | y | 10 | 0 | 0 | 0 | 2 | 0 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 1 | y | y | y | 10 | 0 | 0 | 0 | 2 | 0 |
v_sales_#partition#_00004_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00004_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00005 | 3 | 1 | y | y | y | 10 | 0 | 0 | 0 | 2 | 2 |
v_sales_#partition#_00005_#refresh_online#_00001 | 3 | 1 | n | n | n | 10 | 0 | 11 | 11 | 0 | 0 |
v_sales_#partition#_00005_#refresh_online#_00002 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#refresh_online#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#refresh_online#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
select
list(line_id) from
v_sales;
refresh_force
dirty
view
;
desc
table
callback
like(1,'sales'
) sort(1,'asc'
);
table_name | column_count | line_count | has_delete | has_update | parent_view_hidden |
sales | 6 | 0 | n | n | n |
sales_#partition#_00001 | 6 | 0 | n | n | n |
sales_#partition#_00002 | 6 | 0 | n | n | n |
sales_#partition#_00003 | 6 | 0 | n | n | n |
sales_#partition#_00004 | 6 | 0 | n | n | n |
sales_#partition#_00005 | 6 | 2 | n | n | n |
desc
view
callback
like(1,'v_sales'
) sort(1,'asc'
);
view_name | table_count | rel_count | is_dirty | is_mp_dirty | is_partial | first_iline | mp_rare_count | mp_often_count | mp_count | r_online_count | mp_r_online_count |
v_sales | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 2 | 0 |
v_sales_#partition#_00001 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 2 | 0 |
v_sales_#partition#_00001_#refresh_online#_00001 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00001_#refresh_online#_00002 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00002 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 2 | 0 |
v_sales_#partition#_00002_#refresh_online#_00001 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00002_#refresh_online#_00002 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00003 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 2 | 0 |
v_sales_#partition#_00003_#refresh_online#_00001 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00003_#refresh_online#_00002 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00004 | 3 | 0 | n | n | n | 10 | 0 | 10 | 10 | 2 | 0 |
v_sales_#partition#_00004_#refresh_online#_00001 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00004_#refresh_online#_00002 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#partition#_00005 | 3 | 2 | n | n | n | 10 | 0 | 12 | 12 | 0 | 0 |
v_sales_#refresh_online#_00001 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
v_sales_#refresh_online#_00002 | 3 | 0 | y | n | n | 10 | 0 | 10 | 10 | 0 | 0 |
select
list(line_id) from
v_sales;
save
;
shutdown
;