Posts

Oracle 21c — Using Result Cache in Standby

By -
  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...
Post a Comment
Read more

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

By -
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...
Post a Comment
Read more

Installing Oracle AI Database 26ai Free on Oracle Linux 9

By -
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...
Post a Comment
Read more