select a where min(b)
in [Oracle]
|
Prev: 2nd Listener
Next: #$%! Metalink is down again!!!
From: Robert Klemme on 30 Mar 2010 13:10 On 03/30/2010 04:45 PM, magicwand wrote: > But I still think, the solution of the OP is more efficient. Frankly, I'd also stick with the original solution just because it is easy to understand, rock solid and likely performs better because since it's so basic the optimizer is likely well tuned for this type of query. For the fun of it here are some more variants: -- join with inline view select a from step join ( select min(b) mb from step ) mini on step.b = mini.mb -- subquery factoring clause with mini as ( select min(b) mb from step ) select a from step join mini on step.b = mini.mb (From memory since I don't have a DB handy right now.) Of course there should be an index on B since it will make the min as well as the joins faster (sufficient amount of data assumed). If values are repetitive as shown in the original post then index compression should be considered, too. Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: ddf on 30 Mar 2010 16:55 On Mar 30, 10:45 am, magicwand <magicw...(a)gmx.at> wrote: > On 30 Mrz., 16:20, ddf <orat...(a)msn.com> wrote: > > > > > > > On Mar 30, 6:40 am, steph <stepha...(a)yahoo.de> wrote: > > > > I've got a table STEP defined as (a number, b number). It contains > > > these data: > > > > A,B > > > 1,1 > > > 1,2 > > > 1,3 > > > 4,3 > > > 2,3 > > > 2,1 > > > 5,0 > > > > Now I want to find this value of A where B is at it's minimum. > > > > I made up the following SQL: > > > > select a > > > from step > > > where b= > > > ( > > > select min(b) > > > from step > > > ) > > > > But I suspect there must be a much more elegant way to achieve this. > > > Is there? > > > > thanks, > > > Stephan > > > SQL> create table step(a number, b number); > > > Table created. > > > SQL> > > SQL> begin > > 2 for i in 1..10 loop > > 3 insert into step values (i, mod(i,4)); > > 4 end loop; > > 5 end; > > 6 / > > > PL/SQL procedure successfully completed. > > > SQL> > > SQL> select * > > 2 from step; > > > A B > > ---------- ---------- > > 1 1 > > 2 2 > > 3 3 > > 4 0 > > 5 1 > > 6 2 > > 7 3 > > 8 0 > > 9 1 > > 10 2 > > > 10 rows selected. > > > SQL> > > SQL> select a, b > > 2 from > > 3 (select a, b, dense_rank() over (order by b) rnk from step) > > 4 where rnk = 1; > > > A B > > ---------- ---------- > > 4 0 > > 8 0 > > > SQL> > > > David Fitzjarrell > > David, > > of course your statement is correct. > But I still think, the solution of the OP is more efficient. > > If there is an index on B (which, I'm sure we agree - should be there > anyway) you get the following plans: > > SQL> create index step_idx on step(b); > > Index created. > > SQL> set autotrace on > SQL> select a, b > 2 from > 3 (select a, b, dense_rank() over (order by b) rnk from step) > 4 where rnk = 1; > > A B > ---------- ---------- > 5 0 > > Execution Plan > ---------------------------------------------------------- > Plan hash value: 286943537 > > --------------------------------------------------------------------------------- > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| > Time | > --------------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | 7 | 273 | 3 (34)| > 00:00:01 | > |* 1 | VIEW | | 7 | 273 | 3 (34)| > 00:00:01 | > |* 2 | WINDOW SORT PUSHED RANK| | 7 | 182 | 3 (34)| > 00:00:01 | > | 3 | TABLE ACCESS FULL | STEP | 7 | 182 | 2 (0)| > 00:00:01 | > --------------------------------------------------------------------------------- > > Predicate Information (identified by operation id): > --------------------------------------------------- > > 1 - filter("RNK"=1) > 2 - filter(DENSE_RANK() OVER ( ORDER BY "B")<=1) > > Note > ----- > - dynamic sampling used for this statement > > Statistics > ---------------------------------------------------------- > 5 recursive calls > 0 db block gets > 15 consistent gets > 0 physical reads > 0 redo size > 464 bytes sent via SQL*Net to client > 416 bytes received via SQL*Net from client > 2 SQL*Net roundtrips to/from client > 1 sorts (memory) > 0 sorts (disk) > 1 rows processed > > SQL> select a > 2 from step > 3 where b= > 4 ( > 5 select min(b) > 6 from step > 7 ) ; > > A > ---------- > 5 > > Execution Plan > ---------------------------------------------------------- > Plan hash value: 3436790788 > > ----------------------------------------------------------------------------------------- > | Id | Operation | Name | Rows | Bytes | Cost > (%CPU)| Time | > ----------------------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | 1 | 26 | > 2 (0)| 00:00:01 | > | 1 | TABLE ACCESS BY INDEX ROWID | STEP | 1 | 26 | > 1 (0)| 00:00:01 | > |* 2 | INDEX RANGE SCAN | STEP_IDX | 1 | | > 1 (0)| 00:00:01 | > | 3 | SORT AGGREGATE | | 1 | 13 > | | | > | 4 | INDEX FULL SCAN (MIN/MAX)| STEP_IDX | 7 | 91 | > 1 (0)| 00:00:01 | > ----------------------------------------------------------------------------------------- > > Predicate Information (identified by operation id): > --------------------------------------------------- > > 2 - access("B"= (SELECT MIN("B") FROM "STEP" "STEP")) > > Note > ----- > - dynamic sampling used for this statement > > Statistics > ---------------------------------------------------------- > 38 recursive calls > 0 db block gets > 25 consistent gets > 0 physical reads > 0 redo size > 411 bytes sent via SQL*Net to client > 416 bytes received via SQL*Net from client > 2 SQL*Net roundtrips to/from client > 0 sorts (memory) > 0 sorts (disk) > 1 rows processed > > SQL> > > regards > Werner- Hide quoted text - > > - Show quoted text - Take it for what it is: a variant of Shakespeare's offering that will return all 'interested' rows. No claim was made of efficiency, and I see no issue with the original query. The OP, however, asked if there exists a more 'elegant' solution. I don't know how 'elegant' my offering may be, but it's presented for the sake of having another option to return the requested data. David Fitzjarrell
From: Shakespeare on 31 Mar 2010 16:44 Op 30-3-2010 22:55, ddf schreef: > On Mar 30, 10:45 am, magicwand<magicw...(a)gmx.at> wrote: >> On 30 Mrz., 16:20, ddf<orat...(a)msn.com> wrote: >> >> >> >> >> >>> On Mar 30, 6:40 am, steph<stepha...(a)yahoo.de> wrote: >> >>>> I've got a table STEP defined as (a number, b number). It contains >>>> these data: >> >>>> A,B >>>> 1,1 >>>> 1,2 >>>> 1,3 >>>> 4,3 >>>> 2,3 >>>> 2,1 >>>> 5,0 >> >>>> Now I want to find this value of A where B is at it's minimum. >> >>>> I made up the following SQL: >> >>>> select a >>>> from step >>>> where b= >>>> ( >>>> select min(b) >>>> from step >>>> ) >> >>>> But I suspect there must be a much more elegant way to achieve this. >>>> Is there? >> >>>> thanks, >>>> Stephan >> >>> SQL> create table step(a number, b number); >> >>> Table created. >> >>> SQL> >>> SQL> begin >>> 2 for i in 1..10 loop >>> 3 insert into step values (i, mod(i,4)); >>> 4 end loop; >>> 5 end; >>> 6 / >> >>> PL/SQL procedure successfully completed. >> >>> SQL> >>> SQL> select * >>> 2 from step; >> >>> A B >>> ---------- ---------- >>> 1 1 >>> 2 2 >>> 3 3 >>> 4 0 >>> 5 1 >>> 6 2 >>> 7 3 >>> 8 0 >>> 9 1 >>> 10 2 >> >>> 10 rows selected. >> >>> SQL> >>> SQL> select a, b >>> 2 from >>> 3 (select a, b, dense_rank() over (order by b) rnk from step) >>> 4 where rnk = 1; >> >>> A B >>> ---------- ---------- >>> 4 0 >>> 8 0 >> >>> SQL> >> >>> David Fitzjarrell >> >> David, >> >> of course your statement is correct. >> But I still think, the solution of the OP is more efficient. >> >> If there is an index on B (which, I'm sure we agree - should be there >> anyway) you get the following plans: >> >> SQL> create index step_idx on step(b); >> >> Index created. >> >> SQL> set autotrace on >> SQL> select a, b >> 2 from >> 3 (select a, b, dense_rank() over (order by b) rnk from step) >> 4 where rnk = 1; >> >> A B >> ---------- ---------- >> 5 0 >> >> Execution Plan >> ---------------------------------------------------------- >> Plan hash value: 286943537 >> >> ---------------------------------------------------------------------------�------ >> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| >> Time | >> ---------------------------------------------------------------------------�------ >> | 0 | SELECT STATEMENT | | 7 | 273 | 3 (34)| >> 00:00:01 | >> |* 1 | VIEW | | 7 | 273 | 3 (34)| >> 00:00:01 | >> |* 2 | WINDOW SORT PUSHED RANK| | 7 | 182 | 3 (34)| >> 00:00:01 | >> | 3 | TABLE ACCESS FULL | STEP | 7 | 182 | 2 (0)| >> 00:00:01 | >> ---------------------------------------------------------------------------�------ >> >> Predicate Information (identified by operation id): >> --------------------------------------------------- >> >> 1 - filter("RNK"=1) >> 2 - filter(DENSE_RANK() OVER ( ORDER BY "B")<=1) >> >> Note >> ----- >> - dynamic sampling used for this statement >> >> Statistics >> ---------------------------------------------------------- >> 5 recursive calls >> 0 db block gets >> 15 consistent gets >> 0 physical reads >> 0 redo size >> 464 bytes sent via SQL*Net to client >> 416 bytes received via SQL*Net from client >> 2 SQL*Net roundtrips to/from client >> 1 sorts (memory) >> 0 sorts (disk) >> 1 rows processed >> >> SQL> select a >> 2 from step >> 3 where b= >> 4 ( >> 5 select min(b) >> 6 from step >> 7 ) ; >> >> A >> ---------- >> 5 >> >> Execution Plan >> ---------------------------------------------------------- >> Plan hash value: 3436790788 >> >> ---------------------------------------------------------------------------�-------------- >> | Id | Operation | Name | Rows | Bytes | Cost >> (%CPU)| Time | >> ---------------------------------------------------------------------------�-------------- >> | 0 | SELECT STATEMENT | | 1 | 26 | >> 2 (0)| 00:00:01 | >> | 1 | TABLE ACCESS BY INDEX ROWID | STEP | 1 | 26 | >> 1 (0)| 00:00:01 | >> |* 2 | INDEX RANGE SCAN | STEP_IDX | 1 | | >> 1 (0)| 00:00:01 | >> | 3 | SORT AGGREGATE | | 1 | 13 >> | | | >> | 4 | INDEX FULL SCAN (MIN/MAX)| STEP_IDX | 7 | 91 | >> 1 (0)| 00:00:01 | >> ---------------------------------------------------------------------------�-------------- >> >> Predicate Information (identified by operation id): >> --------------------------------------------------- >> >> 2 - access("B"= (SELECT MIN("B") FROM "STEP" "STEP")) >> >> Note >> ----- >> - dynamic sampling used for this statement >> >> Statistics >> ---------------------------------------------------------- >> 38 recursive calls >> 0 db block gets >> 25 consistent gets >> 0 physical reads >> 0 redo size >> 411 bytes sent via SQL*Net to client >> 416 bytes received via SQL*Net from client >> 2 SQL*Net roundtrips to/from client >> 0 sorts (memory) >> 0 sorts (disk) >> 1 rows processed >> >> SQL> >> >> regards >> Werner- Hide quoted text - >> >> - Show quoted text - > > Take it for what it is: a variant of Shakespeare's offering that will > return all 'interested' rows. No claim was made of efficiency, and I > see no issue with the original query. The OP, however, asked if > there exists a more 'elegant' solution. I don't know how 'elegant' my > offering may be, but it's presented for the sake of having another > option to return the requested data. > > > David Fitzjarrell Agree. I'd go for the original query, but I like the dense_rank example as a way of showing its use. Shakespeare
From: steph on 1 Apr 2010 03:32
On 31 Mrz., 22:44, Shakespeare <what...(a)xs4all.nl> wrote: > Op 30-3-2010 22:55, ddf schreef: > > > > > On Mar 30, 10:45 am, magicwand<magicw...(a)gmx.at> wrote: > >> On 30 Mrz., 16:20, ddf<orat...(a)msn.com> wrote: > > >>> On Mar 30, 6:40 am, steph<stepha...(a)yahoo.de> wrote: > > >>>> I've got a table STEP defined as (a number, b number). It contains > >>>> these data: > > >>>> A,B > >>>> 1,1 > >>>> 1,2 > >>>> 1,3 > >>>> 4,3 > >>>> 2,3 > >>>> 2,1 > >>>> 5,0 > > >>>> Now I want to find this value of A where B is at it's minimum. > > >>>> I made up the following SQL: > > >>>> select a > >>>> from step > >>>> where b= > >>>> ( > >>>> select min(b) > >>>> from step > >>>> ) > > >>>> But I suspect there must be a much more elegant way to achieve this. > >>>> Is there? > > >>>> thanks, > >>>> Stephan > > >>> SQL> create table step(a number, b number); > > >>> Table created. > > >>> SQL> > >>> SQL> begin > >>> 2 for i in 1..10 loop > >>> 3 insert into step values (i, mod(i,4)); > >>> 4 end loop; > >>> 5 end; > >>> 6 / > > >>> PL/SQL procedure successfully completed. > > >>> SQL> > >>> SQL> select * > >>> 2 from step; > > >>> A B > >>> ---------- ---------- > >>> 1 1 > >>> 2 2 > >>> 3 3 > >>> 4 0 > >>> 5 1 > >>> 6 2 > >>> 7 3 > >>> 8 0 > >>> 9 1 > >>> 10 2 > > >>> 10 rows selected. > > >>> SQL> > >>> SQL> select a, b > >>> 2 from > >>> 3 (select a, b, dense_rank() over (order by b) rnk from step) > >>> 4 where rnk = 1; > > >>> A B > >>> ---------- ---------- > >>> 4 0 > >>> 8 0 > > >>> SQL> > > >>> David Fitzjarrell > > >> David, > > >> of course your statement is correct. > >> But I still think, the solution of the OP is more efficient. > > >> If there is an index on B (which, I'm sure we agree - should be there > >> anyway) you get the following plans: > > >> SQL> create index step_idx on step(b); > > >> Index created. > > >> SQL> set autotrace on > >> SQL> select a, b > >> 2 from > >> 3 (select a, b, dense_rank() over (order by b) rnk from step) > >> 4 where rnk = 1; > > >> A B > >> ---------- ---------- > >> 5 0 > > >> Execution Plan > >> ---------------------------------------------------------- > >> Plan hash value: 286943537 > > >> --------------------------------------------------------------------------------- > >> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| > >> Time | > >> --------------------------------------------------------------------------------- > >> | 0 | SELECT STATEMENT | | 7 | 273 | 3 (34)| > >> 00:00:01 | > >> |* 1 | VIEW | | 7 | 273 | 3 (34)| > >> 00:00:01 | > >> |* 2 | WINDOW SORT PUSHED RANK| | 7 | 182 | 3 (34)| > >> 00:00:01 | > >> | 3 | TABLE ACCESS FULL | STEP | 7 | 182 | 2 (0)| > >> 00:00:01 | > >> --------------------------------------------------------------------------------- > > >> Predicate Information (identified by operation id): > >> --------------------------------------------------- > > >> 1 - filter("RNK"=1) > >> 2 - filter(DENSE_RANK() OVER ( ORDER BY "B")<=1) > > >> Note > >> ----- > >> - dynamic sampling used for this statement > > >> Statistics > >> ---------------------------------------------------------- > >> 5 recursive calls > >> 0 db block gets > >> 15 consistent gets > >> 0 physical reads > >> 0 redo size > >> 464 bytes sent via SQL*Net to client > >> 416 bytes received via SQL*Net from client > >> 2 SQL*Net roundtrips to/from client > >> 1 sorts (memory) > >> 0 sorts (disk) > >> 1 rows processed > > >> SQL> select a > >> 2 from step > >> 3 where b= > >> 4 ( > >> 5 select min(b) > >> 6 from step > >> 7 ) ; > > >> A > >> ---------- > >> 5 > > >> Execution Plan > >> ---------------------------------------------------------- > >> Plan hash value: 3436790788 > > >> ----------------------------------------------------------------------------------------- > >> | Id | Operation | Name | Rows | Bytes | Cost > >> (%CPU)| Time | > >> ----------------------------------------------------------------------------------------- > >> | 0 | SELECT STATEMENT | | 1 | 26 | > >> 2 (0)| 00:00:01 | > >> | 1 | TABLE ACCESS BY INDEX ROWID | STEP | 1 | 26 | > >> 1 (0)| 00:00:01 | > >> |* 2 | INDEX RANGE SCAN | STEP_IDX | 1 | | > >> 1 (0)| 00:00:01 | > >> | 3 | SORT AGGREGATE | | 1 | 13 > >> | | | > >> | 4 | INDEX FULL SCAN (MIN/MAX)| STEP_IDX | 7 | 91 | > >> 1 (0)| 00:00:01 | > >> ----------------------------------------------------------------------------------------- > > >> Predicate Information (identified by operation id): > >> --------------------------------------------------- > > >> 2 - access("B"= (SELECT MIN("B") FROM "STEP" "STEP")) > > >> Note > >> ----- > >> - dynamic sampling used for this statement > > >> Statistics > >> ---------------------------------------------------------- > >> 38 recursive calls > >> 0 db block gets > >> 25 consistent gets > >> 0 physical reads > >> 0 redo size > >> 411 bytes sent via SQL*Net to client > >> 416 bytes received via SQL*Net from client > >> 2 SQL*Net roundtrips to/from client > >> 0 sorts (memory) > >> 0 sorts (disk) > >> 1 rows processed > > >> SQL> > > >> regards > >> Werner- Hide quoted text - > > >> - Show quoted text - > > > Take it for what it is: a variant of Shakespeare's offering that will > > return all 'interested' rows. No claim was made of efficiency, and I > > see no issue with the original query. The OP, however, asked if > > there exists a more 'elegant' solution. I don't know how 'elegant' my > > offering may be, but it's presented for the sake of having another > > option to return the requested data. > > > David Fitzjarrell > > Agree. I'd go for the original query, but I like the dense_rank example > as a way of showing its use. > > Shakespeare That's a real surprise for me that my original query is recommended. Thought that some fancy analytic function would do better. Anyway thanks everybody for their partivipation:) regards, stephan |