EverSQL Database Performance Knowledge Base

Question 1

Oracle query with rownum and orderBy return wrong records

Database type:

I got an confusing result when working with rownum of Oracle10g.

select * from A where name like '%test' order by name asc

==> return 1 record

select * from (
    select * from A where name like '%test'
    order by name asc
 )
where rownum <= 2

==> return 2 records

If I remove 'order by' then it will return 1 record.

Any one can help me to explain this behavior?

Answer

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

Avoid LIKE Searches With Leading Wildcard (query line: 6): The database will not use an index when using like searches with a leading wildcard (e.g. '%test'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

CREATE INDEX a_idx_name ON A (name);

SELECT
        * 
    FROM
        A 
    WHERE
        A.name LIKE '%test' 
    ORDER BY
        A.name ASC

Question 2