Re: Tuning "INSERT" for small table

would the analytic function RANK (or DENSE_RANK) be of any use here (in lieu of 'score'?)?
I confess, I haven't read your query all that closely, and it seems there is a fair bit going on here.

Just a thought, not really applicable to your immediate question, though.

;-)

Good luck!

Lee M Horowitz wrote:
>
> 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:
>
> 1. 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:38:37 CDT