Re: Indexes, Increase query speed

Thanks Dan,
Here are the answers to your questions.
1. Operating system: Win95, my desktop only has 64Mb Ram users will have at least 128. We are using Oracle 8.1.7 and developer 6.0. I'm still in development and testing. The users will be accessing the database over a network.
2. Table 1 (Master - Clients) has 60000 rows, columns record client name and address info. I need to sort by client last_name. Table 2 (Detail 1 - Assessments) has 100,000 rows and has foreign keys from many other tables not just the clients table (7 Look up tables). Table 2 also has several large varchar2 note columns. Table 2 is the master of several other tables and I need to sort by created_on desc. Table 3 (detail 2) has 13000 rows.
3. For form 1: No where clauses are used. Form 1 has three data blocks the clients data block is ordered by last_name the assessments data block is order by created_on desc and I defer query of Table 3 until the user navigates to it. In form 2 (View with one data block) the order by is last_name, created_on desc. Form 2 View is something like this: SELECT clients.first_name, clients.last_name, clients.social_security, assessments.created_on, assessments..6 other columns.., table3.all columns(10) from clients, assessments, table3 where assessments.cli_clients_rec = clients.clients_rec and table3.ass_assessments_rec = assessments.assessments_rec. For both forms in a new form instance trigger I use "execute_query" The number or records displayed is 5 everything else is at the default settings.
4. No optimizer hint is used. I'm not familiar with this. 5. No to analyze. Also not familiar with this. I hope this helps
Thanks
Dave

In article <3A8085B5.F1A9E3C5_at_exesolutions.com>,   "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote:
> > I'm a new developer and I want to increase the speed of my queries
> > retrieved in Forms 6.0.
> > I have a master - detail1 - detail2 (Table1 - Table2 - Table3)
> > relationship. Where detail1(Table2) is the master of detail2
 (Table3).
> > I have a two forms based on the above relationship. One form has the
> > data source set to the tables (3 data blocks), the other form is
 based
> > on a view of all three tables (1 data block).
> > For both forms I need to auto query when the form opens
> > Ordered by Table1 name and table2 date.
> > I have indexes on all primary keys and foreign keys.
> > I also created the following indexes:
> > Table1 (primary key and name)
> > Table2 (primary key, foreign key)
> > Table2 (primary key, date)
> > The queries are still quite slow any advice?
> > What have I done wrong?
>
> What you have done wrong is not provide enough information for anyone
 to
> help you. Here's what I would need to attempt to answer your question.
>
> 1. What operating system on what hardware with how much RAM. And
 the
> Oracle version wouldn't hurt.
> 2. How many rows in each of the tables?
> 3. Provide a copy of your WHERE clause so that anything you have
 there
> that would invalidate index usage can be found.
> 4. What optimizer mode?
> 5. Have you run analyze on the schema after loading the tables?
>
> Dan Morgan
>
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Feb 07 2001 - 07:52:22 CST