In a sybase procedure I see a SQL query as below
select max(mycolumn) from mytable (2) where id = @param1
What is this (2) means
If I want to rewrite this SQL for an oracle procedure, how do I do it
Assuming this is Sybase ASE ... <table_name> (X) is an index hint that says to use the index (on table <table_name>) that has index id = X (sysindexes.indid=X). In your example the hint says to use the index with indid=2.
If there is no index with indid=2 then the optimizer will ignore this hint and go about trying to determine what (if any) index to use.
For migration to Oracle (or any other RDBMS for that matter), just remove the (2).
While ASE obviously supports this feature it's not a good idea to use it unless you know with 100% certainty that the desired index will always have indid=2. Over time as indexes (including those supporting PK/UNIQ constraints) are added/dropped, the indid assigned to a specific index can change; the last thing you want (from a performance perspective) is to force the use of the wrong index (ie, indid=2 no longer references whatever index the SQL developer wanted to use).