Optimizer hint INDEX and record sort

[spicysudhi]

hi

below is the code:
-----------------
CREATE TABLE TEST( ID NUMBER(1), SNAME VARCHAR2(10));
CREATE INDEX SNAMEIDX ON TEST(SNAME);

INSERT INTO TEST VALUES(1,'A');
INSERT INTO TEST VALUES(1,'C');
INSERT INTO TEST VALUES(1,'B');
INSERT INTO TEST VALUES(1,'D');

SELECT * FROM ( SELECT SNAME FROM TEST WHERE SNAME IN('B','C'));

SELECT * FROM ( SELECT /*+INDEX (TEST SNAMEIDX) */ SNAME FROM TEST WHERE SNAME IN('B','C'));

------------------

above, the first select statement returns C and B but second returns B and C.

this could be a stupid question, but just want to make sure with the experts here. using Index hint, it automatically sorts the rows returned??

thanks in advance for ur comments.
Sudhi

 

[sem]

Without explicit ORDER BY you can not rely upon the order of rows! Oracle optimizer is too complex. Parallel query, current cache state and many other factors may affect that order. In some cases hint may be ignored completely. Though, if you add both ORDER BY clause and INDEX hint, most probably you'd get what you need.

Regards, Dima

http://seminihin.narod.ru