All geek questions in one place

How is column database optimization different from relational database optimization?

I have the following database structure stored in a relational database:

  • Two fact tables with ~ 80 million rows each
  • Three dimension tables containing 300,000 to 500,000 rows
  • Both fact tables have 3 foreign keys that are used to connect to dimension tables
  • One security table also has 3 foreign keys that are used to connect to the dimension tables.

The developer uses my data to create an application that uses a columnar database. They had performance problems, and when I suggested adding indexes / keys to their tables, they said that indexing a columnar database does not improve performance. As a result, they ask me to combine fact tables with dimension tables.

This seems to contradict what I know about the basic principles of database management. Is it true that columnar databases cannot use indexes to improve performance? What steps should be taken to optimize column performance?

I am looking for high-level information , but for the sake of completeness, the Teradata relational database, and the columnar database is SAP HANA.

+4
3

, . DB , .

: : 4444 98210 jones 1234 10125

: , 4444, 98210 , 1234, 10125 DB : , jones 4444, 1234 98210, 10125

. HANA UNIQUE, BTREE, CPBTREE. - , BTree - , CPBTREE - B + tree index.

, , . , , . " , ", , . , , . . , , , , .

- .

+2

SAP HANA, Columnstore . .

, . , , , (, , ).

, ( ) , , - .

..: col_fk_to_dim = [1,1,1,1,1,2,2,2,3,3,3,3,3,3,4,5,5,5,5,5,5... ]

[1x5, 2x3, 3x6, 4x1,5x5,...]

, , , , node .

, , , , . , , , .

.

0

, SAP HANA, . , .

, , " ", . SAP HANA ( Star Join) FACT-DIMENSION. , .

, PlanViz. , .

How high-level statements can take you here. For anything other than this, you will need to see the specified information and the corresponding request.

0
source

Source: https://habr.com/ru/post/1682694/

More articles:


All Articles