How to use ORDERED Hint to Tune a SQL with subquery for Oracle?

Here the following is the description of the ORDERED hint.

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.

We usually use an ORDERED hint to control the john order, but how this hint causes a SQL with a subquery. Let’s use the following SQL as an example to see how ORDERED hint works for a subquery.

SELECT *
     FROM DEPARTMENT
where  dpt_id
     in (select emp_dept from employee
      where emp_id >3300000)

Here the following is the query plan of the SQL, it takes 68.84 seconds to finish. The query shows a “TABLE ACCESS FULL” of the DEPARTMENT table and “NESTED LOOPS SEMI” to an “INDEX RANGE SCAN” of EMPLOYEE.

If you think it is not an effective plan, you may want to try to reorder the join path and see if an ORDERED hint is working or not in a subquery case like this:

SELECT  /*+ ORDERED */ *
FROM  department
WHERE  dpt_id IN (SELECT  emp_dept
         FROM  employee
         WHERE  emp_id > 3300000)

Here is the query plan of the hinted SQL and the speed is 3.44 seconds which is 20 times better than the original SQL. The new query plan shows the new join order that EMPLOYEE is retrieve first and then hash join DEPARTMENT later. You can see the ORDERED hint will order the subquery’s table first. This new order clauses a new data retrieval method from the EMPLOYEE table, it makes the overall performance much better than the original query plan.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other hints-injection SQL with better performance, but it is not suitable to discuss in this short article, maybe I can discuss later in my blog.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

How to Tune SQL Statements with NO_RANGE_OPTIMIZATION Hints Injection?

There are some SQL statements with performance problem can be tuned by Hints injection only. Here is an example to show you how to use NO_RANGE_OPTIMIZATION optimization hints to tune a SQL statement.

A simple example SQL that retrieves data from EMPLOYEE and EMP_SAL_HIST tables.

select * from employee a,emp_sal_hist h
where  a.emp_id =h.sal_emp_id
and  a.emp_dept < ‘B’
and h.sal_salary  between 1000000 and 2000000

Here the following are the query plans of this SQL, it takes 24.3 seconds. The query shows an Index Range Scan (EMPS_DPT_INX) of EMPLOYEE and then Nested Loop to EMP_SAL_HIST with a Non-Unique Key Lookup of SALS_EMP_INX index.

The EMP_SAL_HIST is the employee’s salary history table which keeps more than one salary record for each employee. So, EMPLOYEE to EMP_SAL_HIST is a one-to-many relationship. The speed of a nested loop operation is highly dependent on the driving path of two nested loop tables. MySQL SQL optimizer estimated that the condition (a.emp_dept < ‘B’) can rapidly reduce the result set, so the driving path that “from EMPLOYEE to EMP_SAL_HIST” is selected.

Unless you fully understand the data distribution and do a very precise calculation, otherwise you are not able to tell whether this driving path is the best or not.

How to make MySQL consider another driving path “from EMP_SAL_HIST to EMPLOYEE”? Let’s take a look at MySQL documentation:

NO_RANGE_OPTIMIZATION: Disable index range access for the specified table or indexes. This hint also disables Index Merge and Loose Index Scan for the table or indexes. By default, range access is a candidate optimization strategy, so there is no hint for enabling it.

This hint may be useful when the number of ranges may be high and range optimization would require many resources.

To disable the Index Range Scan of the EMPLOYEE table, I explicitly add a Hints /*+ QB_NAME(QB1) NO_RANGE_OPTIMIZATION(`a`@QB1) */  to the SQL statement and hope that MySQL will use the Index Range Scan by the condition (h.sal_salary between 1000000 and 2000000) as the first driving table.

select  /*+ QB_NAME(QB1) NO_RANGE_OPTIMIZATION(`a`@QB1) */ *
from    employee a,
     emp_sal_hist h
where a.emp_id = h.sal_emp_id
     and a.emp_dept < ‘B’
     and h.sal_salary between 1000000 and 2000000

Here is the result query plan of the Hints injected SQL and the execution time is reduced to 10.01 seconds. The new query plan shows that the driving path is changed from EMP_SAL_HIST table nested loop to EMPLOYEE table. So, sometimes you may make use of the NO_RANGE_OPTIMIZATION hint to control the driving path order to see if MySQL can run your SQL faster.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the Hints injected SQL is more than 2 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

How to Tune SQL Statement with CASE Expression for SQL Server II?

oracle database performance tuning

We have discussed how to tune a CASE expression SQL with hardcoded literals in my last blog:

How to Tune SQL Statement with CASE Expression for SQL Server I?

SELECT *
FROM EMPLOYEE
 WHERE
 CASE
  when emp_id  < 1001000 then ‘Old Employee’ 
  when emp_dept <‘B’     then ‘Old Department’
 ELSE  ‘Normal’
 END =  ‘old Employee’

If I change the hardcoded literal to a @var, what will be the performance of the last blog’s rewritten SQL?

SELECT *
FROM EMPLOYEE
 WHERE
 CASE
  when emp_id  < 1001000 then ‘Old Employee’ 
  when emp_dept <‘B’     then ‘Old Department’
 ELSE  ‘Normal’
 END =  @var

I use the same method in my last blog to rewrite this SQL into the following multiple OR syntax, but the SQL Server optimizer change back to a full table scan of the EMPLOYEE table. It is because the SQL Server cannot do a good cardinality estimation of the variable of @var.

select *
from  EMPLOYEE
where emp_id < 1005000
     and ‘Old Employee’ = @var
     or not ( emp_id < 1005000 )
     and emp_dept < ‘B’
     and ‘Old Department’ = @var
     or not ( emp_id < 1005000 )
     and not ( emp_dept < ‘B’ )
     and ‘Normal’ = @var

We can rewrite the CASE expression into the following syntax with multiple UNION ALL statements, this syntax is more complicated than the rewrite with multiple OR conditions in my last blog. But it can make SQL Server improve the query plan to be more efficient.

select *
from  EMPLOYEE
where emp_id < (select 1005000)
     and ‘Old Employee’ = @var
union all
select *
from  EMPLOYEE
where ( not ( emp_id < 1005000 )
       and ‘Old Employee’ = @var
     or @var is null )
     and emp_id >= 1005000
     and emp_dept < ‘B’
     and ‘Old Department’ = @var
union all
select *
from  EMPLOYEE
where ( not ( emp_id < 1005000 )
       and ‘Old Employee’ = @var
     or @var is null )
     and ( not ( emp_id >= 1005000
         and emp_dept < ‘B’
         and ‘Old Department’ = @var )
       or @var is null )
     and emp_id >= 1005000
     and emp_dept >= ‘B’
     and ‘Normal’ = @var

Here is the query plan of the rewritten SQL and the speed is 0.448 seconds. It is 5 times better than the original syntax. People may think that there are two table scan operations of EMPLOYEE that will slow down the whole process, but actually, the corresponding filter operations will stop the table scan operations immediately due to the filter conditions ‘Normal’ = @var and ‘Old Department’ = @var will not be satisfied. This kind of query plan cannot be generated by SQL Server’s internal SQL optimizer, it means that you cannot use Hints injection to get this query plan.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for SQL Server automatically.

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

How to Tune SQL Statement with CASE Expression for SQL Server I?

oracle database performance tuning

Here the following is a simple SQL statement with a CASE expression syntax.

SELECT *
FROM EMPLOYEE
WHERE
CASE
when emp_id  < 1001000 then ‘Old Employee’
when emp_dept <‘B’     then ‘Old Department’
ELSE  ‘Normal’
END =  ‘old Employee’

Here the following are the query plans of this SQL, it takes 2.23 seconds in a cold cache situation, which means data will be cached during the SQL is executing. The query shows a Full Table Scan of the EMPLOYEE table due to the CASE expression cannot utilize the emp_id index or emp_dept index.

We can rewrite the CASE expression into the following syntax with multiple OR conditions.

select *
from  EMPLOYEE
where emp_id < 1005000
and ‘Old Employee’ = ‘Old Employee’
or not ( emp_id < 1005000 )
and emp_dept < ‘B’
and ‘Old Department’ = ‘Old Employee’
or not ( emp_id < 1005000 )
and not ( emp_dept < ‘B’ )
and ‘Normal’ = ‘Old Employee’

Here is the query plan of the rewritten SQL and the speed is 0.086 seconds. It is 25 times better than the original syntax. The new query plan shows an Index Seek of EMP_ID index.

This SQL rewrite is useful when the CASE expression is equal to a hardcoded literal, but if the literal “  =’Old Employee’ ” replaced by a variable “ = :var ”, this rewrite may not be useful, I will discuss it in my next blog.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for SQL Server automatically.

Expert (TSES™) for SQL Server® – Tosska Technologies Limited

How to tune a SQL that cannot be tuned ?

oracle sql performance tuning

Some mission-critical SQL statements are already reached their maximum speed within the current indexes configuration.  It means that those SQL statements are not able to be improved by syntax rewrite or Hints injection. Most people may think that the only way to improve this kind of SQL may be by upgrading hardware.  For example, the following SQL statement has every column in WHERE clause is indexed and the best query plan is generated by Oracle already. There is no syntax rewrite or hints injection that can help Oracle to improve the SQL performance.

SELECT EMP_ID,
    EMP_NAME,
    SAL_EFFECT_DATE,
    SAL_SALARY
  FROM EMPLOYEE,
    EMP_SAL_HIST,
    DEPARTMENT,
    GRADE
WHERE EMP_ID = SAL_EMP_ID
  AND SAL_SALARY <200000
  AND EMP_DEPT = DPT_ID
  AND EMP_GRADE = GRD_ID 
  AND GRD_ID<1200    AND EMP_DEPT<‘D’

Here the following is the query plan and execution statistics of the SQL, it takes 2.33 seconds to extract all 502 records. It is not acceptable for a mission-critical SQL that is executed thousands of times in an hour. Do we have another choice if we don’t want to buy extra hardware to improve this SQL?

Introduce new plans for Oracle’s SQL optimizer to consider
Although all columns in the WHERE clause are indexed, can we build some compound indexes to help Oracle’s SQL optimizer to generate new query plans which may perform better than the original plan? Let’s see if we adopt the common practice that the following EMPLOYEE’s columns in red color can be used to compose a concatenated index (EMP_ID, EMP_DEPT, EMP_GRADE).

WHERE  EMP_ID = SAL_EMP_ID
  AND  SAL_SALARY <200000
  AND  EMP_DEPT = DPT_ID
  AND  EMP_GRADE = GRD_ID 
  AND  GRD_ID<1200
  AND  EMP_DEPT<‘D’

CREATE INDEX C##TOSSKA.TOSSKA_09145226686_V0043 ON C##TOSSKA.EMPLOYEE
(
 EMP_ID,
 EMP_DEPT,
 EMP_GRADE
)

The following is the query plan after the concatenated index is created. Unfortunately, the speed of the SQL is 2.40 seconds although a new query plan is introduced by Oracle’s SQL optimizer.

To be honest, it is difficult if we just rely on common practices or human knowledge to build indexes to improve this SQL. Let me imagine that if we got an AI engine that can help me to try the most effective compound indexes to explore Oracle’s SQL optimizer potential solutions for the SQL. The following concatenated indexes are the potential recommendation by the imagined AI engine.

CREATE INDEX C##TOSSKA.TOSSKA_13124445731_V0012 ON C##TOSSKA.EMP_SAL_HIST
(
 SAL_SALARY,
 SAL_EFFECT_DATE,
 SAL_EMP_ID
)
CREATE INDEX C##TOSSKA.TOSSKA_13124445784_V0044 ON C##TOSSKA.EMPLOYEE
(
 EMP_GRADE,
 EMP_DEPT,
 EMP_ID,
 EMP_NAME
)

The following is the query plan after these two concatenated indexes are created and the speed of the SQL is improved to 0.13 seconds. It is almost 18 times better than that of the original SQL without the new indexes.

The above indexes include some columns that appear on the SELECT list of the SQL and there is a correlated indexes relationship for Oracle’s SQL optimizer to generate the query plan, it means that missing any columns of the recommended indexes or reshuffling of the column position of the concatenated indexes may not be able to produce such query plan structure. So, it is difficult for a human expert to compose these two concatenated indexes manually. I am glad to tell you that this kind of AI engine is actually available in the following product.

https://www.tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

Selecting a SQL Server Database Version for Your Company

SQL Server database and SQL

Choosing the right version of SQL Server is important for the performance you desire. If you’re installing an older one because your organization’s management prefers an older build or the vendor is unable to support newer versions, it is important to let them know which version your company needs, and why.

For this reason, we will discuss some popular versions of SQL Server from older to newer and mention their advantages in this blog.

Which SQL Server Version Works Best with SQL Performance Tuning?

Knowing the versions that support this task is extremely important because it will give you the ability to improve the SQL Server database and SQL performance.

To that effect, we will discuss the SQL Server 2016, 2017, and 2019 versions here.

SQL Server 2016

This version was chosen by a lot of independent software vendors or ISVs for one reason – 2016’s Service Pack 1 edition came with Enterprise features in Standard mode. These helped create a single application version that worked simultaneously for both Standard as well as Enterprise clients.

Advantages of Choosing this Version:

  • It is easy to find support material online as this version is quite popular and numerous database professionals are well-versed with this version’s tools.
  • Standard Edition users may find this version appealing since it supports 128GB RAM and additional space for internal functions such as query plans.
  • Support for this version ends after 2026 – longer than the older versions (2012/2014).
  • Newer applications that have additional compliance requirements will benefit from features in this version such as Always Encrypted, temporal tables, and Dynamic Data Masking. These will make it somewhat easier to protect and monitor sensitive information.
  • You can have both row store and column store indexes in this version, unlike the earlier ones that only had row store indexes.
  • If you need query plan monitoring to help with SQL performance tuning, you can use the Query Store’s features provided in SQL Server 2016 for this purpose.

SQL Server 2017

Being a newer release, it is one of the most regularly updated versions with patches coming in almost every other month. These patches are important because they resolve significant problems. It also comes with a minimum commit replica configuration to ensure commits are accepted by several replicas.

Advantages of Choosing this Version:

  • The upgrades are easier to get from this version onward due to a Distributed Availability Group that contains multiple SQL Server versions in it. Before this, we had AG version upgrades that were not as convenient, leading most users to construct a new cluster and migrate to it rather than opt for an upgrade.
  • This version contains batch mode execution plans, which gives those who require high-performance column store statements an advantage.
  • If you must run your SQL Server on Linux, you may consider SQL Server 2017 as several bugs have been resolved in the Cumulative Updates.
  • It’s a newer version so support will last longer than that of its predecessor.

SQL Server 2019

Released on November 4, 2019, this version is the latest in the SQL Server series. Naturally, it comes with the longest support lifespan, i.e. it will be supported until 2030. This version also receives regular patch updates to fix many significant issues in the form of Cumulative Updates.

Changes and Features in this Version:

  • Patch contents aren’t documented anymore. Moreover, you are likely to receive updates with undocumented new features – something to consider in case you require it for mission-critical production environments.
  • There is a bit of a learning curve thanks to some cutting-edge features in this version, so be prepared to perform some experimentation as you learn.
  • Some of the best performance features are included in the 2019 compatibility mode. However, you will have to keep a close eye on all SQL Server databases and SQL queries – even the ones running fast at present – as these will alter your current execution plans. In other words, you will have to test both slow and fast queries to make sure the slow ones speed up and the fast ones don’t fall behind in performance.
  • Table variables have gotten better in this version along with user-defined functions.
  • Additional features to watch out for including Big Data Clusters, Java support, and high container availability, so you may want to explore this version if you’re looking for perks like these in the SQL Server you want.

In Conclusion

At this point, SQL Server 2017 might seem like the best version to go with, thanks to a balance of features, stability, and support lifespan. Furthermore, you’ll receive plenty of help with SQL performance tuning – a lifesaver for overworked professionals who may not have the time or resources to upgrade every server every year.

How to Tune SQL Statements with Rewrite and Hints Injection for MySQL?

sql tuning for MySQL

There are some SQL statements with performance problem have to be tuned by SQL syntax rewrite and Hints injection, it is a little bit difficult for SQL tuning newcomers to master this technique. Developers not only have to understand the relationship between SQL syntax and the final query plan generation but have to understand the usage of optimizer hints and its limitations. Sometimes these two tuning techniques application will affect each other in a complex SQL statement.

Here is a simple example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables.

select  * from employee,department
where emp_dept=dpt_id
   and emp_dept<‘L’
   and emp_id<1500000
   and emp_salary= dpt_avg_salary
order by dpt_avg_salary

Here the following are the query plans of this SQL, it takes 7.7 seconds to finish. The query shows a “Full Table Scan Department” and nested loop Employee table with a Non-Unique Key Lookup EMPS_SALARY_INX.

You can see that this SQL cannot utilize index scan even though the dpt_dept is an indexed field. It is because the condition emp_dept<‘L’ is not explicitly induced the condition dpt_id < ‘L’ although emp_dept=dpt_id is also listed in the where clause.

To enable the index search of Department table, I explicitly add a condition dpt_id < ‘L’ to the SQL statement as the following:

select   *
from  employee,
     department
where  emp_dept = dpt_id
     and dpt_id < ‘L’
     and emp_dept < ‘L’
     and emp_id < 1500000
     and emp_salary = dpt_avg_salary
order by  dpt_avg_salary

Here is the query plan of the rewritten SQL and the execution time is reduced to 3.4 seconds. The new query plan shows that an Index Range Scan is used for the Department table and nested loop Employee table.

You may find that the nested loop to Employee by EMPS_SALARY_INX lookup may result into a lot of random access to the Employee table. Let me add a BKA hint to ask MySQL to use ‘Batched Key Access’ to join the two tables.

select   /*+ QB_NAME(QB1) BKA(`employee`@QB1) */ *
from  employee,
     department
where  emp_dept = dpt_id
     and dpt_id < ‘L’
     and emp_dept < ‘L’
     and emp_id < 1500000
     and emp_salary = dpt_avg_salary
order by  dpt_avg_salary

The new query plan shows a Batched Key Access is used to join Department and Employee tables, you can BAK information from MySQL manual for details, the new plan takes only 1.99 seconds and it is more than 3 times better than the original SQL syntax.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

How to Tune SQL Statement with CASE Expression for SQL Server I?

sql performance monitoring

Here the following is a simple SQL statement with a CASE expression syntax.

SELECT *
FROM EMPLOYEE
WHERE
CASE
when  emp_id   < 1001000 then ‘Old Employee’
when  emp_dept <‘B’   then ‘Old Department’
ELSE‘Normal’
END = ‘old Employee’

Here the following are the query plans of this SQL, it takes 2.23 seconds in a cold cache situation, which means data will be cached during the SQL is executing. The query shows a Full Table Scan of the EMPLOYEE table due to the CASE expression cannot utilize the emp_id index or emp_dept index.

We can rewrite the CASE expression into the following syntax with multiple OR conditions.

select *
from  EMPLOYEE
where  emp_id < 1005000
and ‘Old Employee’ = ‘Old Employee’
or not  ( emp_id < 1005000 )
and emp_dept < ‘B’
and‘Old Department’ = ‘Old Employee’
or not  ( emp_id < 1005000 )
and not ( emp_dept < ‘B’ )
and‘Normal’ = ‘Old Employee’

Here is the query plan of the rewritten SQL and the speed is 0.086 seconds. It is 25 times better than the original syntax. The new query plan shows an Index Seek of EMP_ID index.

This SQL rewrite is useful when the CASE expression is equal to a hardcoded literal, but if the literal “  =’Old Employee’ ” replaced by a variable “ = :var ”, this rewrite may not be useful, I will discuss it in my next blog.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for SQL Server automatically.

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

How to Tune SQL Statement with OR conditions in a Subquery for SQL Server?

sql performance monitoring

The following is an example that shows a SQL statement with an EXISTS subquery. The SQL counts the records from the EMPLOYEE table if the OR conditions are satisfied in the subquery of the DEPARTMENT table.

select countn(*) from employee a where
exists (select ‘x’ from department b
    where a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary
     )

Here the following is the query plan in the Tosska proprietary tree format, it takes 4 minutes and 29 seconds to finish.

The query plan shows a Nested Loops from EMPLOYEE to full table scan DEPARTMENT, it is the main problem of the entire query plan, the reason is the SQL Server cannot resolve this OR conditions  ”a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary” by other join operations.

Let me rewrite the OR conditions in the subquery into a UNION ALL subquery in the following, the first part of the UNION ALL in the subquery represents the “a.emp_id=b.dpt_manager” condition, the second part represents the “a.emp_salary=b.dpt_avg_salary” condition but exclude the data that already satisfied with the first condition.

select  count(*)
from   employee a
where  exists ( select  ‘x’
        from   department b
        where  a.emp_id = b.dpt_manager
        union all
        select  ‘x’
        from   department b
        where  ( not ( a.emp_id = b.dpt_manager )
            or b.dpt_manager is null )
            and a.emp_salary = b.dpt_avg_salary )

Here the following is the query plan of the rewritten SQL, it looks a little bit complex, but the performance is very good now, it takes only 0.447 seconds. There are two Hash Match joins that are used to replace the original Nested Loops from EMPLOYEE to full table scan DEPARTMENT.

Although the steps to the final rewrite is a little bit complicated, this kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 600 times fastAlthough the steps to the final rewrite is a little bit complicated, this kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 600 times faster than the original SQL.

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

How to Tune SQL Statements to Run SLOWER… but Make Users Feel BETTER (Oracle)?

MySQL database and SQL

Your end-users may keep on complaining about some functions of their database application are running slow, but you may found that those SQL statements are already reached their maximum speed in the current Oracle and hardware configuration. There may be no way to improve the SQL unless you are willing to upgrade your hardware. To make your users feel better, sometimes, you don’t have to tune your SQL to run faster but to tune your SQL to run slower for certain application’s SQL statements.

This is an example SQL that is used to display the information from tables Emp_sal_hist and Employee if they are satisfied with certain criteria. This SQL is executed as an online query and users have to wait for at least 5 seconds before any data will be shown on screen after the mouse click.

select * from employee a,emp_sal_hist c
where a.emp_name like ‘A%’
     and a.emp_id=c.sal_emp_id
     and c.sal_salary<1800000
order by c.sal_emp_id

Here the following is the query plan and execution statistics of the SQL, it takes 10.41 seconds to extract all 79374 records and the first records return time ”Response Time” is 5.72 seconds. The query shows a MERGE JOIN of EMPLOYEE and EMP_SAL_HIST table, there are two sorting operations of the corresponding tables before it is being merged into the final result. It is the reason that users have to wait at least 5 seconds before they can see anything shows on the screen.

As the condition “a.emp_id = c.sal_emp_id”, we know that “ORDER BY c.sal_emp_id“ is the same as “ORDER BY a.emp_id“,  as SQL syntax rewrite cannot force a specified operation in the query plan for this SQL, I added an optimizer hint /*+ INDEX(@SEL$1 A EMPLOYEE_PK) */ to reduce the sorting time of order by a.emp_id.

SELECT  /*+ INDEX(@SEL$1 A EMPLOYEE_PK) */ *
FROM    employee a,
      emp_sal_hist c
WHERE a.emp_name LIKE ‘A%’
    AND a.emp_id=c.sal_emp_id
    AND c.sal_salary<1800000
ORDER BY c.sal_emp_id

Although the overall Elapsed Time is 3 seconds higher in the new query plan, the response time is now reduced from 5.72 seconds to 1.16 seconds, so the users can see the first page of information on the screen more promptly and I believe most users don’t care whether there are 3 more seconds for all 79374 records to be returned. That is why SQL tuning is an art rather than science when you are going to manage your users’ expectations.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/