Re: Delphi/Oracle Database Perfromance - Order By Problem

Hello, Eddy:

In article <gmsTuAA1LIFyEw$x_at_mcts.demon.co.uk>, Edward J Hooper <eddy_at_mcts.demon.co.uk> wrote:
>Would appreciate any comments on the following problem
>
 (snip)
>
>We used a third party Oracle tool that allowed us to view the shared
>pool in the SGA. We could see that the principal query that accessed the
>doc. table is in fact a full table scan, a select with no where clause,
>followed by an order by clause using an index placed on the time_enter
>column. The full table scan on the doc. table is highly inefficient, and
>was thought to be the most likely cause of the performance problem as
>the whole table is being scanned before the user sees any results.
>
> SELECT NAME_PRE,NAME_ID,NAME_DESCR,TEXTSTART
> TIME_ENTER,INCOMING,SENT,MARKDOC,CATEGORY,LINE_ID
> SENDER_ID,REMARK,D_RIGHTS,U_RIGHTS,READBY,ROWID
> FROM MARK.DOC ORDER BY TIME_ENTER ASC
>
>An examination of the Delphi source revealed that it uses the Borland
>Ttables class functions rather than those of the Tquery which allow
>standard sql queries to be issued.
>
>It was generally felt that the relationship between the Borland Ttable
>data access functions and the Oracle server is not a happy one and
>Oracle appeared to be satisfying Ttable requests by performing a full
>table scan.
>
>The general recommendation was to replace all Ttable calls with Tquery
>calls, as this may then result in the required performance gains. This
>would mean significant re-coding of COMM1, so we continued to look for
>a more efficient solution.

A SELECT without a WHERE clause properly retrieves the target columns from all rows of the table (i.e., a "full table scan", although that term is more often applied to a portion of an execution plan that retrieves all rows of a table). Conceptually, the entire table must be read/scanned prior to the sorting of the result set due to the ORDER BY clause. The existence of an index on the TIME_ENTER column should allow an optimizer to retrieve the result set in the desired order without having to perform an actual sort.

This sounds familiar, but I unfortunately do not have any documentation ready to hand. I vaguely remember that a simple SELECT...ORDER BY of this sort should use the index, but I am not sure whether this optimization is present at all, or in specfic Oracle versions. It may also depend on whether the index is unique or non-unique. I also do not remember whether this can be coerced/cajoled via hints.

A primary key index, while proper, does not apply to this particular problem.

All I can suggest, until I can consult some Oracle documentation, is to ensure that an index on the TIME_ENTER column does exist, and to review the init.ora arguments pertaining to sort optimization. (If you can't obviate the sort, you may as well speed it up).

Also, when presenting problems of this sort, please perform an EXPLAIN PLAN on the statement(s) and provide the formatted output.

>
>We looked at the Oracle query optimization mode, you are able to
>provide hints to oracle in the query, e.g. tell it to use RULE mode
>and/or an index. Our database is currently set to CHOOSE as the
>optimization mode, we wanted to change this to use RULE based
>optimization, as this would allow us access by using the order by clause
>on indexed columns. After some several failed attempts to change the
>optimization mode on our database to rule, we decided that this would
>not work on our current query anyway because we are not using a where
>clause.
>

Leave it at CHOOSE. The addition of hints allows you to dynamically change how the optimizer works, including a RULE hint.

>4. CONCLUSION
>We believe that the performance problem is caused by the order by clause
>on the time_enter field in the main query used to access the doc table.
>Although the time_enter field is indexed, the index does not appear to
>be being used, instead Oracle is doing a full table scan to fulfill the
>order by.

Actually, a full table scan as mandated (conceptually) by the absence of a WHERE clause, followed by a sort as (conceptually) mandated by the ORDER BY.

>We need to find a way to force the index to be used, so that the data is
>returned immediatley.
>We are using delphi ttable components, and have no means of optimizing
>the SQL statement produced by the component, so we hope the answer lies
>within the Oracle database.

The statement appears to defy optimization. Aside from speeding up the sort, the only other way is to induce usage of the index. I'll check the manuals over the weekend.

Regards,

Gary

-- 
__________________________________________________________
Gary Gapinski                email: Gapinski_at_lerc.nasa.gov
NASA Lewis Research Center   voice: +1 216 433 5251