Re: select a where min(b)
From: steph <stephan0h_at_yahoo.de>
Date: Thu, 1 Apr 2010 00:32:28 -0700 (PDT)
Message-ID: <69f22659-fde1-420b-9192-50960b0f9e8a_at_l25g2000yqd.googlegroups.com>
On 31 Mrz., 22:44, Shakespeare <what..._at_xs4all.nl> wrote:
> Op 30-3-2010 22:55, ddf schreef:
>
>
>
> > On Mar 30, 10:45 am, magicwand<magicw..._at_gmx.at> wrote:
> >> On 30 Mrz., 16:20, ddf<orat..._at_msn.com> wrote:
>
> >>> On Mar 30, 6:40 am, steph<stepha..._at_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
Date: Thu, 1 Apr 2010 00:32:28 -0700 (PDT)
Message-ID: <69f22659-fde1-420b-9192-50960b0f9e8a_at_l25g2000yqd.googlegroups.com>
On 31 Mrz., 22:44, Shakespeare <what..._at_xs4all.nl> wrote:
> Op 30-3-2010 22:55, ddf schreef:
>
>
>
> > On Mar 30, 10:45 am, magicwand<magicw..._at_gmx.at> wrote:
> >> On 30 Mrz., 16:20, ddf<orat..._at_msn.com> wrote:
>
> >>> On Mar 30, 6:40 am, steph<stepha..._at_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
Received on Thu Apr 01 2010 - 02:32:28 CDT