Oracle optimizer exploring

Hi!

The question is simple. There exists a view A_VIEW. The view is based on several joined tables. Each column in the view has its own index on the correspondent table. A select statement from this view is built and run dynamically using dbms_sql capability. The select statement retrievs all columns from the view. Each view column can participate in the WHERE clause of the select statement. String column builds condition with LIKE 'Sample%' filter. Date or number column builds condition with either
>=/<= signs or between sign. A column participates in the
mandatory for this select ORDER BY clause. The select statement is executed and some rows (10-30) are fetched from the cursor for the application presentation. The task is optimise the speed of the select executing and the retrieving of these first rows.

Average table size participating in the view is about 100'000 rows. Oracle 7.3.

Thinking about the solution brings the following result. In case when the final result set is big (more than 3000 records) the hint /*+ FIRST_ROWS */ produces good performance. In case of the final result set is small (less than 400 records) this hint works very bad but the hint
/*+ FIRST_ROWS INDEX(A_VIEW column_participated_in_order_by) */ produces good speed. Determination of the size of the result set before the statement executing is based on the empiric knowlage about the applicaiton and the custom table analyzing that means creation of the applicaiton histograms tables for each column.

The obvious questions here are:

1. Is it possible to force oracle optimiser run the statement in the optimal way automatically? It seems oracle has enough data for the task of determination of the size of the result set because of the dictionary histograms.
2. It is possible to use somehow oracle internal histograms? Does anybody know a way of converting the minimum and maximum column values stored in the dictionary after analysing into the real values and also histograms column values as well?

Thank you,
Igor.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Apr 08 1998 - 04:05:20 CDT