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 Mon Jul 01 2002 - 12:02:27 CDT
