Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VERY slow ORDER BY, what suggestions?

Status
Not open for further replies.
pandpp

pandpp

Programmer
Joined
Sep 17, 2003
Messages
110
Location
AU
Hello.

Oracle 8i

I have a query which I have just tuned, returning 54K rows in around 4 seconds.

This query is being pumped into a Crystal Report, which, optimally, wants to issue an ORDER BY clause.
If I let it do the sort on Oracle, the sort itself, on just a single, field takes some 46 MINUTES.

A few questions I guess:
1) To get good sort times, should the field being sorted upon be indexed? What if I sorted on multiple fields, a single index covering multiple fields, or multiple indexes?
2) What if the field I am sorting on is the result of a DECODE function call? How can I get good performance from this?
3) Should I be looking at the DB setup for any specific issue(s)?

I am aware I have a fairly open ended set of questions, but, I'm running out of options when tackling the problem from the Crystal Reports side of the fence.

I am NOT a DBA, but a developer, as such, any suggestions may need a little more explanation for me.

Thanks,

Peter.
 
Sort by date Sort by votes
Peter,

Are you at liberty to post your query?

The only time that indexes behave as a "sort" mechanism is if all of the columns that you want to SELECT are also columns that are part of an index. And if your "order by" depends upon the results of a DECODE function (unless you are using Oracle 9i that allows function-based indexes), you are between hard places and rocks.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
Upvote 0 Downvote
Mufasa,

The query is pretty big, so I appreciate your time.

The query itself is created by Crystal, running off a VIEW:
Code: 
SELECT
    VW_CFA_RECUR_CAPITAL_PROJ.PROJECT_NO,
    VW_CFA_RECUR_CAPITAL_PROJ.PROJ_CLASSIFX1,
    VW_CFA_RECUR_CAPITAL_PROJ.REF_CODE1,
    VW_CFA_RECUR_CAPITAL_PROJ.SOURCE_FUNDS,
    VW_CFA_RECUR_CAPITAL_PROJ.CAP_ASS_CLASS,
    VW_CFA_RECUR_CAPITAL_PROJ.CAP_ASS_CLASS_DESC,
    VW_CFA_RECUR_CAPITAL_PROJ.BUDGET_CODE,
    VW_CFA_RECUR_CAPITAL_PROJ.REVSD_PERIOD,
    VW_CFA_RECUR_CAPITAL_PROJ.WO_COUNT,
    VW_CFA_RECUR_CAPITAL_PROJ.PROJ_DIR_ACT_COST,
    VW_CFA_RECUR_CAPITAL_PROJ.PROJ_REVSD_PERIOD,
    VW_CFA_RECUR_CAPITAL_PROJ.DIR_EST_COST,
    VW_CFA_RECUR_CAPITAL_PROJ.MASTER_DATA,
    VW_CFA_RECUR_CAPITAL_PROJ.CAP_ASS_GROUP,
    VW_CFA_RECUR_CAPITAL_PROJ.CAP_ASS_GROUP_DESC,
    VW_CFA_RECUR_CAPITAL_PROJ.ACT_COUNT,
    VW_CFA_RECUR_CAPITAL_PROJ.REF_CODE2,
    VW_CFA_RECUR_CAPITAL_PROJ.REF_CODE3,
    VW_CFA_RECUR_CAPITAL_PROJ.REF_CODE4,
    VW_CFA_RECUR_CAPITAL_PROJ.REF_CODE5,
    VW_CFA_RECUR_CAPITAL_PROJ.BUDGET,
    VW_CFA_RECUR_CAPITAL_PROJ.ESTIMATE_COUNT,
    VW_CFA_RECUR_CAPITAL_PROJ.ACQUISITION_DISPOSAL
FROM
    CRYSTAL.VW_CFA_RECUR_CAPITAL_PROJ VW_CFA_RECUR_CAPITAL_PROJ
WHERE
    (
        VW_CFA_RECUR_CAPITAL_PROJ.PROJ_CLASSIFX1 = 'Z2'
    OR
        VW_CFA_RECUR_CAPITAL_PROJ.PROJ_CLASSIFX1 = 'Z4'
    )
    AND
    (
        VW_CFA_RECUR_CAPITAL_PROJ.BUDGET IS NULL
    OR
        VW_CFA_RECUR_CAPITAL_PROJ.BUDGET = '045'
    )
    AND
    (
        VW_CFA_RECUR_CAPITAL_PROJ.REF_CODE1 IS NULL
    OR
        VW_CFA_RECUR_CAPITAL_PROJ.REF_CODE1 = VW_CFA_RECUR_CAPITAL_PROJ.REF_CODE1
    )
    AND
    (
        VW_CFA_RECUR_CAPITAL_PROJ.SOURCE_FUNDS IS NULL
    OR
        VW_CFA_RECUR_CAPITAL_PROJ.SOURCE_FUNDS = VW_CFA_RECUR_CAPITAL_PROJ.SOURCE_FUNDS
    )
    AND
    (
        VW_CFA_RECUR_CAPITAL_PROJ.CAP_ASS_CLASS IS NULL
    OR
        VW_CFA_RECUR_CAPITAL_PROJ.CAP_ASS_CLASS = VW_CFA_RECUR_CAPITAL_PROJ.CAP_ASS_CLASS
    )
ORDER BY
    VW_CFA_RECUR_CAPITAL_PROJ.ACQUISITION_DISPOSAL

Code: 
CREATE OR REPLACE VIEW Crystal.VW_CFA_Recur_Capital_Proj
AS
    SELECT /*+ ORDERED USE_NL(ProjTransCount WOTransCount MSF668 MSF620 MSFX99 VW_CFA_Transactions MSF667 EstimateCount) INDEX(MSF620 MSF62001IX0) */
        MSF660.Project_No,
        MSF660.Final_Costs Proj_Final_Costs,
        SUBSTR(MSF660.Account_Code, 1, 5) Cost_Centre,
        MSF660.Proj_Desc,
        MSF660.Pc_Complete,
        Crystal.FN_CFA_Master_Data(Recursive.Project_No) Master_Data,
        MSF660.Proj_Classifx1,
        DECODE(MSF660.Proj_Classifx1, 'Z2', 'Acquisition',
                                      'Z3', 'Acquisition',
                                      'Z4', 'Acquisition',
                                      'Z5', 'Acquisition',
                                      'Z6', 'Disposal',
                                      'Z7', 'Disposal') Acquisition_Disposal,
        MSF668.Dir_Act_Cost Proj_Dir_Act_Cost,
        MSF668.Revsd_Period Proj_Revsd_Period,
        NVL(ProjTransCount.Tran_Count, 1) Act_Count,
        MSF620.Work_Order,
        MSF620.WO_Desc,
        MSF620.Final_Costs WO_Final_Costs,
        VW_CFA_Transactions.Tran_Amount WO_Tran,
        VW_CFA_Transactions.Full_Period WO_Fin_Period,
        NVL(WOTransCount.Tran_Count, 1) WO_Count,
        H1.Ref_Code Ref_Code1,
        H2.Ref_Code Ref_Code2,
        H3.Ref_Code Ref_Code3,
        H4.Ref_Code Ref_Code4,
        H5.Ref_Code Ref_Code5,
        Recursive.RecurseLevel,
        SOF.Ref_Code Source_Funds,
        DECODE(AssClass.Ref_Code, NULL, 'ZZ',
                                        AssClass.Ref_Code) Cap_Ass_Class,
        AssClassName.Table_Desc Cap_Ass_Class_Desc,
        AssEval.Ref_Code Cap_Ass_Group,
        AssEvalName.Table_Desc Cap_Ass_Group_Desc,
        SUBSTR(MSF667.Budget_Code, 1, 3) Budget,
        MSF667.Budget_Code,
        MSF667.Revsd_Period,
        DECODE(MSF667.Exp_Rev_Ind, 'E', MSF667.Dir_Est_Cost,
                                   'R', MSF667.Dir_Est_Cost * -1) Dir_Est_Cost,
        NVL(EstimateCount.Estimate_Count, 1) Estimate_Count
    FROM
        (
            SELECT
                Project_No,
                Level RecurseLevel
            FROM
                MSF660
            WHERE
                Dstrct_Code = 'CFA'
            START WITH
                Project_No IS NOT NULL AND
                Parent_Proj = '        ' AND
                Capital_Sw = 'Y'
            CONNECT BY PRIOR
                Project_No = Parent_Proj
        ) Recursive,
        MSF660,
        MSF071 H1,
        MSF071 H2,
        MSF071 H3,
        MSF071 H4,
        MSF071 H5,
        MSF071 AssClass,
        MSF010 AssClassName,
        MSF071 AssEval,
        MSF010 AssEvalName,
        MSF071 SOF,
        (
            SELECT /*+ INDEX(MSF668 MSF66801IX0) */
                Project_No,
                COUNT(*) Tran_Count
            FROM
                MSF668
            WHERE
                Dstrct_Code = 'CFA' AND
                Dir_Act_Cost <> 0 AND
                Exp_Rev_Ind > ' ' AND
                Revsd_Period > '000000'
            GROUP BY
                Project_No
        ) ProjTransCount,
        (
            SELECT /*+ INDEX(MSFX99 MSFX09901IX0) */
                Project_No,
                COUNT(*) Tran_Count
            FROM
                MSFX99
            WHERE
                Dstrct_Code = 'CFA'
            GROUP BY
                Project_No
        ) WOTransCount,
        MSF668,
        MSF620,
        MSFX99,
        VW_CFA_Transactions,
        MSF667,
        (
            SELECT /*+ INDEX(MSF667 MSF66701IX0) */
                Project_No,
                SUBSTR(Budget_Code, 1, 3) Budget,
                COUNT(*) Estimate_Count
            FROM
                MSF667
            WHERE
                Dstrct_Code = 'CFA' AND
                Budget_Code > ' ' AND
                Revsd_Period > '000000' AND
                Category_Code = ' ' AND
                Exp_Rev_Ind > ' ' AND
                Dir_Est_Cost <> 0
            GROUP BY
                Project_No,
                SUBSTR(Budget_Code, 1, 3)
        ) EstimateCount
    WHERE
        MSF660.Project_No = Recursive.Project_No AND
        MSF660.Dstrct_Code = 'CFA' AND
        MSF660.Proj_Classifx1 BETWEEN 'Z2' AND 'Z7' AND
        SUBSTR(MSF660.Account_Code, 1, 5) < '99999' AND
        --
        H1.Entity_Type(+) = 'PRJ' AND
        H1.Entity_Value(+) = 'CFA ' || MSF660.Project_No AND
        H1.Ref_No(+) = '020' AND
        H1.Seq_Num(+) = '001' AND
        --
        H2.Entity_Type(+) = 'PRJ' AND
        H2.Entity_Value(+) = 'CFA ' || MSF660.Project_No AND
        H2.Ref_No(+) = '021' AND
        H2.Seq_Num(+) = '001' AND
        --
        H3.Entity_Type(+) = 'PRJ' AND
        H3.Entity_Value(+) = 'CFA ' || MSF660.Project_No AND
        H3.Ref_No(+) = '022' AND
        H3.Seq_Num(+) = '001' AND
        --
        H4.Entity_Type(+) = 'PRJ' AND
        H4.Entity_Value(+) = 'CFA ' || MSF660.Project_No AND
        H4.Ref_No(+) = '023' AND
        H4.Seq_Num(+) = '001' AND
        --
        H5.Entity_Type(+) = 'PRJ' AND
        H5.Entity_Value(+) = 'CFA ' || MSF660.Project_No AND
        H5.Ref_No(+) = '024' AND
        H5.Seq_Num(+) = '001' AND
        --
        AssClass.Entity_Value(+) = 'CFA ' || MSF660.Project_No AND
        AssClass.Entity_Type(+) = 'PRJ' AND
        AssClass.Ref_No(+) = '030' AND
        AssClass.Seq_Num(+) = '001' AND
        --
        AssClassName.Table_Code(+) = AssClass.Ref_Code AND
        AssClassName.Table_Type(+) = '+CAC' AND
        --
        AssEval.Entity_Value(+) = 'CFA ' || MSF660.Project_No AND
        AssEval.Entity_Type(+) = 'PRJ' AND
        AssEval.Ref_No(+) = '031' AND
        AssEval.Seq_Num(+) = '001' AND
        --
        AssEvalName.Table_Code(+) = AssEval.Ref_Code AND
        AssEvalName.Table_Type(+) = '+SAG'  AND
        --
        SOF.Entity_Value(+) = 'CFA ' || MSF660.Project_No AND
        SOF.Entity_Type(+) = 'PRJ' AND
        SOF.Ref_No(+) = '040' AND
        SOF.Seq_Num(+) = '001' AND
        --
        ProjTransCount.Project_No(+) = MSF660.Project_No AND
        --
        WOTransCount.Project_No(+) = MSF660.Project_No AND
        --
        MSF668.Project_No(+) = MSF660.Project_No AND
        MSF668.Dstrct_Code(+) = 'CFA' AND
        MSF668.Dir_Act_Cost(+) <> 0 AND
        MSF668.Exp_Rev_Ind(+) > ' ' AND
        MSF668.Revsd_Period(+) > '000000' AND
        --
        MSF620.Project_No(+) = MSF660.Project_No AND
        MSF620.Dstrct_Code(+) = 'CFA' AND
        --
        MSFX99.Work_Order(+) = MSF620.Work_Order AND
        MSFX99.Dstrct_Code(+) = 'CFA' AND
        --
        VW_CFA_Transactions.Dstrct_Code(+) = MSFX99.Dstrct_Code AND
        VW_CFA_Transactions.Process_Date(+) = MSFX99.Process_Date AND
        VW_CFA_Transactions.Transaction_No(+) = MSFX99.Transaction_No AND
        VW_CFA_Transactions.UserNo(+) = MSFX99.UserNo AND
        VW_CFA_Transactions.Rec900_Type(+) = MSFX99.Rec900_Type AND
        --
        MSF667.Project_No(+) = MSF660.Project_No AND
        MSF667.Dstrct_Code(+) = 'CFA' AND
        MSF667.Budget_Code(+) > ' ' AND
        MSF667.Revsd_Period(+) > '000000' AND
        MSF667.Category_Code(+) = ' ' AND
        MSF667.Exp_Rev_Ind(+) > ' ' AND
        MSF667.Dir_Est_Cost(+) <> 0 AND
        --
        EstimateCount.Project_No(+) = MSF667.Project_No AND
        EstimateCount.Budget(+) = SUBSTR(MSF667.Budget_Code, 1, 3);

As you can see, the main problem is that, the ORDER BY is running off a value that can potentially have any of 6 values.

I hear what you are saying about indexes and sorting. I can guarantee that is NOT the case.
Also, only using 8i, so I don't have access to function based indexes.

Still, I'm thinking 46 minutes is just a little excessive.

I've tried from the Crystal Reports side to resolve this issue, sorting it myself by doing 2 seperate queries joined with a UNION ALL, but Crystal then reads the records in at about 1/50th the speed. Over 54K records, that's noticable.
I've also tried a few other things with Crystal, but none of them have given me ANY joy.

Thanks,

Peter.
 
Upvote 0 Downvote
Function based indexes are available in 8i too - just has to be Enterprise Edition.
Syntax example:
Code: 
09:04:30 LVR > select * from v$version;

BANNER
----------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for IBM/AIX RISC System/6000: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

Elapsed: 00:00:00.00
09:04:34 LVR > create table t (a number);

Table created.

Elapsed: 00:00:00.12
09:04:44 LVR > create index idx_fnct_ta on t (decode(a,9,1,2));

Index created.

Stefan
 
Upvote 0 Downvote
I don't think that index may help because sorting 56k records should not take that time even without any index. Check that you're sorting in temporary tablespace, not in permanent. Check also your sort_area_size

Regards, Dima
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mibeach7
  • Locked
  • Question Question
Group by challenge
Replies
5
Views
548
Andrzejek
Andrzejek
Olutee360
  • Locked
  • Question Question
Group by using values in a range
Replies
4
Views
534
Olutee360
Olutee360
sirnose1
  • Locked
  • Question Question
Filter by SysDate not working
Replies
3
Views
653
Andrzejek
Andrzejek
torturedmind
  • Locked
  • Question Question
Instance #2 is waiting for what?
Replies
0
Views
267
torturedmind
torturedmind
szeiss
  • Locked
  • Question Question
Increment Number by Date with Reset
Replies
2
Views
347
szeiss
szeiss

Part and Inventory Search

Sponsor

Back
Top