Re: Tuning "INSERT" for small table

From: Lee M Horowitz <lee_at_JamToday.com>
Date: Tue, 09 Apr 2002 16:30:40 -0400
Message-ID: <78i6bu4mlctdlbnoahkpkl5sfba3o9l7o4@4ax.com>

On Tue, 09 Apr 2002 19:22:39 GMT, Daniel Morgan <damorgan_at_exesolutions.com> wrote:

>My suggestion would be to stop doing the inserts. They care completely
>unnecessary in Oracle. That should speed things up substantially.
>
>Strikes me that likely you come from a SQL Server or Sybase background
>where that kind of workaround is necessary ... here it is not.
>
>Daniel Morgan

Wow! Thats wonderful news indeed.

Unfortunately, my little 1/4 watt brain cant think of a way to make a single query that satisfies all the constraints and still has half a hope of performing well.

Maybe a more experienced eye can cut through the fog:

The result, when all the dust settles will be taken from two tables in "master-detail" relation which need to be returned to the user as a ref cursor. Something like Select a.col1,a.col2, ..., b.col1,b.col2, x.SCORE From master a, detail b, iintermediate_Table x Where a.master_key=b.master_key And a.master_key=x.master_key Order by x,SCORE

Notice the fly in the ointment, I need to order the results by the score (A numerical score which tells how well the master_table's record fits the query criteria by which the intermediate_Table was fllled.

2. The intermediate table has the following columns ( master_key, score)

3. The master_table records are arranged in a hierarchy, i.e. each record has a master_key (a primary key column) AND a parent_key column which is a foreign key to itself (i.e. its the master_key value of some other row in the master table.)

The master table records form a "tree" with arbitrary depth and breadth

4. The"score" depends on how close a match we find by looking in varios other tables (having master_key value as a foreign key). There are intermedia Text (Context) indexes on some of the columns of those otehr tables. We do "Contains" clause searches.

5. The result set needs to augmented. We dont want merely the records we've found via the context searches, we also want al the children of the records (if any) that we've found. The score for a child is determined by the score of the parent and the level of remove (think "level" pseudo column of a "connect by" query.

6. Thats the crucial step that drove me to an intermediate table. How can I collect the children with thier calculated scores (and union those with the original "hits") while keeping the ability to order by the "best" score. An item might be found multiple times, once directly via the context search and again because it is the child or grandhchild (Etc) of some record that was directly found. We want each master item found only once, using its "Best" score

So the full query is more like
Open output_ref_Cursor FOR
.Select a.col1,a.col2, ..., b.col1,b.col2, x.SCORE From master a,

            detail    b,
           ( Select master_key, min(Score)  From iintermediate_Table 
            group by master_key ) x

Where a.master_key=b.master_key
And a.master_key=x.master_key
Order by x,SCORE

By the way, when the intermediate_table is a "global temporary table" the union is pig slow compared to the same formal query when the intermediate table is a "Real" table. Huh? I cant explain it, but there you are, cant argue with the experimental numbers.

On the other hand, inserting into a temporary table is way faster than inerting into a real table which does make sense to me ( Alll in memory? Mabe less overhead for other stuff ...rollback segments? )

SO....If you can make a query which does Connect by for a SET of starting points and puts the result into a "view" (you need connect by to find the level to calculate the score) which can drive a "join" you get the 500 pound Oracle Gorilla prize and a hearty handshake besides.

I dont say it cant be done, only that I dont expect to find the solution all by my lonesome any time real soon. Received on Tue Apr 09 2002 - 15:30:40 CDT