How to optimize this SQL statement that run about 4 seconds in ORACLE?I want to query and use less time

Question

The following sql in my project runs about 4 seconds.TB_Order has a full table scan. There is an index on the state field of TB_Order.The data volume of the table is 20 million, and the data volume of E and P is less than 50.This SQL can be optimized.and I have use UNION ALL,is it right?Do you have a better sql?

--The first version
SELECT T.ORDER_ID, T.PRIORITY, T.SUB_TASK
FROM TB_ORDER T
WHERE (T.STATE = 'E' OR (T.STATE = 'P' AND SYSDATE > T.EFFECTIVE_DATE))
AND (T.PRIORITY <= 100 OR T.PRIORITY IS NULL)
AND ROWNUM <= :1

--The second version
SELECT * FROM (
SELECT T.ORDER_ID, T.PRIORITY, T.SUB_TASK
FROM TB_ORDER T
WHERE (T.STATE = 'E')
AND (T.PRIORITY <= 100 OR T.PRIORITY IS NULL)
UINON ALL 
SELECT T.ORDER_ID, T.PRIORITY, T.SUB_TASK
FROM TB_ORDER T
WHERE (T.STATE = 'P' AND SYSDATE > T.EFFECTIVE_DATE)
AND (T.PRIORITY <= 100 OR T.PRIORITY IS NULL)
)
where ROWNUM <= :1

Please provide sample data, desired results, and an explanation of the logic you are implementing. For the record, you mention timings for one query -- and then you show two queries. Confusing. — Gordon Linoff, May 08 '20 at 16:18
it's hard to say something without knowing your data profile, how many distinct values and distribution of those is for the **T.STATE** column and for **T.PRIORITY** as well. If — micklesh, May 08 '20 at 16:27
The *first* thing you should examine in the *execution plan* used by the query. see [here](https://stackoverflow.com/a/34975420/4808122) how to get it and what other data you must provide to be able to get help. Also if the data in the column `STATE` is *skew* (i.e. there is lot of rows with some values and few rows with other values) what you should check is the column *histogram*. — Marmite Bomber, May 08 '20 at 19:06
@flower Use this query to check for histograms: `select histogram, column_name from all_tab_columns where table_name = 'TB_ORDER';` If there isn't one, try gathering stats to generate one: `begin dbms_stats.gather_table_stats(user, 'TB_ORDER'); end;`. — Jon Heller, May 10 '20 at 02:54

score 2 · Accepted Answer · answered May 08 '20 at 16:38

Not a best approach,eliminated OR and forced INDEX Scan,Try explain plan without HINT to see if index on TB_ORDER.STATE is utilized. Add the hint only if index is not used. Also recommend run statistics on TB_ORDER table.

SELECT * FROM
(SELECT /*+ INDEX(T STATE_NDX) */ T.ORDER_ID, T.PRIORITY, T.SUB_TASK
FROM TB_ORDER T
WHERE T.STATE = 'E'                        
AND DECODE(T.PRIORITY,null,0,T.PRIORITY) <= 100
AND ROWNUM <= :1
UNION ALL
SELECT /*+ INDEX(T STATE_NDX) */ T.ORDER_ID, T.PRIORITY, T.SUB_TASK
FROM TB_ORDER T
WHERE T.STATE = 'P' 
AND SYSDATE > T.EFFECTIVE_DATE
AND DECODE(T.PRIORITY,null,0,T.PRIORITY) <= 100
AND ROWNUM <= :1)

score 0 · Answer 2 · answered May 10 '20 at 14:29

You may use IN to replace with UNION.The sql will query the table only one time.

  SELECT T.ORDER_ID, T.PRIORITY, T.SUB_TASK
   FROM TB_ORDER T
  WHERE T.STATE IN ('E', 'P')
  AND (CASE
     WHEN (T.STATE = 'P' AND SYSDATE > T.EFFECTIVE_DATE) THEN
      1
     WHEN T.STATE = 'E' THEN
      1
     ELSE
      0
   END) = 1
 AND (T.PRIORITY <= 100 OR T.PRIORITY IS NULL)
 AND ROWNUM <= :1

How to optimize this SQL statement that run about 4 seconds in ORACLE?I want to query and use less time

2 Answers2