SELECT poid_id0 FROM item_t WHERE account_obj_id0 = :lOwner_id AND effective_t = :lBill_tThe 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
Related Q&A from Phillip Bracken
A user complains that every time he tries to access a table (select only), it takes more than two hours to get the results. There are no DML ... Continue Reading
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four... Continue Reading
I have always used TKPROF to do SQL tuning in previous versions of Oracle (7,8 and 9). Can I still use TKPROF in Oracle 10g R2? Continue Reading