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

Response time on table with one primary key is slowing

I have one transaction table which contains lakhs of records. The table has one primary key which is queried very often through the D2K form. As the data size is increasing day by day, the response time in getting the output is getting delayed. Please suggest to me different options through which I can increase my performance. I am giving you two queries whose costs are extremely high.
SELECT  COUNT(TRUCK_NO)
FROM    TRUCK_DET
WHERE   TRUCK_NO = :TRUCK_NO
AND DATE_TIME_OUT IS NULL
AND     RCPT_DT = ( select max(rcpt_dt) from TRUCK_DET
               WHERE   TRUCK_NO = :TRUCK_NO )
/
select max(TRUCK_KEY)
 from TRUCK_DET
  where TRUCK_NO = :TRUCK_NO
  and  DATE_TIME_OUT IS NOT NULL
/
There are no partitions and no indexes on any columns of the tables.
If there are no indexes, performance will suffer. Also, and more important, if there are no indexes then you have not defined your primary key correctly. Review the Oracle manuals for information on how to specify the PK (ALTER TABLE ... ADD CONSTRAINT... PRIMARY KEY).

Dig Deeper on Oracle database performance problems and tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close