Your hands are tied, but there are some (well?) known tricks to optimize unmodifiable SQLs.
Some of them are
- stored outline
- sql profile(10g)
- advanced rewrite(10g)
In your case, stored outline and sql profile don't work(I don't like to explain them here).
So, advanced rewrite would be the only way
- If you're on 10g
- and your query does not have any bind variable
- and you can write the optimized version of your query
See following demonstration.
UKJA@ukja116> create table t1(c1, c2, c3, c4)
UKJA@ukja116> as
UKJA@ukja116> select to_char(level), to_char(level), to_char(level), to_char(level)
UKJA@ukja116> from dual
UKJA@ukja116> connect by level <= 1000000
UKJA@ukja116> ;
UKJA@ukja116>
UKJA@ukja116> create index t1_n1 on t1(c1); -- c1 has index
UKJA@ukja116>
UKJA@ukja116> explain plan for
2 select *
3 from t1
4 where c1 like '%11%'; -- but index is not applicable for this type of predicate
Explained.
UKJA@ukja116> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 1318K| 1182 (3)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T1 | 50000 | 1318K| 1182 (3)| 00:00:06 |
--------------------------------------------------------------------------
UKJA@ukja116> explain plan for -- assume that following query is better
2 select /*+ leading(x) use_nl(x t1) */ t1.*
3 from
4 (select /*+ index_ffs(t1) */ rowid as row_id, c1
5 from t1 where c1 like '%11%') x,
6 t1
7 where
8 t1.rowid = x.row_id
9 ;
Explained.
UKJA@ukja116> select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 2246K| 50697 (1)| 00:04:14 |
| 1 | NESTED LOOPS | | 50000 | 2246K| 50697 (1)| 00:04:14 |
|* 2 | INDEX FAST FULL SCAN | T1_N1 | 50000 | 927K| 653 (5)| 00:00:04 |
| 3 | TABLE ACCESS BY USER ROWID| T1 | 1 | 27 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
UKJA@ukja116> -- advanced rewrite
UKJA@ukja116> -- grant corresponding previleges to current user.
UKJA@ukja116> -- grant priv to ukja (as sys user)
UKJA@ukja116> -- grant execute on dbms_advanced_rewrite to ukja;
UKJA@ukja116> -- grant create materialized view to ukja;
UKJA@ukja116>
UKJA@ukja116> alter session set query_rewrite_integrity = trusted;
Session altered.
UKJA@ukja116>
UKJA@ukja116> -- rewrite the query
UKJA@ukja116> begin
2 sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
3 name => 'rewrite1',
4 source_stmt =>
5 'select *
6 from t1
7 where c1 like ''%11%''',
8 destination_stmt =>
9 'select /*+ leading(x) use_nl(x t1) */ t1.*
10 from
11 (select /*+ index_ffs(t1) */ rowid as row_id, c1
12 from t1 where c1 like ''%11%'') x,
13 t1
14 where
15 t1.rowid = x.row_id',
16 validate => false,
17 rewrite_mode => 'text_match');
18 end;
19 /
PL/SQL procedure successfully completed.
UKJA@ukja116> -- see that the query is replaced and plan is changed
UKJA@ukja116> explain plan for
2 select *
3 from t1
4 where c1 like '%11%'
5 ;
Explained.
UKJA@ukja116> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2525156207
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 1660K| 50697 (1)| 00:04:14 |
| 1 | NESTED LOOPS | | 50000 | 1660K| 50697 (1)| 00:04:14 |
|* 2 | INDEX FAST FULL SCAN | T1_N1 | 50000 | 341K| 653 (5)| 00:00:04 |
| 3 | TABLE ACCESS BY USER ROWID| T1 | 1 | 27 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
UKJA@ukja116> -- drop rewrite equivalence
UKJA@ukja116> begin
2 sys.dbms_advanced_rewrite.drop_rewrite_equivalence( name=> 'rewrite1');
3 end;
4 /
PL/SQL procedure successfully completed.
================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
================================
