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

Using a nested table for a view-based form

We have a "display only" form that is based on a view. The problem is that it is slow because of the number of records in the tables. We are getting ready to upgrade to 10g and will have a lot of new capabilities available. Would a nested table or varray be appropriate for this type of thing?

We have a "display only" form that is based on a view. A view is used instead of a straight query/base table block because so many tables are involved and the data has to be queried using unions. The data actually is at two levels like sections and chapters. Each section has the chapters within it displayed immediately after. For example: Section 1 is on the first row, Chapter 1A is indented and on row 2, Chapter 1B is also indented and on row 3, Section 2 is not indented and on row 4, etc. It looks similar to a table of contents.

The problem is that it is slow because of the number of records in the tables.

We are getting ready to upgrade to 10g and will have a lot of new capabilities available that we are not familiar with. Would a nested table or varray be appropriate for this type of thing? Would bulk collect apply? Or is there something else that we could use?

My inclination would not be to use nested tables or other object-relational features unless required by an application. I doubt that they would be helpful in improving performance (but I have little experience with them, so I'd welcome evidence that they could improve performance).

Your first step should be to improve the view's performance as much as possible using the features of your current Oracle version. It's hard to give specific advice without seeing the view definition. Once you've made response time as fast as you can, you can then consider, based on the most time-consuming operation in the view query, if a 10g enhancement can help. Take a look at this document for an overview of 10g's new features.

Dig Deeper on Oracle database installation, upgrades and patches

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