| Query Performance [message #685430] |
Thu, 06 January 2022 02:25  |
 |
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  |
 |
Michel Cadot
Messages: 68417
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).
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
