Question: May I know is there any solution to get the result without ordering in Oracle? It is because when I execute the query as follows, it seems to automatically helps me to sort it by ID field.

```
SELECT ID FROM USER WHERE ID IN (5004, 5003, 5005, 5002, 5008);

Actual results          Expected results
---5002                 ---5004
---5003                 ---5003
---5004                 ---5005
---5005                 ---5002
---5008                 ---5008

```

Million thanks if you guys have solutions on this.

Answer: 
if you want to keep the order as your in list, you can do something like this:

```
SQL> create type user_va as varray(1000) of number;
  2  /

Type created.

SQL> with users as (select /*+ cardinality(a, 10) */ rownum r, a.column_value user_id
  2                   from table(user_va(11, 0, 19, 5)) a)
  3  select d.user_id, d.username
  4    from dba_users d
  5         inner join users u
  6                 on u.user_id = d.user_id
  7   order by u.r
  8  /

   USER_ID USERNAME
---------- ------------------------------
        11 OUTLN
         0 SYS
        19 DIP
         5 SYSTEM

```

i.e we put the elements into a varray and assign a rownum prior to merging the set. we can then order by that `r` to maintain the order of our in list. The `cardinality` hint just tells the optimizer how many rows are in the array (doesn't have to be dead on, just in the ballpark..as without this, it will assume 8k rows and may prefer a full scan over an index approach)

if you don't have privs to create a type and this is just some adhoc thing, there's a few public ones:

```
select owner, type_name, upper_bound max_elements, length max_size, elem_type_name
  from all_Coll_types
 where coll_type = 'VARYING ARRAY' 
and elem_type_name in ('INTEGER', 'NUMBER');

```
There is no guarantee of sort order without an ORDER BY clause.