Re: Make ORDER BY in a view?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 13 Dec 1998 19:54:30 GMT
Message-ID: <36841a07.21358221@192.86.155.100>

A copy of this was sent to "KeyStroke (Jack L. Swayze Sr.)" <KeyStrk_at_Feist.Com> (if that email address didn't require changing) On Thu, 10 Dec 1998 22:19:38 -0600, you wrote:

>Since you have gotten the 'company line' from the other two who posted a
>reply, I will tell you how to do it, and not preach to you that you
>shouldn't (I have frequently thought that such 'preaching' is simply an
>indication that the person answering isn't willing to admit that they
>don't know how to do it).

read on before...

>
>the trick to all this is that GROUP BY will order the result, just as
>well as ORDER BY will. and you _CAN_ have a GROUP BY in a view!
>
>Suppose a table has this structure:
>
>the_table
>---------
>pk1
>pk2
>nk1
>nk1
>
>and that pk1 and pk2 together (concatenated) form the primary key. Now
>suppose you want to create a view that shows the_table ordered by nk1
>and nk2. Here is what you do:
>
>create the_view as
>select pk1, pk2, nk1, nk2 from
>(select nk1, nk2, pk1, pk2, count(*) a_count
> from the_table
> group by nk1, nk2, pk1, pk2
>)
>;
>
>See, the trick is to have the primary key as part of the GROUP BY claus
>in the in-line view, then just strip away the count (which will always
>be the number '1') in the 'outer' select. If you don't have the
>primary key as part of the GROUP BY then you will be 'forgetting' some
>of the rows as they will be aggregated together (the count will be more
>than 1, but only one row will be returned).
>

I would recommend not relying on GROUP BY to SORT under any circumstances. Simple things such as indexes, optimizer modes, degree of parallism, partitions, and table organization will render a GROUP BY useless as a sorting device (never mind the performance implication of sorting the entire table before any predicates could be applied -- every time any queries through a view of the above type, with the group by, the group by must be done BEFORE any predicates the application puts on the view are done -- every time). If your application depends on the data being sorted, you had better sort it.

I will simply post 2 counter examples to the above and make no other comments.

example 1 -------------------------------------- SQL> create table the_table ( pk1 int, 2 pk2 int, 3 nk1 int not null, 4 nk2 int not null, 5 primary key(pk1,pk2) ) 6 / Table created.

SQL> create index the_table_idx on the_table(pk1,nk1,pk2,nk2) 2 /
Index created.

SQL> insert into the_table values (1, 1, 2, 2 ); 1 row created.

SQL> insert into the_table values (2, 1, 1, 1 ); 1 row created.

SQL> analyze table the_table compute statistics; Table analyzed.

SQL> set autotrace on explain

SQL> select nk1, nk2, pk1, pk2, count(*) a_count   2 from the_table
  3 group by nk1, nk2, pk1, pk2
  4 ;

NK1 NK2 PK1 PK2 A_COUNT ---------- ---------- ---------- ---------- ----------

         2          2          1          1          1
         1          1          2          1          1

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=30) 1 0 SORT (GROUP BY NOSORT) (Cost=1 Card=2 Bytes=30)

   2    1     INDEX (FULL SCAN) OF 'THE_TABLE_IDX' (NON-UNIQUE) (Cost=
          1 Card=2 Bytes=30)

end result -- data not sorted by NK1, NK2.
- example 2 ------------------------------------ SQL> create table the_table ( pk1 int, 2 pk2 int, 3 nk1 int, 4 nk2 int, 5 primary key(pk1,pk2) ) 6 organization index 7 /

Table created.

SQL>
SQL> insert into the_table values (1, 1, 2, 2 );

1 row created.

SQL> insert into the_table values (2, 1, 1, 1 );

1 row created.

SQL> 
SQL> set autotrace on explain
SQL> 
SQL> select nk1, nk2, pk1, pk2, count(*) a_count
  2    from     the_table

3 group by nk1, nk2, pk1, pk2
4 ;

NK1 NK2 PK1 PK2 A_COUNT ---------- ---------- ---------- ---------- ----------

         2          2          1          1          1
         1          1          2          1          1

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1 Bytes=104) 1 0 SORT (GROUP BY NOSORT) (Cost=28 Card=1 Bytes=104)

   2    1     INDEX (FULL SCAN) OF 'SYS_IOT_TOP_80023' (UNIQUE) (Cost=
          26 Card=1 Bytes=104)

end result -- data is not sorted by NK1, NK2

>suisum_at_ecn.ab.ca wrote:
>
>> Hi:
>>
>> I know that we can't use ORDER BY in a view. Is there any way to get
>> around this?
>>
>> --
>> Best regards,

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Dec 13 1998 - 13:54:30 CST