optimization - Improving performance on an Oracle view -

optimization - Improving performance on an Oracle view -

i have query save view:

with subquery ( select aaa_id, ... table_aaa ... ) select subquery.aaa_id, ... table_bbb bring together subquery using ( ... ) ;

[note: in real-life, query much more complicated this, several with sub-queries, many performing joins, beingness joined together. i'm looking general guidance can utilize work problem.]

the execution query include total table scans. makes sense since there no criteria included in where clause. however, can eliminate of total table scans including such clause:

with subquery ( select aaa_id, ... table_aaa ... aaa_id = :id) select subquery.aaa_id, ... table_bbb bring together subquery using ( ... ) ;

however, doesn't seem have alternative of putting where status in right place when create view:

create or replace view vw_my_view subquery ( select aaa_id, ... table_aaa ... ) select subquery.aaa_id, ... table_bbb bring together subquery using ( ... ) ; select ... vw_my_view aaa_id = :id ;

in case, execute plan still contains total table scans. there way me hint where clause can inserted with sub-query?

i've had similar experiences , while have no general solution, suggest following:

run "select * v$parameter2;" , create sure _complex_view_merging on. there nasty bug in 1 of 10g releases related dbas turned off , might have forgotten turn on 1 time fixed.

leave consideration hints lastly measure. in experience, they're useful preventing total table scans because optimizer doing can avoid them.

if have base of operations table primary key you're going filtering view on, seek set things view's main query starts , joins complicated clause queries, even if bring together redundant (i.e. give oracle chance easy filtering on base of operations table before joining complicated bits). create sure columns view going filtered on selected straight base of operations table , not complicated_query. like

.

(complicated_query) select base_table.key1, complicated_query.* base_table bring together complicated_query on base_table.key1 = complicated_query.key1;

if have filters utilize uncorrelated subquery, seek switching them correlated equivalent (and vice versa).

play around order of bring together statements and/or table start out in clause, if logically not create difference outcome. bit of desperate gambit, i've had execution plans alter improve doing this. optimizing oracle queries not rational process.

optimization view oracle10g query-optimization