| how to tuning a hierachical query having "or" conditions [message #65500] |
Thu, 07 October 2004 19:31  |
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  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
