Re: Help needed tuning SQL statement.

In article <CzuLC6.CEJ_at_iglou.com>, proberts_at_iglou.iglou.com (Phil Roberts) writes:
>
>
> We have a SQL statement a co-worker wrote that is giving us some trouble.
> We've tried several variations but none seem to do what we want as quickly
> as we feel it should. The statement takes a very long time on small tables
> of less than 10,000 rows. Sometimes it creates too many extents errors too.
>
> Can some SQL expert reading this tune the statement so that it does what we
> want quickly? The enclosed example is what we started with, and to our
> thinking best portrays what we seek to accomplish. Neither of us has had
> any SQL training yet, so any help or pointers will be greatly appreciated.
>
> We first create table_1 with the storage parameters we want. Then to
> populate the table we are trying the following:
>
> insert into table_1
> (column_A, column_B, column_C)
> select table_2.column_A, table_2.column_B, substr(table_3.column_C,1,5)
> from table_2, table_3, table_1
> where table_2.column_D = '999999' and
> table_2.column_B != table_1.column_B;
>
>
> Notes:
> ------
> We are using Oracle 6.0.36.
>
> There are seven columns in table_1 when it is created, but we are only
> placing data in three of the columns. The other columns will be updated
> using a form later.
>
> column_B which is in both table_1 and table_2 is an indexed char column.
>
> column_D which is in table_2 is not an indexed column.
>
> table_1 should have approximately 7,000 rows and has no indexed columns.
>
>
> What we are attempting to do is if table_2.column_D has 999999 in the
> row it is an active row any thing else in that column and it is inactive
> and shouldn't be entered in table_1. If a record is active it should be
> added to table_1 if table_2.column_B is not the same as table_1.column_B.
>
> I hope I haven't confused you too much. We sure have been. :)
>
> Please post or e-mail any respones. E-mailed responses should be sent to
> proberts_at_iglou.com or proberts_at_ky-ngnet.army.mil
>
>

You are selecting substr(table3.column_c,1,5) without defining a join (where clause) for this table with other tables (2 & 3). Assuming that you have more than one row in table3, your query selects table2.column_1 and table2. column_2 and than joins it with all rows of table3 - which will return more than one rows. That does not makes sense. I think you probably want to add following lines in your code:

	And table3.<key-column> = table2.<key-column>
	and table3.<key-column> = table1.<key-column>

where key-column will define the joining relationship for table3 with table1 and table2.

You may also need to create index for table2.column_4 (Column_d).

Hope this will help.

Cheers,

Shariq Received on Thu Dec 01 1994 - 19:37:54 CET