CODEWITHSUNDEEP
sqloracle-databaseperformance
John Doe
John Doe

Reputation: 319

How to optimize SQL query (Oracle Database)

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?

Upvotes: 1

Views: 1018

Answers (2)

The Impaler
The Impaler

Reputation: 48865

I would start by improving two simple things:

  1. 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.

  2. 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.

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

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)

Upvotes: 0

Related Questions