Recently I have been trying to incorperate more WITH statements into my Oracle SQL in order to create cleaner, more efficient code. However, I continue to feel like it is actually less efficient, but only under certain conditions, which is frustrating.

One example is a WITH statement created to rank phone numbers. I want it to retrieve on the best of all the valid/active phone numbers.

Here's an example of the WITH statement I'm using for ranking phone numbers:

Select * From (
WITH
PHONE_RANK as        
-- Description: Phone numbers with a RANK_NO generated based on importance.
(
    Select 
        ID as ID,
        PHONE_AREA || PHONE_NUMBER || PHONE_EXT as PHONE_NUMBER,
        TELE_CODE as TELE_CODE,
        PRIMARY_IND as PRIMARY_IND,
    --Generate a RANK_NO
        row_Number() over
        (
            Partition By ID
            Order By 
                ID,
                Decode(PRIMARY_IND,  'Y',1,  2),
                Decode(TELE_CODE,  'MA',1,  'PR',2,  'CA',3,  'CELL',4,  99)
        ) as RANK_NO
    From 
        SPRTELE
    Where 
        STATUS_IND is null
        and TELE_CODE in ('MA', 'PR')
        and Length(PHONE_AREA || PHONE_NUMBER || PHONE_EXT) >= 7
)

Select SPRIDEN.ID, PHONE_NUMBER
From SPRIDEN, PHONE_RANK  --SPRIDEN contains basic info (name, id, etc)
Where 
    SPRIDEN.CHANGE_IND is NULL
    SPRIDEN.ID = PHONE_RANK.ID
    and RANK_NO = 1
)

PHONE_RANK runs fairly quickly if joined to the SPRIDEN table. However, when I try to limit it by the RANK_NO, it takes much longer to run.

• With out any criteria on PHONE_RANK, it runs in less than 0.2 seconds.
• With and PHONE_RANK.RANK_NO = 1 it takes about 3.5 seconds.
• When using and PHONE_RANK.RANK_NO = 1 it is a part of more complex code, it tends to scale.

(Begin Edit)

I have used Subqueries for this process. The speed is great, and they are decent for phone numbers. However, since the most common numbers are PR and MA, and one or the other may not exist, I to use two subqueries to pull each one.

My "plan" is simple: Pull the best record for each student/person based on the Primary Indicator and Telephone Code. Thus, if there is only a PR or MA record, we will still get a result, since the best will be pulled. However, sometimes no Phone MA or PR # will exist, but we still want the student information, thus having to limit the query inside the main query is not ideal (but a second WITH statement should be able to take care of that).

Ultimately, I would like to apply the result to the Address table, which is similar to SPRTELE. A subquery to pull a phone number is fine, but using separate subqueries to pull the street, city, state and zip is not ideal. And, again, we have to pull both PR and MA address, in case one or the other does not exist.

Both the Address and Phone tables are set up poorly in my opinion, as one can have multiples of any Phone/ADDRESS Type (TELE_CODE, ATYP_CODE), and all/none of the records for each type may be active. Also, the data is not maintained well (and it would be a beast to maintain it).

For the SPRTELE table field (indexes are starred):

*ID (004000, 123456, etc)
*SEQNO (1, 2... n)
*TELE_CODE (MA, PR, etc)
ACTIVITY_DATE
COMMENT
CTRY_CODE_PHONE
DATA_ORIGIN
INTL_ACCESS
PHONE_AREA
PHONE_EXT
PHONE_NUMBER
PRIMARY_IND
ADDR_SEQNO
ATYP_CODE
STATUS_IND
UNLIST_IND
USER_ID

If it is useful, here is the subquery I am use for phone numbers. I also include one for Permanent Phone numbers (TELE_CODE = 'PR').

(
Select PHONE_AREA || PHONE_NUMBER || PHONE_EXT
From SPRTELE
Where 
    ID = S1.ID
    and STATUS_IND is Null
    and TELE_CODE = 'MA'
    and SEQNO =
    (
        Select Max(SEQNO)
        From SPRTELE
        Where 
            ID = S1.ID
            and STATUS_IND is Null
            and TELE_CODE = 'MA'
    )
) as MA_Phone

Also, these are the Subqueries used to pull ONE address. Again, I have to use two to get both MA and PR address types, in case one or the other is missing.

--MAILING STREET ADDRESS
(
    Select STREET_LINE1 || ' ' || STREET_LINE2 || ' '|| STREET_LINE3
    From SPRADDR S2
    Where 
        S2.ID = S1.ID
        and ATYP_CODE = 'MA'
        and STATUS_IND is NULL
        and SEQNO =
        (
            Select MAX(SEQNO)
            From SPRADDR S2
            Where 
                S2.ID = S1.ID
                and ATYP_CODE = 'MA'
                and STATUS_IND is NULL
        )
) as MA_Street,

--MAILING CITY STATE ZIP
(
    Select CITY || ', ' || STAT_CODE || ' ' || ZIP
    From SPRADDR S2
    Where 
        S2.ID = S1.ID
        and ATYP_CODE = 'MA'
        and STATUS_IND is NULL
        and SEQNO =
        (
            Select MAX(SEQNO)
            From SPRADDR S2
            Where 
                S2.ID = S1.ID
                and ATYP_CODE = 'MA'
                and STATUS_IND is NULL
        )
) as MA_Address,

(End Edit)

Any help with the following would be great:

1. Why would selecting 'RANK_NO = 1' cause such a lack in performance? Is it the WITH statement, the Partition By, or something else?
2. Is there a better method than using "Partition By" and then selecting RANK_NO = 1 to get the top phone number?
3. Suggestions to improve the above code.
4. Any other suggestions.

• sql
• performance
• oracle
• with-statement

In this query, the hard(expensive) operation is to obtain the rank - row_number function - this requires (sometimes not explicit in the query) sorts.

1. Probably this is because when you don't write RANK_NO = 1, you don't use the RANK_NO column elswere, so Oracle does not need to calculate it.

2. you can order the inner query, and, in the outer queryes, use rownum, but you can't have partitions. You have one partion, a sigle 1, a single 2, and so on.

3. If you can filter the rows you're getting from SPRTELE table, You'll get better speed. Few rows -> better speed.
4. You should see the entire explain plan in order to improve something. Post the explain plan of the query.