Problem solve Get help with specific problems with your technologies, process and projects.

Help optimizing a SQL query

I have this query that I was asked to optimize. So far I havent't been able to do it.

Hello Phillip, I will appreciate your help on this. I have this query that I was asked to optimize. So far I havent't been able to do it.
SELECT poid_id0 
  FROM item_t 
 WHERE account_obj_id0 = :lOwner_id 
   AND effective_t = :lBill_t
The table has several indices, including one for account_obj_id0, another one for effective_t and one composed by effective_t and ar_account_obj_id0. The table is not partitioned and has 31011917 rows. Your answer is most welcome.

Have you done an Explain Plan on the query to see what access path it is using? The most efficient access path from what you wrote below would be one using the index composed of effective_t and ar_account_obj_id0 columns. If it is not using this index, then verify that the index is not disabled or invalid. Also verify that your table and index statistics are current.

If it is using this index, then the next thing I would check is your top Wait events and average read times on your tablespaces and datafiles in your statspack or AWR reports. If your average read times in the report is high, greater than 25 ms, then you need to check your disk I/O subsystem.

Since this table contains over 30 million rows, if it were me, I would seriously consider partitioning the table and using parallel query. That might be the best way to optimize it if everything else checks out.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.