How to optimize SQL query (Oracle Database)

Question

I want to optimize my SQL query. It looks really simple but compiles too long for me. My database is Oracle 12.1.0.3. When table AB_MESSAGE contains about 1K records (test environment), query compilation time equals more than 6 seconds.

My SQL query:

SELECT * from AB_MESSAGE m 
left join AB_MESSAGE_TYPE mt ON m.MESSAGE_TYPE_ID=m.ID
where to_char(m.CREATION_DATE,'YY/MM/DD') >= '18/09/30'  
and m.MESSAGE_ID is null 
and m.id in (select max(m2.ID) 
             from AB_MESSAGE m2
             where m2.MESSAGE_ID is null 
             group by m2.AB_MESSAGE_ID, m2.SEND_DATE)  
order by m.SEND_DATE desc;

Table AB_MESSAGE:

CREATE TABLE "AB"."AB_MESSAGE" 
(   "ID" NUMBER(*,0) NOT NULL ENABLE, 
"MESSAGE_ID" NUMBER(*,0), 
"AB_MESSAGE_ID" NUMBER(*,0), 
"SNDR_MSG_REF" VARCHAR2(20 BYTE), 
"SENDER_ID" VARCHAR2(20 BYTE), 
"RECEIVER_ID" VARCHAR2(20 BYTE), 
"ESDK_MESSAGE_TYPE_ID" NUMBER, 
"MESSAGE_TYPE_ID" NUMBER NOT NULL ENABLE, 
"MT_FIELD_DEF_VARIANT_ID" NUMBER, 
"ISSUE_ID" NUMBER, 
"PARENT_ID" NUMBER, 
"PARENT_MESSAGE_ID" NUMBER, 
"CREATION_DATE" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP NOT NULL ENABLE, 
"CHANGE_DATE" TIMESTAMP (6), 
"SEND_DATE" TIMESTAMP (6), 
"MESSAGE_FILE_NAME" VARCHAR2(100 BYTE), 
"MESSAGE_TYPE" VARCHAR2(24 BYTE), 
"RAW_ESDK_MESSAGE" BLOB, 
"XML_MESSAGE_CLOB" CLOB, 
"MESSAGE_STATUS" VARCHAR2(50 BYTE), 
 CONSTRAINT "AB_MESSAGE_PK" PRIMARY KEY ("ID")
 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "ABA"  ENABLE, 
 CONSTRAINT "AB_MESSAGE_FK5" FOREIGN KEY ("PARENT_ID")
  REFERENCES "AB"."AB_MESSAGE" ("ID") ENABLE, 
 CONSTRAINT "AB_MESSAGE_FK1" FOREIGN KEY ("MESSAGE_TYPE_ID")
  REFERENCES "AB"."AB_MESSAGE_TYPE" ("ID") ON DELETE CASCADE ENABLE)

Table AB_MESSAGE:

CREATE TABLE "AB"."AB_MESSAGE_TYPE" 
("ID" NUMBER(*,0) NOT NULL ENABLE, 
"NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
"DESCRIPTION" VARCHAR2(150 BYTE), 
"CREATION_DATE" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP NOT NULL ENABLE, 
 CONSTRAINT "AB_MESSAGE_TYPE_PK" PRIMARY KEY ("ID")
 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "ABA" ENABLE)

Question: Is there any way to optimize this query?

First of all, you are negating the use of an index with the condition to_char(m.CREATION_DATE,'YY/MM/DD') >= '18/09/30', that suffers from the "expression on the left side of the equality". Change it to something like m.CREATION_DATE >= date '2018-09-30'. — The Impaler
– The Impaler, Commented Oct 1, 2018 at 20:40
Shouldn't the second line end with =mt.ID instead of m.ID? — kfinity
– kfinity, Commented Oct 1, 2018 at 20:42

The Impaler · Accepted Answer · 2018-10-01 20:49:26Z

1

I would start by improving two simple things:

Improve your SQL: change to_char(m.CREATION_DATE,'YY/MM/DD') >= '18/09/30' to m.CREATION_DATE >= date '2018-09-30'. This change will help with the usage of an index.

Add the following indexes:

create index ix1 on AB_MESSAGE (AB_MESSAGE_ID, SEND_DATE, MESSAGE_ID, ID);

create index ix2 on AB_MESSAGE (CREATION_DATE, MESSAGE_ID);

If the query is still slow, I would get the execution plan of the query to verify it's using the correct indexes.

answered Oct 1, 2018 at 20:49

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kamil Gosciminski · Accepted Answer · 2018-10-01 20:46:07Z

Your query is not using an index (if there's one) defined on ab_message(creation_date). Change your left-hand part of >= comparison to not use functions, as they are blackboxes to optimizer.

Also, your JOIN columns is probably wrong since it should take a column from m and another one from mt. Try m.message_type_id = mt.id instead.

SELECT * 
from AB_MESSAGE m 
left join AB_MESSAGE_TYPE mt ON m.MESSAGE_TYPE_ID = mt.ID -- change here
where 
      m.CREATION_DATE >= '2018-09-30' -- change here
  and m.MESSAGE_ID is null 
  and m.id in (
    select max(m2.ID) 
    from AB_MESSAGE m2 
    where m2.MESSAGE_ID is null 
    group by m2.AB_MESSAGE_ID, m2.SEND_DATE)  
order by m.SEND_DATE desc;

Other than that, there is a possibility to rewrite the subquery, but we would need to see indexes you have created on your tables.

Read the chapter about EXPLAIN PLAN in Oracle's documentation. This could be a good addition to your question so that we know what plan your database engine chooses to execute the query.

I'd suggest for starters to have indexes defined like below and see how/if the plan changes

ab_message(ab_message_id, send_date)
ab_message(message_type_id, creation_date)

Collectives™ on Stack Overflow

How to optimize SQL query (Oracle Database)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related