Re: select ... order by PK performance

Kevin W. wrote:
> 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 )
> );
>

[...]
>
> 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?
>

[...]

Hello Kevin,

yes the performance will be the same since no index is used ordering by character columns unless you are using nls_sort=BINARY. If you set nls_sort to BINARY I think the index is used if you order by the columns in index order.

Hope that helps,

Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de