ADD INDEX

ADD Index is the command that allows users to add an index on FACT Tables.

Syntax

ALTER TABLE <table> ADD [<Main>] INDEX [<Index_Name>] (<key>,<Agregate>)[INNER | LEFT JOIN myschema.TableY ON (Join conditions)]

<table>

The table you want to add an index on.

<Main> (optional)

The MAIN option acts as if all columns are duplicates. This means, all rows and columns are stored in the Indexima K-store.

<Index_Name> (optional)

Users can define an index name. When a user doesn't define an index name, the default name is <table><#>  where  <#> is the next available index number (starting at 0).

<Index Definition>

The index can contain:

• any column of the altered table.
• any formula ( using build-in functions) based on one or several column names.
• any basic aggregations: Sum, Min, Max, Avg, Count.

ALTER TABLE myschema.my_table ADD INDEX ( Col1, Year(Col2), Sum(Col3));

PreJoin index: Index using an extra table (with a JOIN)

An index can also contain one or more joins. 

The index can contain:

• any column of the altered table.
• any column of the joined tables.
• any formula ( using build-in functions) based on one or more column names of the altered table or the joined table.
  
• any basic aggregate: Sum, Min, Max, Avg, Count.
• Join can only be LEFT or INNER.

ALTER TABLE myschema.mytable ADD Index (mytable.col1, join_table1.colN, Sum(mytable.col2))
JOIN join_table1
     on (mytable.Col3= join_table1.Col3bis)
;

Output

This SQL command does not return anything.