Unlocking the Power of EPAS 16: Exploring New Advanced Features with Examples

The release of EDB Postgres Advanced Server (EPAS) 16 brings a suite of exciting new features, each designed to enhance the functionality and efficiency of database operations. In this blog, we’ll explore five notable additions, providing working code examples and discussing the benefits of each.

1. FETCH cursor BULK COLLECT INTO

Feature: The FETCH cursor BULK COLLECT INTO feature allows for the efficient fetching of multiple rows into a collection in a single operation.

Example:

DECLARE
   TYPE EmpList IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER;
   emp_collection EmpList;
   CURSOR emp_cursor IS SELECT ROWID, * FROM employee;
BEGIN
   OPEN emp_cursor;
   FETCH emp_cursor BULK COLLECT INTO emp_collection LIMIT 3 ;
   CLOSE emp_cursor;

   -- Process the collection
   FOR i IN 1..emp_collection.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_collection(i).name);
   END LOOP;
END;

Employee Name: Employee_1
Employee Name: Employee_2
Employee Name: Employee_3

EDB-SPL Procedure successfully completed

Benefits:

1. Efficiency in Data Retrieval: Fetches large datasets quickly.
2. Reduced Server Load: Minimizes the number of round-trips between client and server.

2. NANVL, LNNVL, and DUMP

Features:

• NANVL: Replaces NaN (not-a-number) values with a specified value.
• LNNVL: Provides a logical negation of a condition, particularly useful with NULLs.
• DUMP: Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value.

Examples:

SELECT NANVL(comm, 0) FROM emp WHERE empno=7839;
 nanvl 
-------
     0
(1 row)

SELECT * FROM emp WHERE LNNVL(sal > 1000);
 empno | ename |  job  | mgr  |      hiredate      |  sal   | comm | deptno 
-------+-------+-------+------+--------------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 |      |     20
  7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 |      |     30
(2 rows)

SELECT DUMP('Hello, World!') FROM dual;
                             dump                              
---------------------------------------------------------------
 Typ=25 Len=13: 72,101,108,108,111,44,32,87,111,114,108,100,33
(1 row)

Benefits: These functions enhance data handling capabilities, especially in complex analytical queries. NANVL and LNNVL improve the handling of special or null values, while DUMP aids in debugging and understanding data representation.

3. Push Down Aggregate (Upper Relation) Operations

Feature: This optimization feature allows aggregate operations to be pushed down to a lower level in the query plan.

Example:

EXPLAIN ANALYZE SELECT SUM(id) FROM employee@xe_oracle;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..25.00 rows=1 width=32) (actual time=4.503..4.549 rows=1 loops=1)
   Relations: Aggregate on (_dblink_employee_2 employee)
   Remote Query: SELECT sum(id) FROM employee
 Planning Time: 3.580 ms
 Execution Time: 5.091 ms
(5 rows)

Benefits: The push-down optimization can lead to significant performance improvements by reducing the amount of data that needs to be processed and transferred within the database engine.

4. NLS_CHARSET Functions

NLS_CHARSET provides functions to determine the character set used by the database, crucial for handling multilingual data.

Example:

SELECT NLS_CHARSET_NAME(6);
 nls_charset_name 
------------------
 UTF8
(1 row)

SELECT NLS_CHARSET_ID('UTF8');
 nls_charset_id 
----------------
              6
(1 row)

5. Procedure Synonyms

Example:

CREATE OR REPLACE PROCEDURE my_procedure IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello from my procedure!');
END;
CREATE PROCEDURE

CREATE SYNONYM proc_synonym FOR my_procedure;
CREATE SYNONYM

BEGIN
   proc_synonym;
END;
Hello from my procedure!

EDB-SPL Procedure successfully completed

Benefits:

• Simplified Referencing: Easier to call procedures from different schemas.
• Improved Maintainability: Changes to procedure paths only need to be updated in the synonym.

Conclusion

These features showcase EDB’s powerful capabilities in handling complex data operations efficiently. By integrating these functionalities into your EDB database strategies, you can significantly enhance the performance and flexibility of your data management processes.