This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 16, 2012 1:33 AM by 969801 RSS

how can i ,rewrite this sql!

969801 Newbie
Currently Being Moderated
i have a sql like this :

<div class="jive-quote">
CREATE TABLE TEST(NAME VARCHAR2(10),BOOK NUMBER,FANS NUMBER,BLOG NUMBER);

INSERT INTO TEST VALUES('A',11,13,14);
INSERT INTO TEST VALUES('b',14,15,19);
INSERT INTO TEST VALUES('c',10,5,15);
INSERT INTO TEST VALUES('m',12,19,10);
INSERT INTO TEST VALUES('n',11,13,11);
INSERT INTO TEST VALUES('o',15,16,12);
INSERT INTO TEST VALUES('p',11,15,12);
INSERT INTO TEST VALUES('q',22,23,21);
INSERT INTO TEST VALUES('e',21,3,4);
INSERT INTO TEST VALUES('s',1,17,16);
INSERT INTO TEST VALUES('d',31,24,34);
INSERT INTO TEST VALUES('f',22,23,34);
insert into test values('t',42,43,5);


SELECT * FROM (SELECT * FROM TEST ORDER BY BOOK desc) WHERE ROWNUM=1
UNION ALL
SELECT * FROM (SELECT * FROM TEST ORDER BY FANS DESC) WHERE ROWNUM=1
UNION ALL
select * from (select * from test order by blog desc) where rownum=1;</div>


how can i reduce the times of full scan!


i used row_number() or rank() over function !

<div class="jive-quote">

SELECT * FROM
( SELECT TEST.*,RANK() OVER(ORDER BY BOOK DESC) RN1,RANK() OVER(ORDER BY FANS DESC)RN2 ,RANK()OVER(ORDER BY BLOG) RN3 FROM TEST)
where rn1=1 or rn2=1 or rn3=1;
</dev>


it returns two rows ,it doesn't match what i need;


<div class="jive-quote">

WITH T AS (SELECT TEST.*,RANK() OVER(ORDER BY BOOK DESC) RN1,RANK() OVER(ORDER BY FANS DESC)RN2 ,RANK()OVER(ORDER BY BLOG) RN3 FROM TEST)
SELECT * FROM T WHERE RN1=1
UNION ALL
SELECT * FROM T WHERE RN2=1
UNION ALL
select * from T where rn3=1



the original :

Statistics
----------------------------------------------------------
568 recursive calls
0 db block gets
1008379 consistent gets
79728 physical reads
0 redo size
1834 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
6 rows processed


when i changed my sql ,use with clause
Statistics
----------------------------------------------------------
4000 recursive calls
287 db block gets
72221 consistent gets
7782 physical reads
1360 redo size
1834 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
69 sorts (memory)
0 sorts (disk)
6 rows processed


</dev>

Edited by: 966798 on 2012-11-16 上午1:36

Edited by: 966798 on 2012-11-16 上午1:49
  • 1. Re: how can i ,rewrite this sql!
    Purvesh K Guru
    Currently Being Moderated
    Use Analytic functions (RANK, DENSE_RANK etc. shall fulfil your purpose)
    select *
      from (
            select e.*, row_number() over (order by sal desc) rn_sal, row_number() over (order by hire_date desc) rn_hire
              from emp e
           ) a
     where a.rn_sal = 1 and a.rn_hire = 1;
  • 2. Re: how can i ,rewrite this sql!
    jeneesh Guru
    Currently Being Moderated
    966798 wrote:
    i have a sql like this :


    select from ( select * ,rownum rn from emp order by sal desc ) where rn=1*
    union all
    select from ( select * ,rownum rn from emp order by hiredate desc ) where rn=1*
    Your above code will not work, it will give an error
    select * from ( select * ,rownum rn from emp order by sal desc ) where rn=1
    union all
    select * from ( select * ,rownum rn from emp order by hiredate desc ) where rn=1;
    
    ORA-00923: FROM keyword not found where expected
    And even if it works, you cannot identify which employee is highst paid and which one is the senior most - coz, you dont have an identifier to differentiate them in the above query.

    You can write something like this using analytic function ROW_NUMBER

    with emp_data as
    (
     select e.*,row_number() over (order by sal desc nulls last) sal_rn,
            row_number() over (order by hiredate  desc nulls last) dt_rn
     from emp e
    )
    select *
    from emp_data
    where sal_rn = 1 or dt_rn = 1;
    
    EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO SAL_RN DT_RN
    ----- ---------- --------- ---- --------- ------- ------- ------ ------ -----
     7876 ADAMS      CLERK     7788 23-MAY-87    1100             20     12     1 
     7839 KING       PRESIDENT      17-NOV-81    5000             10      1     6 
    Again, in this code, if two employees are having same highest salary (or most senior), you will get only one of them in the output.To get all the rows, you can use rank
    update emp set sal=5000 where rownum <= 2;
    
    2 rows updated.
    
    with emp_data as
    (
     select e.*,rank() over (order by sal desc nulls last) sal_rn,
            rank() over (order by hiredate  desc nulls last) dt_rn
     from emp e
    )
    select *
    from emp_data
    where sal_rn = 1 or dt_rn = 1;
    
    EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO SAL_RN DT_RN
    ----- ---------- --------- ---- --------- ------- ------- ------ ------ -----
     7876 ADAMS      CLERK     7788 23-MAY-87    1100             20     13     1 
     7839 KING       PRESIDENT      17-NOV-81    5000             10      1     6 
     7499 ALLEN      SALESMAN  7698 20-FEB-81    5000     300     30      1    13 
     7369 SMITH      CLERK     7902 17-DEC-80    5000             20      1    14 
    Read more about analytic functions

    And more about Top-N queris
  • 3. Re: how can i ,rewrite this sql!
    jeneesh Guru
    Currently Being Moderated
    Purvesh K wrote:
    where a.rn_sal = 1 and a.rn_hire = 1;
    And..?

    Typo?
  • 4. Re: how can i ,rewrite this sql!
    969801 Newbie
    Currently Being Moderated
    thank you so much


    it works , and i still have a question about order by ?


    my sql is like this ,



    select * from ( select * from emp order by sal) where rownum<10; it works perfect


    but when i add a another column into the order by clause



    select * from ( select * from emp order by sal,hiredate) where rownum<10

    it became very slow ,is there anything i cat do ?
  • 5. Re: how can i ,rewrite this sql!
    969801 Newbie
    Currently Being Moderated
    yes ,i made a mistake !



    thank you !

    Edited by: 966798 on 2012-11-15 下午9:22
  • 6. Re: how can i ,rewrite this sql!
    969801 Newbie
    Currently Being Moderated
    thk you again, this question puzzle me many days , first i use row_number function,as you say ,i will get less than i expect;
  • 7. Re: how can i ,rewrite this sql!
    Purvesh K Guru
    Currently Being Moderated
    jeneesh wrote:
    Purvesh K wrote:
    where a.rn_sal = 1 and a.rn_hire = 1;
    And..?

    Typo?
    Oops... Yes :)
  • 8. Re: how can i ,rewrite this sql!
    Purvesh K Guru
    Currently Being Moderated
    966798 wrote:
    select * from ( select * from emp order by sal) where rownum<10; it works perfect
    but when i add a another column into the order by clause

    select * from ( select * from emp order by sal,hiredate) where rownum<10

    it became very slow ,is there anything i cat do ?
    How slow it is?
    Did you check the timing?
    Did you check the explain plan for the query?

    I do not think adding another column will degrade the performance by great lengths, because by "order by sal, hiredate" means it will order by SAL first and for matching SAL, it will order by HIREDATE; So yes, there may be some performance penalty but it should not enough to become un-acceptable.
    Please check the questions I asked, you might yourself find the answer. If not, please post a new question by reading {message:id=3292438}.
  • 9. Re: how can i ,rewrite this sql!
    969801 Newbie
    Currently Being Moderated
    select * from (select * from emp order by hiredate desc) where rownum=1;

    the result:


    7876     ADAMS     CLERK     7788     23-5月 -87     1100          20



    when i update the sal of 7876,make it the highest one


    update emp set sal=8000 where empno=7876;


    and then

    i write a sql like this:


    select * from (

    select emp.* ,rank() over(order by sal) rn1,rank() over(order by hiredate) rn2 from emp
    )

    where rn1=1 or rn2=1


    it will return one rows?


    my application is a web,the rows woult not match!

    Edited by: 966798 on 2012-11-15 下午10:09

    Edited by: 966798 on 2012-11-15 下午10:09
  • 10. Re: how can i ,rewrite this sql!
    jeneesh Guru
    Currently Being Moderated
    Did you need two rows?
    with emp_data as
    (
     select e.*,rank() over (order by sal desc nulls last) sal_rn,
            rank() over (order by hiredate  desc nulls last) dt_rn
     from emp e
    )
    select *
    from emp_data
    where sal_rn = 1 
    union all
    select *
    from emp_data
    where dt_rn = 1;
    
    EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO SAL_RN DT_RN
    ----- ---------- --------- ---- --------- ------- ------- ------ ------ -----
     7876 ADAMS      CLERK     7788 23-MAY-87    8000             20      1     1 
     7876 ADAMS      CLERK     7788 23-MAY-87    8000             20      1     1 
  • 11. Re: how can i ,rewrite this sql!
    Purvesh K Guru
    Currently Being Moderated
    966798 wrote:
    select * from (select * from emp order by hiredate desc) where rownum=1;

    the result:

    7876     ADAMS     CLERK     7788     23-5月 -87     1100          20
    when i update the sal of 7876,make it the highest one
    update emp set sal=8000 where empno=7876;
    i write a sql like this:


    select * from (

    select emp.* ,rank() over(order by sal) rn1,rank() over(order by hiredate) rn2 from emp
    )

    where rn1=1 or rn2=1


    it will return one rows?
    Not necessarily.

    Again, you have used "order by sal", this will order the records in Ascending order and the record you just updated will be the last. Hence, this does not ensure single record.

    And when you have used the OR condition in your where predicate, it will not ensure a single record too. You will have to change it to AND at the risk of hampering the Business requirement.
    Thinking logically, why do need a single record? An employee with Highest earning Salary need not be the Oldest employee in an organization, isn't it?
  • 12. Re: how can i ,rewrite this sql!
    969801 Newbie
    Currently Being Moderated
    yes , you are right !

    but in fact , i have a table that have these columns !


    desc v_listinfo

    HS_FANSNUM NOT NULL NUMBER ---fans number
    HS_BOOKNUM NOT NULL NUMBER ---booking number
    HS_BOLGNUM NOT NULL NUMBER ---blogs


    i have to find out,the max(hs_fansnum) ,max(hs_booknum) ....records, theat may be return same record!


    i am thinking about using with clause ! but phsical reads will generate ,
  • 13. Re: how can i ,rewrite this sql!
    Purvesh K Guru
    Currently Being Moderated
    966798 wrote:
    desc v_listinfo

    HS_FANSNUM NOT NULL NUMBER ---fans number
    HS_BOOKNUM NOT NULL NUMBER ---booking number
    HS_BOLGNUM NOT NULL NUMBER ---blogs

    i have to find out,the max(hs_fansnum) ,max(hs_booknum) ....records, theat may be return same record!
    I understood that you have to find hte Max of HS_FANSNUM and HS_BOOKNUM but not the further part of statement. You should elaborate it by providing the Create Table, Insert Into statements and the Expected output from the Sample data.

    >
    i am thinking about using with clause ! but phsical reads will generate ,
    Why do you think WITH clause will generate Physical Reads but the SQL query will not? Can you base your statement with any proof from TKPROF etc.?
    That, IMO, is a mis-conception that you should get rid of immediately.

    It would be appreciated, if you can post a new question for the above query, with all the details. Since this thread has been marked as Answered, not many people will focus on it.
  • 14. Re: how can i ,rewrite this sql!
    969801 Newbie
    Currently Being Moderated
    top -n
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points