Skip to Main Content
  • Questions

  • Tune SQL statement with max subquery

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, josh.

Asked: January 10, 2003 - 12:18 pm UTC

Last updated: February 23, 2003 - 5:46 pm UTC

Version: 9.2

Viewed 10K+ times! This question is

You Asked

Greetings Tom,

I have been wracking my brain to figure out a different way of doing this. I have had no luck. So before I quit on this statement all together, I thought I'd throw it your way.

SELECT /*+ PARALLEL (table_a,4) */
COL_A
,COL_B
,COL_C
,COL_D
,COL_E
,COL_F
,COL_CHNG_ID
FROM table_a
WHERE COL_G = 'ZQ'
AND COL_H= (
SELECT MAX(COL_H) col_h
FROM table_a sub_table_a
WHERE table_a.COL_A= sub_table_a.COL_A
AND table_a.COL_B= sub_table_a.COL_B
AND table_a.COL_C= sub_table_a.COL_C
AND table_a.COL_D= sub_table_a.COL_D
AND sub_table_a.COL_G =ZQ')

COL_A, B C D are a natural primary key. Can't change that.

There can be several values for col_h and I need the row that is returned to be the one with the max value for col_h. I am trying to figure out how to do this in one pass on the table, its quite large.

Update:

Sorry for the mistake, I had to rename the columns to take out the business information stuff :).
I need to select the max col_h where Col_g = 'ZQ'. Then I need the row set that comes with the max col_h for the natural key and a couple other columns.

Update:
For each primary key, there can be multiple combinations of
col_e, col_f COL_CHNG_ID and col_h. Further more Col A B C D are only a part of the natural key. Apparently, the natural key also has a few more columns, but I think that's irrelevant, the key thing to note here is that there are multiple combinations of col_h in conjuction with col_e col_f col_chng_id, along with multiple combinations of col_h in conjuction with the columns in the subquery where clause? Follow me? If not I can restate the question, perhaps with better column names! :)





and Tom said...

select *
from (
select a, b, c, d, e, f, chgn_id,
H,
max(H) over (partition by a,b,c,d) max_h
from table_a
where g = 'ZQ'
)
where h = max_h
/

should do it.

analytics rock and roll....

Rating

  (5 ratings)

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

Comments

indeed analytics Rock

J, January 10, 2003 - 2:39 pm UTC

Thanks Tom!

Additional difficulty

Josh, January 10, 2003 - 5:48 pm UTC

Tom,

I am using this as an inline view. In the query that it runs in, it is joined with an outer join to the driving table. When the predicate gets pushed to the view it puts an outer join on both h and max_h.

so that the predicate that gets put against the inline view looks like

h(+)=max_h(+)





Tom Kyte
January 10, 2003 - 7:11 pm UTC

er? not getting it. examples please

Never mind my friend

Josh, January 10, 2003 - 7:31 pm UTC

Thanks for your sweet code Tom.

I figured it out. But to explain futher, because of the self join, when I used this query as an inline view and outer joined on a,b,c,d to the driving table, the outer join predicate got pushed down to the underlying table in the inline view. This resulted in a predicate that looked like
h(+)=max_h(+) when it got rewritten. I got the predicates from the dbms_xplan. Which Rocks also!

I just removed the self join so now the query looks like

select a,b,c,d,e,f,chng_id
from (
select a, b, c, d, e, f, chgn_id
max(H) over (partition by a,b,c,d) max_h
from table_a
where g = 'ZQ'
)

Max query to return NULL when there is no data.

Matt, February 20, 2003 - 7:55 pm UTC

I have an inline view defined as below. The view is used to identify the values of t_sid for a set of days. The t_sid value is then checked against tables in the remainder of the query as a filter. THere is also a filter on the day column. The remaining tables have a similar t_sid column which is a number > 0, but may be NULL. As a result I need my query to either return the t_sid (if it exists) or else a NULL. I NVL the results (t_sid and day) in the remainder of the query.

This is the inline view:

SELECT t_sid, day FROM
(SELECT t_sid
,day
,MAX(ver) AS version
FROM
((SELECT lt.t_sid, lt.day, lt.ver
FROM my_tab lt
WHERE lt.rep_p = ' SOME TEXT'
AND lt.t_type = 'MY_TYPE'
AND lt.stat = 'S'
)
UNION ALL
(SELECT NULL AS t_sid, NULL AS day, NULL AS ver FROM sys.dual)
)
GROUP BY t_sid, day

This works well, since I always get a row returned. This row is all NULL if there is no data in the my_tab table that satisfy the query criteria. If there is data I get the results I want.

Can the query be written in a more efficient way?

There is an index on:

my_table(rep_p,day,t_type,stat)

Ideally I would like to avoid the sort and the FTS. Is this possible?

Thanks and Regards.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=331 Card=1766 Byte
s=42384)

1 0 SORT (GROUP BY) (Cost=331 Card=1766 Bytes=42384)
2 1 VIEW (Cost=325 Card=1766 Bytes=42384)
3 2 UNION-ALL
4 3 TABLE ACCESS (FULL) OF 'MY_TABLE' (Cost=324 Card
=1684 Bytes=183556)

5 3 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)




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

Tom Kyte
February 21, 2003 - 8:23 am UTC 

well, your index is "out of order" -- not very useful -- for this query as you stuck DAY in the middle of it.  if rep_p,t_type,stat were on the leading edge, it would make more sense.

I do not understand the spurious reference to VER in the query.  what is its purpose -- is this a whittled down example of something else?

Here is an example with a "better" index:

ops$tkyte@ORA920> @test
ops$tkyte@ORA920> /*
DOC>drop table t;
DOC>
DOC>create table t ( t_sid number, day number, ver number, rep_p varchar2(9), t_type varchar2(7), stat varchar2(1) );
DOC>create index t_idx on t(rep_p,t_type,stat,day,t_sid);
DOC>
DOC>insert into t
DOC>select 1, 1, 1, 'SOME TEXT', 'MY_TYPE', 'S'
DOC>  from all_objects;
DOC>insert into t
DOC>select 1, 1, 1, 'TEXT', 'MY_TYPE', 'S'
DOC>  from all_objects;
DOC>commit;
DOC>*/
ops$tkyte@ORA920>
ops$tkyte@ORA920> set autotrace on
ops$tkyte@ORA920> select rownum, a.* from (
  2  SELECT t_sid, day
  3    FROM t lt
  4   WHERE lt.rep_p = 'SOME TEXT'
  5     AND lt.t_type = 'MY_TYPE'
  6     AND lt.stat = 'S'
  7   UNION
  8  SELECT null, null
  9    from dual
 10  ) a
 11  /

    ROWNUM      T_SID        DAY
---------- ---------- ----------
         1          1          1
         2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT
   2    1     VIEW
   3    2       SORT (UNIQUE)
   4    3         UNION-ALL
   5    4           INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
   6    4           TABLE ACCESS (FULL) OF 'DUAL'




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

ops$tkyte@ORA920>
ops$tkyte@ORA920> select rownum, a.* from (
  2  SELECT t_sid, day
  3    FROM (SELECT t_sid ,day ,MAX(ver) AS version
  4            FROM ((SELECT lt.t_sid, lt.day, lt.ver
  5                     FROM t lt
  6                    WHERE lt.rep_p = 'SOME TEXT'
  7                      AND lt.t_type          = 'MY_TYPE'
  8                      AND lt.stat            = 'S')
  9                    UNION ALL
 10                   (SELECT NULL AS t_sid, NULL AS day, NULL AS ver
 11                                      FROM sys.dual)
 12                 )
 13           GROUP BY t_sid, day)
 14  ) a
 15  /

    ROWNUM      T_SID        DAY
---------- ---------- ----------
         1
         2          1          1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT
   2    1     VIEW
   3    2       SORT (GROUP BY)
   4    3         VIEW
   5    4           UNION-ALL
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'T'
   7    6               INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
   8    5             TABLE ACCESS (FULL) OF 'DUAL'




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

ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920>

Doh! - Missed the index ordering.

Matt, February 23, 2003 - 5:34 pm UTC

I missed the index ordering. I was focussed too much on generating the required results using analytics. I was trying something like

max(decode(ver)) over (partition by ...)

In order to avoid the union all to dual. I mistakenly believed that this would also avoid a sort. Not the case.

Can this the same results be achieved using analytics?

ver is a numeric that is a "count" of how many unique combinations of data there are in the table. This could be calculated on the fly I guess, but this value is typically this value is displayed to the end user. To avoid the calculation each time we just store it.

What you see is indeed a whittled down example.

Thanks for your response.

Tom Kyte
February 23, 2003 - 5:46 pm UTC

I'd need to see an unwhittled or less whittled one to understand really what is needed -- so i do not know if analytics can be used to answer your question.




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.