SQL Tuning

[reydp]

Originally posted by reydp
That's exactly what I'm trying to say dear.

But what about function-based Index? I am sure it will help the performance too.

Sorry, I don't agree totally on creating temporary tables.
Depends on the size of data.

[kris123]

Well, the goal is to try to minimize data being joined, simplify
complex equation, makes life easier

[reydp]

Originally posted by kris123
Well, the goal is to try to minimize data being joined, simplify
complex equation, makes life easier

I don't know if you're serious or what.
Please don't tell me that every set of data that the user wants you have to create data segments because you want to minimize fields being joined between tables.

Are we done here Mama Kris?

[kris123]

Why not? if u can produce output?U see he said is program is timing out wasting resources/processing time
Its not the fields but the data/rows, maybe the tables is filled with
lots of historical data that can be filter.

Ms C3

PS papa deydp, can u answer my post thread pls...the numeric
procedure error

[reydp]

Originally posted by kris123
Why not? if u can produce output?U see he said is program is timing out wasting resources/processing time
Its not the fields but the data/rows, maybe the tables is filled with
lots of historical data that can be filter.

Ms C3

PS papa deydp, can u answer my post thread pls...the numeric
procedure error

dear, the way I look at it maybe he/she needs a materialized views rather than temporary tables.

[kris123]

I agree, but if he just use it once then just create temporary.

[reydp]

Originally posted by kris123
I agree, but if he just use it once then just create temporary.

How did you know that he wil use it only once?

[abhaysk]

reydp & kris.. is it some chat session going on here?..


Well @chintz.. post the plan.. code of the view in readable format..

Abhay.

[tamilselvan]

========
SELECT a.operator
, a.run_id
, a.f_batch_prefix
, b.oprid
FROM batch_vg a
, oprdefn b
WHERE b.oprid = a.oprid
AND substr(a.run_id,1,2)='04';
=====

A function on a col will stop using the index.

Try this:

SELECT a.operator
, a.run_id
, a.f_batch_prefix
, b.oprid
FROM batch_vg a
, oprdefn b
WHERE b.oprid = a.oprid
AND a.run_id like '04%';

If there is a index on run_id, the CBO may use it.
Let me know the result.

Tamil