Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Using Analytical Function

    Hi,

    I've one table called customer and one table , i.e, phone.
    Now the data set of these table would be like this :

    CUSTOMER
    ------------------------------------------------
    ID
    1
    2
    3
    4

    INDIVIDUAL_TELEPHONE
    --------------------------------------------
    ID CODE RANK PHONE
    1 'A' 01 111
    1 'B' 02 222
    1 'C' 04 234
    2 'C' 03 345
    3 'A' 02 456
    8 'A' 03 767








    Now the output result will be like this :

    id cust1phone cust1rank cust2phone cust2rank cust3phone cust3rank
    ---------------------------------------------------------------------------------------
    1 111 01 222 02 234 04
    2 null null null null 345 03
    3 456 02 null null null null


    In PHONE table , one ID can have at most 3 records with code(A,B,C).
    In this table (comination of ID and CODE) is the primary key.
    PHONE and ID and CODE are NOT NULL fields.

    PHONE table has 600 millions of data.
    CUSTOMER table has 200 millions of data.


    Following IS THE QUERY which ANDREWST suggested :

    select c.id
    , max(DECODE(t.code,'A', t.phone)) as cust1phone
    , max(DECODE(t.code,'A', t.preference)) as cust1preference
    , max(DECODE(t.code,'B', t.phone)) as cust2phone
    , max(DECODE(t.code,'B', t.preference)) as cust2preference
    , max(DECODE(t.code,'C', t.phone)) as cust3phone
    , max(DECODE(t.code,'C', t.preference)) as cust3preference
    from customer c, phone t
    where c.id = t.id (+)
    group by c.id
    order by c.id;

    This QUERY IS working very fine .
    But I need TO USE analytical FUNCTION IN THE above QUERY .
    Because somewhere I came TO know that analytical FUNCTION IS faster THAN ANY other thing.
    Can I USE THE analytical FUNCTION TO get THE same result AS THE above QUERY ?
    I mean can I CHANGE THE above QUERY WITH THE help OF analytiacal FUNCTIONS?

    Thanks IN advance ............
    himridul

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Analytics dont provide the functionality you require. Have a look at the analytic functions in the sql manual on technet to get a better idea of what they are used for.

    Alan

    http://download-west.oracle.com/docs...ns2a.htm#80856

    http://download-west.oracle.com/docs...a96520/toc.htm

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    why do you need the outer-join? looks like you don't want to display id's that do not exist in both tables anyways.

    also, if you GROUP BY a column then you intrinsically order asc by that same column. there is no reason to specify the order-by in this instance (which could cause needless overhead or steps by Oracle).

    As Alan stated, I don't think Analytics will help you in this situation since
    you want to pivot.

    Also, keep in minf that Analytics is just another OPTION and not necessarily the FASTEST method. Always benchmark every method to determine best results.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jan 2004
    Posts
    66
    Here in this query Outer join is needed . Because I need all the rows from customer table .
    Yes , Order by should be avoided .

    I'm trying to rebuilt the query using lead analytical function.
    If I get success then I will let u know .

    anyway thanks ....
    himridul

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    here is another way to do it, but not necessarily better:

    PHP Code:
    SELECT
    ident
    ,
    (
    SELECT phone FROM phone WHERE id ident AND code 'A'phone1,
    (
    SELECT phone FROM phone WHERE id ident AND code 'B'phone2,
    (
    SELECT phone FROM phone WHERE id ident AND code 'C'phone3
    FROM
    (SELECT id ident
    FROM customer
    ); 
    (i left out preference since phone1 implies this)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •