Re: CBO & different execution plans

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1173645901.481554_at_bubbleator.drizzle.com...
> Charles Hooper wrote:
>
>> I experimented a bit some time ago with dbms_advanced_rewrite. I had
>> a SQL statement in a packaged application that was taking excessively
>> long to execute - roughly 3.4 seconds per execution and the packaged
>> application was trying to execute the statement thousands of times.
>> The report that should have required 15 seconds to complete required
>> 12+ minutes. The problem in this case had to do with Oracle picking
>> the wrong index for a table access. Providing a hint to Oracle to use
>> the correct index dropped the execution time down to roughly 0.04
>> seconds per execution, allowing the report to again complete in 15
>> seconds (bad news is that, if the application were specifically coded
>> for Oracle, it could have built the report in less than 2 seconds).
>> During my experimentation, I could not make dbms_advanced_rewrite
>> recognize the presence of the hint and to have Oracle act on the hint
>> that was embedded.
>
> Every time I have seen this behaviour it came down to one of two
> issues:
>
> 1. query_rewrite not set to trusted
> 2. a database character set not supported
>
> If anyone has an example of this not working I would appreciate
> receiving it off-line.
>
> Thank you.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

I tried this:

begin
SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( 'rs3 equivalence',
'select
htsdecode.assayName(r.alt_assay_id) c1, htsdecode.resulttype(r.result_type) c2, operator||nvl(to_char(r.result_value), r.result_val_char) || '' '' || htsdecode.resultunit(r.result_unit) result_value, '' '', r.concentration || '' '' || htsdecode.concUnit(r.conc_unit) cu, '''' ,htsdecode.assayVersion(r.alt_assay_id) c4, to_char(r.experiment_date,''DD-MON-RRRR'') c5, r.covariance,r.std_devn,
r.result_id,nvl(r.parent_result_id,0),art.drill_order,1,1, 1,'' '', htsdecode.sampleLot(r.sample_id) c10,'' '' ,''N'' c12, r.sample_id from HTS_ASSAY_RESULT R,HTS_ASSAY_RESULT_TYPE ART,HTS_ASSAY_PROTOCOL AP ,HTS_ASSAY A ,HTS_COMPOUND_LOT CL where r.alt_assay_id=ap.alt_assay_id and art.assay_id=ap.assay_id and art.result_type=r.result_type and r.sample_id = cl.sample_id and cl.compound_id = 2866242 and a.assay_id = ap.assay_id and a.assay_name in (''PKCZ_IE'') order by
r.experiment_id,r.result_id,r.parent_result_id,art.drill_order' ,
 'select htsdecode.assayName(r.alt_assay_id) c1, htsdecode.resulttype(r.result_type) c2, operator||nvl(to_char(r.result_value),
r.result_val_char) || '' '' || htsdecode.resultunit(r.result_unit) result_value, '' '', r.concentration || '' '' || htsdecode.concUnit(r.conc_unit) cu, '''' ,htsdecode.assayVersion(r.alt_assay_id) c4, to_char(r.experiment_date,''DD-MON-RRRR'') c5, r.covariance,r.std_devn,
r.result_id,nvl(r.parent_result_id,0),art.drill_order,1,1, 1,'' '', htsdecode.sampleLot(r.sample_id) c10,'' '' ,''N'' c12, r.sample_id from HTS_ASSAY_RESULT_TYPE ART,HTS_ASSAY_PROTOCOL AP ,HTS_ASSAY A ,HTS_COMPOUND_LOT CL, HTS_ASSAY_RESULT R where r.alt_assay_id=ap.alt_assay_id and art.assay_id=ap.assay_id and art.result_type=r.result_type and r.sample_id = cl.sample_id and cl.compound_id = 2866242 and a.assay_id = ap.assay_id and a.assay_name in (''PKCZ_IE'') order by
r.experiment_id,r.result_id,r.parent_result_id,art.drill_order',

   FALSE,
   'TEXT_MATCH');
end;

So basically, I'm rewriting the query with HTS_ASSAY_RESULT as the last table in the FROM part of the query, because this gives me a good execution plan.

But I received the following error:

ORA-30389: the source statement is not compatible with the destination statement

ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

My question : why ? Can't you switch the order of tables in the destination query ?

I also did:
alter session set query_rewrite_integrity = 'TRUSTED'; grant execute on DBMS_ADVANCED_REWRITE to matthiash; GRANT CREATE MATERIALIZED VIEW TO matthiash;

Our character set is WE8MSWIN1252, but I couldn't find a list of supported character sets.

Matthias Received on Mon Mar 12 2007 - 14:26:33 CDT