Michael-O

@JackDouglas, yes that was my first thought when I saw the improvement. I think a weekly job should do it.

@JackDouglas, yes this is how I understoood it now. I have ran another query with this range scan only. It performs fast, returns 1.14 million rows but still has overall costs of 26212. So this is simply the root cause. Since your answer led me into the right direction. It deserves an accept.

@JackDouglas, I am not using the hint. It was just used to see how the query would perform with the index after gathering stats. Getting back to the table, it has more than 60 millions rows. Probably that range scan makes the search extremely slow -- even with an index. I can attached a fully expanded tree. Will that help somehow? I narrowed it down to the important spot. Regarding the cardinalities, how can I enable this in SQL Developer? Thank you very much for the tips.

@ChrisSaxon, see my edit please.

@JackDouglas, as indicated in the tag, I am on 11.2.0.2.0. Quick means less than 10 seconds. The query runs in a webapp dozens of times per day. Prepared statements are employed.

@kevinsky, way better but I am on 3.6.2 and 3.x is not supported anymore .I am planning to migrate to 4.x next year. So, still outdated :-(

@kevinsky, thanks for the advise but this seems to be ages old -- Lucene 2.9. No option anymore.

@Ivan, XML isn't an option because it's to bloated.

Where did you find that? Besides Compass's JBDCDirectory I wasn't able to find anything else. The JBDCDirectory does not solve my problem.

This would be some sort of workaround but the column names would not match those which are passed to the function. This is a problem. I was thinking about rotating the whole issue to two columns and then pivoting the entire stuff. It would require some evaluation.

Yes, the second argument of the function contains all the columns the system command will return as CSV data.

Thanks, I will look into it but don't give the thing a big chance to solve my problem. After examing the thread, they are still talking about typed columns and so forth. This might be a solution as same as pivoting.

According to this I need to supply return type any collection (varray, nested table) needs to be typed. So thanks for the help. It won't work unless I use temp tables and return a cursor for it.

I would prefer returning a table and spare the users from any post-precessing. Something like this would be a good solution: select * from table(query_index('query...',['col1','col2',...])); A solution like this would work with a Java function, I guess.

Sad to hear that for a 50 000 $ product :-(. Though I will evaluate the Java approach. Maybe this will work otherwise I will have to provide the Lucene index as SOAP/REST service.

No no, it won't query a database. I will query a Lucene index and return the matching records. I want to make that result available in an transparent way to other DB users.