-
Function Based Index not working.
I have created a function based index, but it keeps doing a full table scan. I used the following script.
SQL> DROP TABLE HEY;
Table dropped.
SQL> GRANT QUERY REWRITE TO MI;
Grant succeeded.
SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
Session altered.
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
Session altered.
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
Session altered.
SQL> create table hey (id number, txt varchar2(20));
Table created.
SQL> BEGIN
2 FOR I IN 1..10000 LOOP
3 INSERT INTO HEY VALUES(I,'Test this');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 FOR I IN 1..100 LOOP
3 INSERT INTO HEY VALUES(I,'Check');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> create index hey_idx on hey(upper(txt));
Index created.
SQL> exec dbms_stats.gather_table_stats( 'mi', 'hey' -
> , method_opt => 'for all indexed columns size 1' -
> , cascade => true -
> , degree=>2 -
> , granularity => 'ALL' -
> );
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> select count(*) from hey where upper(txt) = UPPER('CHECK');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=1 Bytes=1
2)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'HEY' (Cost=5 Card=5050 Bytes=606
00)
However, if I analyze the table with the following command I can use the index.
SQL> analyze table hey compute statistics for table for all indexed columns for all indexes;
What is the difference? I understand that should no longer use the analyze statement rather the new package dbms_stats. So what is the coorisponding dbms_stats call to make this work? Do you know of any good book the describes this package in great detail?
Thanks!!!
-
Privs:
grant query rewrite to
init.ora:
QUERY_REWRITE_INTEGRITY = TRUSTED
QUERY_REWRITE_ENABLED = TRUE
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|