-
Hi,
1. I need to sort a date column by desc order. If I have added in an index on that column in desc order, then my sql statement do not need to include the order by clause and it will be sorted already, am I right about this?
2. I have created the following sql to show the first 20 students who have studied math between the date period and sort then according to desc order. However this sql doesn't work if the month is different, only within the same month, it can be sorted out neatly. Is there any problems with my sql or is it an oracle problem? I am using Oracle 8.1.5.
select a.*
(select st.student_id, to_char(st.register_date, 'dd-mm-yyyy hh24:mi') reg_date, rownum news_row
from student, sb.subject
where st.sudent_id = sb.student_id
and sb.subject_name like '%math%'
and st.register_date >= to_date('01-10-2000, 'dd-mm-yyyy')
and st.register_date <= to_date('01-11-2000, 'dd-mm-yyyy') + 1
/* error? without + 1 it will not be inclusive */
order by reg_date desc ) a,
student st
where st.student_id = a.student_id
and rownum >= 1
and rownum <= 20
/
Please help and suggest anything u have, all are welcome and appreciated.
Thanks! :)
-
I'm not sure about question one, but how much hassle is it to type those four little characters 'DESC' :-)
On the second question, you could use BETWEEN so it would be something like
select a.*
(select st.student_id, to_char(st.register_date, 'dd-mm-yyyy hh24:mi') reg_date, rownum news_row
from student, sb.subject
where st.sudent_id = sb.student_id
and sb.subject_name like '%math%'
and st.register_date between to_date('01-10-2000, 'dd-mm-yyyy') and st.register_date <= to_date('01-11-2000, 'dd-mm-yyyy') + 1
/* error? without + 1 it will not be inclusive */
order by reg_date desc ) a,
student st
where st.student_id = a.student_id
and rownum >= 1
and rownum <= 20
[Edited by m1l on 11-29-2000 at 09:22 AM]
-
The query should not use both rownum > and rownum <.
You may get wrong result set.
-
Well, there are several issues here.
First off, the query you posted was syntactically incorrect. I made changes based on what I assumed you were trying to do.
Next, an index on a column has *nothing* to do with the order of the columns *unless* it is an INDEX ORDERED table on that index. The optimizer *might* just happen to use a plan that starts with that index and you *might* then get the rows back in the order you want. DO NOT count on this!
Next, you were converting the date to a string, *then* sorting on it. in the string, the days came first - hence your sort seemed wierd across months. Save the conversion until the end and it will work.
Next, your comparison of dates was slightly flawed. Based on the usage, I assume that the REGISTER_DATE column holds times as well, despite its name. Given that, you were almost correct with you restriction. It should be ST.REGISTER_DATE < TO_DATE('01-11-2000', 'DD-MM-YYYY') + 1
, not <=, since you don't want the midnight entries from the next day, just everything up to but not including that, correct?
Last, you went to the trouble of alisasing the ROWNUM column from the inner query, then forgot to use the aliased column in the outside query. Remember that a ROWNUM exists at *every* level. Since you just said ROWNUM, and not NEWS_ROW, you were using the outside query's ROWNUM column, not the inner one that you wanted.
Here is the corrected query:
SELECT
A.STUDENT_ID ,
TO_CHAR(A.REGISTER_DATE, 'DD-MM-YYYY HH24:MI')
AS REG_DATE,
FROM
(
SELECT
ST.STUDENT_ID ,
ST.REGISTER_DATE ,
ROWNUM
AS NEWS_ROW
FROM
STUDENT ST,
SUBJECT SB
WHERE
ST.SUDENT_ID = SB.STUDENT_ID AND
SB.SUBJECT_NAME LIKE '%MATH%' AND
ST.REGISTER_DATE >= TO_DATE('01-10-2000', 'DD-MM-YYYY')
ST.REGISTER_DATE < TO_DATE('01-11-2000', 'DD-MM-YYYY') + 1
/* ERROR? WITHOUT + 1 IT WILL NOT BE INCLUSIVE */
ORDER BY
REG_DATE DESC
) A ,
STUDENT ST
WHERE
ST.STUDENT_ID = A.STUDENT_ID AND
NEWS_ROW >= 1 AND
NEWS_ROW <= 20
Hope this helps,
- Chris
-
Thanks! It works for the first 20 rows, however it doesn't for the next 20 rows.
/* doesn't work for next 20 rows*/
SELECT A.STUDENT_ID , TO_CHAR(A.REGISTER_DATE, 'DD-MM-YYYY HH24:MI') AS REG_DATE, NEWS_ROW
FROM
( SELECT ST.STUDENT_ID ,
ST.REGISTER_DATE , ROWNUM AS NEWS_ROW
FROM STUDENT ST, SUBJECT SB
WHERE ST.SUDENT_ID = SB.STUDENT_ID AND
SB.SUBJECT_NAME LIKE '%MATH%' AND
ST.REGISTER_DATE >= TO_DATE('01-10-2000', 'DD-MM-YYYY')
ST.REGISTER_DATE < TO_DATE('01-11-2000', 'DD-MM-YYYY') + 1
ORDER BY REG_DATE DESC ) A ,
STUDENT ST
WHERE ST.STUDENT_ID = A.STUDENT_ID AND
NEWS_ROW >= 20 and
NEWS_ROW <= 40
/
/* this works for next 20 rows, why? */
SELECT B.* FROM
( SELECT A.STUDENT_ID , TO_CHAR(A.REGISTER_DATE, 'DD-MM-YYYY HH24:MI') AS REG_DATE, NEWS_ROW
FROM
( SELECT ST.STUDENT_ID ,
ST.REGISTER_DATE , ROWNUM AS NEWS_ROW
FROM STUDENT ST, SUBJECT SB
WHERE ST.SUDENT_ID = SB.STUDENT_ID AND
SB.SUBJECT_NAME LIKE '%MATH%' AND
ST.REGISTER_DATE >= TO_DATE('01-10-2000', 'DD-MM-YYYY')
ST.REGISTER_DATE < TO_DATE('01-11-2000', 'DD-MM-YYYY') + 1
ORDER BY REG_DATE DESC ) A ,
STUDENT ST
WHERE ST.STUDENT_ID = A.STUDENT_ID ) B
WHERE NEWS_ROW >= 20 and
NEWS_ROW <= 40
By the way, will there be any performance issue for the sql as there is a lot of sorting involved? Thanks! :)
-
Always remember to check the explain plan! :)
The first one should work. There appears to be a little bug in Oracle where it goes the wrong way with ROWNUM queries sometimes. It pushes the ROWNUM restriction into the subquery which, of course, yields the wrong results. The way to get around this is to abstract it yet another level, as you have. Sometimes adding an extra SELECT value 'x' will do it. This forces it to treat the inner SELECT as a view and sometimes changes the plan. You should also be able to hint it. I forget which, maybe the PUSH_SUBQ might do the trick. Either that or NO_MERGE or something. Sorry about that - it usually works. Oracle just gets confused sometimes. You would think that upon noticing a ROWNUM being used, it would know not to mess with the sub-query structuring of the statement. Oh well
As for the sorting overhead issue - It is smaller than going into the table twice. Besides, you had to do some sorting to get the max() all those times. Check the plans and time the satements to make sure, but as long as you are not severely limited on sort area, you should find that my approach is faster.
Hope this helps,
- Chris
- Chris
-
Okay, so I just read my own reply. I referred to MAX() calls when you had none. I confused you with a couple of the other replies I've made this week. Sorry about that.
So to more correctly address your concern about the sorting. While it is true that sorting can be a costly step, you basically have no way around this, right? If you logically need to have the result come back in sorted order, then you have to use the ORDER BY. Now, can you alleviate the pain? Sure. First, make sure you have a large enough SORT_AREA_SIZE to handle the sorting in memory. Sorting in memory is boatloads better than sorting on disk.
Next, as you alluded in your original post, can an index help? Yes. If the table is INDEX ORDERED on an index that matches the order you want and you table-scan the table or use that index, you save the sort step. Also, if you have an index that matches your ORDER BY and it is the driving index, you will also save the sort step. NEVER remove the ORDER BY. IF the optimizer does not *have* to sort it, it won't - don't worry. Mind you, if after using this index, you do a GROUP BY or something similar, then all bets are off :)
In your case, you are probably ok. The query will start with the SB table (since no restriction on ST table) and the only usable restriction is the one on the REGISTER_DATE field. So as long as your range is short (so the optimizer will actually use the index as opposed to a table scan), then you are good to go. Without the tables, I can't guarantee this, but I would guess that the optimizer is going to use your index and not have to take the time to sort the results because the index order matches the ORDER BY order.
Hope this helps,
- Chris
-
Thank you Chris for your advices and help :)
