Home » SQL & PL/SQL » SQL & PL/SQL » Query Performance (Orcale 12-c)
Query Performance [message #685430] Thu, 06 January 2022 02:25 Go to next message
RGFJ
Messages: 1
Registered: January 2022
Junior Member
Dear All,

I have table with 10 columns and more than 300 million rows ,
i have id_no column that is Primary Key, and status column which is also indexed

i am executiong following query , which is taking quite a lot time like 20 Sec, i want it to be executed in 2 seconds , how i can achive this,

SELECT id_No,
SUM (
CASE WHEN status = 1
THEN
'A'
ELSE
'B'
END) Read,
SUM (
CASE
WHEN status = 2
THEN
'C'
ELSE
'D'
END) Write,
SUM (
CASE
WHEN status = 3
THEN
'E'
ELSE
'F'
END) Speak
FROM Library
GROUP BY id_no‚Äč

Thanks.
Re: Query Performance [message #685431 is a reply to message #685430] Thu, 06 January 2022 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.

For what you have posted, I could say activate the parallelism (if you can), partition the table (if you are licensed for), buy faster disks and cpu (if your boss agrees with it).

Re: Query Performance [message #685432 is a reply to message #685430] Thu, 06 January 2022 10:56 Go to previous messageGo to next message
John Watson
Messages: 8659
Registered: January 2010
Location: Global Village
Senior Member
As id_no is the primary key, there is no point in using GROUP BY.
Re: Query Performance [message #685438 is a reply to message #685432] Fri, 07 January 2022 06:34 Go to previous message
Frank
Messages: 7885
Registered: March 2000
Senior Member
I think you oversimplified your query, since you cannot sum 'A's or 'B's.
Previous Topic: Two MAX Functions in a Single SELECT
Next Topic: Defining Variables in the Package Spec
Goto Forum:
  


Current Time: Mon Jan 17 11:07:29 CST 2022