Some simple definitions below:

parallel
- I want to use more server resources to complete a task. So the database will invoke more than 1 session (ie, "slaves") to divide a large task into several smaller ones that will run concurrently.

append
- I want to insert lots of rows. Rather than go hunting in existing blocks for free space, I will add them all at the "end" of the table in fresh blocks thus making it more efficiently load them in bulk. (The whole table will be locked whilst I do it)

index
- Tell the the optimizer that I want to use an index, or a particular index, when running this query.

no_index
- The opposite of above. Tell the optimizer not to use an index, or a particular index.

(Both index/no_index hints normally mean some underlying issue you might want to tackle, ie, *why* did the optimizer make a bad choice...it might be out of date or missing statistics etc. Ideally you shouldnt need to use index/no_index hints)

first_rows/all_rows
- When you run a query that will return (say) 1000 rows, you are letting the optimizer know what is more important to you:
a) getting the first batch of rows back as quickly as possible,
b) getting all of the rows back as quickly as possible

Think about (say) a Google search on "Oracle" - there's millions of hits, but I really want the first page of results to come back quick. That's "first_rows".

But if I wanted to (say) add up all of last years sales by day, then seeing the first week quickly doesnt matter to me - I want *all* of the days. Thats "all_rows"