Last week, there were clients. HCC compression: In order to reduce the storage to the database bandwidth consumption, so that the storage of HCC compressed all data transferred to the node is decompressed, and when the use of HCC compression, Exadata Intelligent scanning is not available, all the data need to be processed in the data node, Consume all the CPU of the database node?
But the actual principle is not so, but the storage node still needs to consume CPU to decompress, and use intelligent scanning and storage index at the same time, the specific process is as follows
1. The Exadata store reads 1MB of storage at a time (usually HCC compression unit is 32K or 64K, equivalent to read 32 or 16 CU-compressed units at a time), at this stage can use the storage index for filtering
2. Exadata Storage to cache CU
3. Exadata Smart Scan to process CU
A) Unzip the SQL query to a field that is related to the Where condition, filter the qualified value in the field, discard the CU if there are no eligible ones, and if any, continue to unzip the remaining where condition related fields, repeating the process until all the query where condition fields have been processed, Get to all qualifying values (one field at a time)
b) Extract and filter The fields required for SQL required output based on query criteria values
c) Repackage the field of the eligible output field and the Where condition into a CU back to the database node
4. The database node unzip the received CU, proceed with processing, and return the final result to the client
By the above process, the Exadata platform needs to be decompressed on both the storage side and the database side, all of which need to consume the corresponding CPU.
How does SQL work with HCC compression tables on Exadata?