Category: Blogroll

Have fun with this caps-lock user question :

asktom:Logic behind the DENSE_RANK

This is one more statement on how to not use order by

like in

 
select ename, deptno, row_number() over (order by 1) 
from emp order by 2;
ENAME          DEPTNO ROW_NUMBER()OVER(ORDERBY1)
---------- ---------- --------------------------
CLARK              10                          1
KING               10                          2
MILLER             10                          3
JONES              20                          4
FORD               20                          5
ADAMS              20                          6
SMITH              20                          7
SCOTT              20                          8
WARD               30                          9
TURNER             30                         10
ALLEN              30                         11
JAMES              30                         12
BLAKE              30                         13
MARTIN             30                         14

According to the doc, order by position is invalid. Actually, order by 1 is treated as order by ‘bananas’.
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

Thanks Tom for being so funny 🙂

How to detect cycling records in 9i, remember CONNECT BY NOCYCLE does not exist in 9i

SQL> create table lsc_t as
  2  select 1 parent, 2 child from dual
  3  union all select 2,3 from dual
  4  union all select 4,5 from dual
  5  union all select 5,6 from dual
  6  union all select 6,4 from dual;

Table created.

SQL> select parent,child
  2  from lsc_t
  3  where level=50
  4  connect by parent=prior child
  5  and level<=50
  6  and prior dbms_random.value != dbms_random.value;

    PARENT      CHILD
---------- ----------
         5          6
         6          4
         4          5

Ok, it is a bit abusing the connect by operator, but it is for hierarchic purpose

Oh no, not another post about COUNT(*) and COUNT(1)

Well, it is not exactly the case. I just had the issue this morning that count(*) was too slow.

SQL> create table t as select rownum id,
  2    lpad('a',4000,'a') a,
  3    lpad('b',4000,'b') b,
  4    lpad('c',4000,'c') c,
  5    lpad('d',4000,'d') d
  6  from dual connect by level<10000;

Table created.

SQL> create index i on t(id);

Index created.

SQL> alter table t add primary key (id) 
  2    using index i disable novalidate;

Table altered.

SQL> exec dbms_stats.gather_table_stats(user,'T')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(user,'I')

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> select count(id) from t;

 COUNT(ID)
----------
      9999

Elapsed: 00:00:00.01
SQL> select count(*) from t;

  COUNT(*)
----------
      9999

Elapsed: 00:00:01.43

My count(*) just takes too long… Why that?

I have no index on a not null column. The primary key is disabled.

Of course the count(pk) does not count the rows with pk=null, but it is faster 🙂

How do I retrieve the second highest salary of emp?

There is no right or wrong treatment of duplicates, there are only buggy specifications…

There are plenty of ways to do this.

An Oracle 7 approach would be

SELECT MAX(SAL) 
FROM EMP, (
  SELECT MAX(SAL) MAXSAL FROM EMP) 
WHERE SAL<MAXSAL;

Using TOP-n queries I could use

 
SELECT SAL 
FROM (
  SELECT ROWNUM R,SAL FROM (
    SELECT SAL FROM EMP ORDER BY SAL DESC)) 
WHERE R=2;

Using ROW_NUMBER (or RANK or DENSE_RANK), I could use

SELECT SAL 
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) R,SAL FROM EMP) 
WHERE R=2;

Another approach would be to create a SecondMax aggregate function, as the following example from the doc
Example 11-12 How to Create and Use a User-Defined Aggregate Function

SELECT SecondMax(sal)
FROM emp;

Ok, if I know I have 14 employees in EMP, retrieving the second highest is way easier 🙂

SELECT PERCENTILE_DISC(2/14) WITHIN GROUP (ORDER BY SAL DESC) 
FROM EMP;

I answered a question on otn today about distinct.

Reprased, how to select distinct collection?

select job, collect(distinct deptno) deptnos 
from emp group by job;

JOB       DEPTNOS
--------- -------------------------------------------
ANALYST   SYSTPTJCzBffh0AjgQ59n0o3QCA==(20)
CLERK     SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30)
MANAGER   SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30)
PRESIDENT SYSTPTJCzBffh0AjgQ59n0o3QCA==(10)
SALESMAN  SYSTPTJCzBffh0AjgQ59n0o3QCA==(30)

5 rows selected.

select distinct collect(distinct deptno) deptnos 
from emp group by job;

ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method

the message is clear, i need a MAP or ORDER method. Ok, I can do this

create type tt_n as table of number(2);
/

Type created.

create type tt_n_ord as object(
  c tt_n,
  order member function 
    eq (tc tt_n_ord) return integer);
/

Type created.

create type body tt_n_ord as
  order member function 
    eq (tc tt_n_ord) return integer
  is
  begin 
    if c=tc.c 
    then return 0; 
    else return 1; 
    end if;
  end;
end;
/

Type body created.

select distinct 
  tt_n_ord(cast(collect(distinct deptno) as tt_n))
from emp group by job;

TT_N_ORD(CAST(COLLECT(DISTINCTDEPTNO)ASTT_N))(C)
------------------------------------------------
TT_N_ORD(TT_N(30))
TT_N_ORD(TT_N(10))
TT_N_ORD(TT_N(10, 20, 30))
TT_N_ORD(TT_N(20))

4 rows selected.

Today I answered a question on otn regarding order by.

Let’s build a different test case.

You have a view that contain an order by. You want to retrieve the last 5 records. However you do not have access to the sort column.

SYS@LSC01> GRANT CREATE VIEW TO USER_A IDENTIFIED BY A;

Grant succeeded.

SYS@LSC01> GRANT SELECT ON SCOTT.EMP TO USER_A WITH GRANT OPTION;

Grant succeeded.

SYS@LSC01> CREATE VIEW USER_A.V AS SELECT ENAME FROM SCOTT.EMP 
ORDER BY HIREDATE;

View created.

SYS@LSC01> GRANT CREATE SESSION TO USER_B IDENTIFIED BY B;

Grant succeeded.

SYS@LSC01> GRANT SELECT ON USER_A.V TO USER_B;

Grant succeeded.

SYS@LSC01> CONNECT USER_B/B
Connected.
USER_B@LSC01> SELECT ROWNUM, ENAME FROM USER_A.V WHERE ROWNUM<4;
    ROWNUM ENAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD

To get top 3, that was easy 8)

USER_B@LSC01> SELECT R, ENAME FROM
  2    (SELECT ROWNUM R,ENAME
  3    FROM USER_A.V
  4    ORDER BY ROWNUM DESC)
  5  WHERE ROWNUM<4 ORDER BY R;
         R ENAME
---------- ----------
        12 MILLER
        13 SCOTT
        14 ADAMS

Here we have a sorted view. The OP had an external file. It could be an XML input, or any table or collection that is sorted but does not have a sort key

What is worst? To use DD-MON-RR or to use DD-MON-YY? When entering the birthday of my grandfather, I will write it as 31-JUL-1912, so both formats will be fine. If I enter it 31-JUL-12, both formats will be wrong. Ok, which date will I enter now and in the future? For short-time contracts I will enter dates like 01-AUG-08 or 31-DEC-11, both formats will be fine. For long time contracts like retirement saving plan I will enter 31-MAR-36, which is still fine, because I am old enough Juniors in my company will enter dates like 30-JUN-52, which will be fine with YY and wrong with RR or RRRR.

Ok, what is the recommended format then?

DD-MM-FXYYYY is probably fine. FX forces you to enter 4-digits years.

SQL> select
  2    to_date('31-JUL-1912','DD-MON-YY') YY1912,
  3    to_date('31-JUL-12','DD-MON-YY') YY12,
  4    to_date('31-JUL-12','DD-MON-RR') RR12
  5  from dual;

YY1912     YY12       RR12
---------- ---------- ----------
1912-07-31 2012-07-31 2012-07-31

SQL> select
  2    to_date('31-MAR-2036','DD-MON-YY') YY2036,
  3    to_date('31-MAR-36','DD-MON-YY') YY36,
  4    to_date('31-MAR-36','DD-MON-RR') RR36
  5  from dual;

YY2036     YY36       RR36
---------- ---------- ----------
2036-03-31 2036-03-31 2036-03-31

SQL> select
  2    to_date('30-JUN-2052','DD-MON-YY') YY2052,
  3    to_date('30-JUN-52','DD-MON-YY') YY52,
  4    to_date('30-JUN-52','DD-MON-RR') RR52
  5  from dual;

YY2052     YY52       RR52
---------- ---------- ----------
2052-06-30 2052-06-30 1952-06-30

SQL> select
  2    to_date('8/4/2008','DD-MM-FXYYYY') FXYYYY2008
  3  from dual;

FXYYYY2008
----------
2008-04-08

SQL> select
  2    to_date('08-04-08','DD-MM-FXYYYY') FXYYYY08
  3  from dual;
  to_date('08-04-08','DD-MM-FXYYYY') FXYYYY08
          *
ERROR at line 2:
ORA-01862: the numeric value does not match the length 
of the format item

SQL>