Hi.

It's a little difficult to understand when it will no run only once per query. Therefore I give you an another example:

delete from mytable
where id = ( select min(id)
from mytable
where id >= :bind )

Will the subquery only be executed once per execution, or for each row in mytable?

Hiya Tom, 

This is on a similar subject. Consider this :

SQL> create table a (a  VARCHAR2(20) );

Table created.

SQL> create table b (a  VARCHAR2(20) );

Table created.

SQL> INSERT INTO a VALUES ( 'X' );

1 row created.

SQL> INSERT INTO b VALUES ( 'X' );

1 row created.

SQL> set autotrace traceonly explain

SQL> select (select a from a) from b
  2  /

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


Can you explain why the explain plan doesn't show an access
path through table A? This is on 9.0.1.1.1 by the way.

Thanks in advance
 

hi tom

I have noticed that to find an employee´s name with highest salary we normally have to use sub-queries

like

select ename
from emp
where sal = (select max(sal) from emp)

which implies two index/table scan in emp.

from 8.1.6 we have these new analytic functions, is there any way to write above query without scanning twice the table?

Well, in the above, you were not "scanning" the table twice, especially if SAL was indexed.

scott@ORA817DEV.US.ORACLE.COM> select *
  2    from ( select ename, sal,
  3                  dense_rank() over ( order by sal desc ) dr
  4             from emp )
  5   where dr = 1
  6  /

ENAME             SAL         DR
---------- ---------- ----------
KING             5000          1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=462)
   1    0   VIEW (Cost=3 Card=14 Bytes=462)
   2    1     WINDOW (SORT PUSHED RANK)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=252)



<b>Now, while analytic functions are in fact the coolest thing since sliced bread -- they are also not a silver bullet</b>

Here, the subquery is by far the best approach.  You are not really doing things by "set", you are doing things over the entire result set and then keeping a teeny tiny piece of it.  Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table emp;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec gen_data( 'EMP', 10000 )

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index sal_idx on emp(sal);

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table emp compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

Table analyzed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace  on
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select ename, sal
  2    from emp
  3   where sal = ( select max(sal) from emp )
  4  /

ENAME             SAL
---------- ----------
LP^YXiTKRn   99996.61


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=24)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=2 Bytes=24)
   2    1     INDEX (RANGE SCAN) OF 'SAL_IDX' (NON-UNIQUE) (Cost=1 Card=2)
   3    2       SORT (AGGREGATE)
   4    3         INDEX (FULL SCAN (MIN/MAX)) OF 'SAL_IDX' (NON-UNIQUE) (Cost=3 Card=10014 Bytes=50070)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
  2    from ( select ename, sal,
  3                                  dense_rank() over ( order by sal desc ) dr
  4                     from emp )
  5   where dr = 1
  6  /

ENAME             SAL         DR
---------- ---------- ----------
LP^YXiTKRn   99996.61          1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=10014 Bytes=330462)
   1    0   VIEW (Cost=36 Card=10014 Bytes=330462)
   2    1     WINDOW (SORT PUSHED RANK)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=10014 Bytes=120168)




Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
         84  consistent gets
          0  physical reads
          0  redo size
        482  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

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

<b>here the old fashioned way is the best way -- this is because the old fashioned way gets the max sal very very fast and then just does an index pickup to get the matching row(s).

The analytic function however needs to build the entire answer and then find the row(s) with dense_rank of 1</b>


So, fear not the old ways, they are still very good in many cases. 

show us the gen_data( 'EMP', 10000 ) procedure, we want to see how it produces data

Hi

I am trying to convert a query so I am doing some tests with some test data using EMP table

I came up with these two queries

select * from emp x
where sal = (select max(sal) from emp where x.deptno = deptno and emp.empno not between 7600 and 7800 )
order by empno

select * from emp x
where sal = (select max(sal) from emp where x.deptno = deptno)
and empno not between 7600 and 7800
order by empno

They return different results but I cannot see the difference.... Can you explain why they are different please

many many thanks

Dear Tom,
Fine and expect the same from you.What is the use of the
"with" keyword?Does it relate to query possiblities like
"Merge" statement?Please provide a sample explanation.

Hi Tom,
I am trying to rewrite the following query with correlated subqueries.

SELECT
A.ID
FROM
A,
B
WHERE
A.ID=B.ID AND
(B.CONTACT_DATE > (SELECT MAX(CONTACT_DATE) FROM C WHERE RESPONSE_CODE ='RV_BOUNCE' AND
A.ID = C.ID) OR
(SELECT MAX(CONTACT_DATE) FROM CS_CASE_DETAILS
WHERE A.ID = C.ID AND RESPONSE_CODE ='RV_BOUNCE') IS NULL);

Tables A,B,C have indexes on respective ID columns. Table A is the master table and Table B and C are transaction tables. B and C may or may not have corresponding records in Table A.

I rewrote the above query as follows to avoid correlated subqueries

SELECT ID
FROM
(
SELECT MAX_CNT_DATE,A.CS_CASE_INFO_ID
FROM
(
SELECT A.ID,C.CONTACT_DATE,
C.RESPONSE_CODE,
MAX(C.CONTACT_DATE) OVER (PARTITION BY A.ID) MAX_CNT_DATE
FROM A,C
WHERE A.ID=C.ID AND C.RESPONSE_CODE ='RV_BOUNCE'
) C ,
A,
B
WHERE A.ID=C.ID(+) AND A.ID=B.ID
)
WHERE (CONTACT_DATE > max_cnt_date OR max_cnt_date IS NULL)

Though, this query is running in approximately the same time as the original one, I feel there are better ways to do it.

Could you Please, suggest a better way to rewrite such a subquery where a third (i.e table C is not involved in outer queries) table is involved?

Since, this is a trivial query, may be someone else can suggest a solution ,if not Tom. Though I would prefer him).

Thanks & Regards
Sam

Hi Tom,
Apropos my previous post, could you Please, suggest a better alternative to the following query?
SELECT
A.ID
FROM
A,
B
WHERE
A.ID=B.ID AND
(B.CONTACT_DATE > (SELECT MAX(CONTACT_DATE) FROM C WHERE RESPONSE_CODE
='RV_BOUNCE' AND
A.ID = C.ID) OR
(SELECT MAX(CONTACT_DATE) FROM CS_CASE_DETAILS
WHERE A.ID = C.ID AND RESPONSE_CODE ='RV_BOUNCE') IS NULL);

Thanks & Regards
Sam

Hi Tom,
How does the following correlated subquery proceed,whether
from subquery to parent query or viceversa?The query is

SQL>select deptno,ename,sal from emp e where
    sal = (select max(sal) from emp where deptno =   e.deptno);


 

Hello Tom,
We are new to Oracle and We don't have access to New Oracle
Books.We are not clear with "How a correlated subquery
works?".Could you please explain it with a simple example?Do correlated subqueries refer to same tables just as Parent query does?
Expecting your reply,
K.Mohammed

I've noticed, though never tested, that adding "and rownum <= 1" to the predicate of an exists subquery, the processing time can go down.

If
<QUOTE>
select *
from dept
where exists ( select null
from emp
where emp.deptno = DEPT.DEPTNO ) <<<=== correlated subquery

is a lot like:

for x in ( select * from dept )
loop
select count(*) into n from emp where emp.deptno = X.DEPTNO;
if ( n > 0 )
then
output X;
end if;
end loop;
</QUOTE>

Wouldn't a "and rownum <= 1" predicate on the inner query speed things along? I know what you wrote was conceptual in nature, but it seems quite close to reality. I had a query that was

SELECT *
FROM MASTER_TABLE t1
WHERE EXISTS(
SELECT NULL
FROM DETAIL_TABLE t2
WHERE t1.ID = t2.ID
)

It took about 30 seconds to run. Adding "AND ROWNUM <= 1" to the inner subquery predicate cut the time down to 3 - 4 seconds. I was thinking that if the optimizer knew it had to evaluate the subquery once for each row evaluated, then it would implicitly hint the subquery with a /*+ FIRST_ROWS */ and potentially add the rownum <= 1 (i.e. get me any row as quick as possible). The only reasons I could come up with for not doing it this way would be performance issues when no rows are returned and the possibility of being able to fully materialize and load the subquery into memory.

Hi Tom,

SQL> select deptno from dept d where not exists(
       select null from emp e where e.deptno = d.deptno);

I tried to transform the above query using a subquery as
follows :
 
SQL> select d.deptno from dept d,(select deptno from emp)e
      where d.deptno <> e.deptno;

But it is not working properly as expected.It results 
in a cross product.How to correct this?
  

Hi Tom,
I asked why this query with inline view is not
working properly?

SQL> select d.deptno from dept d,(select deptno from emp)e
      where d.deptno <> e.deptno;

Please reply for that. 

Hi Tom,

i want to know the flow of this query. can you please help me in this.

select empno,ename,sal from emp a
where 3 >= (select count(distinct sal) from emp b
where a.sal <= b.sal);

Thanks

Tom,
I want to create a procedure which will insert some 5000000 records to empdup from different EMP table which contains same number of records.

But in EMP there are some duplicate records for empno and in empdup there unique constraint for empno.
I don’t want to use distinct clause in cursor rather then I want like when ever there the same employee information is inserting in empdup the program can raise an exception and go to next record and insert in empdup and so on keep inserting all the records except the duplicate

Can we do this if yes then how?

Hello Tom,
Is it possible to get Nth max or min sal
with out using subquery?

Hi Tom,
Actually the question asked in an interview was
"Can you get the second maximum sal from emp table
with out using subquery?"
Any solution you have for this?

hi tom,
I try urs example and it works great...
but if i write it like :
select ename,sal
from(select *
from emp
order by sal desc)
where rownum=&n
..
the result is :::
Enter value for n: 1
old 5: where rownum=&n
new 5: where rownum=1

ENAME SAL
---------- ----------
bhavesh 12000
which is the highest..

BUT if we enter the value like :
Enter value for n: 2
old 5: where rownum=&n
new 5: where rownum=2

no rows selected.

although there are more records..and i want to know second highest or what ever..
What is the secret behind that rownum function??
it will not work some times with '='
some times i also find that when i want to retrive the record between some ranges:

select *
from emp
where rownum>&min and rownum<&max;
it will not work

what is the reason..
pls reply..

hi tom,
you are right yours query runs fine...
but i want to know what is wrong with my query..??

excellent answers, more useful, needs some graphical representations with answers

Is there a way to rewrite this without using correlated subqueries/or cursors/loops? Using temp tables/batch based sql?

SELECT E.department_id, last_name, salary
FROM employee E
WHERE salary = (SELECT MAX (salary)
FROM employee E2
WHERE E.department_id = E2.department_id);

Thanks.

All the q's asked in ur site are good enough, they are sometimes very useful when one know the solution but don't get on the moment. The best software that i choose after experience is Oracle. Its not only user friendly to users but also for DBA's and developers, its really good.
i will get accurate ans to my q's in ur site. Users who are working in ur site have also good experience and its really nice when someone ask q and users use their mind to give the perfect solution. This job is very nice.

Thanks for such a wonderful site
Ranjana Ahuja

hi Tom

I have to write a query which has output of both these queries

select c,d,e from a,b where where date_id=v_date
group by c,d,e
or
select c,d,e from a,b where where date_id=trunc(to_date(v_date)-10)
group by c,d,e

how can i write it as subquries don't use group by clause

select c,d,e from a,b where where date_id=v_date
group by c,d,e
having count(*)>1
or
select c,d,e from a,b where where date_id=trunc(to_date(v_date)-10)
group by c,d,e
having count(*)>4

It was good explaination

Tom,

If I have a query like

1)
select ename,job,sal
from emp
where exists ( select 'X' from dept
where a.deptno = b.deptno);

then whether first inner query will executed then out query or for each of the row returned by outer query inner query will be executed

2)
select ename,job,sal
from emp
where exists ( select 'X' from dept
where deptno = :dno);

In case 2 is it that first inner query is executed and then based on true/false outer query will be executes ?

regds
Girish

Tom,

any suggestions on improving a code like this. please keep in mind that is part of a big query, but if I cut this out it run in seconds else > 4 min.

Thank you

here is the piece of code...

--- AND party_status_cd IN ('STAFF', 'VERIF')
--- AND effective_dt =
--- (SELECT MAX (effective_dt)
-- FROM MAIN_TABLE T1
-- WHERE T1.party_id = P2.party_id
-- GROUP BY T1.party_id)

SELECT DISTINCT pty_mina_party_id,
max_search_mbe_dbe_date
(pty_mina_party_id,
pty_party_effective_dt
) AS pty_party_effective_dt,
cdt_party_type_cd, cdt_party_status_cd, bus_name,
name_ovrride, physical_location_id,
DECODE (cdt_party_type_cd,
'MINA_PTY_TYP_BR', physical_nm,
bus_name
) AS physical_location_nm,
city_nm, country_nm, cdt_iso2_country_cd, state_province_cd,
state_province_nm, postal_zip_cd, cdt_address_type_cd,
cubicle, mailstop, FLOOR, address_line_1, address_line_2,
address_line_3, address_line_4, suite, duns_nr, status_cd,
subsidiary_in, domestic_ultimate_ind, global_ultimate_in,
dnb_business_nm, tradestyle_primary_nm, tradestyle_second_nm,
tradestyle_third_nm, tradestyle_fourth_nm,
tradestyle_fifth_nm,
(SELECT (SELECT cdt_business_classification_cd
FROM business_record_brc brc
WHERE brc.pty_mina_party_id(+) =
temp_table.pty_mina_party_id
AND brc.pty_party_effective_dt(+) =
temp_table.pty_party_effective_dt)
|| (SELECT cdt_business_classification_cd
FROM mina_business_entity_mbe mbe
WHERE mbe.pty_mina_party_id(+) =
temp_table.pty_mina_party_id
AND mbe.pty_party_effective_dt(+) =
temp_table.pty_party_effective_dt)
FROM DUAL) AS cdt_business_classification_cd
FROM (SELECT pty.pty_mina_party_id, pty.pty_party_effective_dt,
pty.cdt_party_status_cd,
(SELECT name_value_tx
FROM business_name_bnm bnm
WHERE bnm.cdt_business_name_type_cd = 'BUS_NM_TYP_BUS'
AND bnm.pty_mina_party_id = pty.pty_mina_party_id
AND bnm.pty_party_effective_dt =
pty.pty_party_effective_dt)
AS bus_name,
(SELECT name_value_tx
FROM business_name_bnm bnm_ovr
WHERE bnm_ovr.cdt_business_name_type_cd ='BUS_NM_TYP_OVR'
AND bnm_ovr.cdt_status_cd = 'BUS_NM_STAT_CD_ACTIV'
AND bnm_ovr.pty_mina_party_id = pty.pty_mina_party_id
AND bnm_ovr.pty_party_effective_dt = pty.pty_party_effective_dt)
AS name_ovrride,
DECODE
(pty.cdt_party_type_cd,
'MINA_PTY_TYP_BR', (SELECT ptp_inn.pty_mina_party_one_id
FROM party_to_party_assoc_ptp ptp_inn
WHERE ptp_inn.pty_mina_party_two_id =
pty.pty_mina_party_id
AND ptp_inn.pty_party_effective_two_dt =
pty.pty_party_effective_dt
AND ptp_inn.cdt_party_association_type_cd =
'ASSOC_BE_TO_BR'
AND ptp_inn.cdt_status_cd =
'ASSOC_ACTIV'
AND ptp_inn.ptp_party_party_assoc_eff_dt =
(SELECT MAX
(ptp_inn2.ptp_party_party_assoc_eff_dt
)
FROM party_to_party_assoc_ptp ptp_inn2
WHERE ptp_inn.pty_mina_party_two_id =
ptp_inn2.pty_mina_party_two_id
AND ptp_inn.pty_party_effective_two_dt =
ptp_inn2.pty_party_effective_two_dt
AND ptp_inn2.cdt_party_association_type_cd =
'ASSOC_BE_TO_BR'
GROUP BY ptp_inn2.pty_mina_party_two_id,
ptp_inn2.pty_party_effective_two_dt)),
pty.pty_mina_party_id
) AS physical_location_id,
(SELECT name_value_tx
FROM business_name_bnm bnm,
party_to_party_assoc_ptp ptp_inn
WHERE ptp_inn.pty_mina_party_two_id =
pty.pty_mina_party_id
AND ptp_inn.pty_party_effective_two_dt =
pty.pty_party_effective_dt
AND ptp_inn.cdt_party_association_type_cd =
'ASSOC_BE_TO_BR'
AND ptp_inn.cdt_status_cd = 'ASSOC_ACTIV'
AND ptp_inn.ptp_party_party_assoc_eff_dt =
(SELECT MAX
(ptp_inn2.ptp_party_party_assoc_eff_dt
)
FROM party_to_party_assoc_ptp ptp_inn2
WHERE ptp_inn.pty_mina_party_two_id =
ptp_inn2.pty_mina_party_two_id
AND ptp_inn.pty_party_effective_two_dt =
ptp_inn2.pty_party_effective_two_dt
AND ptp_inn2.cdt_party_association_type_cd =
'ASSOC_BE_TO_BR'
GROUP BY ptp_inn2.pty_mina_party_two_id,
ptp_inn2.pty_party_effective_two_dt)
AND ptp_inn.pty_mina_party_one_id =
bnm.pty_mina_party_id
AND ptp_inn.pty_party_effective_one_dt =
bnm.pty_party_effective_dt
AND bnm.cdt_business_name_type_cd =
'BUS_NM_TYP_BUS')
AS physical_nm,
loc.city_nm, loc.country_nm, loc.cdt_iso2_country_cd,
loc.state_province_cd, loc.state_province_nm,
loc.postal_zip_cd, pad.cdt_address_type_cd,
pad.cubicle, pad.mailstop, pad.FLOOR,
loc.address_line_1, pad.address_line_2,
pad.address_line_3, pad.address_line_4, pad.suite,
dbe.duns_nr, dbe.status_cd, dbe.dnb_business_nm,
dbe.tradestyle_primary_nm, dbe.tradestyle_second_nm,
dbe.tradestyle_third_nm, dbe.tradestyle_fourth_nm,
dbe.tradestyle_fifth_nm, pty.cdt_party_type_cd,
dbe.subsidiary_in, dbe.domestic_ultimate_ind,
dbe.global_ultimate_in,
ptp.cdt_party_association_type_cd
FROM party_address_pad pad,
business_record_brc brc,
(SELECT /*+ INDEX(bnm TX_ID)*/
pty_mina_party_id, pty_party_effective_dt
FROM business_name_bnm bnm
WHERE bnm.cdt_business_name_type_cd IN
('BUS_NM_TYP_BUS',
'BUS_NM_TYP_DNB_LGL',
'BUS_NM_TYP_OVR',
'BUS_NM_TYP_TRD_STY_1',
'BUS_NM_TYP_TRD_STY_2',
'BUS_NM_TYP_TRD_STY_3',
'BUS_NM_TYP_TRD_STY_4',
'BUS_NM_TYP_TRD_STY_5'
)
AND contains (name_value_tx, 'IBM%') > 0) bnm,
mina_business_entity_mbe mbe,
party_to_party_assoc_ptp ptp,
location_loc loc,
dnb_business_entity_dbe dbe,
mina_party_pty pty
WHERE pad.pty_mina_party_id = pty.pty_mina_party_id
AND pad.pty_party_effective_dt = pty.pty_party_effective_dt
AND bnm.pty_mina_party_id = pty.pty_mina_party_id
AND bnm.pty_party_effective_dt = pty.pty_party_effective_dt
AND loc.loc_location_key = pad.loc_location_key
AND cdt_party_type_cd IN ('MINA_PTY_TYP_BE', 'MINA_PTY_TYP_BR')
AND brc.pty_mina_party_id(+) = pty.pty_mina_party_id
AND brc.pty_party_effective_dt(+) = pty.pty_party_effective_dt
AND mbe.pty_mina_party_id(+) = pty.pty_mina_party_id
AND mbe.pty_party_effective_dt(+) = pty.pty_party_effective_dt
AND ptp.pty_mina_party_one_id(+) = pty.pty_mina_party_id
AND ptp.pty_party_effective_one_dt(+) = pty.pty_party_effective_dt
AND ptp.cdt_party_association_type_cd = 'ASSOC_BE_TO_DNB' ---do not need the outer join
AND ptp.cdt_status_cd = 'ASSOC_ACTIV' ---do not need the outer join
AND dbe.pty_mina_party_id(+) = ptp.pty_mina_party_two_id
AND dbe.pty_party_effective_dt(+) = ptp.pty_party_effective_two_dt
AND pty.cdt_party_status_cd IN ('BL_STAT_UNVERIF', 'BL_STAT_VERIF')----PROBLEM
AND pty.pty_party_effective_dt =
(SELECT MAX (pty1.pty_party_effective_dt)
FROM mina_party_pty pty1
WHERE pty1.pty_mina_party_id = pty.pty_mina_party_id
GROUP BY pty1.pty_mina_party_id )
AND ( ptp.ptp_party_party_assoc_eff_dt =
(SELECT MAX (ptp_party_party_assoc_eff_dt)
FROM party_to_party_assoc_ptp
WHERE pty_mina_party_one_id =
ptp.pty_mina_party_one_id
AND pty_party_effective_one_dt =
ptp.pty_party_effective_one_dt
AND cdt_party_association_type_cd = 'ASSOC_BE_TO_DNB' ---do not need the outer join
AND cdt_status_cd = 'ASSOC_ACTIV' ---do not need the outer join
GROUP BY pty_mina_party_one_id,
pty_party_effective_one_dt)
OR NOT EXISTS (
SELECT 1
FROM party_to_party_assoc_ptp
WHERE pty_mina_party_one_id = ptp.pty_mina_party_one_id
AND pty_party_effective_one_dt = ptp.pty_party_effective_one_dt
AND cdt_party_association_type_cd = 'ASSOC_BE_TO_DNB'
AND cdt_status_cd = 'ASSOC_ACTIV'
GROUP BY pty_mina_party_one_id, pty_party_effective_one_dt)
)
AND '0' = '0'
AND ( brc.cdt_business_classification_cd NOT IN ('BL_CLASS_INTER')
OR mbe.cdt_business_classification_cd NOT IN ('BL_CLASS_INTER')
)) temp_table
WHERE ROWNUM < 102

scott%ORA10GR2> select deptno, ename, hiredate
  2  from emp e1
  3  where hiredate = (select max(hiredate) from emp e2 where e2.deptno = e1.deptno);

    DEPTNO ENAME      HIREDATE
---------- ---------- ---------
        20 ADAMS      23-MAY-87
        30 JAMES      03-DEC-81
        10 MILLER     23-JAN-82

scott%ORA10GR2> select *
  2  from (select deptno, ename, hiredate, max(hiredate) over (partition by deptno) max_dt from emp)
  3  where hiredate = max_dt;

    DEPTNO ENAME      HIREDATE  MAX_DT
---------- ---------- --------- ---------
        10 MILLER     23-JAN-82 23-JAN-82
        20 ADAMS      23-MAY-87 23-MAY-87
        30 JAMES      03-DEC-81 03-DEC-81

I was thinking along those lines from a previous post. I knew also that you won't touch that one because of how big it is. Thanks for looking at it anyway.

Tom,

how can someone replace(max)the following with analystical as you have suggested? Please advice.


AND pty.pty_party_effective_dt =
(SELECT MAX (pty1.pty_party_effective_dt)
FROM mina_party_pty pty1
WHERE pty1.pty_mina_party_id = pty.pty_mina_party_id
GROUP BY pty1.pty_mina_party_id )

too much traveling maybe???!!?!

Dear Tom,

good day to you, I was going through the post and tried the two queries one using subquery and another with analytical function. We are using 10g ver 10.2.0.1.0 on Windows. Please see below the output from autotrace.

SCOTT@perf>select * from emp where sal= (select max(sal) from emp);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=37)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=
37)

2 1 SORT (AGGREGATE)
3 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 B
ytes=56)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
837 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SCOTT@perf>select * from ( select empno,ename,sal, dense_rank() over(order by sal desc) dr from emp) where dr=1;

EMPNO ENAME SAL DR
---------- ---------- ---------- ----------
7839 KING 5000 1


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=64
4)

1 0 VIEW (Cost=4 Card=14 Bytes=644)
2 1 WINDOW (SORT PUSHED RANK) (Cost=4 Card=14 Bytes=196)
3 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 B
ytes=196)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
595 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

The consistent gets using Analytical function is less but there's 1 sort in memory, I have two questions here.

1. any changes made in 10g compared to 8i since the earlier stats posted by you show consistent more when using Analytical functions.

2. since there's one sort in memory when using Analytical function compared to none when using subquery, should we still use subquery or analytical function is better.

Please shed some light on this.

Regards.

Dear Tom,

good day to you and thank you for taking up my query. I used your procedure gen_data to populate the table and after following all the steps you listed I found that the stats at my end are similar to the ones posted by you.

I dropped the index on salary column and executed the two queries, I found that the consistent gets by subquery is more compared to the query using analytical function, but the query using analytical function performs a sort in memory.

In a case when there's no index on the column being used for max(),how should this be evaluated, consistent gets are less but sort is being performed vs. consistent gets are more.

Thanks for your time and efforts on this and all the other questions you take, there are lot of learnings from your site.

Regards,
your fan.

Dear Tom,

good day to you as always, just wanted to know if I am going in a wrong direction or is my question in above post invalid/incorrect.


Regards,
your fan.

Hi Tom,
I want to ask that whether using for loop is more efficient or using sub queries
For example consider the following two cases...
1)
DELETE FROM COMP_SPEC_CHAR_VALUE WHERE COMP_SPEC_CHAR_ID IN
(SELECT COMP_SPEC_CHAR_ID FROM COMP_SPEC_CHAR WHERE COMP_SPEC_ID = compSpecId);

2)
FOR comp_spec_char_record IN (SELECT * FROM COMP_SPEC_CHAR WHERE COMP_SPEC_ID = compSpecId)
LOOP
DELETE FROM COMP_SPEC_CHAR_VALUE WHERE COMP_SPEC_CHAR_ID = comp_spec_char_record.COMP_SPEC_CHAR_ID;
END LOOP;

In the above case which is more efficient and which we should use

I have the following below issue need your help



select count(*) from table_1 t1 , table_2 t2
where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3= t2.col3

The above query returning 100000 rows and these rows which i have delete from "table_1"


So I am writing the delete statement as follows

delete from table_1 where exists (

select 1 from table_1 t1 , table_2 t2
where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3= t2.col3 ), This query is returing the 5,000,000 rows which are nothing but now rows in table_1

can you tell me how can write the above delete statement

thanks for you help

I have tired this way but the count is not matching

ops$tkyte%ORA10GR2> create table t1 ( col1 int, col2 int, col3 int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( col1 int, col2 int, col3 int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values ( 1,1,1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t2 values ( 1,1,1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t2 values ( 1,1,1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t2 values ( 1,1,1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t2 values ( 1,1,1 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select count(*) from t1 , t2
  2  where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3= t2.col3
  3  /

  COUNT(*)
----------
         4

ops$tkyte%ORA10GR2> delete from t1
  2  where (col1,col2,col3) in (select t2.col1, t2.col2, t2.col3 from t2);

1 row deleted.

Dear Tom,

In your replay for this question you said that "correlated subqueries are as old as dirt" could you please explain to us why did you say that?

Regards,
Khalid.

This is regarding execution path of SQL involving subquery. 

Are there any init parameters (or hints) that can enable Oracle to take t3 out of the sub-query in the 1st SQL and do the join in the right order like the 2nd SQL? 

Something like query rewrite. The following parameters are set (if relevant): 

query_rewrite_enabled=TRUE             
query_rewrite_integrity=enforced    

We have a SQL like the 1st SQL below. Tests have been done to show that the cartesian join generates too much data (10,000 times more than the final result). We've tried and the 2nd SQL ran 5,000 times faster than the 1st SQL. 
If possible, we don't want code change, which requires functional validation from the developer, who has already left the job. 

CREATE TABLE t1(c1 NUMBER, c2 NUMBER);
CREATE TABLE t2(c1 NUMBER);
CREATE TABLE t3(c2 NUMBER, c3 NUMBER PRIMARY KEY);
CREATE TABLE t4(c3 NUMBER);

SQL> -- 1st SQL (with subquery)
SQL> SELECT *
  2    FROM t1,
  3         t2,
  4         t4
  5   WHERE t1.c1 = t2.c1
  6     AND t1.c2 = (SELECT c2
  7                    FROM t3
  8*                  WHERE t3.c3 = t4.c3);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3383608335

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    52 |     8  (13)| 00:00:01 |
|*  1 |  FILTER                      |              |       |       |            |          |
|   2 |   MERGE JOIN CARTESIAN       |              |     1 |    52 |     7  (15)| 00:00:01 |
|*  3 |    HASH JOIN                 |              |     1 |    39 |     5  (20)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | T1           |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL        | T2           |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |    BUFFER SORT               |              |     1 |    13 |     5  (20)| 00:00:01 |
|   7 |     TABLE ACCESS FULL        | T4           |     1 |    13 |     2   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID| T3           |     1 |    26 |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN         | SYS_C0011014 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."C2"= (SELECT "C2" FROM "T3" "T3" WHERE "T3"."C3"=:B1))
   3 - access("T1"."C1"="T2"."C1")
   9 - access("T3"."C3"=:B1)

Note
-----
   - dynamic sampling used for this statement
   - SQL plan baseline "SYS_SQL_PLAN_11c15db694987734" used for this statement


Statistics
----------------------------------------------------------
         15  recursive calls
         15  db block gets
         23  consistent gets
          0  physical reads
       9792  redo size
        434  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> -- 2nd SQL (without subquery, with hints)
SQL> SELECT /*+ ordered */ *
  2    FROM t1,
  3         t2,
  4         t3,
  5         t4
  6   WHERE t1.c1 = t2.c1
  7     AND t1.c2 = t3.c2
  8     AND t3.c3 = t4.c3;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 894925296

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    78 |    10  (20)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    78 |    10  (20)| 00:00:01 |
|*  2 |   HASH JOIN          |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  3 |    HASH JOIN         |      |     1 |    39 |     5  (20)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL  | T4   |     1 |    13 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."C3"="T4"."C3")
   2 - access("T1"."C2"="T3"."C2")
   3 - access("T1"."C1"="T2"."C1")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          6  recursive calls
          4  db block gets
          9  consistent gets
          0  physical reads
        672  redo size
        538  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Hi Tom,

Thanks for the reply.

I would still consider dbms_advanced_rewrite.declare_rewrite_equivalence() as _manual_ code change because I need to decide what the new code is. It still requires functional validation. I think it would be helpful when code change is simple but troublesome, e.g., when that piece of code is everywhere. In my case, I'd rather changing the code itself rather than using declare_rewrite_equivalence(). I think the 1st and 2nd SQL are equivalent, but our process does not allow the new code to go in without reviewed by the developer. I tried comparing the result sets generated by the original and new code, but the original code does not finish running.

I am looking for automatic execution path optimization (via hints, init parameter, or automatic query rewrite - it does not require functional validation whatsoever). Is your answer No?

Thanks.

Hi Tom,

I've a question on the correlated sub query.

We have the table data as below

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-DEC-17        800                    20
      7499 ALLEN      SALESMAN        7698 1981-FEB-20       1600        300         30
      7521 WARD       SALESMAN        7698 1981-FEB-22       1250        500         30
      7566 JONES      MANAGER         7839 1981-APR-02       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-SEP-28       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-MAY-01       2850                    30
      7782 CLARK      MANAGER         7839 1981-JUN-09       2450                    10
      7788 SCOTT      ANALYST         7566 1982-DEC-09       3000                    20
      7839 KING       PRESIDENT            1981-NOV-17       5000                    10
      7844 TURNER     SALESMAN        7698 1981-SEP-08       1500          0         30
      7876 ADAMS      CLERK           7788 1983-JAN-12       1100                    20
      7900 JAMES      CLERK           7698 1981-DEC-03        950                    30
      7902 FORD       ANALYST         7566 1981-DEC-03       3000                    20
      7934 MILLER     CLERK           7782 1982-JAN-23       1300                    10

14 rows selected.

SQL> SELECT empno, mgr,ename, sal
  2  FROM emp outer
  3  WHERE sal >
  4    (SELECT AVG(sal)
  5     FROM emp inner
  6     WHERE inner.empno = outer.mgr);

     EMPNO        MGR ENAME             SAL
---------- ---------- ---------- ----------
      7788       7566 SCOTT            3000
      7902       7566 FORD             3000

Please let me know how the correlated subquery logic works and what would be the average salary that is produced as output in the sub query. Also how many records are eligible for selection for the output of subquery before the AVERAGE function is applied and what are they. Thanks for your time.

TIA

Srini

Thanks v much Tom. Even i felt obscure when i looked at the query the first time as i took it from one of the online examples for correlated subquery.

Now i understood the logic part of it.

Srini

Hi Tom,

I have a doubt which is from long time. This is a trivial example where I want to select multiple columns from the sub table using similar join condition.

select
(select prod_name from products where prod_id = sales.prod_id) names,
(select prod_subcategory from products where prod_id = sales.prod_id) descr
from sales;

When I checked the explain plan I found out that it is accessing the table products twice.

Can oracle also have an explain plan in which it will be understanding that both the subquery refers to same row and the table join has to be executed only once.

Basically I want to know whether this kind of sub-query uses some kind of caching to achieve the result set.

I checked in forums and no one have seem to be ask this question

Thanks,
Sarath

1. Display the details of the manufacturers along with the certification and quality
certification type details.
1
CLM_MFTR_ID
CLM_MFTR_NME
CLM_MFTR_ABVN
CLM_MFTR_LIC_NR
CLM_MFTR_LIC_DT
CLM_MFTR_DESC
2
CLM_CRFN_ID
CLM_MFTR_ID
CLM_CRFN_TYP_ID
CLM_CRFN_DT
CLM_CRFN_DESC
CLM_CRFN_ADTR_ID

Hi Tom ; 
Thanks a lot for your very useful site. I'm just trying to split a table in a recordset, for a web application, using the rowid of the table. The idea is to return "n" records every time this procedure is called. I'm performing the test and when I perform the following: 

SQL> select rownum, a.id_bitacora
  2  from ssi_bitacora a
  3  where rownum > 1;

ninguna fila seleccionada

SQL> select rownum, a.id_bitacora
  2  from ssi_bitacora a
  3  where rownum > 0;

    ROWNUM ID_BITACORA
---------- -----------
         1           1
         2           2
         3           3
         4           4
         5           5

Obiously, I have records in this table, as you can see in the second query. 

rownum = 1
for x in ( select * from t )
loop
   if ( rownum = 1 )
   then
      output row
      rownum = rownum + 1;
   end if;
end loop;

select * 
  from (select t.*, row_number() over (order by something) rn
          from t
        )
where rn between :m and :n;

select * 
  from (select x.*, rownum rnum
          from (select * from t order by something) X
         where rownum <= :n)
 where rnum >= :m;

thanks a lot for your quick and useful response; I' reading the article provided and it is exactly what I need.

Hi Tom,
I need to review all SPs that have a (correlated subquery) to switch them to JOIN --if i could--
My Question: how can i extract all queries that have a correlated subqueries from USER_SOURCE table?

ops$tkyte%ORA11GR2> create table t1 ( x int primary key, y int, z int );

Table created.

ops$tkyte%ORA11GR2> create table t2 ( x int primary key, y int, z int );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 1000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 10, numblks => 1 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t2 where exists ( select null from t1 where t1.x = t2.x );

Execution Plan
----------------------------------------------------------
Plan hash value: 3586655764

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |    10 |   520 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |              |    10 |   520 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2           |    10 |   390 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C0045878 |  1000K|    12M|     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."X"="T2"."X")

ops$tkyte%ORA11GR2> select * from t1 where exists ( select null from t2 where t1.x = t2.x );

Execution Plan
----------------------------------------------------------
Plan hash value: 2782716045

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100K|  5078K|     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |       |       |            |          |
|   2 |   NESTED LOOPS               |              |   100K|  5078K|     4  (25)| 00:00:01 |
|   3 |    SORT UNIQUE               |              |    10 |   130 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | T2           |    10 |   130 |     2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C0045878 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| T1           |   100K|  3808K|     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."X"="T2"."X")

ops$tkyte%ORA11GR2> set autotrace off