Re: usage of a calculated index

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 25 Nov 2008 20:45:11 +0100
Message-ID: <492c55c9$0$187$e4fe514c@news.xs4all.nl>

ddf schreef:
> On Nov 25, 11:27 am, Shakespeare <what..._at_xs4all.nl> wrote:

>> Norbert Winkler schreef:
>>
>>
>>
>>
>>
>>> Hello,
>>> I've created the following table and view:
>>> _____
>>> CREATE TABLE X_EF (
>>>   ID_EF    INTEGER      NOT NULL,
>>>   A_VALUE  VARCHAR2(1)  NOT NULL,
>>>   A_NUMBER NUMBER(15,0) NOT NULL
>>> )
>>> /
>>> CREATE INDEX IX_X_EF_A_YEAR
>>>   ON X_EF (
>>>     TRUNC(A_NUMBER/100000000,0)
>>>   )
>>> /
>>> CREATE INDEX IX_X_EF_A_VALUE
>>>   ON X_EF (
>>>     A_VALUE
>>>   )
>>> /
>>> create view XV_EF as
>>> SELECT d.*, trunc(A_NUMBER/100000000,0) A_YEAR
>>> FROM X_EF
>>> /_____
>>> I've tested this SELECT's
>>> ____
>>> SELECT A_YEAR, A_VALUE, Count(*) Anz
>>> FROM XV_EF
>>> WHERE A_YEAR = 2008
>>> Group By A_YEAR, A_VALUE;
>>> --> 3 A_VALUEs
>>> SELECT A_YEAR, A_VALUE, Count(*) Anz
>>> FROM XV_EF
>>> WHERE A_VALUE = '1'
>>> Group By A_YEAR, A_VALUE;
>>> --> 7 Years
>>> ____
>>> the "execution plan" shows for "WHERE A_VALUE" the usage of
>>> IX_X_EF_A_VALUE;
>>> but for "WHERE A_YEAR" I see "full table scan".
>>> I think I've read that using the same expression in SELECT likE in CREATE
>>> INDEX causes the usage of the index.
>>> Thanks for any advice!
>> If it's Oracle 10.x: what is your optimizer goal?
>> if I perform your query with
>> SELECT /*+ ALL_ROWS */ A_YEAR, A_VALUE, ....
>>
>> the query uses its function based index.
>> With 'CHOOSE' it does not (at least not until you calculate your
>> statistics!)
>>
>> Shakespeare- Hide quoted text -
>>
>> - Show quoted text -

>
> Using 11.1.0.6 I get this:
>
> SQL> CREATE TABLE X_EF (
> 2 ID_EF INTEGER NOT NULL,
> 3 A_VALUE VARCHAR2(1) NOT NULL,
> 4 A_NUMBER NUMBER(15,0) NOT NULL
> 5 )
> 6 /
>
> Table created.
>
> SQL>
> SQL> begin
> 2 for i in 1..10000 loop
> 3 insert into x_ef
> 4 values(i, chr(mod(i, 256)), to_number(to_char(add_months
> (sysdate,i),'RRRRMMDDHH24MI')));
> 5 end loop;
> 6 end;
> 7 /
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> CREATE INDEX IX_X_EF_A_YEAR
> 2 ON X_EF (
> 3 trunc(A_NUMBER/100000000)
> 4 )
> 5 /
>
> Index created.
>
> SQL>
> SQL> CREATE INDEX IX_X_EF_A_VALUE
> 2 ON X_EF (
> 3 A_VALUE
> 4 )
> 5 /
>
> Index created.
>
> SQL>
> SQL> exec dbms_stats.gather_table_stats(ownname => null, tabname =>
> 'X_EF', cascade=>true, estimate_percent =>99)
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> set autotrace on
> SQL>
> SQL> create view XV_EF as
> 2 SELECT id_ef, a_value, a_number, trunc(A_NUMBER/100000000) A_YEAR
> 3 FROM X_EF
> 4 /
>
> View created.
>
> SQL>
> SQL> SELECT A_YEAR, A_VALUE, Count(*) Anz
> 2 FROM XV_EF
> 3 WHERE A_YEAR = 2008
> 4 Group By A_YEAR, A_VALUE;
>
> A_YEAR A ANZ
> --------------- - ---------------
> 2008
1
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 451250037
>
> -----------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes
> | Cost (%CPU)| Time |
> -----------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 9 | 54
> | 3 (34)| 00:00:01 |
> | 1 | HASH GROUP BY | | 9 | 54
> | 3 (34)| 00:00:01 |
> | 2 | TABLE ACCESS BY INDEX ROWID| X_EF | 12 | 72
> | 2 (0)| 00:00:01 |
> |* 3 | INDEX RANGE SCAN | IX_X_EF_A_YEAR | 12 |
> | 1 (0)| 00:00:01 |
> -----------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 3 - access(TRUNC("A_NUMBER"/100000000)=2008)
>
>
> Statistics
> ----------------------------------------------------------
> 8 recursive calls
> 0 db block gets
> 5 consistent gets
> 0 physical reads
> 0 redo size
> 532 bytes sent via SQL*Net to client
> 416 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL>
> SQL> SELECT A_YEAR, A_VALUE, Count(*) Anz
> 2 FROM XV_EF
> 3 WHERE A_VALUE = '1'
> 4 Group By A_YEAR, A_VALUE;
>
> A_YEAR A ANZ
> --------------- - ---------------
> 2034 1 1
> 2396 1 1
> 2247 1 1
> 2460 1 1
> 2482 1 1
> 2588 1 1
> 2162 1 1
> 2012 1 1
> 2418 1 1
> 2823 1 1
> 2076 1 1
>
> A_YEAR A ANZ
> --------------- - ---------------
> 2439 1 1
> 2332 1 1
> 2375 1 1
> 2610 1 1
> 2695 1 1
> 2738 1 1
> 2290 1 1
> 2183 1 1
> 2652 1 1
> 2546 1 1
> 2567 1 1
>
> A_YEAR A ANZ
> --------------- - ---------------
> 2716 1 1
> 2354 1 1
> 2204 1 1
> 2268 1 1
> 2674 1 1
> 2140 1 1
> 2098 1 1
> 2226 1 1
> 2503 1 1
> 2524 1 1
> 2119 1 1
>
> A_YEAR A ANZ
> --------------- - ---------------
> 2311 1 1
> 2780 1 1
> 2055 1 1
> 2631 1 1
> 2802 1 1
> 2759 1 1
>
> 39 rows selected.
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 672759038
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 27 | 162 | 10 (10)|
> 00:00:01 |
> | 1 | HASH GROUP BY | | 27 | 162 | 10 (10)|
> 00:00:01 |
> |* 2 | TABLE ACCESS FULL| X_EF | 39 | 234 | 9 (0)|
> 00:00:01 |
> ---------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("A_VALUE"='1')
>
>
> Statistics
> ----------------------------------------------------------
> 8 recursive calls
> 0 db block gets
> 33 consistent gets
> 0 physical reads
> 0 redo size
> 1159 bytes sent via SQL*Net to client
> 438 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 39 rows processed
>
> SQL>
> SQL> set autotrace off
> SQL> set echo off
>
> which doesn't match what you report in your original post. Again,
> POST which Oracle version you're using. Also it would be nice to
> supply the same sample data you're using.
>
>
> David Fitzjarrell

And the optimizer settings.

Shakespeare Received on Tue Nov 25 2008 - 13:45:11 CST