Home » RDBMS Server » Performance Tuning » derived column index (oralce 10g)
derived column index [message #501988] Sat, 02 April 2011 02:14 Go to next message
miroconnect@yahoo.com
Messages: 199
Registered: April 2006
Senior Member
here is my query
               SELECT CURRENTSTEP
                  FROM (SELECT (   WFENTRY.NAME
                                || ','
                                || CURRENTSTEP.STEP_ID
                               ) AS CURRENTSTEP,
                               (CASE
                                   WHEN WFENTRY.NAME IN
                                          ('audit-program-fo-workflow',
                                           'audit-program-amendment-fo-workflow'
                                          )
                                   AND WFENTITY.OBJECT_TYPE_ID = 3
                                      THEN (SELECT EAG.SYS_AUDIT_ID
                                              FROM EA_AUDIT_GENERAL EAG,
                                                   EA_AUDIT_PROGRAM EAP,
                                                   EA_AUDIT_PROG_ANALYST EAPA
                                             WHERE EAG.SYS_AUDIT_ID =
                                                              EAP.SYS_AUDIT_ID
                                               AND EAP.SYS_AUDIT_PROG_ID =
                                                        EAPA.SYS_AUDIT_PROG_ID
                                               AND EAPA.SYS_AUDIT_PROG_ANALYST_ID =
                                                            WFENTITY.OBJECT_ID)
                                   WHEN WFENTRY.NAME IN
                                          ('audit-program-notification-workflow'
                                          )
                                   AND WFENTITY.OBJECT_TYPE_ID = 4
                                      THEN (SELECT EAG.SYS_AUDIT_ID
                                              FROM EA_AUDIT_GENERAL EAG,
                                                   EA_AUDIT_PROGRAM EAP,
                                                   EA_AUDIT_PROG_NOTIFICATION EAPN
                                             WHERE EAG.SYS_AUDIT_ID =
                                                              EAP.SYS_AUDIT_ID
                                               AND EAP.SYS_AUDIT_PROG_ID =
                                                        EAPN.SYS_AUDIT_PROG_ID
                                               AND EAPN.SYS_NOTIFICATION_ID =
                                                            WFENTITY.OBJECT_ID)
                                   WHEN WFENTRY.NAME IN
                                          ('audit-program-workflow',
                                           'audit-program-amendment-workflow',
                                           'audit-program-dfi-review-findings-workflow'
                                          )
                                   AND WFENTITY.OBJECT_TYPE_ID = 2
                                      THEN (SELECT EAG.SYS_AUDIT_ID
                                              FROM EA_AUDIT_GENERAL EAG,
                                                   EA_AUDIT_PROGRAM EAP
                                             WHERE EAG.SYS_AUDIT_ID =
                                                              EAP.SYS_AUDIT_ID
                                               AND EAP.SYS_AUDIT_PROG_ID =
                                                            WFENTITY.OBJECT_ID)
                                   WHEN WFENTRY.NAME IN
                                          ('stg-audit-workflow',
                                           'ihs-audit-workflow'
                                          )
                                   AND WFENTITY.OBJECT_TYPE_ID = 1
                                      THEN (SELECT EAG.SYS_AUDIT_ID
                                              FROM EA_AUDIT_GENERAL EAG
                                             WHERE EAG.SYS_AUDIT_ID =
                                                            WFENTITY.OBJECT_ID)
                                END
                               ) AS SYS_AUDIT_ID
                          FROM OS_WFENTRY WFENTRY,
                               OS_CURRENTSTEP CURRENTSTEP,
                               WF_ENTITY WFENTITY
                         WHERE WFENTRY.ID = CURRENTSTEP.ENTRY_ID
                           AND WFENTITY.ENTRY_ID = WFENTRY.ID)


in this query I am concatenating tow columns , I use this query as a sub query in my other queries and filter the results with and CURRENTSTEP = ?
here is how I use it
select
                        sys_audit_id  
                    from
                        (       SELECT
                            *   
                        FROM
                            (SELECT
                                F.FINDING_NUMBER,
                                F.FINDING_AMT,
                                PROG.ADMIN_BY,
                                PROG.SYS_PROG_ID,
                                PROG.GRANT_NBR,
                                AG.SYS_AUDIT_ID,
                                (SELECT
                                    FIRST_NAME || ' ' || LAST_NAME                   
                                FROM
                                    INF_PERSON                  
                                WHERE
                                    SYS_PERSON_ID = AG.FMS_USER_ID) AS FMS,
                                (SELECT
                                    LAST_NAME                   
                                FROM
                                    INF_PERSON                  
                                WHERE
                                    SYS_PERSON_ID = AG.AS_USER_ID) AS ASSEC,
                                PROG.GRANTS_OFFICER,
                                PROG.PD,
                                PROG.FOS,
                                AMENDMENT_STATUS,
                                AUDIT_STATUS,
                                AG.CURRENTSTEP           
                            FROM
                                EA_FINDING F,
                                (SELECT
                                    CURRENTSTEP,
                                    EAG.SYS_AUDIT_ID,
                                    EAG.FMS_USER_ID,
                                    EAG.AS_USER_ID                   
                                FROM
                                    (SELECT
                                        (   WFENTRY.NAME                                 || ','                                 || CURRENTSTEP.STEP_ID                                ) AS CURRENTSTEP,
                                        (CASE                                    
                                            WHEN WFENTRY.NAME IN                                           ('audit-program-fo-workflow',
                                            'audit-program-amendment-fo-workflow'                                           )                                    
                                            AND WFENTITY.OBJECT_TYPE_ID = 3                                       THEN (SELECT
                                                EAG.SYS_AUDIT_ID                                               
                                            FROM
                                                EA_AUDIT_GENERAL EAG,
                                                EA_AUDIT_PROGRAM EAP,
                                                EA_AUDIT_PROG_ANALYST EAPA                                              
                                            WHERE
                                                EAG.SYS_AUDIT_ID =                                                               EAP.SYS_AUDIT_ID                                                
                                                AND EAP.SYS_AUDIT_PROG_ID =                                                         EAPA.SYS_AUDIT_PROG_ID                                                
                                                AND EAPA.SYS_AUDIT_PROG_ANALYST_ID =                                                             WFENTITY.OBJECT_ID)                                    
                                            WHEN WFENTRY.NAME IN                                           ('audit-program-notification-workflow'                                           )                                    
                                            AND WFENTITY.OBJECT_TYPE_ID = 4                                       THEN (SELECT
                                                EAG.SYS_AUDIT_ID                                               
                                            FROM
                                                EA_AUDIT_GENERAL EAG,
                                                EA_AUDIT_PROGRAM EAP,
                                                EA_AUDIT_PROG_NOTIFICATION EAPN                                              
                                            WHERE
                                                EAG.SYS_AUDIT_ID =                                                               EAP.SYS_AUDIT_ID                                                
                                                AND EAP.SYS_AUDIT_PROG_ID =                                                         EAPN.SYS_AUDIT_PROG_ID                                                
                                                AND EAPN.SYS_NOTIFICATION_ID =                                                             WFENTITY.OBJECT_ID)                                    
                                            WHEN WFENTRY.NAME IN                                           ('audit-program-workflow',
                                            'audit-program-amendment-workflow',
                                            'audit-program-dfi-review-findings-workflow'                                           )                                    
                                            AND WFENTITY.OBJECT_TYPE_ID = 2                                       THEN (SELECT
                                                EAG.SYS_AUDIT_ID                                               
                                            FROM
                                                EA_AUDIT_GENERAL EAG,
                                                EA_AUDIT_PROGRAM EAP                                              
                                            WHERE
                                                EAG.SYS_AUDIT_ID =                                                               EAP.SYS_AUDIT_ID                                                
                                                AND EAP.SYS_AUDIT_PROG_ID =                                                             WFENTITY.OBJECT_ID)                                    
                                            WHEN WFENTRY.NAME IN                                           ('stg-audit-workflow',
                                            'ihs-audit-workflow'                                           )                                    
                                            AND WFENTITY.OBJECT_TYPE_ID = 1                                       THEN (SELECT
                                                EAG.SYS_AUDIT_ID                                               
                                            FROM
                                                EA_AUDIT_GENERAL EAG                                              
                                            WHERE
                                                EAG.SYS_AUDIT_ID =                                                             WFENTITY.OBJECT_ID)                                 
                                        END                                ) AS SYS_AUDIT_ID                           
                                    FROM
                                        OS_WFENTRY WFENTRY,
                                        OS_CURRENTSTEP CURRENTSTEP,
                                        WF_ENTITY WFENTITY                          
                                    WHERE
                                        WFENTRY.ID = CURRENTSTEP.ENTRY_ID                            
                                        AND WFENTITY.ENTRY_ID = WFENTRY.ID
                                    ) WF,                        EA_AUDIT_GENERAL EAG                  
                                WHERE
                                    EAG.SYS_AUDIT_ID = WF.SYS_AUDIT_ID(+)
                                ) AG,                (
                                    SELECT
                                        AP.SYS_AUDIT_ID,
                                        AP.SYS_PROG_ID,
                                        IG.GRANT_NBR,
                                        IP.ADMIN_BY,
                                        (SELECT
                                            FIRST_NAME || ' '                                || LAST_NAME                           
                                        FROM
                                            INF_PERSON                          
                                        WHERE
                                            SYS_PERSON_ID =                                               AP.GO_USER_ID)                                                             AS GRANTS_OFFICER,
                                        (SELECT
                                            FIRST_NAME || ' ' || LAST_NAME                           
                                        FROM
                                            INF_PERSON                          
                                        WHERE
                                            SYS_PERSON_ID = AP.PD_USER_ID) AS PD,
                                        (SELECT
                                            FIRST_NAME || ' ' || LAST_NAME                           
                                        FROM
                                            INF_PERSON                          
                                        WHERE
                                            SYS_PERSON_ID = APA.SYS_USER_ID) AS FOS,
                                        (SELECT
                                            STATUS                           
                                        FROM
                                            EA_AUDIT_PROG_AMENDMENT EAPG                          
                                        WHERE
                                            EAPG.SYS_AUDIT_PROG_ID = AP.SYS_AUDIT_PROG_ID)                                                           AS AMENDMENT_STATUS,
                                        AG.AUDIT_REVIEW_CODE AS AUDIT_STATUS                   
                                    FROM
                                        EA_AUDIT_PROGRAM AP,
                                        EA_AUDIT_PROG_GRANT EAPG,
                                        INF_GRANT IG,
                                        INF_PROGRAM IP,
                                        EA_AUDIT_GENERAL AG,
                                        EA_AUDIT_PROG_ANALYST APA                  
                                    WHERE
                                        AG.SYS_AUDIT_ID = AP.SYS_AUDIT_ID                    
                                        AND AP.SYS_AUDIT_PROG_ID = EAPG.SYS_AUDIT_PROG_ID                    
                                        AND EAPG.SYS_GRANT_ID = IG.SYS_GRANT_ID                    
                                        AND IP.SYS_PROG_ID = AP.SYS_PROG_ID                    
                                        AND AP.SYS_AUDIT_PROG_ID = APA.SYS_AUDIT_PROG_ID(+)
                                    ) PROG          
                                WHERE
                                    AG.SYS_AUDIT_ID = F.SYS_AUDIT_ID 
                                    AND AG.SYS_AUDIT_ID = PROG.SYS_AUDIT_ID(+)
                                )    
                            WHERE
                                1 = 1              
                                and CURRENTSTEP  =:?  
                            )  
                        


I saw adding this as a subquery with the filter
and CURRENTSTEP  = ?
is slowing my query very much , as this is a derived column i cannot add index then how to improve performance for this subquery ?
Re: derived column index [message #501991 is a reply to message #501988] Sat, 02 April 2011 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the usual required and requested information.

Regards
Michel
Re: derived column index [message #501994 is a reply to message #501991] Sat, 02 April 2011 07:51 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 199
Registered: April 2006
Senior Member
I have a sub query which has a derived column which is created by
concatinate two actual columns , when I search for a particular row of derived column the query is very slow , i am wondering if I can create index on this derived column or give some hint to oralce to improve performance ?
Re: derived column index [message #501995 is a reply to message #501994] Sat, 02 April 2011 08:37 Go to previous message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Previous Topic: using between slows query
Next Topic: Query not executing (3 Merged)
Goto Forum:
  


Current Time: Tue Oct 21 22:39:45 CDT 2014

Total time taken to generate the page: 0.08161 seconds