Well, given your conditions I think temp table is the right choice. As far
as I understand, you cannot build a deterministic ranking function that
depends only on row data, thus functional index is not an option.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"jock" <jockey_sql_at_indya.com> wrote in message
news:77a43c6a.0207041254.5e983ea6_at_posting.google.com...
> My ranking function is dependent on the user input. The user
> determines on a scale how important each parameter is. For example for
> lead time, i may have a score of 100. 100 is user inputted. All
> supplier who have lead times within the specified lead time(lead time
> inputted by user again)will have 100 added to their overall score.
> Like lead time , i have several such criterion. The supplier with the
> largest score is ranked first.
>
> I use Oracle 8i.
>
> regards
>
>
> Right now, i have decided on a PLSQL function which ranks them and
> puts them into a temporary table
> Then i read from the temporary table.
>
> thanks.
>
>
>
> "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message
news:<afq1vb$5hh$1_at_babylon.agtel.net>...
> > If the ranking function does not depend on user input and other rows
> > in the table, it sounds like a good use of a functional index. You create
> > a deterministic ranking function in PL/SQL which returns some ranking
> > value for each row in a table, and then you create an index on this
> > function. Selecting my_rank(id) from my_table order by my_rank(id)
> > will return results very fast in this case (you will be penalized with delays
> > on index creation and inserts/updates as Oracle will have to call your
> > function for new/updated rows). If your function depends on other rows,
> > this method probably does not apply - function will not be deterministic in
> > this case (it will not always return the same result given the same input).
> >
> > --
> > Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> > Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> > All opinions are mine and do not necessarily go in line with those of my employer.
> >
> >
> > "jock" <jockey_sql_at_indya.com> wrote in message
> > news:77a43c6a.0206290014.2543c8d0_at_posting.google.com...
> > > Dear friends,
> > >
> > > I have a table with 3000 suppliers on it. Now,i want to rank these
> > > suppliers
> > > when the user clicks on "Submit" button on my JSP Form.
> > >
> > > The ranking function is not a simple "ORDER BY". I apply a function to
> > > each of the rows in the supplier table. The supplier who satisfies my
> > > conditions the best is ranked first.
> > >
> > > As an example, if my supplier has a lead time less than 20 days i give
> > > him 20 points. If he is in my state, i give him 20 points more. The
> > > supplier with the most points is my preferred supplier. I have half a
> > > dozen such criteria.
> > >
> > > What is the best method of doing this via JSP?
> > >
> > > **Create a temporary table with the ranked suppliers and then populate
> > > from this temporary table. The temporary table is created by calling a
> > > Stored procedure.
> > > Now, how do i generate the temporary table name and make sure its
> > > unique?
> > >
> > > **PLSQL Table-- How do i return a PLSQL Table to populate a JSP page?
> > >
> > > **Updating the base table is ruled out as there may be several users
> > > simaltaneously using the page.
> > >
> > > Some sample code or tips is deeply appreciated.
> > >
> > > Thank you very much.
Received on Fri Jul 05 2002 - 06:35:18 CDT
