Re: usage of a calculated index
Date: Tue, 25 Nov 2008 10:03:33 -0800 (PST)
Message-ID: <e7e898bf-0b67-4863-acb0-7df2ef368daf@j32g2000yqn.googlegroups.com>
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 Received on Tue Nov 25 2008 - 12:03:33 CST