Fetch First Rows feature in Oracle

FETCH FIRST feature is known as the "ROWNUM" pseudocolumn or the "FETCH FIRST n ROWS ONLY" clause. It allows you to limit the number of rows returned in a query result. Here's how you can use it:

Using ROWNUM pseudocolumn:
SELECT *
FROM table_name
WHERE ROWNUM <= n;

In this example, replace table_name with the name of the table you're querying, and n with the desired number of rows you want to fetch.

Note: When using the ROWNUM pseudocolumn, it's essential to apply the condition on ROWNUM after any necessary ordering or filtering, as ROWNUM is assigned to rows before ordering is applied.

Using FETCH FIRST n ROWS ONLY clause (starting from Oracle 12c):
SELECT *
FROM table_name
ORDER BY column_name
FETCH FIRST n ROWS ONLY;

In this example, replace table_name with the name of the table you're querying, column_name with the column you want to order by, and n with the desired number of rows you want to fetch.

The FETCH FIRST clause allows you to specify an ORDER BY clause, ensuring consistent results when retrieving a limited number of rows.

Performance benefits of using the "FETCH FIRST":
Reduced Data Transfer: By limiting the number of rows fetched from the database, the "FETCH FIRST" feature reduces the amount of data transferred between the database server and the client application. This can significantly improve query performance, especially when dealing with large result sets.
 
Query Optimization: The "FETCH FIRST" feature allows the Oracle optimizer to optimize the query execution plan based on the specified row limit. It enables the optimizer to choose more efficient access paths, such as using an index scan or a fast full scan, instead of performing a full table scan when only a limited number of rows are required.
 
Faster Response Time: By fetching only the necessary rows, the "FETCH FIRST" feature can reduce the time it takes to process and return query results. This can lead to faster response times for queries, improving the overall user experience.
 
Efficient Pagination: When implementing pagination functionality, the "FETCH FIRST" feature can be particularly useful. It allows you to retrieve specific pages of data efficiently by combining the "FETCH FIRST" clause with appropriate offset values. This can enhance performance when displaying paginated results in applications.
 
Improved Scalability: By limiting the number of rows fetched, the "FETCH FIRST" feature helps in optimizing resource utilization. It reduces memory consumption and can improve the scalability of the application by efficiently handling large result sets.  
       

Microservice architecture Vs monolithic architecture

Microservice architecture and monolithic architecture are two different approaches to software development.

Monolithic architecture is a traditional approach where the entire application is built as a single, self-contained unit. All the functionalities of the application are tightly integrated and dependent on each other. This approach can be simple to develop and deploy, but it can become complex and difficult to maintain as the application grows and changes over time.

 

On the other hand, Microservice architecture is an approach where the application is broken down into smaller, independent services that can communicate with each other through APIs. Each microservice is responsible for a specific business capability and can be developed, deployed, and scaled independently. This approach provides flexibility, scalability, and allows for faster innovation and release cycles. However, it can also add complexity in terms of managing the communication and coordination between the microservices.

Main differences between microservice architecture and monolithic architecture are:

• Monolithic architecture is a single, self-contained unit while microservice architecture is composed of smaller, independent services.
• In monolithic architecture, all functionalities are tightly integrated and dependent on each other while in microservice architecture, each service is responsible for a specific business capability.
• Monolithic architecture can be simpler to develop and deploy but can become complex and difficult to maintain while microservice architecture provides flexibility, scalability, and faster innovation but can add complexity in terms of managing communication and coordination between services.

ORA-00979: not a GROUP BY expression

ORA-00979 error in Oracle usually occurs when you try to execute a SQL statement that includes a GROUP BY clause, but one or more non-aggregated columns in the SELECT clause are not included in the GROUP BY clause.

Here's an example that demonstrates the issue:

SELECT dept_name, employee_name, AVG(salary)
FROM employees
GROUP BY dept_name;

This query calculates the average salary for each department in the employees table, but it includes the non-aggregated employee_name column in the SELECT clause without including it in the GROUP BY clause. This will result in the ORA-00979 error, because employee_name is not included in the GROUP BY clause and it's not part of an aggregate function.

To fix this error, you need to include all non-aggregated columns in the SELECT clause in the GROUP BY clause as well. Here's the corrected query:

SELECT dept_name, employee_name, AVG(salary)
FROM employees
GROUP BY dept_name, employee_name;

In this query, we include the employee_name column in the GROUP BY clause along with the dept_name column. This ensures that all non-aggregated columns in the SELECT clause are included in the GROUP BY clause, and the AVG function can be calculated for each combination of dept_name and employee_name.

HANDLECOLLISIONS in GoldenGate

When data is replicated from a source database to a target database, conflicts can occur if the same data is modified in both databases simultaneously. For example, if a row is updated in the source database and the same row is also updated in the target database, a conflict arises."HANDLECOLLISIONS" parameter allows you to specify how these conflicts should be handled.

The "HANDLECOLLISIONS" parameter can take various values to determine the resolution strategy for conflicts. Some common values include:

ABORT: This value causes Oracle GoldenGate to abort the Replicat process when a conflict is encountered. This might be appropriate if you want to manually address conflicts before continuing replication.

SKIP: This value instructs Oracle GoldenGate to skip the conflicting transaction and continue replication with subsequent transactions.

OVERWRITE: This value allows the target transaction to overwrite the source transaction when a conflict occurs.

WARNING: This value generates a warning message and continues replication with one of the transactions.

DISCARD: This value discards the conflicting transaction without applying it to the target.

FAILONCOLLISION: This value causes Replicat to fail when a collision is encountered.

The appropriate value to use depends on your business logic and the importance of maintaining data integrity. You can set the "HANDLECOLLISIONS" parameter in your Oracle GoldenGate configuration file for the Replicat process. Here's an example:

HANDLECOLLISIONS OVERWRITE;