Re: Another rotten query
Date: Thu, 19 Feb 2009 15:37:42 -0800 (PST)
Message-ID: <9067dcfa-997f-4a68-8dcf-8b99eef08719_at_l1g2000yqj.googlegroups.com>
On Feb 19, 8:55 pm, Mtek <m..._at_mtekusa.com> wrote:
> I can do this, probably the long way with PLSQL code. I was just
> seeing if someone knew of a better way.
>
> Here is our query:
>
> v_select :=
> 'SELECT my_left_table.fund_name,
> my_left_table.ticker,
> DECODE(fund_rank.curr_rank,NULL,'NA',fund_rank.curr_rank)
> AS fund_rank,
> my_left_table.total_return_ytd,
> my_left_table.total_return_1mt,
> my_left_table.total_return_3mt,
> my_left_table.total_return_1yr,
> my_left_table.total_return_3yr,
> my_left_table.total_return_5yr,
> my_left_table.total_return_10yr
> FROM (SELECT mf.fund_name,
> mf.ticker,
> NVL(fm.total_return_ytd,-9999) AS total_return_ytd,
> NVL(fm.total_return_1mt,-9999) AS total_return_1mt,
> NVL(fm.total_return_3mt,-9999) AS total_return_3mt,
> NVL(fm.total_return_1yr,-9999) AS total_return_1yr,
> NVL(fm.total_return_3yr,-9999) AS total_return_3yr,
> NVL(fm.total_return_5yr,-9999) AS total_return_5yr,
> NVL(fm.total_return_10yr,-9999) AS total_return_10yr
> FROM mutual_fund mf, fund_master fm, fund_daily_prices dp
> WHERE mf.ticker = dp.ticker
> AND mf.m_fund_id = fm.fund_id
> AND mf.ticker IN ' || v_tickers || ') my_left_table ,
> fund_classes fc
> WHERE my_left_table.ticker = fc.ticker(+)
> ORDER BY class_rank, total_return_1yr DESC, expense_ratio,
> mgr_start_date DESC';
>
> Now, the v_tickers will be passed in. It will contain 1 to 5 values.
> The customer wants more now.
> For each ticker, we will look up what CATEGORY it belongs to in
> another table. We will then find the average for all the tickers in
> that category for the given column and compare it against the value in
> the query and rank it as higher or lower than the value.
>
> So, let's say I have IBM which belongs to a CATEGORY called TECH and
> has a total_return_ytd value of 100. I sum all TECH total_return_ytd
> and get the average. Then I compare it with the total_return_ytd for
> IBM and give it a 'Below' or 'Above' literal.
>
> I need to do that for each column, for each ticker. Does all that
> make any sense? If ticker #2 is XYZ and belongs to a CATEGORY of
> GROWTH and have a total_return_10yr of 1000, I need to do the same for
> all GROWTH category, find the average and compare it to the value for
> XYZ, giving it the 'Above' or 'Below'.
>
> My guess is serious PLSQL code and dynamically putting together some
> type of query........
I can already think of at least one way you could do that using a static SQL query within a PL/SQL routine. Unfortunately, I'm in a rush at the moment and can't really go into two much depth.
Can you pass an array of values into a PL/SQL stored procedure from your PHP app? If so, you could do this via a collection-based subquery.For example:
CREATE TYPE ty_collection_type IS TABLE OF VARCHAR2(30); /
SET SERVEROUTPUT ON
DECLARE
ct_ticket_constant CONSTANT VARCHAR2(3) := 'ABC';
ct_search_ticket CONSTANT VARCHAR2(4) := 'ABC1';
t_ticker_collection ty_collection_type := ty_collection_type();
l_found PLS_INTEGER;
BEGIN
- Populate the collection for test purposes. FOR idx IN 1 .. 5 LOOP t_ticker_collection.EXTEND; t_ticker_collection( t_ticker_collection.COUNT ) := 'ABC'|| TO_CHAR(idx); END LOOP;
INTO l_found
FROM dual,
( SELECT column_value AS ticker
FROM TABLE( SELECT CAST( t_ticker_collection AS
ty_collection_type )
FROM dual
)
) tl
WHERE tl.ticker = ct_search_ticket;
DBMS_OUTPUT.PUT_LINE( 'l_found: '||l_found );
END;
/
That is one way you can populate a varying list of values you want to use in an IN statement, except with the above example, you'd obviously use the collection query as a subquery whose results set you will use as the input to the IN function.
By using this approach, you won't need to build a dynamic query. You could just code a static query in a PL/SQL procedure. Advantages of static queries are:
- They perform marginally better at runtime, because they are parsed at compile-time rather than at runtime.
- Oracle does all the bind variable substitutions needed behind the scenes, so you don't have to write of code to do this.
DO NOT assemble a string of comma_separated values (which is what it looks like you're going to do), and then concatenate that into a massive query you're going to dynamically execute at runtime. If you could cast your mind back to my response to your last post, we're into the hard parsing issue again.
However, if you want to write database performance-wrecking code (which does give great opportunities for highly paid knowledgeable consultants to earn their living by having to come in and sort it out further down the line), then by all means, feel free to keep building gigantic dynamic queries via string concatenation.
In all seriousness Mate, I think you really need to read both these books:
- http://www.amazon.com/Mastering-Oracle-SQL-Practical-Solutions/dp/1590592174/ref=sr_1_1?ie=UTF8&s=books&qid=1235086316&sr=8-1
- http://www.amazon.com/Expert-Oracle-Database-Architecture-Programming/dp/1590595300/ref=sr_1_1?ie=UTF8&s=books&qid=1235086380&sr=1-1
I'd recommend reading all the first book, and the Introduction and Chapters 1 - 4 of the second. Both are a couple of the best books you could ever read if you have any interest whatsover in becoming a better Oracle database developer. Personally, I think an interest is warranted because you appear to be well on the way to writing a PHP app that is going to potentially cause both performance and security issues when it goes live.
Just out of interest, how many people are going to be using your app, and how many times per day are some of these queries likely to be run? Received on Thu Feb 19 2009 - 17:37:42 CST