Additional features of Attribute Clustering – Interleaved Ordering
Posted: Mon Jan 27, 2025 4:50 am
Oracle's attribute clustering offers further possibilities beyond the basic variant just described. The standard case, which can also be mapped using clustered indexes , index organized tables (IOTs) , Oracle Cluster structures / ORDER BY clauses, provides, as just explained, a primary criterion (a column of the table) according to which the data is organized. This means that you have to decide on a primary criterion during the design. Only access via this primary criterion is optimally supported by storing the data contiguously according to this criterion. With Oracle IOTs, this can be just the leading part or the entire primary key.
The attribute clustering for heap tables also supports a so-called interleaved order , for example . Here, within certain limits, you can even specify several independent algeria telegram screening criteria according to which the data should be organized. Oracle makes this possible internally using a so-called "Z-ordering" algorithm. If these different criteria do not have too many different characteristics, it works quite well. Access per criterion may not be quite as optimal as with the "linear order", where there is only one primary criterion, but I can optimize access to the table for several independent access paths. This option is not available with the other, alternative storage forms.
This does not work in the previous example - there are too many occurrences of both TICKER_CODE and TRADE_DATE (1000 times 1000) in the STOCK_HISTORY table , or the combination of TICKER_CODE and TRADE_DATE is unique. You cannot therefore optimize for access via TICKER_CODE and TRADE_DATE independently of each other. This is only possible with attribute clustering if the number of combinations of the values of the different attributes is significantly lower than the number of rows in the table.
So here's another example where this can work. Here we have a fact table and two dimension tables, so a classic star schema.
The attribute clustering for heap tables also supports a so-called interleaved order , for example . Here, within certain limits, you can even specify several independent algeria telegram screening criteria according to which the data should be organized. Oracle makes this possible internally using a so-called "Z-ordering" algorithm. If these different criteria do not have too many different characteristics, it works quite well. Access per criterion may not be quite as optimal as with the "linear order", where there is only one primary criterion, but I can optimize access to the table for several independent access paths. This option is not available with the other, alternative storage forms.
This does not work in the previous example - there are too many occurrences of both TICKER_CODE and TRADE_DATE (1000 times 1000) in the STOCK_HISTORY table , or the combination of TICKER_CODE and TRADE_DATE is unique. You cannot therefore optimize for access via TICKER_CODE and TRADE_DATE independently of each other. This is only possible with attribute clustering if the number of combinations of the values of the different attributes is significantly lower than the number of rows in the table.
So here's another example where this can work. Here we have a fact table and two dimension tables, so a classic star schema.