CODEWITHSUNDEEP
oracle-databaseperformance
chikkada
chikkada

Reputation: 75

How to optimize this select query

Hi I'm using the below select query which fetches all the rows from the table based on a single condition. It is like an export that we are geenrating. Here is the query.

Select emp_name, phone_number, emp_id from Employees where 
emp_dept = 100;

Suppose if i have millions of records here and i'm not allowed to create any temporary table and index, what is the best way to optimize this query. Please note i need all the records of the table so the filters are actually less.

In the process of learning Oracle. So please let me know in case any mistakes are there.

Thanks.

Upvotes: 0

Views: 55

Answers (1)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

Since

i'm not allowed to create any temporary table and index

then your only option seems to be to run this in parallel:

select --+ parallel(E,16)
    emp_name, phone_number, emp_id
from employees E
where emp_dept = 100;

And your useful options are, depending on many factors ...

  • to ask your local DBA to add an index for you
  • to ask your local DBA to reorganize the table for you (partitioning, clusters)
  • to ask your local DBA to create a on-commit fast-refreshable query-rewritable mview for you
  • to ask your company to buy Exadata machine for you
  • etc.

Upvotes: 1

Related Questions