OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » View index

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

View index [message #64985]

Wed, 24 March 2004 06:05

bill
Messages: 75
Registered: November 1999

Member

I am trying to tune a very large query that gets part of the results from a view. Basically I have something like this:

CREATE OR REPLACE VIEW v1 as )
select pk_1 key_col, etc
from tab1
union al
select pk_1 key_col, etc
from tab2
union all
select pk_1 key_col, etc
from tab3);

I am joining to this view by� key_col (primary key for each table). What is happening is a full table scan on each table in the view. This is probably a very basic question but, is there a way around these full table scans? I have to use this view in the query.

I appreciate any help you can offer me!!!

Thanks,

Bill

�

Report message to a moderator

I was a little off but still need help tuning. [message #64986 is a reply to message #64985]

Wed, 24 March 2004 07:15

bill
Messages: 75
Registered: November 1999

Member

If I query the view directly, it will use the index. Here is the exact query I am trying to tune:

select rec_type,
c.dir_cd,
c.dir_seq,
c.dir_title,
b.eic,
b.eic_title,
b.eic_seq,
data_dt,
start_dt,
cmplt_dt,
qac,
bqws,
bqwp,
aqwp,
bac,
bcws,
bcwp,
acwp,
ot,
auth_qac
from
(
select rec_type,
c.eic_seq,
d.dir_seq,
a.data_dt data_dt,
min(c.start_dt) start_dt,
max(c.cmplt_dt) cmplt_dt,
sum(nvl(a.qac,0)) qac,
sum(nvl(a.bqws,0)) bqws,
sum(nvl(a.bqwp,0)) bqwp,
sum(nvl(a.aqwp,0)) aqwp,
sum(nvl(a.bac,0)) bac,
sum(nvl(a.bcws,0)) bcws,
sum(nvl(a.bcwp,0)) bcwp,
sum(nvl(a.acwp,0)) acwp,
sum(nvl(a.ot,0)) ot,
sum(nvl(a.auth_qac,0)) auth_qac
from vw_pcn_wc_data a,
project b,
pcn c,
lkup_wc d
where a.project_seq = b.project_seq
and a.wc_seq = d.wc_seq
and b.pcn_seq = c.pcn_seq
group by rec_type,
a.data_dt,
c.eic_seq,
d.dir_seq
) a,
lkup_eic b,
lkup_directorate c
where a.eic_seq = b.eic_seq
and a.dir_seq = c.dir_seq
and b.eic_seq = 5185 and c.dir_seq = 226;

The last line 'and b.eic_seq = 5185 and c.dir_seq = 226' is added dymanically at runtime. They are the primary keys for their respective tables. They are also indexes on the PCN and lkup_wwc tables. When I run this query it doesn't use these. Any ideas why?

Thanks again!

Report message to a moderator

Re: View index [message #65012 is a reply to message #64985]

Mon, 29 March 2004 12:14

Ivan
Messages: 180
Registered: June 2000

Senior Member

Here's the section of the manual you may want to take a look at:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/hints.htm#5253

Try this:

SELECT /*+ INDEX (v1.tab1 index_name_tab1_on_pk_1)
           INDEX (v1.tab2 index_name_tab2_on_pk_1)
           INDEX (v1.tab3 index_name_tab3_on_pk_1)
       */
       *
  FROM v1
     , your_table yt
 WHERE yt.some_column = v1.key_col
/* additional conditions to make the indexes being used */

Report message to a moderator

Previous Topic:	Shared Pool.
Next Topic:	Buffer Cache Hit rate stays low

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 20:57:48 CST 2024