Home » RDBMS Server » Performance Tuning » index problem on partition based table
index problem on partition based table [message #290019] Thu, 27 December 2007 03:00 Go to next message
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.
Re: index problem on partition based table [message #290095 is a reply to message #290019] Thu, 27 December 2007 06:49 Go to previous messageGo to next message
michael_bialik
Messages: 611
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
Re: index problem on partition based table [message #290102 is a reply to message #290019] Thu, 27 December 2007 07:11 Go to previous messageGo to next message
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 128 times)
Re: index problem on partition based table [message #290105 is a reply to message #290019] Thu, 27 December 2007 07:28 Go to previous messageGo to next message
michael_bialik
Messages: 611
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

Re: index problem on partition based table [message #290124 is a reply to message #290105] Thu, 27 December 2007 10:28 Go to previous messageGo to next message
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
Re: index problem on partition based table [message #290167 is a reply to message #290124] Thu, 27 December 2007 21:52 Go to previous messageGo to next message
rleishman
Messages: 3724
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

Re: index problem on partition based table [message #290322 is a reply to message #290167] Fri, 28 December 2007 06:32 Go to previous messageGo to next message
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
Re: index problem on partition based table [message #290422 is a reply to message #290322] Fri, 28 December 2007 16:33 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: index problem on partition based table [message #290473 is a reply to message #290019] Sat, 29 December 2007 01:47 Go to previous messageGo to next message
michael_bialik
Messages: 611
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
Re: index problem on partition based table [message #290543 is a reply to message #290473] Sat, 29 December 2007 12:02 Go to previous messageGo to next message
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.
Re: index problem on partition based table [message #290549 is a reply to message #290019] Sat, 29 December 2007 16:13 Go to previous message
BlackSwan
Messages: 25036
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?
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:
  


Current Time: Mon Dec 05 04:33:55 CST 2016

Total time taken to generate the page: 0.07877 seconds