Home » RDBMS Server » Performance Tuning » Optimalization problem with static subquery
Optimalization problem with static subquery [message #215186] Fri, 19 January 2007 10:13 Go to next message
piotr.kozak
Messages: 1
Registered: January 2007
Junior Member
Hello.

I've got problem with query

select * from
tb_node n join vw_free_comp_card fc on n.id = fc.node_id
join tb_resource r on fc.id = r.id
join tb_resource_pattern rp on r.resource_pattern_id = rp.id
where
fc.node_id in (
select nk.node_id from
vw_node_ksp nk, tb_commutation_ksp_unit ksp
where nk.ksp_id = ksp.id and ksp.name = 'STR Strzelce i Gorzów Komutacja')
order by "subrangeNode.identifier"

its cost is 203123

when I derive static subquery and put into the query static results from subquery ie

select * from tb_node n join vw_free_comp_card fc on (n.id = fc.node_id)
join tb_resource r on (fc.id = r.id)
join tb_resource_pattern rp on (rp.id = r.resource_pattern_id)
where
fc.node_id in (1300036450, 1300006640, 1300036507, 1300044644, 1300012607, 1300015969, 1300045297, 1300017167, 1300027770, 1300040604, 1300019242, 1300032146, 1300023368, 1300032222, 1300015889, 1300040259, 1300077878, 1300032191, 1300036136, 1300071905, 1300032213, 1300032220, 1300113009, 1300021048, 1300045308, 1300038963, 1300015013, 1300034454, 1300079865, 1300046181, 1300071507, 1300070148, 1300015982, 1300032231, 1300032211, 1300098958, 1300077877, 1300006678, 1300036424, 1300034441, 1300015466, 1300006936, 1300015009, 1300013562, 1300040605, 1300024259, 1300015880, 1300012456, 1300036484, 1300077873, 1300040606, 1300048826, 1300010471, 1300031299, 1300033928, 1300034542, 1300076806, 1300077882, 1300071695, 1300032209, 1300006661, 1300072003, 1300029259, 1300036565, 1300031269, 1300046179, 1300024257, 1300032225, 1300055462, 1300019248, 1300032692, 1300006692, 1300045345, 1300040602, 1300020795, 1300006642, 1300113010, 1300036058, 1300017166, 1300040256, 1300079870, 1300032156, 1300031230, 1300012460, 1300006643, 1300006961, 1300040601, 1300074609, 1300077875, 1300034682, 1300070149, 1300074605, 1300036500, 1300035902, 1300034257, 1300024086, 1300040599, 1300015987, 1300006967, 1300055412, 1300035900, 1300038261, 1300036513, 1300018230, 1300024423, 1300015991, 1300032218)
order by "subrangeNode.identifier"

then cost is 2956

Why there is such a diferrence?
How to force Oracle to do query with subquery faster?
Help!

Thanks in advance.
Peter

Report message to a moderator

Re: Optimalization problem with static subquery [message #215194 is a reply to message #215186] Fri, 19 January 2007 10:57 Go to previous message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
It seems logical that the cost of the second query is lower. Because in the first query, two additional tables are accessed whereas in the second one all values are just given.

If you just need to make the first query facter, post explain plan, some people here will surely have a look at it.

Report message to a moderator

Previous Topic: Regarding sort operation
Next Topic: Selecting from a big table.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 16 03:58:51 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.