Re: usage of a calculated index
Date: Wed, 26 Nov 2008 10:09:41 +0100
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 184.108.40.206.0 - 64bit Production | With the Partitioning, OLAP and Oracle Data Mining options | JServer Release 220.127.116.11.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> 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.
Thanks to you both.
-- Norbert Oracle9i Enterprise Edition Release 18.104.22.168.0 64BitReceived on Wed Nov 26 2008 - 03:09:41 CST