Implementing Virtual Columns- Tables and Constraints
A virtual column is based on one or more existing columns from the same table or a combination of constants, SQL functions, and user-defined PL/SQL functions or both. Virtual columns are not stored on disk; they are evaluated at runtime, when the SQL query executes. Virtual columns can be indexed and have stored statistics.
Note GENERATED ALWAYS is optional, but it helps to recognize the virtual column.To modify the virtual column value, you can modify the function or calculation as an ALTER TABLE statement.
The advantages of doing so are as follows:
• You can create an index on a virtual column; internally, Oracle creates a function-based index.
• You can store statistics in a virtual column that can be used by the cost-based optimizer (CBO).
• Virtual columns can be referenced in WHERE clauses.
• Virtual columns are permanently defined in the database; there is one central definition of such a column.
Implementing Invisible Columns
The main use for an invisible column is to ensure that adding a column to a table will not disrupt any of the existing application code. If the application code does not explicitly access the invisible column, then it appears to the application as if the column does not exist.
When a column is invisible, it cannot be viewed via the following:
• DESCRIBE
• SELECT * (to access all of the table’s columns)
• %ROWTYPE (in PL/SQL)
However, the column can be accessed if explicitly specified in a SELECT clause or referenced directly in a DML statement (INSERT, UPDATE, DELETE, or MERGE). Invisible columns can also be indexed just like visible columns.
A table can be created with invisible columns, or a column can be added or altered so as to be invisible. A column that is defined as invisible can also be altered so as to be visible. Here is an example of creating a table with an invisible column:
When you create a table that has invisible columns, at least one column must be visible.
Creating Blockchain Tables
Introduced in 21c, the blockchain tables are append-only tables. Inserts are allowed, but deletes are prohibited or restricted based on time. The blockchain table is made tamper-resistant by special sequencing and chaining algorithms.
All participants in the blockchain network have access to the same tamper-resistant ledger, and since it is a centralized ledger model in the database, it reduces overhead and is lower latency.
Blockchain is just added to the CREATE TABLE statement, and you need to specify additional attributes that determine when a blockchain table can be dropped if it is no longer in use and a hash can be used.
NO DROP until 31 days idle NO DELETE locked
HASHING USING
Information about blockchain tables is in the USER_BLOCKCHAIN_TABLES. You can grant permissions on the table, and even with delete permissions, you will get an error message if you attempt to execute a delete statement.
