Oracle Database 20c listing of ten new features
On February 14, 2020 , Oracle officially announced the release of a preview version of Database 20c in Oracle Cloud, as well as all official documents.
I hope this will help to know the impressive new features of Oracle 20c.
1. Native Blockchain Support-Native Blockchain Tables
With the continuous maturity and development of blockchain technology, Oracle has introduced native blockchain table support in its multi-modal database support.
In the database in 20c, the blockchain keyword can be used to create a blockchain table:
CREATE Blockchain TABLE < blockchain_table_name > ;
The blockchain table allows customers to manage data that requires a high degree of tamper resistance without distributing ledgers across multiple organizations Or use Oracle database when relying on a decentralized trust model.
The rows in the blockchain table are tamper-proof. Each row contains a password hash value, which is based on the data in that row and the hash value of the previous row in the chain. If a row is tampered with, the hash value of that row will change, which will cause the hash value of the next row in the chain to change. To enhance fraud protection, an optional user signature can be added to the line. If you sign on the blockchain table line, you must use a digital certificate. When verifying the chain in the blockchain table, the database needs a certificate to verify the row signature.
Note: In order to follow the trust and security mechanism of the blockchain, the blockchain table can only be INSERT records, and cannot be modified, and the chain can be verified by multiple parties.
The following figure is a schematic diagram of the blockchain table:
Blockchain tables can be indexed and partitioned. You can control whether and when to delete rows from the blockchain table through the options when creating the table. You can also control whether the blockchain table can be deleted. Blockchain tables can be used with (regular) tables in transactions and queries.
The blockchain table is used to implement centralized blockchain applications, where the central authority is the Oracle database. Centralized blockchain provides organizations with greater customization and control because they can decide who can join the network. Participants are different database users, and they trust the Oracle database to maintain a tamper-proof blockchain for transactions. All participants must have the privilege to insert data into the blockchain table. The content of the blockchain is defined and managed by the application. Compared with decentralized blockchains, centralized blockchains are preferred to be used in scenarios with higher throughput and lower transaction latency than consensus-based distributed blockchains.
This crucial capability way that other customers can believe that the information held inside the blockchain desk is an accurate report of events. Oracle Database 20c permits you to run a manner that will verify all of the records are constant with their hash signature.
Blockchain tables can be associated with other regular tables for transaction processing or query.
For the blockchain table, the following operations are prohibited:
o Updating and merging rows
o Adding, dropping, and renaming columns
o Truncating the blockchain table
o Dropping partitions
o Defining BEFORE ROW triggers that fire for update operations (other triggers are allowed)
o Direct-path loading
o Inserting data using parallel DML
o Converting a regular table to a blockchain table or vice versa
o XA transactions
2. Persistent memory store support-Persistent Memory Store
Since Oracle 19c, Oracle has begun to modify programs to better match persistent memory and improve database performance.
In 20c, Oracle clearly supports persistent memory-Persistent Memory. Although the information currently released is supported in Exadata, the software upgrade is adopted. Persistent memory is used in various all-in-one machines or traditional architectures. There is no obstacle.
The introduction of persistent memory has allowed Oracle to store up to six levels: SATA, SAS, SSD, Flash, PMEM, RAM, cold and hot data separation, and tiered storage, allowing for more refined architecture design. The overall architecture provides IO latency of fewer than 19 microseconds.
In Oracle’s newly released Exadata X8M all-in-one machine, 100Gb Ethernet, and RoCE are supported. This is the first time that a RoCE-based architecture has been introduced in the Oracle all-in-one machine;
On the storage server, PMEM accelerates before Flash, RoCE and PMEM provide extremely fast performance;
3.SQL Macro Support-SQL Macro
The role of macros is to allow SQL to obtain further generalization and abstraction capabilities, allowing developers to define complex processing logic through macros, which can then be referenced repeatedly in subsequent program processing.
The SQL Macro introduced in 20c supports two macro types, Scalar and Table types.
SCALR expressions can be used in SELECT lists, WHERE/HAVING, GROUP BY/ORDER BY clauses;
TABLE expressions can be used in FROM statements.
Now, we can demonstrate this feature a little bit. Looking at a simple example, for example, a function is needed to return the lower and upper limits of the data. For example, if x <lower (specific small value), then return lower (as the lower limit); if x> upper (specific large value), then return upper (as the upper limit), otherwise return x itself.
For example, create a Clipin function as follows and return a SCALAR value:
create function clipin(lo number, x number, hi number) return varchar2 SQL_MACRO(SCALAR) is
begin
return ‘least(greatest(x, lo), hi)’;
end;
Then you can refer to this function as follows:
SELECT ename,CLIPIN (:lower, sal, :upper) FROM employee;
Macro function was rewritten to execute as follows:
SELECT ename, least(greatest(sal, :lower), :upper) FROM employee;
The results returned by this query are as follows, you can see the effect of CLIPIN:
SQL> SELECT ename,clipin(1000,sal,2000) FROM employee;
ENAME CLIPIN(1000,SAL,2000)
— — — — — — — — — — — — — — -
Sachin 1000
Ravi 1600
Aniket 1250
Shweta 2000
karan 1250
BOB 2000
RISHI 2000
KAVITA 2000
RITESH 2000
RJ 1500
MANISH 1100
RAJ 1000
AJAY 2000
PRIYA 1300
14 rows selected.
Summarize the meaning of SQL Macro:
• SQL Macro provides a simple SQL-based framework for encapsulating business/technical logic
-No need to call custom PL/SQL procedures and functions in queries
• Can be used anywhere in the SQL statement;
• Automatically inherit all commonly used query optimization in the database;
• Can establish a parameterized view;
• Simplified migration from the non-Oracle database to Oracle database;
4.SQL new features and function extensions-Extensions
In Oracle 20c, there are many SQL function extensions, including partial support for the ANSI 2011 standard, which further improves the processing power of SQL.
In the analysis and calculation, 20c provides two new distribution clustering algorithms, skew-SKEWNESS, kurtosis-KURTOSIS, through these two algorithms, you can perform a richer distribution calculation on the given data, new features support materialized view, Following the same semantics as VARIANCE.
In 20c, Oracle also added the CHECKSUM function to check the integrity of the data. This function can be used to replace the DBMS_SQLHASH.GETHASH function and the DBA does not need to authorize it separately.
Regarding the CHECKSUM function, the simple form is as follows, you can think about its useful scenarios:
SQL> select checksum(all(ename)) from employee;
CHECKSUM(ALL(ENAME))
— — — — — — — — — —
60288
SQL> update employee set ename=’EYGLE’ where ename=’FORD’;
1 row updated.
SQL> select checksum(all(ename)) from employee;
CHECKSUM(ALL(ENAME))
— — — — — — — — — —
401727
SQL> rollback;
Rollback complete.
SQL> select checksum(all(ename)) from employee;
CHECKSUM(ALL(ENAME))
— — — — — — — — — —
60288
New bit operators have also been introduced. The new bit operations supported in 20c include BIT_AND_AGG, BIT_OR_AGG, BIT_XOR_AGG.
For the analysis function, Oracle 20c extends the window boundary and can count the specific grouped data through the GROUPS keyword.
The keyword GROUPS employeehasizes the relationship with group queries. Using the GROUPS keyword, we can answer questions such as the amount spent and the number of different stock codes purchased during the last five trading days of each transaction account performing a “buy”.
5. Automated In-Memory Management-Self-Managing In-Memory
After the introduction of In-Memory technology, it brings in-memory column-based storage capabilities to the Oracle database and supports mixed computing of OLTP and OLAP.
In 20c, Oracle supports autonomous In-Memory management. Through a simple initialization parameter inmemory_automatic_level setting, the DBA will no longer need to manually specify which data tables are placed in memory, and the database will automatically determine which objects need to be added or evicted. In-memory column storage.
The management of memory objects is automatically realized by the machine learning algorithm built into the database, and the database can further automatically compress the less frequently accessed memory column data.
inmemory_automatic_level = HIGH setting, can be used to specify the height of the automatic memory management level.
6. Extensive machine learning algorithms and AutoML support
In Oracle 20c, more machine learning algorithms have been added to implement wider machine learning algorithm support.
Extreme gradient boosting tree-eXtreme Gradient Boosting Trees (XGBoost) database implementation, and various algorithms, such as classification, regression, ranking, survival analysis (survial analysitic), etc.;
MSET-SPRT supports abnormal detection of sensors and Internet of Things data sources, etc., non-linear, non-parametric abnormal detection ML technology;
In addition, Oracle machine learning algorithms support various languages, such as OML4SQL, OML4Py, OML4R, of which AutoML provides full support for Python.
7. Multi-tenant fine-grained resource model-New Resource Modeling Scheme
Before 20c, the multi-tenant database management was service-driven, and the PDB resource placement was determined by the service. The opening of the PDB was also implicitly driven by the service.
In a cluster environment, there is a problem. The PDB may be placed on a server with tight resources. The service-driven model is not perfect.
In 20c, Oracle introduced a fine-grained resource model, introducing the importance of load and PDB into a management perspective. For example, users can change the priority of PDB through the definition of Cardinality and Rank. When the database is started, PDB with higher priority is opened first.
In addition, before the PDB is opened, the database will check the host’s operating load, availability, number of CPUs and CPU speed and other information to scientifically determine which node and in which order the PDB should be started.
Another change regarding multi-tenancy is that in 20c, the Non-CDB mode will no longer be supported, which will force users to use the multi-tenant feature.
In the authorization file of Oracle 20c, there is a description: You can use a multi-tenant environment of 3 PDBs without a license, and more than 3 PDBs require additional authorization.
“For all offerings, if you are not licensed for Oracle Multitenant, then you may have up to 3 user-created PDBs in a given container database at any time.”
8. Zero Downtime for Planned Outages-Zero Downtime for Planned Outages
In the continuous evolution of different versions of Oracle, the availability of the database has been strengthened. In 20c, for planned downtime maintenance or rolling upgrades, Oracle uses features such as Smart DRM to achieve zero impact on applications.
For maintenance operations, the database can perform dynamic resource redistribution before the instance is closed. This feature is called Smart DRM. Through GRD’s dynamic resource reorganization, the re-selected Master node does not require any recovery and maintenance. The application is completely unaware and impactless.
9. Spatial and Text support of In-Memory
For the multi-model nature, geographic information-Spatial and full-text retrieval-Text components built into the Oracle database, in 20c, through the memory feature of In-Memory, further support was obtained.
For spatial data, Oracle adds spatial summary information to the spatial columns in memory (limited to in-memory, no external storage is required), through SIMD vector rapid filtering, replacement of R-Tree index and other means to speed up spatial data query retrieval, you can query Speed increased by 10 times.
For full-text search (Text), an inverted index is added to each text column in memory, and the original disk index is replaced with memory by mapping words to documents containing words, thereby accelerating the performance of full-text search. By combining relational data and text mixed queries, full-text search can achieve a performance improvement of more than 3 times.
10. Result Cache support of standby library-Standby Result Cache
In Oracle 12.2 and 18c, ADG’s session connection retention and Buffer Cache retention have been implemented. In 20c, Result Cache is further retained on the standby database to ensure that the master and standby performance of this detail feature passes.
The Result Cache feature means that for a specific query (such as a result set that does not change), the query result is kept in memory, and for a statement that is repeatedly queried (especially large-scale aggregation), the cost is almost reduced to 0.
Reference Link:
1. https://docs.oracle.com/en/database/oracle/oracle-database/20/newft/oracle-blockchain-table.html
2. https://blogs.oracle.com/oracle-database-20c-preview-v2
Thanks for reading this post.
Pankaj K(www.pankajconnect.com)
