Re: Tuning Issue or Redevelopment Issue ???

Obviously, you can tackle this in two ways. You could rethink your design. Personally, I haven't seen an entity that had 600 components to it. But I do hear from others that they do exist for some really weird applications.

You could also try to tune for better performance. If using an index is slow, have you tried a full table scan? Using an index does not always lead to better performance. With 600 columns in your table, you could be experiencing chaining as well. You didn't specify the block size, but have you looked at increasing the block size? Are their LOB columns?

There are other options as well.

HTH,
Brian

Tony Rees wrote:
>
> Dear Oracle 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

-- 
===================================================================

Brian Peasland
oracle_dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"