select ... order by PK performance

From: Kevin W. <kcw3388_at_hotmail.com>
Date: 27 Oct 2004 17:03:16 -0700
Message-ID: <69087156.0410271603.61b299a3@posting.google.com>

Hi,

I have two tables. The PK are reversed.

create table de_autotest_basic_s

(c1 varchar2(8),

     c2   varchar2(12),
     c3   number,
     constraint test_basic_s PRIMARY KEY (c1,c2 )

    );  

create table de_autotest_basic_s

(c1 varchar2(8),

     c2   varchar2(12),
     c3   number,
     constraint test_basic_s PRIMARY KEY (c2,c1 )

    );  

When I use the following sql statement and found out the PK columns are
in the same order in both cases.

SQL> select nvl(enabled,0) from sys.cdef$ where obj#=79994 and type# = 2;  

NVL(ENABLED,0)

---

         79995  

SQL> select c.segcol# from sys.col$ c, sys.icol$ i where i.obj#=79995 and i.bo#=c.obj# and i.col#=c.col#;  

   SEGCOL#

---

         1
         2

I got about one million rows with my tables. I wonder what will be the performance if I use:

1. select * from ...... order by "1,2" or
2. select * from ...... order by "2,1"

to both tables.

Same?

I know I can run a benchmark testing myself but I shared my server with other people. So, I may not able to get an accurate result.

Does any know have any ideas?

Thanks,

Kevin Received on Wed Oct 27 2004 - 19:03:16 CDT