Re: Query Performance Problem

Which version of Oracle?

You need to "trick" the optimizer into using the indexes and tables in the order you want.

Some ways to do this:

1. Evaluate your execution plan with and without the tables being analyzed.
2. use hints to suggest indexes to use first, second, etc.
3. change the order of the tables in your FROM clause.
4. change the multiblock_read_count (initSID.ora)
5. force the optimzer into believing that indexes are more likely to be in cache (see documentation on "optimizer_index_cost_adj" and "optimizer_index_caching")

I suggest these things believing that you've alread built indexes on the relevant fields.
If you haven't, well, then as Gilda would say: Never Mind.

"Jim" <jimmy.liew_at_ogilvy.com> wrote in message news:e7d269ce.0207232254.1a6448ad_at_posting.google.com...
> I have a query which is taking forever to run. Once I ran it for 3
> days before finally killing it. I realize that I am working on rather
> big tables (table1 has 260000 rows, while table2 has 70 million,
> table3 has only 2000) but the execution time is ridiculous
>
> I have tried different hints such as ORDERED, USE_HASH without much
> success.
> I hope someone can give a clue as to how to tune this statement
> properly
> The query:
>
> SELECT ind_lbr_batch_number,
> ina.ina_qua_answer_uid ,
> sum(case when ind.ind_parent_urn || ind.ind_suppress is null
> then 1 else 0 end) Ars_Valid,
> sum(case when ind.ind_parent_urn || ind.ind_suppress is not
> null then 1 else 0 end) Ars_Invalid,
> count(distinct ind_urn) ars_total
> FROM individuals ind, individual_answers ina, quest_life ql,
> (SELECT ina_ind_urn,
> c.ord ord ,
> MAX(b.ina_inr_uid) ina_inr_uid
> FROM individual_answers b,
> quest_life c
> where b.ina_qua_answer_uid = c.ans_uid
> group by b.ina_ind_urn,
> c.ord ) mx
> where ind.ind_urn= ina.ina_ind_urn
> and ina.ina_qua_answer_uid = ql.ans_uid
> and ind_urn = mx.ina_ind_urn
> AND ina.ina_inr_uid = mx.ina_inr_uid
> and ql.ord = mx.ord
> and ind.ind_oup_code || ind.ind_ctrl_cell is null
> GROUP BY ind_lbr_batch_number , ina.ina_qua_answer_uid
>
> here is the plan:
> Plan Table
> --------------------------------------------------------------------------