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

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

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

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

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>

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.

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> 

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 ;

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

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>
 

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

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;

Sorry...about the version..
8.1.7.4

Thanks...

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

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

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>
 

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 -
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...

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.

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!

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

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.

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. 

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,
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.
 

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)
 

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

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...." )
 

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.

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.


 

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

 

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.

Sun-Fire 480R.

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

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.

/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.

/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

 

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;

Excellent discussion. Thanks Tom!

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

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)

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...

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

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;

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

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)))
       )
    )

Hi Tom,

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