OraFAQ Forum: Performance Tuning » index problem on partition based table

Home » RDBMS Server » Performance Tuning » index problem on partition based table

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

index problem on partition based table [message #290019]

Thu, 27 December 2007 03:00

dusoo
Messages: 41
Registered: March 2007

Member

Hi everyone.

could someone advice me what to do with following problem i have now ?
thanks a lot for any suggestions...

i have a query selecting from two views => view_1 and view_2 (ORACLE 9)
both views are created as select from partition based tables.

view_1 => table_1_master ( m_id, key_cols ), table_1_detail ( m_id, day, starttime, colxy )
view_2 => table_2_master ( m_id, key_cols ), table_2_detail ( m_id, day, starttime, colxy )
Detail tables are partitioned based on DAY, have local UK idx on (m_id,day,starttime)
Master tables have local BITMAP idx on key cols.

query:

select v1.day, v1.starttime, v1.key_col, v1.col1,v1.col2,v2.tab2_col1
from view_1 v1,
(select day, starttime, key_col, sum(tab2_col1) tab2_col1
from view_2 v2
group by day,starttime, key_col)
where
v1.day = v2.day and
v1.starttime = v2.starttime and
v1.key_col = v2.key_col and
v1.day = to_date('13.12.2007','dd.mm.yyyy')

The problem is that the optimizer is not adding by himself the day condition into the inner select, and therefore the inner select (view_2) is going PARTITION FULL SCAN instead of PARTITION RANGE SINGLE which is "activated" only for view_1...

Few weeks ago optimizer was working fine for this type of select and was going PART.RANGE SINGLE for both views.
Dont know if anything changed on DB side, i only know that DB admins "have put" detail tables into compress mode, but i guess it's not the problem...

Thanks for any reply...

j.

Report message to a moderator

Re: index problem on partition based table [message #290095 is a reply to message #290019]

Thu, 27 December 2007 06:49

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Try:

select v1.day, v1.starttime, v1.key_col, v1.col1,v1.col2,v2.tab2_col1
from
 view_1 v1,
 (select day, starttime, key_col, sum(tab2_col1) tab2_col1
  from view_2 v2
  WHERE v2.day = to_date('13.12.2007','dd.mm.yyyy')
  group by day,starttime, key_col) V2
where 
v1.day = v2.day and 
v1.starttime = v2.starttime and 
v1.key_col = v2.key_col and 
v1.day = to_date('13.12.2007','dd.mm.yyyy')

HTH.
Michael

If is doesn't help - post TKPROF and EXPLAIN

Report message to a moderator

Re: index problem on partition based table [message #290102 is a reply to message #290019]

Thu, 27 December 2007 07:11

dusoo
Messages: 41
Registered: March 2007

Member

Hi, i cannot simply just add that condition into the inner select.

I have VIEW outer_view created as

CREATE outer_view as
select v1.day as DAY, v1.starttime as STARTTIME, v1.key as KEY, v2.col1
from view1 v1,
(select v2.day, v2.starttime, v2.key, sum(v2.col1)
from view2 v2
group by v2.day,v2.starttime, v2.key)
where v1.day = v2.day and v1.start=v2.start and v1.key=v2.key;

If i do explain plan on
select * from outer_view where day = '13.12.2007'

then i see that VIEW1 is going PARTITION RANGE SINGLE which is ok,
but VIEW2 is going PARTITION RANGE ALL ...

Thanks

Attachment: explain.JPG
(Size: 49.04KB, Downloaded 758 times)

Report message to a moderator

Re: index problem on partition based table [message #290105 is a reply to message #290019]

Thu, 27 December 2007 07:28

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Try:

CREATE New_outer_view as
select v1.day as DAY, v1.starttime as STARTTIME,
 v1.key as KEY, 
 (SELECT sum(v2.col1)FROM view2 v2
  WHERE v2.day = v1.day AND v2.start=v1.start AND
        v2.key = v1.key) col1 
from view1 v1

Then:

SELECT * FROM new_outer_view 
WHERE day = TO_DATE('13.12.2007','DD.MM.YYYY')

Michael

Report message to a moderator

Re: index problem on partition based table [message #290124 is a reply to message #290105]

Thu, 27 December 2007 10:28

dusoo
Messages: 41
Registered: March 2007

Member

hi,
it worked the way u posted which is great Smile

So now it is going for both views through PARTI.RANGE SINGLE ...

but, is it ok that for each line of the main query (view1) it is doing one select for the second query (view2)?

thx

Report message to a moderator

Re: index problem on partition based table [message #290167 is a reply to message #290124]

Thu, 27 December 2007 21:52

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

I would be wary of Michael's solution. Subquery expressions are fine if you are selecting only a few rows, but they scale poorly.

Have you tried the PUSH_PRED and/or MERGE hints?

Ross Leishman

Report message to a moderator

Re: index problem on partition based table [message #290322 is a reply to message #290167]

Fri, 28 December 2007 06:32

dusoo
Messages: 41
Registered: March 2007

Member

Well i actualy have not even heard about those hints at all.
I have checked oracle doc., but still have no clue how and why to use them ..
Both tables are huge, and day-partitioned.
Now, with Michael's solution im at least using the partition i need. So i get the results in some time.
If u know how to tune up that select, please advise me so.
By the way, am i able to view explain plan in which i can see how many times is that select executed and so on, using oracle9 packages (without using sqlplus)
thanks

Report message to a moderator

Re: index problem on partition based table [message #290422 is a reply to message #290322]

Fri, 28 December 2007 16:33

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

select /*+PUSH_PRED(v2)*/ v1.day, v1.starttime, v1.key_col,
       v1.col1,v1.col2,v2.tab2_col1
from   view_1 v1,
      (select day, starttime, key_col, sum(tab2_col1) tab2_col1
       from view_2
       group by day,starttime, key_col) v2
where 
       v1.day = v2.day 
and    v1.starttime = v2.starttime 
and    v1.key_col = v2.key_col 
and    v1.day = to_date('13.12.2007','dd.mm.yyyy')

Ross Leishman

Report message to a moderator

Re: index problem on partition based table [message #290473 is a reply to message #290019]

Sat, 29 December 2007 01:47

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Consider creating materialized view for

select day, starttime, key_col, sum(tab2_col1) tab2_col1
       from view_2
       group by day,starttime, key_col

Michael

Report message to a moderator

Re: index problem on partition based table [message #290543 is a reply to message #290473]

Sat, 29 December 2007 12:02

dusoo
Messages: 41
Registered: March 2007

Member

i have tried to create materialized view for that part, but, it has slowed down do inserting process into the main table a lot.
I dont have so many info arround m_views. I have created it as update on commit. Maybe that was the reason, but as i said, i dont know any better way to create m_view with real data when needed.
thanks for any suggestions.

Report message to a moderator

Re: index problem on partition based table [message #290549 is a reply to message #290019]

Sat, 29 December 2007 16:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Have you read & followed ANY of the suggestions in the URL below:
http://www.orafaq.com/forum/t/84315/74940/
especially the one involving SQL_TRACE & TKPROF?

If so, what were the results?

If not, why NOT?

Report message to a moderator

Previous Topic:	Max time for all sql's in my process being consumed by db file sequential read
Next Topic:	help on indexes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 05:01:54 CST 2024