Posts

Showing posts from October, 2025

Oracle 21c — Using Result Cache in Standby

  Another new feature introduced in Oracle Database 21c is the ability to use the   RESULT_CACHE   hint in a   Physical Standby   environment. The following section explains how to use this feature. The execution time of the following query in the primary database is about one minute: –Primary: SQL > select count ( * ) from usef.tbl1; COUNT ( * ) ——— - 62775296 Elapsed: 00 : 01 : 06.45 SQL > / COUNT ( * ) ——— - 62775296 Elapsed: 00 : 01 : 03.61 By using the  RESULT_CACHE  hint, this time is reduced to less than one second! –Primary: SQL > select /*+ result_cache */ count ( * ) from usef.tbl1; COUNT ( * ) ——— - 62775296 Elapsed: 00 : 01 : 20.74 SQL > select /*+ result_cache */ count ( * ) from usef.tbl1; COUNT ( * ) ——— - 62775296 Elapsed: 00 : 00 : 00.00 Executing this query in the standby environment takes about 30 seconds (our standby database has better resources than the primary one!): –Physical Standby: –P...

Oracle Database 26ai: Filtering Analytic Function Results with the QUALIFY Clause

Image
  Suppose we want to display, from the  employees   table, only the employee with the highest salary in each department. Before version  26ai(23.26) , we usually had to use a subquery to achieve this result: SELECT * FROM ( SELECT employee_id, first_name, department_id, salary, RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS Rank_Per_DEP FROM employees ) WHERE Rank_Per_DEP = 1 ; In version  26ai , you can achieve the same result  without using a subquery , thanks to the  QUALIFY  clause: SELECT employee_id, first_name, department_id, salary, RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS Rank_Per_DEP FROM employees QUALIFY Rank_Per_DEP = 1 ; Therefore, the  QUALIFY  clause is used to filter the results of analytic functions. It eliminates the need for a subquery and improves both the  readability  an...

Installing Oracle AI Database 26ai Free on Oracle Linux 9

Image
  Oracle introduced   Oracle AI Database Free 26ai   just a few hours ago, the next generation of its AI-enabled database platform. In this guide, we’ll walk through the installation of this version on  Oracle Linux 9 . System Resource Limits Oracle AI Database Free 26ai  allows you to use up to: 2 CPU cores 2 GB of RAM 12 GB of user data storage This edition is designed for developers, learners, and small-scale testing environments. 1. Configure Hostname and Hosts File Set the hostname and update the  /etc/hosts  file: [ root@OL95 ~ ] # vi /etc/hosts 192.168 .1 .31 OL95DB [ root@OL95 ~ ] # vi /etc/hostname OL95DB 2. Prepare the Operating System Repository Before installing Oracle packages, prepare the OS for preinstallation RPM by mounting the Oracle Linux ISO and creating a local repository: [ root@OL95DB ~ ] # mkdir /dvd [ root@OL95DB ~ ] # mount /dev/sr0 /dvd mount: /dvd: WARNING: source write- protected , mounted read-only. [ root@OL95DB ~ ] # rm...