Re: Sorting functionality
- From: "David Cressey" <cressey73@xxxxxxxxxxx>
- Date: Sat, 01 Sep 2007 12:27:00 GMT
"Ed Prochak" <edprochak@xxxxxxxxx> wrote in message
news:1188587824.812183.139010@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Aug 30, 2:10 pm, Sameer <sameervijay...@xxxxxxxxx> wrote:
I am rather new to databases and do not have much idea about
application specific sql functionality. I have to implement a ranking
system for which the details are as follows:
Each tuple in a table has an attribute whose value is modified at
frequent intervals. After a certain span of time, the tuples in the
table are to be arranged in the order of the attribute. However after
sorting the tuples, a rank must be assigned to each tuple depending
upon the position in the sorted table. This rank is stored in a
separate attribute in the same table.
Can someone guide me on implementing this model in a MySql database.
Also if it is easier, in the Oracle database.
First off, TABLES ARE NOT SORTED. Table repesent Sets of data. Sets
are UNORDERED collections.
Separately from that issue, such ranking is really an attribute of a
reporting application. I think it is a poor idea to store the rank in
the table, especially as it depends on
... an attribute whose value is modified at
frequent intervals.
lets assume this interval is one minute. Then your second requirement:
... After a certain span of time, the tuples in the
table are to be arranged in the order of the attribute. However after
sorting the tuples, a rank must be assigned to each tuple depending
upon the position in the sorted table. This rank is stored in a
separate attribute in the same table.
Lets assume this "span of time" is 5minutes. That means for the minute
after the rank is assigned it is correct. Then the attribute is
updated on one or more rows and likely at least two of the rows have
the wrong rank. So for the other 4minutes the ranking is wrong. That
means 80% of the time your table has the wrong ranking!
So two things:
1. consider creating a view, which computes the rank when needed (ie
when queried)
2. in Oracle the view can use the RANK operation (see Analytic
Functions). I have no idea if MySQL supports this feature.
What Ed said. Simplified, this means that it's better under most
circumstances, to order (sort) data at the time of retrieval, and not at the
time of insertion. The "order by" clause of a "select" does exactly this.
However, many times it's advantageous to bear the COST of sorting at
insertion time, because the delay is just too long at retrieval time.
Appropriate index design will accomplish this goal, for the most part. If
there is an index on the table that maintains the data in the order it will
be retrieved, then updating this index will usually incur some cost at
insertion time, but retrieval in the same order as the index will generally
be pretty fast.
It gets more complicated than this, but I wanted to give you an overview.
.
- Prev by Date: Good Sybase 15 Books
- Next by Date: Re: Country code to Country info
- Previous by thread: Good Sybase 15 Books
- Next by thread: Re: Country code to Country info
- Index(es):
Relevant Pages
|
