Re: Ingress Knowledge Management - Statistics-Based Optimizer

In article <gavin.713173622_at_mulgulgum>, gavin_at_asis.unimelb.EDU.AU (Gavin Trigg) writes:
> rrabin_at_atl.ge.com (Richard Rabin) writes:
>
>

 stuff deleted
>
> The statistics based optimizer is part of the base package. I understand that
> Sybase operates in a similar way. I seem to remember reading somewhere lately
> that Oracle was working on something similar, anyone know?
>

Yes, Oracle now has a statistical optimiser. It can be found in the Version 7 of Oracle. I have not had much experience with it but can tell you that is is what I call a 'half-baked' statistical optimiser.

Let me explain myself. Ingres has what I call resource limiter. This is to stop 'run away' processes from executing. For example,

    SELECT * from TRANSACTION_TABLE;

where number of rows in the millions and each row takes Kbytes. If there was resource limit of say 1,000 rows for a particular user and he types the above select, the optimiser would infer that the query will definitely retrieve more than its quota and does not proceed with the statement.

Oracle on the other hand, only stops the query when the limit has been reached ie. after the event has occurred. Try this for example,

	no of rows = 1,000,001
	limit set at 1,000,000
	select * from large_table;

On the other hand it does have some nice features. Like you are able to over-ride the optimiser and provide it with 'hints'. Below are 2 example:

      SELECT --+ NOCOST
            empno, ename, sal, job
            FROM emp
            WHERE  empno = 7566

      SELECT /*+ INDEX(accounts accounts_index) */ accno, bal
            FROM accounts
            WHERE accno = 7086854

using different syntax. The 1st example asks the optimiser to not use the statistics and the 2nd forces it to use the index accounts_index on table accounts.

-- 
-----------------------------------------------------------------------------
 _____       ________ / ____ |Tony Jambu, Database Administrator
  /_  __       /_ __ /       |Colonial Mutual Life Australia. (ACN 004021809)
 /(_)/ ((_/ \_/(///(/_)/_(   |EMAIL:  TJambu_at_cmutual.com.au