Re: usage of a calculated index

From: Norbert Winkler <norbert.winkler1_at_gmx.de>
Date: Wed, 26 Nov 2008 10:09:41 +0100
Message-ID: <69ld4cx0ukfl$.1n4x5qa7d40xe.dlg@40tude.net>


I'm answering to both, David and William. Many thanks for your help.

Am Tue, 25 Nov 2008 07:55:33 -0800 (PST) schrieb ddf:

>
> There is an Oracle version involved? Is it possible for you to share
> such information???
> David Fitzjarrell

  | SQL*Plus: Release 10.2.0.3.0 - Production on Mi Nov 26 08:12:23 2008
  | Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
  | Verbunden mit:
  | Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
  | With the Partitioning, OLAP and Oracle Data Mining options
  | JServer Release 9.2.0.8.0 - Production

I've added it to mi signature.

Am Tue, 25 Nov 2008 18:27:06 +0100 schrieb Shakespeare:

> SELECT /*+ ALL_ROWS */ A_YEAR, A_VALUE, ....
That's does it - thanks.

Am Tue, 25 Nov 2008 10:03:33 -0800 (PST) schrieb ddf:

> SQL>
> SQL> exec dbms_stats.gather_table_stats(ownname => null, tabname =>
> 'X_EF', cascade=>true, estimate_percent =>99)

The procedure was succesful completed, but the simple select results again in "full table scan".

I've tested your script (with a table named X_CAI instead of X_EF), and - surprise - the Index IX_X_CAI_A_YEAR was taken.

I've deleted the records from X_CAI and inserted all from X_EF and executed gather_table_stats and the select resulted in "TABLE ACCESS (FULL)"

I've tested /*+ ALL_ROWS */ but it doesn't it anymore. and - negative surprise -
SELECT /*+ ALL_ROWS */ A_YEAR, A_VALUE, .... from X_EF results also in full table scan.
Possibly I've mislooked at the results in the above mentioned test.

It's the optimizers decision, obviously. Because of optimizer selectivity:
I've 7 years and about 15.000 records each year. David's statement generates 800 years and 12 records each year. I've tested a similiar index that includes the quarter (2 digits after the year)
It results in 26 year-quarters with about 4000 records each, and its index is used.
q.e.d.

Thanks to you both.

-- 
Norbert
Oracle9i Enterprise Edition Release 9.2.0.8.0 64Bit
Received on Wed Nov 26 2008 - 03:09:41 CST

Original text of this message