You could also get my book, which has a sample walkthrough of a 4-table join in chapter 14.

Be aware of this comment from the Oracle pages though: “The 10053 is typically not the best place to start looking for tuning opportunities – the execution plan and TKProf have better information in this regard”

It is about a recursive query which is run Oracle Text index is being updated.
By the way some unusual setting is in place
optimizer_index_caching=90
optimizer_index_cost_adj=5
With default values for this two parameters CBO choose right index.

Note the function call in the predicate section. Your problem is an example of the “unknown range scan” dictating Oracle’s options for choosing a path.

There’s actually a little clue in the fragment of the trace file you sent me – the (AllEqGuess) on the first access path: “All Equality with Guessing”. It’s a little puzzling why the optimizer should choose the path you want when you get rid of the silly parameter settings – but there may be a scaling factor that limits the rule of ignoring the guess, there may simply be an odd code path that gets followed because of some side effect of the parameter settings (I found a couple of odd cases in the calculations which I think I mentioned in CBO – Fundamentals)