Database Tuning Issue or Redesign ??

Dear Gurus,

I have in the last couple of months been working on a project to trap all techniciam test data onto an Oracle 817 database. I am using an AMDOCS formerly Clarify application. Here is the problem I am now facing:-

When doing a simple select from a table it is taking ages to return the data back. Here is the statement

select /*+rule*/
* from table_x_audit_hdr where x_detail_number = 'RMA-61214-1'

I have created relevant indexes and analysed both tables and indexes and by looking at the plan for the above statement it is deffinately using the indexes which have been refreshed. There are only 1200 records in the above table so this statement should be working really quickly.

The Table that I had to create has just over 600 fields in it !!!. My DBA's in the States (I am in the UK) have said that I should redesign the application and try to get the size of the table right down which i have come up with a plan that will take 6 weeks to redevelop that will bring the size of the table down to just over a 100 records.

If I do a similar select from another table with only 100 fields in it but with 116256 rows of data the select statement is coming back really quickly ie

select /*+rule*/
* from table_demand)dtl where detail_number = 'RMA-61214-1'

Are there any memory parameters I should be looking at to get this to work quicker or would I be better in taking my DBA's advise and redeveloping the whole app to have only 100 fields in it ?????? Or is there some other thing I can not think of to fix this.

Any help and advise would be great as I have been trying to come up with a resolve to this for weeeks now

regards

Frustrated developer

Tony Received on Thu Jun 05 2003 - 06:13:28 CDT