SQLplus result with/without PK.

[pawi73]

Can anyone please explain the difference Oracle does regarding this to me?
Having an index or not on a varchar2 field gives different result when using 0 or '0' (zero that is). See the test below.

Test:
* Create a table with a varchar2 field.
create table test(aaa varchar2(5));
* Populate the table with rows containing numbers.
insert into test (aaa) values('&var_number');
* Run the queries
select aaa from test where aaa > 0 and rownum < 6;
select aaa from test where aaa > '0' and rownum < 6;
and you get the same result.
* Create an index on the table
create index test_idx on test(aaa);
* Run the queries from above again, now
the order is different.

Regards
Patrik

 

[carp]

Patrik -
This question would be more appropriate in one of the Oracle forums instead of ANSI SQL.

With that said, the answer to your question is that because Oracle can use the index you have created, it uses the index to retrieve rows rather than just traversing the table. While you did not mention what the display order looks like, my guess is that with the index, the results come back in a sorted order rather than a nonsorted order (which you may have received prior to the creation of the index).