Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, William.

Asked: June 21, 2002 - 8:42 am UTC

Last updated: February 01, 2013 - 3:26 pm UTC

Version: 8.1.7

Viewed 100K+ times! This question is

You Asked

Hi Tom,
Could you please tell me how can I determine if a column is greater
or less than a value inside DECODE. I put < or > before a number but
I was told it was invalid. (eg. Decode(sale_amount, > 100000, 'High Level') ) Thanks

Bill

and Tom said...

Well, in 817, I would just use CASE:


select CASE when sale_amount > 250000, 'Really High Level'
when sale_amount > 100000, 'High Level'
else 'Not so high'
END
from t


Using decode, we can do this with SIGN:

select decode( sign( 100000 - sale_amount ) ,
-1, 'High Level',
'Not so high' )
from t;

Rating

  (39 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 21, 2002 - 3:17 pm UTC

Should we use When ... then instead of When ... , ?

Tom Kyte
June 21, 2002 - 4:23 pm UTC

scott@ORA817DEV.US.ORACLE.COM> select CASE when sal > 250000 then 'Really High Level'
2 when sal > 100000 then 'High Level'
3 else 'Not so high'
4 END
5 from emp
6 /

yes, don't know what I was thinking with the comma ;)



Be careful with CASE in pre-9i

Colin Davies, June 21, 2002 - 4:47 pm UTC

The CASE SQL statement will NOT work in PL/SQL until 9i.

Tom Kyte
June 21, 2002 - 6:35 pm UTC

It is easy to use it however in 8i. You can

a) use a view -- very nice for encapsulation anyway
b) use a ref cursor

begin
open l_cursor for 'select CASE .....' using <binds>;

loop
fetch l_cursor into ....;




CASE in 8i

John Ridgway, June 21, 2002 - 4:57 pm UTC

I have used the CASE statement in PL/SQL in 8.1.7 without any problems. The trick is using dynamic SQL.



How to do using "DECODE"

Suresh, June 21, 2002 - 5:19 pm UTC

Tom,
How to get the following output by using "DECODE" command.

select empno, ename, SAL,
CASE when SAL > 4000 THEN 'ABOVE 4000'
when SAL > 2500 THEN 'ABOVE 2500'
ELSE 'VERY LESS' END
FROM EMP;


EMPNO ENAME SAL CASEWHENSA
--------- ---------- ---------- ----------
7369 SMITH 800 VERY LESS
7499 ALLEN 1600 VERY LESS
7521 WARD 1250 VERY LESS
7566 JONES 2975 ABOVE 2500
7654 MARTIN 1250 VERY LESS
7698 BLAKE 2850 ABOVE 2500
7782 CLARK 2450 VERY LESS
7788 SCOTT 3000 ABOVE 2500
7839 KING 5000 ABOVE 4000
7844 TURNER 1500 VERY LESS
7876 ADAMS 1100 VERY LESS
7900 JAMES 950 VERY LESS
7902 FORD 3000 ABOVE 2500
7934 MILLER 1300 VERY LESS

I tried with the following statement, but I have issues with SALARY 5000

select EMPNO, ENAME, SAL,
decode( sign( 2500 - SAL ), -1, 'ABOVE 2500','VERY LESS') from EMP;

Thanks,
Suresh



Tom Kyte
June 21, 2002 - 6:47 pm UTC

scott@ORA817DEV.US.ORACLE.COM> select empno, ename, SAL,
2 decode( sign(4000-sal),
3 -1, 'Above 4000',
4 decode( sign(2500-sal),
5 -1, 'Above 2500', 'Very Less' ) ),
6 CASE when SAL > 4000 THEN 'ABOVE 4000'
7 when SAL > 2500 THEN 'ABOVE 2500'
8 ELSE 'VERY LESS' END
9 FROM EMP;

EMPNO ENAME SAL DECODE(SIG CASEWHENSA
---------- ---------- ---------- ---------- ----------
7369 SMITH 800 Very Less VERY LESS
7499 ALLEN 1600 Very Less VERY LESS
7521 WARD 1250 Very Less VERY LESS
7566 JONES 2975 Above 2500 ABOVE 2500
7654 MARTIN 1250 Very Less VERY LESS
7698 BLAKE 2850 Above 2500 ABOVE 2500
7782 CLARK 2450 Very Less VERY LESS
7788 SCOTT 3000 Above 2500 ABOVE 2500
7839 KING 5000 Above 4000 ABOVE 4000
7844 TURNER 1500 Very Less VERY LESS
7876 ADAMS 1100 Very Less VERY LESS
7900 JAMES 950 Very Less VERY LESS
7902 FORD 3000 Above 2500 ABOVE 2500
7934 MILLER 1300 Very Less VERY LESS

14 rows selected.

Why use decode for ranges?

John Ridgway, June 21, 2002 - 6:23 pm UTC

In my experience, decode is good for getting one value to be replaced with another (a decode). Doing ranges seems to be better handled with CASE statements. If you have the query working using a CASE statement then why try to get it to work using a DECODE? If it's a problem of getting it to work with PL/SQL then just use dynamic SQL. Tom has got an example here.
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:948277183607,%7BPLS%7D%20and%20%7B00103%7D%20and%20%7BEncountered%7D%20and%20%7Bthe%7D%20and%20%7Bsymbol%7D%20and%20%7BCASE%7D <code>



Tom Kyte
June 21, 2002 - 7:09 pm UTC


Agreed -- before 816 however, there was no other option.

How to do this?

Sikandar Hayat Awan, June 22, 2002 - 2:16 am UTC

Hi,
The following is a table and my requirement is R - I (Receipt - Issue) for each code.
CODE T TDATE QTY
------------ - ---------- ------------
1 I 29/05/2002 2
1 I 28/05/2002 6
1 R 28/05/2002 15
1 R 29/05/2002 52
1 R 28/05/2002 5
2 R 28/05/2002 45
3 R 28/05/2002 3
3 R 28/05/2002 12
1 I 28/05/2002 1
1 I 31/05/2002 2
1 I 31/05/2002 6
1 I 28/05/2002 1
1 R 31/05/2002 15
1 R 31/05/2002 52
1 R 28/05/2002 5
2 R 28/05/2002 45
3 R 28/05/2002 3
3 R 28/05/2002 12

Thanks for ur co.

Tom Kyte
June 22, 2002 - 9:36 am UTC

ops$tkyte@ORA817DEV.US.ORACLE.COM> select code,
  2         sum( decode( type, 'I', qty, 0 ) ) itype,
  3         sum( decode( type, 'R', qty, 0 ) ) rtype,
  4         sum( decode( type, 'R', qty, 0 ) ) -sum( decode( type, 'I', qty, 0 ) ) diff
  5    from t
  6   group by code
  7  /

      CODE      ITYPE      RTYPE       DIFF
---------- ---------- ---------- ----------
         1         18        144        126
         2          0         90         90
         3          0         30         30

ops$tkyte@ORA817DEV.US.ORACLE.COM> 

One More Way

ANUP SINGH, June 22, 2002 - 9:26 am UTC

I Think we Can Also Use Greatest And Least Sql Functions ..

Select Decode( Greatest( Sale_Amount, 100000), Sale_Amount, 'high Level', 'Not So High' )
From T ;


Tom Kyte
June 22, 2002 - 9:56 am UTC

yes, it is not any more readable. I would still go with CASE personally.

scott@ORA817DEV.US.ORACLE.COM> select empno, ename, SAL,
2 decode( sign(4000-sal),
3 -1, 'Above 4000',
4 decode( sign(2500-sal),
5 -1, 'Above 2500', 'Very Less' ) ),
6 decode( greatest(sal,4000),
7 4000, decode( greatest(sal,2500),
8 2500, 'Very Less', 'Above 2500' ), 'Above 4000' ),
9 CASE when SAL > 4000 THEN 'ABOVE 4000'
10 when SAL > 2500 THEN 'ABOVE 2500'
11 ELSE 'VERY LESS' END
12 FROM EMP;

EMPNO ENAME SAL DECODE(SIG DECODE(GRE CASEWHENSA
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 800 Very Less Very Less VERY LESS
7499 ALLEN 1600 Very Less Very Less VERY LESS
7521 WARD 1250 Very Less Very Less VERY LESS
7566 JONES 2975 Above 2500 Above 2500 ABOVE 2500
7654 MARTIN 1250 Very Less Very Less VERY LESS
7698 BLAKE 2850 Above 2500 Above 2500 ABOVE 2500
7782 CLARK 2450 Very Less Very Less VERY LESS
7788 SCOTT 3000 Above 2500 Above 2500 ABOVE 2500
7839 KING 5000 Above 4000 Above 4000 ABOVE 4000
7844 TURNER 1500 Very Less Very Less VERY LESS
7876 ADAMS 1100 Very Less Very Less VERY LESS
7900 JAMES 950 Very Less Very Less VERY LESS
7902 FORD 3000 Above 2500 Above 2500 ABOVE 2500
7934 MILLER 1300 Very Less Very Less VERY LESS

14 rows selected.



Case or decode

Sa?a, June 24, 2002 - 11:23 am UTC

Hi Tom,

Just want to compare one example with "case" and "decode".

1.
select
decode((select deptno from dept d where d.deptno = e.deptno), 10,'A', 20, 'B', 30, 'REST' ) as test
from emp e

2.
select
(case
when (select deptno from dept d where d.deptno = e.deptno) = 10
then 'A'
when (select deptno from dept d where d.deptno = e.deptno) = 20
then 'B'
else 'REST'
end) as test
from emp e

How many times inline select would be executed in "case" example (per record )? Is it better to use decode in such cases although I prefer "case" because it's much more readably then "decode".

Thanks

Sasa

Tom Kyte
June 25, 2002 - 8:26 am UTC

Well, here is a pretty cool optimization.  The answer is "none of the above" -- it apparently doesn't do the case per record as you and I understand it.  

In any case, the decode you have would be superior to the case statement performance wise HOWEVER, in 9i, there is a searched case (demonstrated below) which corrects that.  

Consider this example:


ops$tkyte@ORA9I.WORLD> create or replace package demo_pkg
  2  as
  3     cnt number;
  4     function f( x in number ) return number;
  5  end;
  6  /

Package created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace package body demo_pkg
  2  as
  3  
  4  function f( x in number ) return number
  5  is
  6  begin
  7      cnt := cnt + 1;
  8      return x;
  9  end;
 10  
 11  end;
 12  /

Package body created.

ops$tkyte@ORA9I.WORLD> drop table emp;
Table dropped.

ops$tkyte@ORA9I.WORLD> drop table dept;
Table dropped.

ops$tkyte@ORA9I.WORLD> create table emp
  2  as
  3  select rownum empno, mod(rownum,10) deptno
  4    from all_objects
  5   where rownum <= 500;

Table created.

ops$tkyte@ORA9I.WORLD> create table dept
  2  as
  3  select rownum-1 deptno
  4    from all_objects
  5   where rownum <= 10;

Table created.

ops$tkyte@ORA9I.WORLD> set autotrace traceonly

ops$tkyte@ORA9I.WORLD> exec demo_pkg.cnt := 0;
PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> select decode( (select demo_pkg.f(deptno)
  2                    from dept
  3                   where deptno = emp.deptno), 10, 'a', 20, 'b', 'rest' ) as test
  4    from emp;

500 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
         38  recursive calls
         22  db block gets
        127  consistent gets
          5  physical reads
          0  redo size
      20342  bytes sent via SQL*Net to client
       4826  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
        500  rows processed

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> exec dbms_output.put_line( 'cnt = ' || demo_pkg.cnt );
cnt = 10

PL/SQL procedure successfully completed.

<b>So here, it executed our query 10 times -- the number of dept rows (if you vary the number of rows in dept -- this count will vary accordingly).  We might have expected it to do this 500 times but no - only 10.  It apparently built a hash table out there (or some other structure) and just joined to that....</b>

ops$tkyte@ORA9I.WORLD> exec demo_pkg.cnt := 0;
PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> select (case
  2            when (select demo_pkg.f(deptno)
  3                    from dept
  4                   where deptno = emp.deptno) = 10 then 'a'
  5            when (select demo_pkg.f(deptno)
  6                    from dept
  7                   where deptno = emp.deptno) = 20 then 'b'
  8            else 'rest'
  9          end) as test
 10    from emp;

500 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
          0  recursive calls
         42  db block gets
        181  consistent gets
          0  physical reads
          0  redo size
      20342  bytes sent via SQL*Net to client
       4826  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        500  rows processed

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> exec dbms_output.put_line( 'cnt = ' || demo_pkg.cnt );
cnt = 20

PL/SQL procedure successfully completed.

<b>Here, we have 2x the calls (but still not 500)</b>


ops$tkyte@ORA9I.WORLD> exec demo_pkg.cnt := 0;

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> select (case (select demo_pkg.f(deptno)
  2                  from dept
  3                 where deptno = emp.deptno)
  4            when 10 then 'a'
  5            when 20 then 'a'
  6            else 'rest'
  7          end) as test
  8    from emp;

500 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
          0  recursive calls
         22  db block gets
        111  consistent gets
          0  physical reads
          0  redo size
      20342  bytes sent via SQL*Net to client
       4826  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        500  rows processed

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> exec dbms_output.put_line( 'cnt = ' || demo_pkg.cnt );
cnt = 10

PL/SQL procedure successfully completed.

<b>and there is the searched case expression that gets it back to normal..</b>
 

Great

Sikandar Hayat Awan, June 27, 2002 - 10:31 pm UTC


SINGLE INSERT FROM MULTIPLE TABLE

vinodhps, June 18, 2003 - 6:40 am UTC

Hi Tom,
iam trying to insert into single table from 2 select statements. Is it possible .

For example i got table t_raw_matl in which i have to insert.

DESC T_RAW_MATL

IND_MATL_DAY NUMBER
IMP_MATL_DAY NUMBER
IND_MATL_MONTH NUMBER
IMP_MATL_MONTH NUMBER
IND_MATL_YEAR NUMBER
IMP_MATL_YEAR NUMBER

other 2 tables are 1)t_indegenious_raw_matl.
2)t_imported_raw_matl.

Here what iam doing is

1)select sum(qty) from t_indegenious_raw_matl;
2)select sum(qty) from t_imported_raw_matl;

Is it possible to DO

insert into t_raw_matl
CASE WHEN (SOME CONDITION) THEN
select SUM(CASE WHEN TRUNC(DATE_OF_CONS)=TRUNC(SYSDATE)
THEN Qty
ELSE 0
END)DAY,
SUM(CASE WHEN TRUNC(DATE_OF_CONS,'MM')=TRUNC(SYSDATE,'MM')
THEN Qty
ELSE 0
END)MONTH from t_indegenious_raw_matl
ELSE
select SUM(CASE WHEN TRUNC(DATE_OF_CONS)=TRUNC(SYSDATE)
THEN Qty
ELSE 0
END)DAY,
SUM(CASE WHEN TRUNC(DATE_OF_CONS,'MM')=TRUNC(SYSDATE,'MM')
THEN Qty
ELSE 0
END)MONTH from t_imported_raw_matl
END;

OR

insert into t_raw_matl
select sum(qty) from t_indegenious_raw_matl
select sum(qty) from t_imported_raw_matl;

IS THERE ANY EASY METHOD TO DO THIS TOM.

Thanks

Tom Kyte
June 18, 2003 - 6:15 pm UTC

union all?

Case Case Case...

Sachin, July 15, 2003 - 5:51 pm UTC

Hi tom....
I wrote this query..it does what it needs to:
We need to send this query a date...and this is what it should do:
> 0 < 1 -- LESS THAN 1 MONTH
>= 1 < 2 -- 1 MONTH
>= 2 < 3 -- 2 MONTHS
>= 3 < 4 -- 3 MONTHS
>= 4 < 5 -- 4 MONTHS
....
.....
>= 11 < 12 -- 11 months
>= 12 < 24 -- 1 year
>= 24 < 36 -- 2 years
>= 36 < 48 -- 3 years
......
........

I was just wondering..if there is a better way to write it..I will be converting into a function:
SELECT CASE WHEN FLOOR((FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE ('07-JUL-2003'))))/12) > 0 THEN
CASE
WHEN FLOOR((FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE ('07-JUL-2003'))))/12) = 1 THEN 1 || ' Year'
ELSE FLOOR((FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE ('07-JUL-2003'))))/12) || ' Years'

END
WHEN FLOOR((FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE ('07-JUL-2003'))))/12) = 0 THEN
CASE
WHEN FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE ('07-JUL-2003'))) = 0 THEN 'Less than 1 Month'
WHEN FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE ('07-JUL-2003'))) = 1 THEN '1 Month'
ELSE FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE ('07-JUL-2003'))) || ' Months'
END
ELSE NULL
END
FROM DUAL;



Tom Kyte
July 15, 2003 - 5:57 pm UTC

no version, oh well

i would probably use an INLINE view to hide the floor(months.....)

so the query would be:


select case when N < 1 then .....
...
from ( select FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE('07-JUL-2003')) N
from dual );



case

Sachin, July 16, 2003 - 8:33 am UTC

Sorry...about the version..
8.1.7.4

Thanks...

Chuck Jolley, July 16, 2003 - 9:54 am UTC

This is how I write these using decode.
It seems easier to read to me than nested decodes if there are a lot of paths.
Though, of course, now case is even better.

chajol@tax.cupid> ed
Wrote file afiedt.buf

1 select empno, name, sal,
2 decode(-1,
3 sign(4000 - sal), 'ABOVE 4000',
4 sign(2500 - sal), 'ABOVE 2500',
5 'VERY LESS')
6* from emp
chajol@tax.cupid> /

EMPNO NAME SAL DECODE(-1,
---------- --------- ---- ----------
7369 SMITH 800 VERY LESS
7499 ALLEN 1600 VERY LESS
7521 WARD 1250 VERY LESS
7566 JONES 2975 ABOVE 2500
7654 MARTIN 1250 VERY LESS
7698 BLAKE 2850 ABOVE 2500
7782 CLARK 2450 VERY LESS
7788 SCOTT 3000 ABOVE 2500
7839 KING 5000 ABOVE 4000
7844 TURNER 1500 VERY LESS
7876 ADAMS 1100 VERY LESS
7900 JAMES 950 VERY LESS
7902 FORD 3000 ABOVE 2500
7934 MILLER 1300 VERY LESS

14 rows selected.

chuck

CASE in UPDATE clause of MERGE statement

Arun Gupta, March 24, 2004 - 11:58 am UTC

Tom,
Just want to find out of it is possible to use CASE statement in the WHEN MATCHED THEN UPDATE SET... clause of MERGE statement. Oracle 9ir2.
Thanks


Tom Kyte
March 24, 2004 - 1:21 pm UTC

ops$tkyte@ORA9IR1> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR1>
ops$tkyte@ORA9IR1> merge into t
  2  using (select 1 xxx, 2 yyy, 3 zzz from dual) d
  3  on ( t.x = d.xxx )
  4  when matched then update set y = case when xxx = 1 then yyy else zzz end
  5  when not matched then insert values ( xxx,zzz );
 
1 row merged.
 
ops$tkyte@ORA9IR1> select * from t;
 
         X          Y
---------- ----------
         1          3
 
ops$tkyte@ORA9IR1> merge into t
  2  using (select 1 xxx, 2 yyy, 3 zzz from dual) d
  3  on ( t.x = d.xxx )
  4  when matched then update set y = case when xxx = 1 then yyy else zzz end
  5  when not matched then insert values ( xxx,zzz );
 
1 row merged.
 
ops$tkyte@ORA9IR1> select * from t;
 
         X          Y
---------- ----------
         1          2
 
ops$tkyte@ORA9IR1>
 

how to use case or decode to sum up monthly data to year level

David, July 12, 2004 - 3:58 pm UTC

Hi Tom,

I have a table which has a monthly oil production data column of each well. The data is in MM/DD/YYYY format, although they just use the first day of the month in that column. The table is partitioned by year and the table contains 8 years production data.

Now I want to sum up the monthly production of each well to the year level in a Materialized View and I thought about using DECODE to do it but not clear how.

Could you help me?

Thanks as always!


Tom Kyte
July 12, 2004 - 11:30 pm UTC

if that is a "char" fields, just select aggregate_functions, substr(date_field,7,4) year from t group by substr( date_field, 7, 4 )

Decode and a date range

Neil, April 14, 2005 - 9:19 am UTC

Tom -
I want to do this:

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Apr 14 14:00:23 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.

SELECT
CASE
WHEN TO_DATE('10-MAR-05') BETWEEN PODL.from_date AND PODL.until_date
THEN
SUM(PODL.exp_bal / 1000)
ELSE
0
END "Amount"
FROM pos_depo_lad PODL
WHERE podl.cmpy_num = 16
/
but I'm getting this:
WHEN TO_DATE('10-MAR-05') BETWEEN PODL.from_date AND PODL.until_date
*
ERROR at line 3:
ORA-00937: not a single-group group function

I'm in a bit of a hurry: am I doomed to mess about with decode on this version, or am I being stupid?
I am considering using something along the lines of:

select decode(sign(from_date + today - until_date), -1, amount, 0, amount, 0)

which is quite messy. There must be a better way...


Tom Kyte
April 14, 2005 - 9:24 am UTC

you want to sum a CASE

not CASE a sum.

actually, looks like you just want a sum and a where clause.


select sum( exp_bal/1000 )
from pos_dep_lad
where cmpy_num = 16
and from_date <= TO_DATE('10-MAR-2005','dd-mon-yyyy')
and until_date >= TO_DATE('10-MAR-2005','dd-mon-yyyy')
/


find cmpy num 16 and sum up the records where the from/until dates are in range. opportunity to use index on cmpy_num,from_date or cmpy_num,until_date or cmpy_num or from_date or until_date.

That would be ideal, but...

Neil, April 14, 2005 - 9:52 am UTC

I'm looking at successive days:

if today falls between from and to then
return amount
else
return zero
end if
if today+1 falls between from and to then
return amount
else
return zero
end if
if today+2 falls between ...

etc. etc.

The idea is to return columns for the coming week.


Oh, wait - the penny dropped...

Neil, April 14, 2005 - 10:11 am UTC

Sorry Tom - I missed your point! I can indeed use sum - case
1 SELECT
2 SUM( CASE
3 WHEN TO_DATE('10-MAR-05') BETWEEN PODL.from_date AND PODL.to_date
4 THEN
5 PODL.exp_bal / 1000
6 ELSE
7 0
8 END) "Amount"
9 FROM pos_depo_lad PODL
10* WHERE podl.cmpy_num = 16

Thanks for the nudge!


Tom Kyte
April 14, 2005 - 10:18 am UTC

but that will return the same result as the more efficent where clause version?

Unless you have more columns with different cases.


Well

select nvl( sum(exp_bal/1000), 0 ) from ....

would anyway.

Advise Please

GP, April 14, 2005 - 10:54 am UTC

Hi Tom,

I an table with 50,000 rows, I am querying the below

select * from schema.abc a
where exists (select b.lk from abc b where a.lk = b.lk group by a2.lk having count(*)>1)
order by a1.lk, a1.update_time desc

but the query took long time so I indexed idx1(lk) and
idx2(lk desc, update_time desc)

In the explain plan everything looks perfect like its using all the indexes as I want them to, but when I run the query actually for results, it does not work the way it should, meaning does
not use descending order

below is the query after indexes

select * from abc a
where exists (select b.lk from abc b where a.lk = b.lk group by a2.lk having count(*)>1)
order by a1.lk, a1.update_time

I analyzed the table, index by issuing statements below

ANALYZE INDEX schema.idx2 VALIDATE STRUCTURE
ANALYZE TABLE schema.abc compute statistics

but still does not work, I mean the results are very quick but not sorted by lk, update time desc.

In the explain plan it shows that it is using idx2(which is composite index on(lk, update_time).

Please advise what am I missing on this.

Thankyou very much



Tom Kyte
April 14, 2005 - 11:01 am UTC

select *
from ( select abc.*, count(*) over (partition by lk) cnt
from abc )
where cnt > 1
order by lk, update_time desc;


you don't want a single index involved, if you see one being used, that would be a problem. indexes could only make this go slower.

if you say you run a query with an order by and the data is not ordered, that would be a bug, please contact support immediately with that test csae.

Thank you for your quick response

GP, April 14, 2005 - 11:19 am UTC

I used the query you replied but it still takes 40secs for 30,000 rows, this is the similar time as my earlier query.

(Note: I dropped all the existing indexes on abc table)

please advise.

Are you sure you I should report this to Oracle Support Customer service because it's the Indexes Desc not working
but when I use that directly in Query as in (lk, upd_time desc) it works but takes time.

Its the inxedes desc idx2(lk desc, update_time desc) thats not working but showing fine in Explain Plan.

If you think it's still worth notifying Oracle I surely would.

Also please advise me on my above query.

Thank a lot.



Tom Kyte
April 14, 2005 - 11:31 am UTC

let me see the tkprof of this, not that I don't believe you but for 30,000 measely rows, this should be very very very fast.


ops$tkyte@ORA9IR2> set autotrace traceonly statistics
ops$tkyte@ORA9IR2> select *
  2    from ( select object_name, last_ddl_time, count(*) over (partition by object_name) cnt
  3             from all_objects
  4             )
  5   where cnt > 1
  6   order by object_name, last_ddl_time desc;
 
23224 rows selected.
 
Elapsed: 00:00:01.07
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     115974  consistent gets
          0  physical reads
          0  redo size
     686187  bytes sent via SQL*Net to client
      17527  bytes received via SQL*Net from client
       1550  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      23224  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off



all_objects is a complex view, mine has about 30k rows in it.

so, lets see a tkprof. 

I missed a point

GP, April 14, 2005 - 11:25 am UTC

In my first posting the query I used with idx1 and idx2 the select works very fast but does not give me sorted results.



Tom Kyte
April 14, 2005 - 11:34 am UTC

if you have a query that has an order by
and the data is not ordered by that
then you have hit a bug and please you need to contact support with that test case.

but, show us a tkprof of my query taking 40 second for 30,000 rows, doesn't seem right.

Below is the Proof

GP, April 14, 2005 - 12:34 pm UTC

Tom,
It took me some to get this done as I did not have priviliges to do this, Below is the required.

SQL> select *
  2    from ( select abc.*, count(*) over (partition by loan_key) cnt
  3             from audit_cdm_foo abc )
  4   where cnt > 1
  5   order by loan_key, update_time desc
  6  ;

20572 rows selected.

Elapsed: 00:00:48.07

Statistics
----------------------------------------------------------
          0  recursive calls
         15  db block gets
      30947  consistent gets
       6797  physical reads
          0  redo size
    4321690  bytes sent via SQL*Net to client
       9872  bytes received via SQL*Net from client
       1373  SQL*Net roundtrips to/from client
          0  sorts (memory)
          2  sorts (disk)
      20572  rows processed.

Please advise where am I going wrong.

Also referring other Customer Support issue, I do not think it is a bug, bcoz works perfect when I give desc in the query. Does not work when I give desc in the index. I guess it is just something to do with my settings or so but I do not know what.

So please advise.
 

Tom Kyte
April 14, 2005 - 1:27 pm UTC

if you have an ORDER BY, and the data is not sorted in ORDER, then you have hit a bug.  

I understand you to say "i have an order by, but when I have a desc index the data is sorted wrong".  If that is accurate, it is a bug.

can I see a TKPROF please?

because -- if there are 50,000 rows in that table, something is up.  

ops$tkyte@ORA9IR2> select count(*) from t;
 
  COUNT(*)
----------
     28251
 
ops$tkyte@ORA9IR2> set autotrace traceonly statistics
ops$tkyte@ORA9IR2> select *
  2  from ( select object_name, last_ddl_time, count(*) over (partition by object_name) cnt
  3  from t
  4  )
  5  where cnt > 1
  6  order by object_name, last_ddl_time desc
  7  /
 
23224 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        391  consistent gets
        387  physical reads
          0  redo size
     686205  bytes sent via SQL*Net to client
      17527  bytes received via SQL*Net from client
       1550  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      23224  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off


I would not expect the consistent gets to be so high, seems your table might be super large?  

Also, I see 2 sorts to disk, are you using workarea_size_policy of manual/auto and what is your pga_aggregate_target/sort_area_size


a tkprof with a 10046 level 12 trace would be great, it'll show us what you are waiting on (and I'm guessing IO, probably to temp and perhaps your sorting space in memory is too small)
 

GP, April 14, 2005 - 2:29 pm UTC

In this case I will sure let the Customer Service know about this happening.

Can you please tell me the steps of how I can do a tkprof?

I thought it was the explain plan, hence I sent that to you earlier

Tom Kyte
April 14, 2005 - 2:48 pm UTC

SQL> alter session set events '10046 trace name context forever, level 12';
SQL> select ....
SQL> exit

get trace file that was just created on database server, run the command line tool tkprof against it

(i'm praying a dba you work with would just say "oh, sure, we know all about that...." )
 

Here is the TKPROF

GP, April 14, 2005 - 2:39 pm UTC

TKPROF: Release 9.2.0.5.0 - Production on Thu Apr 14 14:27:10 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: itprod_ora_4466.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

The following statement encountered a error during parse:

alter session set autotrace=on
WAIT #1: nam='
Error encountered: ORA-02248
********************************************************************************

ALTER session SET EVENTS '10046 trace name context forever, level 12'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.01 0.00 0 0 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 2838

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 52.00 100.13
SQL*Net break/reset to client 1 0.00 0.00
********************************************************************************

select *
from ( select abc.*, count(*) over (partition by loan_key) cnt
from audit_cdm_foo abc )
where cnt > :"SYS_B_0"
order by loan_key, update_time desc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.09 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 26.04 28.77 5168 30948 15 252
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 26.13 28.86 5168 30948 15 252

Misses in library cache during parse: 1
Parsing user id: 2838

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net more data to client 41 0.00 0.00
SQL*Net message from client 3 0.37 0.38
SQL*Net more data from client 3 0.00 0.00
direct path write 258 0.04 3.02
direct path read 2716 0.16 2.35



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.09 0.08 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 2 26.04 28.77 5168 30948 15 252
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 26.14 28.87 5168 30948 15 252

Misses in library cache during parse: 1
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 5 52.00 100.52
SQL*Net break/reset to client 1 0.00 0.00
SQL*Net more data to client 41 0.00 0.00
SQL*Net more data from client 3 0.00 0.00
direct path write 258 0.04 3.02
direct path read 2716 0.16 2.35


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************
Trace file: itprod_ora_4466.trc
Trace file compatibility: 9.02.00
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
2 unique SQL statements in trace file.
3085 lines in trace file.




Tom Kyte
April 14, 2005 - 2:51 pm UTC

one more time -- but make sure to EXIT sqlplus !!!



I need to see the row source operations in there.

and we just need the one section, so it looks like this:

select *
from
emp


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 14

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 267

Rows Row Source Operation
------- ---------------------------------------------------
14 TABLE ACCESS FULL OBJ#(40255) (cr=4 r=0 w=0 time=107 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.62 0.62



so the query, the parse/execute/fetch section, the row source operation sectoin and the waits...


but do a clean exit, some of the numbers are missing there -- eg: the IO waits, the db file scattered reads/db file sequential reads from the query itself are missing.



(cursor_sharing, ugh, afraid to ask why...)

My query takes much longer than yours.

Sean, April 14, 2005 - 5:58 pm UTC

Hi Tom,

I just used exactly the same query, but it took much longer.  Thanks so much for your help.

SQL> set timing on
SQL> set autotrace traceonly statistics;
SQL> ALTER session SET EVENTS '10046 trace name context forever, level 12';
SQL> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.00


SQL>  select *
  2         from ( select object_name, last_ddl_time, count(*) over (partition by 
  3   object_name) cnt
  4                 from all_objects
  5                 )
  6        where cnt > 1
  7        order by object_name, last_ddl_time desc;

21679 rows selected.

Elapsed: 00:00:09.08

Statistics
----------------------------------------------------------
        166  recursive calls
          0  db block gets
     144332  consistent gets
          0  physical reads
          0  redo size
     525800  bytes sent via SQL*Net to client
      10390  bytes received via SQL*Net from client
       1447  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      21679  rows processed

---------------------- tkprof ------------------------

select *
       from ( select object_name, last_ddl_time, count(*) over (partition by
 object_name) cnt
               from all_objects
               )
      where cnt > :"SYS_B_0"
      order by object_name, last_ddl_time desc

call     count       cpu    elapsed       disk      query    current        row
s
------- ------  -------- ---------- ---------- ---------- ----------  ---------
-
Parse        1      0.06       0.06          0         25          0           
0
Execute      1      0.00       0.00          0          0          0           
0
Fetch     1447      5.21       5.10          0     144304          0       2167
9
------- ------  -------- ---------- ---------- ---------- ----------  ---------
-
total     1449      5.27       5.16          0     144329          0       2167
9

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 205  

Rows     Row Source Operation
-------  ---------------------------------------------------
  21679  SORT ORDER BY (cr=144304 r=0 w=0 time=4999834 us)
  21679   VIEW  (cr=144304 r=0 w=0 time=4879443 us)
  28655    WINDOW SORT (cr=144304 r=0 w=0 time=4853366 us)
  28655     FILTER  (cr=144304 r=0 w=0 time=4331536 us)
  32679      TABLE ACCESS BY INDEX ROWID OBJ#(18) (cr=20131 r=0 w=0 time=371903
 us)
  32806       NESTED LOOPS  (cr=296 r=0 w=0 time=133660 us)
    126        TABLE ACCESS FULL OBJ#(22) (cr=5 r=0 w=0 time=544 us)
  32679        INDEX RANGE SCAN OBJ#(37) (cr=291 r=0 w=0 time=92919 us)(object 
id 37)
   2561      TABLE ACCESS BY INDEX ROWID OBJ#(19) (cr=5236 r=0 w=0 time=45239 u
s)
   2617       INDEX UNIQUE SCAN OBJ#(39) (cr=2619 r=0 w=0 time=20958 us)(object
 id 39)
  11211  TABLE ACCESS BY INDEX ROWID OBJ#(90) (cr=118937 r=0 w=0 time=3219570 u
s)
 107721   NESTED LOOPS  (cr=107726 r=0 w=0 time=3065568 us)
  86603    FIXED TABLE FULL X$KZSRO (cr=0 r=0 w=0 time=1827215 us)
  11227    INDEX RANGE SCAN OBJ#(109) (cr=107726 r=0 w=0 time=887626 us)(object
 id 109)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9153 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9165 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9179 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9218 us)
      1      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=1794 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9183 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9153 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9227 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9156 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9202 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=9117 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
      0      FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    1447        0.00          0.00
  SQL*Net message from client                  1447        0.00          0.90



*******************************************************************************
*

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        row
s
------- ------  -------- ---------- ---------- ---------- ----------  ---------
-
Parse        2      0.06       0.06          0         25          0           
0
Execute      3      0.00       0.00          0          0          0           
0
Fetch     1447      5.21       5.10          0     144304          0       2167
9
------- ------  -------- ---------- ---------- ---------- ----------  ---------
-
total     1452      5.27       5.16          0     144329          0       2167
9

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    1449        0.00          0.00
  SQL*Net message from client                  1449       49.51         73.23


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        row
s
------- ------  -------- ---------- ---------- ---------- ----------  ---------
-
Parse        7      0.03       0.02          0          1          0           
0
Execute      7      0.00       0.01          0          0          0           
0
Fetch       17      0.00       0.00          0         27          0          1
4
------- ------  -------- ---------- ---------- ---------- ----------  ---------
-
total       31      0.03       0.04          0         28          0          1
4

Misses in library cache during parse: 7

    3  user  SQL statements in session.
    7  internal SQL statements in session.
   10  SQL statements in session.
*******************************************************************************
*
Trace file: cbprod2_ora_35.trc
Trace file compatibility: 9.00.01
Sort options: default

       3  sessions in tracefile.
       8  user  SQL statements in trace file.
      14  internal SQL statements in trace file.
      10  SQL statements in trace file.
      10  unique SQL statements in trace file.
    4525  lines in trace file.


 

Run the same query on the server itself, much faster.

Sean, April 14, 2005 - 6:02 pm UTC

Hi Tom

Sorry to bothter you again. When I run the same query on the server itsefl.  It is much faster.

SQL> set timing on
SQL> set autotrace traceonly statistics;
SQL> select *
      from ( select object_name, last_ddl  2  _time, count(*) over (partition by 
object_name) cnt
              from all_objects
         )
     where cnt > 1
       order by object_name, last_ddl_time desc;

21679 rows selected.

Elapsed: 00:00:03.19

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     144304  consistent gets
          0  physical reads
          0  redo size
     735436  bytes sent via SQL*Net to client
      16551  bytes received via SQL*Net from client
       1447  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      21679  rows processed

 

Tom Kyte
April 14, 2005 - 6:15 pm UTC

that would be your network then, the time to transfer 21,679 rows. (regarding the different observed between on the server and not on the server)

but what kind of machine is this? I have a relatively puny box I did this on, if i do it on my server, it's super fast.

but rather than looking at ALL_OBJECTS, why don't we look at your table?


My hardware setting

Sean, April 14, 2005 - 8:36 pm UTC

Hi Tom

I am quite alarmed by the performance, especially the result on my server. I have Sun server with 32 GB memory and 4 cpu. My desktop has 2.8 GHz intel cpu and 1 GB memory. Anyone else tried this query?

Thanks so much.

Tom Kyte
April 14, 2005 - 8:40 pm UTC

what is the sun?

Hardware setting

Sean, April 14, 2005 - 9:29 pm UTC

Sun-Fire 480R.

Tom Kyte
April 15, 2005 - 8:25 am UTC

how fast are the chips is what I meant, are the "new, todays best" or "old, 4 years ago they were fast".



Just making sure...

Vladimir Andreev, April 15, 2005 - 5:02 am UTC

Hi GP,

I just want to make sure you're not overlooking something, because the index you mention is not sorted the same as your order by clause:

<quote>
but when I use that directly in Query as in (lk, upd_time desc) it works but takes time.

Its the inxedes desc idx2(lk desc, update_time desc) thats not working but
</quote>

You see -- you seem to want the data in ascending order by lk, but the index is in descending order by lk.
It might be a typo in your post (since the column names don't match either - upd_time <> update_time), but I just wanted to point it out.

Regards,
Flado

continue...

GP, April 15, 2005 - 9:14 am UTC

Tom,
I tried getting the tkprof with the way you mentioned but it was similar as I posted earlier, so I do not know what next.......... Please advise.

Andreev,
Let me put it this way, A query with correct results but slow.
so I create a composite index on (lk, update_time desc).
and I give a hint to use this index.

In the explain plan it shows me very nicely that It is using all the indexes and stuff, I am convinced, but when I run the same query for results. It does not show me update time desc.

-I tried using choose, all_rows everything I knew.

Below are steps I practially performed.
-1 correct results but takes time
Select * from abc a1
where a1.lk exists
(select a2.lk, count(*) cnt from abc a2
where a1.lk = a2.lk
group by a2.lk
having cnt>1)
order by a1.lk, a1.update time desc.

-2 I create index on lk i.e idx1, in explain plan it shows that idx1 for group by clause in inner query but does not use in order by, which is understood. Hence I create a composite index on lk, updatetime i.e idx2(lk, update_time desc) . In explain Plan it still shows that index idx2 not being used even after hints.
Hence now I drop idx2 and recreate i.e idx2(lk desc, update_time desc) which in explain_plan shows awesome. I mean everything the way I want it to be :-)

Then I run this query like below

Select * from abc a1
where a1.lk exists
(select a2.lk, count(*) cnt from abc a2
where a1.lk = a2.lk
group by a2.lk
having cnt>1)
order by a1.lk, a1.update time.

It is very fast :-) but the descending funtion is not performed.

And the postings after TOM wrote me a partitioned query is a different thing altogether.
I mean the query taking more time on my machine compared to his and all.

Andreev, if any suggestions please let me know.



The speed of cpu

Sean, April 15, 2005 - 9:21 am UTC

/export/home/oracle: psrinfo -v
Status of processor 0 as of: 04/15/2005 09:19:24
Processor has been on-line since 01/08/2005 12:15:37.
The sparcv9 processor operates at 1050 MHz,
and has a sparcv9 floating point processor.
Status of processor 1 as of: 04/15/2005 09:19:24
Processor has been on-line since 01/08/2005 12:15:37.
The sparcv9 processor operates at 1050 MHz,
and has a sparcv9 floating point processor.
Status of processor 2 as of: 04/15/2005 09:19:24
Processor has been on-line since 01/08/2005 12:15:37.
The sparcv9 processor operates at 1050 MHz,
and has a sparcv9 floating point processor.
Status of processor 3 as of: 04/15/2005 09:19:24
Processor has been on-line since 01/08/2005 12:15:35.
The sparcv9 processor operates at 1050 MHz,
and has a sparcv9 floating point processor.


Tom Kyte
April 15, 2005 - 10:12 am UTC

> psrinfo -v
Status of processor 1 as of: 04/15/05 10:16:21
Processor has been on-line since 04/11/05 13:48:47.
The sparcv9 processor operates at 400 MHz,
and has a sparcv9 floating point processor.
Status of processor 3 as of: 04/15/05 10:16:21
Processor has been on-line since 04/11/05 13:49:50.
The sparcv9 processor operates at 400 MHz,
and has a sparcv9 floating point processor.



select *
from ( select object_name, last_ddl_time, count(*) over (partition by obje
ct_name) cnt
from all_objects
)
where cnt > 1
order by object_name, last_ddl_time desc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1541 5.28 13.40 888 93589 12 23099
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1543 5.32 13.44 888 93589 12 23099


Here is tkprf result

Sean, April 15, 2005 - 10:37 am UTC

/export/home/oracle: psrinfo -v
Status of processor 0 as of: 04/15/2005 09:19:24
  Processor has been on-line since 01/08/2005 12:15:37.
  The sparcv9 processor operates at 1050 MHz,
        and has a sparcv9 floating point processor.
Status of processor 1 as of: 04/15/2005 09:19:24
  Processor has been on-line since 01/08/2005 12:15:37.
  The sparcv9 processor operates at 1050 MHz,
        and has a sparcv9 floating point processor.
Status of processor 2 as of: 04/15/2005 09:19:24
  Processor has been on-line since 01/08/2005 12:15:37.
  The sparcv9 processor operates at 1050 MHz,
        and has a sparcv9 floating point processor.
Status of processor 3 as of: 04/15/2005 09:19:24
  Processor has been on-line since 01/08/2005 12:15:35.
  The sparcv9 processor operates at 1050 MHz,
        and has a sparcv9 floating point processor.


-- Run this on server side.
SQL> set timing on
SQL> select *
      from ( select object_name, last_ddl_time, count(*) over (partition by 
         object_name) cnt
                  from all_objects
             )
     where cnt > 1   
       order by object_name, last_ddl_time desc;

23833 rows selected.

Elapsed: 00:00:04.00

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     228065  consistent gets
          0  physical reads
          0  redo size
     804697  bytes sent via SQL*Net to client
      18124  bytes received via SQL*Net from client
       1590  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      23833  rows processed


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1590      3.79       3.67          0     228065          0       23833
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1592      3.82       3.72          0     228067          0       23833

 

Can you show me how I can convert this Func into SQL

A reader, June 02, 2005 - 1:55 pm UTC

Tom,

Right now I cam calling this Func within SQL but
I would like to turn into SQL. Any ideas?

FUNCTION Get_Yard_Name_Func
(yardID_IN IN yard_id_TYPE)
RETURN yard_name_TYPE
IS
sYard yard_name_TYPE := NULL;
BEGIN

EXECUTE IMMEDIATE 'SELECT y.yard_name
FROM yards y
WHERE y.yard_id = :1'
INTO sYard USING yardID_IN;

RETURN RTRIM(sYard);

EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN(NULL);
END Get_Yard_Name_Func;




Tom Kyte
June 02, 2005 - 5:17 pm UTC

instead of

select ...., get_yard_name_func( t.x )
from t;

select ...., (select rtrim(yard_name) from yards where yard_id = t.x)
from t;



When Case? Or IF Decode!

Rahul Thakur, September 06, 2005 - 1:45 am UTC

Excellent discussion. Thanks Tom!

How to use case in where clause

Raul, July 30, 2007 - 2:20 pm UTC

one of my and condition in the select statement is

and upper(ppp.project_role_meaning) in ( case when pbv.budget_type_code = 'Capital Budget' then ( select unique task from jms_project_task )
when pbv.budget_type_code = 'Non-Capital Budget' then ( select 'COST CENTER MANAGER' from dual )
else null end )

not working

Tom Kyte
July 30, 2007 - 5:48 pm UTC

because case returns SCALARS, not sets.

in (SELECT UNIQUE TASK FROM JMS_PROJECT_TASK WHERE PBV.BUDGET_TYPE_CODE='Capital Budget' Union all select 'COST CENTER MANAGER' from dual where pbv.budget_type_code = 'Non-Capital Budget' )



Maybe we can avoid the correlated subquery

Andy, July 31, 2007 - 8:25 am UTC

I think we can avoid the correlated subquery by:

(pbv.budget_type_code, upper(ppp.project_role_meaning))  in 
    (SELECT 'Capital Budget', TASK 
     FROM JMS_PROJECT_TASK 
     Union all 
     select 'Non-Capital Budget', 'COST CENTER MANAGER' 
     from dual)

Nested if implementation using decode

Gunjan, January 29, 2013 - 4:43 pm UTC

SELECT u.id userid,
FROM rfn_users u, rfn_client c
WHERE
(trunc(u.effective_pwd_expires) =
trunc(SYSDATE + decode(c.pwd_expires_interval, 1,1, 0,c.pwd_expires_duration_user))))

My current decode in where condition says
if pwd_expires_interval = 1 then
u.effective_pwd_expires =1
else if pwd_expires_interval = 0 then
u.effective_pwd_expires = pwd_expires_duration_user

I however need to add another condition in this decode

if pwd_expires_interval = 1 then
if pwd_expires_duration_user < =24 then
u.effective_pwd_expires =1
else
u.effective_pwd_expires =2
else if pwd_expires_interval = 0 then
u.effective_pwd_expires = pwd_expires_duration_user

how do I interpret as decode in my where clause. please help...
Tom Kyte
January 31, 2013 - 9:33 am UTC

while you could do this in decode, don't. this will be easier to read:

case 
when pwd_expires_interval = 1
then
     case when pwd_expires_durcation_user <= 24
          then 1
          else 2
      end
when pwd_expires_interval = 0
then pwd_expired_duration_user
ELSE NULL  -- <<<<==== your if then else has this implicitly
end


Case in where clause

A reader, February 01, 2013 - 2:37 pm UTC

For a different set of where condition I want to do something like this, ie specify different range for effective_pwd_expires for different values of expires_duration_user.

how so I acieve this syntatically????

AND case
when c.pwd_expires_duration_user = 1
then u.effective_pwd_expires between trunc(sysdate+c.pwd_expires_duration_user)-(1/(24*60*60)) AND trunc(sysdate+c.pwd_expires_duration_user+1)+(1/3)))
else u.effective_pwd_expires between trunc(sysdate+c.pwd_expires_duration_user)-(1/(24*60)) AND trunc(sysdate+c.pwd_expires_duration_user+1)+(1/2)))
end;
Tom Kyte
February 01, 2013 - 3:26 pm UTC

AND case
        when c.pwd_expires_duration_user = 1  
        then case
             when u.effective_pwd_expires between 
                    trunc(sysdate+c.pwd_expires_duration_user)-(1/(24*60*60)) AND
                    trunc(sysdate+c.pwd_expires_duration_user+1)+(1/3)))
             then 1
             end
          else case   
               when u.effective_pwd_expires between  
                      trunc(sysdate+c.pwd_expires_duration_user)-(1/(24*60)) AND
                      trunc(sysdate+c.pwd_expires_duration_user+1)+(1/2)))
               then 1
               end
          end = 1


would be a CASE way, but why bother with case?

and (
       (c.pwd_expires_duration_user = 1
          and u.effective_pwd_expires between 
                 trunc(sysdate+c.pwd_expires_duration_user)-(1/(24*60*60)) AND  
                 trunc(sysdate+c.pwd_expires_duration_user+1)+(1/3)))
       )
       or
       (c.pwd_expires_duration_user <> 1
        and u.effective_pwd_expires between
                 trunc(sysdate+c.pwd_expires_duration_user)-(1/(24*60)) AND
                 trunc(sysdate+c.pwd_expires_duration_user+1)+(1/2)))
       )
    )


(beware of nulls, you might have to use

decode(c.pwd_expires_duration_user,1,0)=0

instead of <> 1 for the second part of the OR - but it looks like you won't since the match involving pwd_expires duration user would be "unknown" anyway.

Alpha numeric split

ravikiran, April 21, 2014 - 6:22 pm UTC

Hi Tom,

Please let me know how to separate alphanumeric character from a column