How to replace rownum in oracle

In addition to Oracle's ROWNUM pseudo-column, you can use the following alternatives to obtain the sequence number of the row: 1. Subquery (using nested subquery); 2. Analytical function (such as ROW_NUMBER()); 3 . Sequence (for newly inserted rows); 4. Temporary table (by creating a temporary table to store row numbers).

Use other methods to replace ROWNUM in Oracle

In Oracle, the ROWNUM pseudo column is used in the result set Get the sequence number of the row. However, there may be limitations or performance issues using ROWNUM in some cases. So you can consider the following alternatives:

1. Subquery

Use a nested subquery to add row numbers from the main query:

<code class="sql">SELECT * FROM (
  SELECT
    row_number() OVER (ORDER BY <sort_column>) AS rownum,
    <columns>
  FROM <table_name>
) AS subquery;</code>

2. Analysis function

Use ROW_NUMBER() analysis function to add row numbers:

<code class="sql">SELECT
  ROW_NUMBER() OVER (ORDER BY <sort_column>) AS rownum,
  <columns>
FROM <table_name>;</code>

3. Sequence

For newly inserted rows, you can use a sequence to generate a unique row number:

<code class="sql">CREATE SEQUENCE rownum_seq START WITH 1;

INSERT INTO <table_name> (id, ..., rownum) VALUES (..., NEXT VALUE FOR rownum_seq);

SELECT
  rownum,
  <columns>
FROM <table_name>;</code>

4. Temporary table

Create a temporary table to store the row number:

<code class="sql">CREATE TEMPORARY TABLE rownum_temp (
  rownum NUMBER,
  <columns>
);

INSERT INTO rownum_temp (rownum, <columns>)
SELECT row_number() OVER (ORDER BY <sort_column>), <columns>
FROM <table_name>;

SELECT
  rownum,
  <columns>
FROM rownum_temp
ORDER BY rownum;</code>

Choose the appropriate approach

Choosing the appropriate alternative depends on your specific requirements and performance considerations. Subqueries and analytic functions are effective for relatively small data sets. For situations where row numbers need to be generated on insert, sequences and temporary tables are better choices.

The above is the detailed content of How to replace rownum in oracle. For more information, please follow other related articles on the PHP Chinese website!