Home » RDBMS Server » Performance Tuning » how to tuning a hierachical query having "or" conditions
how to tuning a hierachical query having "or" conditions [message #65500] Thu, 07 October 2004 19:31 Go to next message
dragon wang
Messages: 2
Registered: October 2004
Junior Member
hi
[sp] I have a problem in performance tuning an oracle hierachical query,query like£º
[sp]
[sp][sp] select * from table1 start with field1 is null connect by
[sp][sp] prior field1=field_id1 or prior field2=field_id1 or prior field3=filed_id2;
[sp][sp]
[sp] the table1 is a big table with about twenty million rows.
[sp] there are indexes on field_id1 and filed_id2,but oracle scan full table when it filters 'connect by' conditons,
[sp] that result in this query is very slow.
[sp] i think that if oracle may transform "or" condition to several "union" querys,the problem will be resolved.
[sp] but i[sp] don't know how to do that.
[sp] please help me.
[sp] My Oracle version is 10g.
[sp] thank you!
[sp]
Re: how to tuning a hierachical query having "or" conditions [message #65518 is a reply to message #65500] Tue, 12 October 2004 03:40 Go to previous messageGo to next message
Gurinder Mann
Messages: 7
Registered: October 2004
Junior Member
I think u try with this query
select * from table1 start with field1 is null connect by
prior field1=field_id1
union all
select * from table1 start with field1 is null connect by
prior field2=field_id1
union all
select * from table1 start with field1 is null connect by
prior field3=field_id2 ;
Best Luck Gurinder
Re: how to tuning a hierachical query having "or" conditions [message #65616 is a reply to message #65518] Tue, 09 November 2004 20:00 Go to previous message
dragon wang
Messages: 2
Registered: October 2004
Junior Member
But that result in different result.
Previous Topic: Coalesion on the tablespaces
Next Topic: To find out the Stored Procedure name and the Full table scan
Goto Forum:
  


Current Time: Sun Sep 20 22:34:53 CDT 2020