There are several questions that need asking about this query that you should think about.

1) many of us have seen often the use of counts to do existential queries. So it is prudent to ask, do you really need to know how many rows there are that meet your criteria, or do you only need to know if there is at least one. If the answer is you need only know there is at least one then you can recast your count as an existential query and it will go real fast.

2) along the same lines, there are mean "dead ends" in your query where clause. you do a lot of joining but in the end never actually look at any of the real data that any of these joins lead you to. This is of course a reflection of your count, but it suggests, is it necessary to actually do all these joins. The likelyhood is yes it is but you should know this to be true if you wish to get the correct answer.

3) out of curiosity, is this code you wrote, or are you inheriting this from developers that no longer work on your project?

4) as a comment, your database design leaves much to be desired as can be noted by the fact that there are many fields for which you are forced to do substring lookups on. You should be able to describe what it is you are substriging. if this is a new system you are building the you should redo your data model so that these substr operations are not required.

5) one last thing. When the oracle cost based optimizer gets it wrong, it is almost always because it has not computed a correct number of rows being returned for one or more steps on a query plan. this is most often because statistics have not been collected on the data. I suggest you collect stats on your schema (compile, not estimate, and cascade=>true to make sure you get indexes). then try your query again.

OK, as has been indicated, you have posted little to go on. But as was also noted, the tuning process is generally the same most of the time so here is one idea.

If this query is very important to you, you can consider constructing indexes specifically to make it go fast. You would have two ideas in mind here:

1) you want your query plan to not visit any tables, only indexes
2) you want to try and elimintate as much data as possible up front in your first two or three rowsource accesses

Here are a suggested set of indexes. Examine them and try to understand how these indexes might achieve these two goals.

Cowardly disclaimer: if any of the ideas suggested here hose you, don't come looking for me or anyone else here. You should be doing this work with your DBA at your side. You DBA is an asset you should be exploiting and learning from.

Good luck, I leave it to you to find any syntax errors in this stuff. Kevin