Home » RDBMS Server » Performance Tuning » select column list vs select * (Oracle 10.1.0.4)
select column list vs select * [message #580120] Wed, 20 March 2013 10:03 Go to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

I have a query which is taking 5 sec with select * but when I include the column list it takes more than 10 min.
Moreover the explain plan is same for both the cases.

Could you please help me to tune this.

Note: I have changed the objects, literals used in the query manually, you may see difference of literals used in in the explain plan.


The table sb.F_P_2 fp1 has 20 million rows
The remaining tables are small holds thousand or lakh rows

SELECT /*+ ordered INDEX_COMBINE(fp1 FP1_DPST_FK_I FP1_SMCV_FK_I FP1_KUBE_GPN_I FP1_KUBE_bb_I FP1_KUBE_MKZ_I
              FP1_ERbbFFNUNG_I FP1_ERLEDIGUNG_I FP1_AVISIERUNG_I FP1_FAELLIGKEIT_I
              FP1_KUNDE_SCHUTZ_CD_I FP1_DKG_GRUPPEN_ID_I FP1_DPK_KATEGORIE_ID_I)*/
      *
  FROM sb.D_PEND_CR smcv,
       sb.D_PEND_TEXTE dpst,
       sb.AU_UNETE_bbS aubb,
       sb.AU_FRONA_BB afmo,
       Sb.REF_V_US_BB kscd,
       sb.F_P_2 fp1
 WHERE     aubb.AUbb_UGEORD_bb = afmo.AFMO_bb
       AND fp1.FP1_KUBE_bb = aubb.AUbb_UGEORD_bb
       AND kscd.USERID = afmo.AFMO_TNUMMER                       --lower(user)
       AND fp1.FP1_KUNDE_SCHUTZ_CD = kscd.SCHUTZCODE
       AND dpst.DPST_STATUS_CD = fp1.FP1_DPST_STATUS_CD
       AND smcv.REF_ID_CATEGORY_VALUE_ID = fp1.FP1_SMCV_KEY
       AND NVL (SYS_CONTEXT ('gs', 'cd'), 'BB') = 'BB'              
       AND dpst.DPST_STATUS_CD IN ('DD', 'CC', 'BB')
       AND FP1_DKG_GRUPPEN_ID = '2222'
       AND FP1_DPK_KATEGORIE_ID = '4444'
       AND FP1_SMCV_KEY IN ('1368487', '1368488', '2885625', '2885624')
       AND AUbb_BB = 'BBBB'

SELECT /*+ ordered INDEX_COMBINE(fp1 FP1_DPST_FK_I FP1_SMCV_FK_I FP1_KUBE_GPN_I FP1_KUBE_bb_I FP1_KUBE_MKZ_I
              FP1_ERbbFFNUNG_I FP1_ERLEDIGUNG_I FP1_AVISIERUNG_I FP1_FAELLIGKEIT_I
              FP1_KUNDE_SCHUTZ_CD_I FP1_DKG_GRUPPEN_ID_I FP1_DPK_KATEGORIE_ID_I)*/
      afmo.AFMO_TNUMMER,
       'bbbb',
       aubb.AUbb_bb,
       aubb.AUbb_UGEORD_bb,
       fp1.FP1_KUBE_MKZ,
       fp1.FP1_KUBE_GPN,
       fp1.FP1_DPST_STATUS_CD,
       fp1.FP1_DKG_GRUPPEN_ID,
       fp1.FP1_DPK_KATEGORIE_ID,
       fp1.FP1_SMCV_KEY,
       fp1.FP1_ERLEDIGUNG,
       fp1.FP1_AVISIERUNG,
       fp1.FP1_FAELLIGKEIT,
       fp1.FP1_ERbbFFNUNG,
       fp1.FP1_ID_PENDING_ITEM,
       CASE
          WHEN FP1_ERLEDIGUNG = TO_DATE ('01.01.0100', 'dd.mm.yyyy')
          THEN
             NULL
          ELSE
             FP1_ERLEDIGUNG
       END,
       CASE
          WHEN FP1_AVISIERUNG = TO_DATE ('01.01.0100', 'dd.mm.yyyy')
          THEN
             NULL
          ELSE
             FP1_AVISIERUNG
       END,
       CASE
          WHEN FP1_FAELLIGKEIT = TO_DATE ('01.01.0100', 'dd.mm.yyyy')
          THEN
             NULL
          ELSE
             FP1_FAELLIGKEIT
       END,
       FP1_KUNDE,
       FP1_ORT,
       DECODE (fp1.FP1_KUNDE_BCNR,
               NULL, fp1.FP1_KUNDE_STAMMNR,
               fp1.FP1_KUNDE_BCNR || '/' || fp1.FP1_KUNDE_STAMMNR),
       FP1_PS_GR_GPID,
       FP1_PS_LE_GPID,
          fp1.FP1_KUBE_bb
       || DECODE (fp1.FP1_KUBE_GPN, NULL, '', '-' || fp1.FP1_KUBE_GPN),
          fp1.FP1_CO_bb
       || DECODE (fp1.FP1_CO_GPN, NULL, '', '-' || fp1.FP1_CO_GPN),
       FP1_RATING,
       FP1_CKKUR,
          fp1.FP1_ERSTELLER_bb
       || DECODE (fp1.FP1_ERSTELLER_GPN,
                  NULL, '',
                  '-' || fp1.FP1_ERSTELLER_GPN),
          fp1.FP1_ERLEDIGER_bb
       || DECODE (fp1.FP1_ERLEDIGER_GPN,
                  NULL, '',
                  '-' || fp1.FP1_ERLEDIGER_GPN),
       dpst.DPST_TEXT_D,
       dpst.DPST_TEXT_F,
       dpst.DPST_TEXT_I,
       dpst.DPST_TEXT_E,
       smcv.REF_TEXT_D,
       smcv.REF_TEXT_F,
       smcv.REF_TEXT_I,
       smcv.REF_TEXT_E,
       fp1.FP1_NAME_PENDING_ITEM_D,
       NVL (fp1.FP1_NAME_PENDING_ITEM_F, fp1.FP1_NAME_PENDING_ITEM_D),
       NVL (fp1.FP1_NAME_PENDING_ITEM_I, fp1.FP1_NAME_PENDING_ITEM_D),
       NVL (fp1.FP1_NAME_PENDING_ITEM_E, fp1.FP1_NAME_PENDING_ITEM_D),
       REPLACE (fp1.FP1_AUFTRAGSDETAILS_D, CHR (1), CHR (10)),
       REPLACE (fp1.FP1_AUFTRAGSDETAILS_F, CHR (1), CHR (10)),
       REPLACE (fp1.FP1_AUFTRAGSDETAILS_I, CHR (1), CHR (10)),
       REPLACE (fp1.FP1_AUFTRAGSDETAILS_E, CHR (1), CHR (10)),
       REPLACE (
          REPLACE (REPLACE (FP1_ZUSATZINFO1, '<br>', CHR (10)),
                   '<BR>',
                   CHR (10)),
          CHR (1),
          CHR (10)),
       REPLACE (
          REPLACE (REPLACE (FP1_ZUSATZINFO2, '<br>', CHR (10)),
                   '<BR>',
                   CHR (10)),
          CHR (1),
          CHR (10)),
       REPLACE (
          REPLACE (REPLACE (FP1_ZUSATZINFO3, '<br>', CHR (10)),
                   '<BR>',
                   CHR (10)),
          CHR (1),
          CHR (10)),
       FP1_ANZAHL_KOMMENTARE,
       REPLACE (FP1_LETZTER_KOMMENTAR, CHR (1), CHR (10)),
       FP1_INITIAL_CREATION_DATE,
       FP1_FOLLOWUP_COUNTER
  FROM sb.D_PEND_CR smcv,
       sb.D_PEND_TEXTE dpst,
       sb.AU_UNETE_bbS aubb,
       sb.AU_FRONA_BB afmo,
       Sb.REF_V_US_BB kscd,
       sb.F_P_2 fp1
 WHERE     aubb.AUbb_UGEORD_bb = afmo.AFMO_bb
       AND fp1.FP1_KUBE_bb = aubb.AUbb_UGEORD_bb
       AND kscd.USERID = afmo.AFMO_TNUMMER
       AND fp1.FP1_KUNDE_SCHUTZ_CD = kscd.SCHUTZCODE
       AND dpst.DPST_STATUS_CD = fp1.FP1_DPST_STATUS_CD
       AND smcv.REF_ID_CATEGORY_VALUE_ID = fp1.FP1_SMCV_KEY
       AND NVL (SYS_CONTEXT ('gs', 'cd'), 'BB') = 'BB'
       AND dpst.DPST_STATUS_CD IN ('DD', 'CC', 'BB')
       AND FP1_DKG_GRUPPEN_ID = '2222'
       AND FP1_DPK_KATEGORIE_ID = '4444'
       AND FP1_SMCV_KEY IN ('1368487', '1368488', '2885625', '2885624')
       AND AUbb_BB = 'BBBB'


The explain plan is same in both the cases.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3606542753

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |     1 |  1131 |   258   (2)| 00:00:03 |
|*  1 |  FILTER                            |                          |       |       |            |       |
|*  2 |   HASH JOIN                        |                          |     1 |  1131 |   258   (2)| 00:00:03 |
|*  3 |    HASH JOIN                       |                          |     3 |  3222 |   256   (2)| 00:00:03 |
|*  4 |     HASH JOIN                      |                          |     3 |  2721 |   253   (2)| 00:00:03 |
|*  5 |      HASH JOIN                     |                          |     1 |   895 |   247   (2)| 00:00:03 |
|*  6 |       TABLE ACCESS BY INDEX ROWID  | F_P_2          					|     1 |   871 |   193   (0)| 00:00:02 |
|   7 |        BITMAP CONVERSION TO ROWIDS |                          |       |       |            |       |
|   8 |         BITMAP AND                 |                          |       |       |            |       |
|*  9 |          BITMAP INDEX SINGLE VALUE | FP1_DPK_KATEGORIE_ID_I   |       |       |            |       |
|* 10 |          BITMAP INDEX SINGLE VALUE | FP1_DKG_GRUPPEN_ID_I     |       |       |            |       |
|  11 |          BITMAP OR                 |                          |       |       |            |       |
|* 12 |           BITMAP INDEX SINGLE VALUE| FP1_DPST_FK_I            |       |       |            |       |
|* 13 |           BITMAP INDEX SINGLE VALUE| FP1_DPST_FK_I            |       |       |            |       |
|* 14 |           BITMAP INDEX SINGLE VALUE| FP1_DPST_FK_I            |       |       |            |       |
|  15 |          BITMAP OR                 |                          |       |       |            |       |
|* 16 |           BITMAP INDEX SINGLE VALUE| FP1_SMCV_FK_I            |       |       |            |       |
|* 17 |           BITMAP INDEX SINGLE VALUE| FP1_SMCV_FK_I            |       |       |            |       |
|* 18 |           BITMAP INDEX SINGLE VALUE| FP1_SMCV_FK_I            |       |       |            |       |
|* 19 |           BITMAP INDEX SINGLE VALUE| FP1_SMCV_FK_I            |       |       |            |       |
|* 20 |       HASH JOIN                    |                          |   895 | 21480 |    53   (4)| 00:00:01 |
|* 21 |        INDEX RANGE SCAN            | AUbb_PK                  |   741 |  7410 |     5   (0)| 00:00:01 |
|* 22 |        INDEX RANGE SCAN            | AFMO_PK                  | 22551 |   308K|    47   (3)| 00:00:01 |
|* 23 |      INDEX FAST FULL SCAN          | PK_AUT_T_USER_SCHUTZCODE |  1436 | 17232 |     6  (17)| 00:00:01 |
|* 24 |     MAT_VIEW ACCESS FULL           | D_PEND_CR   							|     4 |   668 |     2   (0)| 00:00:01 |
|* 25 |    TABLE ACCESS FULL               | D_PEND_TEXTE 						|     1 |    57 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NVL(SYS_CONTEXT('gensales','rqcountrycd'),'CH')='CH')
   2 - access("DPST"."DPST_STATUS_CD"="FP1"."FP1_DPST_STATUS_CD")
   3 - access("SMCV"."REF_ID_CATEGORY_VALUE_ID"="FP1"."FP1_SMCV_KEY")
   4 - access("FP1"."FP1_KUNDE_SCHUTZ_CD"="SCHUTZCODE")
   5 - access("FP1"."FP1_KUBE_bb"="AUbb"."AUbb_UGEORD_bb")
   6 - filter(("FP1"."FP1_DPST_STATUS_CD"='PEN' OR "FP1"."FP1_DPST_STATUS_CD"='VIS' OR
              "FP1"."FP1_DPST_STATUS_CD"='ZUR') AND ("FP1"."FP1_SMCV_KEY"=1368487 OR "FP1"."FP1_SMCV_KEY"=1368488 OR
              "FP1"."FP1_SMCV_KEY"=2885624 OR "FP1"."FP1_SMCV_KEY"=2885625))
   9 - access("FP1_DPK_KATEGORIE_ID"=3020)
  10 - access("FP1_DKG_GRUPPEN_ID"=3004)
  12 - access("FP1"."FP1_DPST_STATUS_CD"='PEN')
  13 - access("FP1"."FP1_DPST_STATUS_CD"='VIS')
  14 - access("FP1"."FP1_DPST_STATUS_CD"='ZUR')
  16 - access("FP1"."FP1_SMCV_KEY"=1368487)
  17 - access("FP1"."FP1_SMCV_KEY"=1368488)
  18 - access("FP1"."FP1_SMCV_KEY"=2885624)
  19 - access("FP1"."FP1_SMCV_KEY"=2885625)
  20 - access("AUbb"."AUbb_UGEORD_bb"="AFMO"."AFMO_bb")
  21 - access("AUbb"."AUbb_bb"='A08G')
  22 - access("AFMO"."AFMO_TNUMMER"='t434250')
  23 - filter(LOWER("USERID")='t434250')
  24 - filter("SMCV"."REF_ID_CATEGORY_VALUE_ID"=1368487 OR "SMCV"."REF_ID_CATEGORY_VALUE_ID"=1368488
              OR "SMCV"."REF_ID_CATEGORY_VALUE_ID"=2885624 OR "SMCV"."REF_ID_CATEGORY_VALUE_ID"=2885625)
  25 - filter("DPST"."DPST_STATUS_CD"='PEN' OR "DPST"."DPST_STATUS_CD"='VIS' OR
              "DPST"."DPST_STATUS_CD"='ZUR')
              


Thank you very much in advance.

Regards,
Pointers
Re: select column list vs select * [message #580121 is a reply to message #580120] Wed, 20 March 2013 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the hints.

Regards
Michel
Re: select column list vs select * [message #580123 is a reply to message #580121] Wed, 20 March 2013 10:41 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi Micheal,

Thank you very much for your reply.

After removing all the hints,the select <column list> query took 8 min.

In general, the query finally gives 220 rows which was just retrived in 5 sec (with hints) when select * was used, but when the column list (it has decode, case, nvl and replace functions) it takes more than 10 min even all hints are removed.

Regards,
Pointers
Re: select column list vs select * [message #580124 is a reply to message #580120] Wed, 20 March 2013 10:44 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Could you please do the following for the both sqls.

1. make the following settings

set linesize 1000
set pagesize 1000

2. then run

alter session set statistics_level=all;

3. then run your sql,

4. after that run the following select:

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));


5. upload the last formatted output.
Re: select column list vs select * [message #580128 is a reply to message #580120] Wed, 20 March 2013 11:38 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi LNossov,

Thank you very mubbbb for the reply.

I have run as suggested. I use Oracle 10.1.0.4 so, I could not run
select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

but I used
select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ALL'));   


The below is output for the select *
                                                              
                                                                                                                     
----------------------------------------------------------------------------------------------------------           
| Id  | Operation                      | Name                     | Rows  | Bytes | Cost (%CPU)| Time                
----------------------------------------------------------------------------------------------------------           
|   0 | SELECT STATEMENT               |                          |       |       |   262K(100)|                     
|*  1 |  FILTER                        |                          |       |       |            |                     
|*  2 |   TABLE ACCESS BY INDEX ROWID  | F_P_2          |     1 |   871 |   262K (48)| 00:35:54            
|   3 |    NESTED LOOPS                |                          |     1 |  1298 |   262K (48)| 00:35:54            
|   4 |     MERGE JOIN CARTESIAN       |                          |  7714K|  3141M| 23208  (15)| 00:03:11            
|*  5 |      HASH JOIN                 |                          |  5372 |  2145K|    76   (3)| 00:00:01            
|*  6 |       INDEX RANGE SCAN         | AFMO_PK                  | 22551 |   506K|    46   (0)| 00:00:01            
|   7 |       MERGE JOIN CARTESIAN     |                          |  4444 |  1675K|    28   (0)| 00:00:01            
|   8 |        MERGE JOIN CARTESIAN    |                          |     6 |  2208 |     4   (0)| 00:00:01            
|*  9 |         MAT_VIEW ACCESS FULL   | D_PEND_CR   |     4 |  1244 |     2   (0)| 00:00:01            
|  10 |         BUFFER SORT            |                          |     1 |    57 |     2   (0)| 00:00:01            
|* 11 |          TABLE ACCESS FULL     | D_PEND_TEXTE |     1 |    57 |     0   (0)|                     
|  12 |        BUFFER SORT             |                          |   741 | 13338 |    27   (0)| 00:00:01            
|* 13 |         INDEX RANGE SCAN       | AUbb_PK                  |   741 | 13338 |     4   (0)| 00:00:01            
|  14 |      BUFFER SORT               |                          |  1436 | 25848 | 23161  (15)| 00:03:11            
|* 15 |       INDEX FAST FULL SCAN     | PK_AUT_T_USER_SbbbbUTZCODE |  1436 | 25848 |     4  (25)| 00:00:01            
|  16 |     BITMAP CONVERSION TO ROWIDS|                          |       |       |            |                     
|  17 |      BITMAP AND                |                          |       |       |            |                     
|* 18 |       BITMAP INDEX SINGLE VALUE| FP1_DPST_FK_I            |       |       |            |                     
|* 19 |       BITMAP INDEX SINGLE VALUE| FP1_KUNDE_SbbbbUTZ_CD_I    |       |       |            |                     
|* 20 |       BITMAP INDEX SINGLE VALUE| FP1_DPK_KATEGORIE_ID_I   |       |       |            |                     
|* 21 |       BITMAP INDEX SINGLE VALUE| FP1_DKG_GRUPPEN_ID_I     |       |       |            |                     
|* 22 |       BITMAP INDEX SINGLE VALUE| FP1_KUBE_bb_I            |       |       |            |                     
|* 23 |       BITMAP INDEX SINGLE VALUE| FP1_SMCV_FK_I            |       |       |            |                     
----------------------------------------------------------------------------------------------------------           
                                                                                                                     
Query Block Name / Object Alias (identified by operation id):                                                        
-------------------------------------------------------------                                                        
                                                                                                                     
   1 - SEL$5C160134                                                                                                  
   2 - SEL$5C160134 / FP1@SEL$1                                                                                      
   6 - SEL$5C160134 / AFMO@SEL$1                                                                                     
   9 - SEL$5C160134 / SMCV@SEL$1                                                                                     
  11 - SEL$5C160134 / DPST@SEL$1                                                                                     
  13 - SEL$5C160134 / AUbb@SEL$1                                                                                     
  15 - SEL$5C160134 / AUT_T_USER_SbbbbUTZCODE_SLS@SEL$3                                                                
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   1 - filter(NVL(SYS_CONTEXT('gensales','rqcountrycd'),'bbbb')='bbbb')                                                  
   2 - filter(("FP1"."FP1_DPST_STATUS_CD" AND "FP1_SMCV_KEY"))                                                       
   5 - access("AUbb"."AUbb_UGEORD_bb"="AFMO"."AFMO_bb")                                                              
   6 - access("AFMO"."AFMO_TNUMMER"='t434250')                                                                       
   9 - filter(("SMCV"."REF_ID_CATEGORY_VALUE_ID"=1368487 OR                                                          
              "SMCV"."REF_ID_CATEGORY_VALUE_ID"=1368488 OR "SMCV"."REF_ID_CATEGORY_VALUE_ID"=2885624 OR              
              "SMCV"."REF_ID_CATEGORY_VALUE_ID"=2885625))                                                            
  11 - filter(("DPST"."DPST_STATUS_CD"='PEN' OR "DPST"."DPST_STATUS_CD"='VIS' OR                                     
              "DPST"."DPST_STATUS_CD"='ZUR'))                                                                        
  13 - access("AUbb_bb"='A08G')                                                                                      
  15 - filter(LOWER("USERID")='t434250')                                                                             
  18 - access("DPST"."DPST_STATUS_CD"="FP1"."FP1_DPST_STATUS_CD")                                                    
       filter(("FP1"."FP1_DPST_STATUS_CD"='PEN' OR "FP1"."FP1_DPST_STATUS_CD"='VIS' OR                               
              "FP1"."FP1_DPST_STATUS_CD"='ZUR'))                                                                     
  19 - access("FP1"."FP1_KUNDE_SbbbbUTZ_CD"="SbbbbUTZCODE")                                                              
  20 - access("FP1_DPK_KATEGORIE_ID"=3020)                                                                           
  21 - access("FP1_DKG_GRUPPEN_ID"=3004)                                                                             
  22 - access("FP1"."FP1_KUBE_bb"="AUbb"."AUbb_UGEORD_bb")                                                           
  23 - access("SMCV"."REF_ID_CATEGORY_VALUE_ID"="FP1"."FP1_SMCV_KEY")                                                
       filter(("FP1_SMCV_KEY"=1368487 OR "FP1_SMCV_KEY"=1368488 OR "FP1_SMCV_KEY"=2885624 OR                         
              "FP1_SMCV_KEY"=2885625))                                                                               
                                                                                                                     
Column Projection Information (identified by operation id):                                                          
-----------------------------------------------------------                                                          
                                                                                                                     
   1 - "AFMO"."AFMO_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4],                                            
       "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8], "AFMO"."AFMO_CREATED"[DATE,7],                                             
       "AFMO"."AFMO_AUT_TYP"[NUMBER,22], "SMCV"."DKG_GRUPPEN_ID"[NUMBER,22],                                         
       "SMCV"."DKG_TEXT_D"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_F"[VARbbbbAR2,250],                                         
       "SMCV"."DKG_TEXT_I"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_E"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_KATEGORIE_ID"[NUMBER,22], "SMCV"."DPK_TEXT_D"[VARbbbbAR2,250],                                      
       "SMCV"."DPK_TEXT_F"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_I"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_TEXT_E"[VARbbbbAR2,250], "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22],                              
       "SMCV"."REF_TEXT_D"[VARbbbbAR2,300], "SMCV"."REF_TEXT_F"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_I"[VARbbbbAR2,300], "SMCV"."REF_TEXT_E"[VARbbbbAR2,300],                                         
       "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3], "DPST"."DPST_TEXT_D"[VARbbbbAR2,50],                                       
       "DPST"."DPST_TEXT_F"[VARbbbbAR2,50], "DPST"."DPST_TEXT_I"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "DPST"."DPST_CREATED"[DATE,7], "AUbb_bb"[VARbbbbAR2,4],                      
       "AUbb"."AUbb_CREATED"[DATE,7], "USERID"[VARbbbbAR2,20], "SbbbbUTZCODE"[VARbbbbAR2,2],                               
       "OSUSER"[VARbbbbAR2,20], "FP1"."FP1_ID_PENDING_ITEM"[NUMBER,22],                                                
       "FP1"."FP1_PROCESS_STATE"[NUMBER,22], "FP1"."FP1_ITEM_STATE"[NUMBER,22],                                      
       "FP1"."FP1_ASSIGN_STATE"[NUMBER,22], "FP1"."FP1_NAME_PENDING_ITEM_D"[VARbbbbAR2,100],                           
       "FP1"."FP1_NAME_PENDING_ITEM_F"[VARbbbbAR2,100], "FP1"."FP1_NAME_PENDING_ITEM_I"[VARbbbbAR2,100],                 
       "FP1"."FP1_NAME_PENDING_ITEM_E"[VARbbbbAR2,100], "FP1"."FP1_KUBE_bb"[VARbbbbAR2,4],                               
       "FP1"."FP1_KUBE_MKZ"[VARbbbbAR2,3], "FP1"."FP1_CO_bb"[VARbbbbAR2,4], "FP1"."FP1_CO_MKZ"[VARbbbbAR2,3],              
       "FP1"."FP1_ERLEDIGER_bb"[VARbbbbAR2,4], "FP1"."FP1_ERLEDIGER_MKZ"[VARbbbbAR2,3],                                  
       "FP1"."FP1_ERLEDIGER_GPN"[VARbbbbAR2,8], "FP1"."FP1_ERSTELLER_bb"[VARbbbbAR2,4],                                  
       "FP1"."FP1_ERSTELLER_MKZ"[VARbbbbAR2,3], "FP1"."FP1_ESKALATOR_LETZTER"[VARbbbbAR2,12],                            
       "FP1"."FP1_ESKALATIONSSTUFE"[NUMBER,22], "FP1"."FP1_KUNDE_BCNR"[VARbbbbAR2,4],                                  
       "FP1"."FP1_KUNDE_STAMMNR"[VARbbbbAR2,8], "FP1"."FP1_PS_GPID"[VARbbbbAR2,12],                                      
       "FP1"."FP1_PS_GR_GPID"[VARbbbbAR2,12], "FP1"."FP1_PS_LE_GPID"[VARbbbbAR2,12],                                     
       "FP1"."FP1_PS_B_GPID"[VARbbbbAR2,12], "FP1"."FP1_ERbbFFNUNG"[DATE,7],                                           
       "FP1"."FP1_ERLEDIGUNG"[DATE,7], "FP1"."FP1_AVISIERUNG"[DATE,7], "FP1"."FP1_FAELLIGKEIT"[DATE,7],              
       "FP1"."FP1_KUNDE"[VARbbbbAR2,100], "FP1"."FP1_ORT"[VARbbbbAR2,50], "FP1"."FP1_RATING"[VARbbbbAR2,10],               
       "FP1"."FP1_CKKUR"[VARbbbbAR2,2], "FP1"."FP1_ANZAHL_KOMMENTARE"[NUMBER,22],                                      
       "FP1"."FP1_LETZTER_KOMMENTAR"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_D"[VARbbbbAR2,2000],                   
       "FP1"."FP1_AUFTRAGSDETAILS_F"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_I"[VARbbbbAR2,2000],                   
       "FP1"."FP1_AUFTRAGSDETAILS_E"[VARbbbbAR2,2000], "FP1"."FP1_ZUSATZINFO1"[VARbbbbAR2,4000],                         
       "FP1"."FP1_ZUSATZINFO2"[VARbbbbAR2,4000], "FP1"."FP1_ZUSATZINFO3"[VARbbbbAR2,4000],                               
       "FP1"."FP1_ZUSATZINFO4"[VARbbbbAR2,4000], "FP1_SMCV_KEY"[NUMBER,22],                                            
       "FP1"."FP1_CEO_ERLEDIGER_bb"[VARbbbbAR2,5], "FP1"."FP1_CPA_TYP_ID"[NUMBER,22],                                  
       "FP1"."FP1_CES_STUFE_ID"[NUMBER,22], "FP1"."FP1_CKS_STATUS_ID"[NUMBER,22],                                    
       "FP1"."FP1_DPST_STATUS_CD"[VARbbbbAR2,3], "FP1"."FP1_KUNDE_PIbbbb_DEF"[VARbbbbAR2,16],                              
       "FP1"."FP1_KUNDE_SbbbbUTZ_CD"[VARbbbbAR2,2], "FP1"."FP1_KUNDE_BAP_FLAG"[VARbbbbAR2,1],                              
       "FP1"."FP1_KUNDE_BC_STAMM"[VARbbbbAR2,13], "FP1"."FP1_KUBE_GPN"[VARbbbbAR2,8],                                    
       "FP1"."FP1_CO_GPN"[VARbbbbAR2,8], "FP1"."FP1_ERSTELLER_GPN"[VARbbbbAR2,8],                                        
       "FP1"."FP1_ZUSATZINFO5"[VARbbbbAR2,4000], "FP1"."FP1_ZUSATZINFO6"[VARbbbbAR2,4000],                               
       "FP1"."FP1_ZUSATZINFO7"[VARbbbbAR2,4000], "FP1"."FP1_ZUSATZINFO8"[VARbbbbAR2,4000],                               
       "FP1"."FP1_INITIAL_CREATION_DATE"[DATE,7], "FP1"."FP1_FOLLOWUP_COUNTER"[NUMBER,22],                           
       "FP1_DKG_GRUPPEN_ID"[NUMBER,22], "FP1_DPK_KATEGORIE_ID"[NUMBER,22]                                            
                                                                                                                     
   2 - "FP1"."FP1_ID_PENDING_ITEM"[NUMBER,22], "FP1"."FP1_PROCESS_STATE"[NUMBER,22],                                 
       "FP1"."FP1_ITEM_STATE"[NUMBER,22], "FP1"."FP1_ASSIGN_STATE"[NUMBER,22],                                       
       "FP1"."FP1_NAME_PENDING_ITEM_D"[VARbbbbAR2,100], "FP1"."FP1_NAME_PENDING_ITEM_F"[VARbbbbAR2,100],                 
       "FP1"."FP1_NAME_PENDING_ITEM_I"[VARbbbbAR2,100], "FP1"."FP1_NAME_PENDING_ITEM_E"[VARbbbbAR2,100],                 
       "FP1"."FP1_KUBE_bb"[VARbbbbAR2,4], "FP1"."FP1_KUBE_MKZ"[VARbbbbAR2,3], "FP1"."FP1_CO_bb"[VARbbbbAR2,4],             
       "FP1"."FP1_CO_MKZ"[VARbbbbAR2,3], "FP1"."FP1_ERLEDIGER_bb"[VARbbbbAR2,4],                                         
       "FP1"."FP1_ERLEDIGER_MKZ"[VARbbbbAR2,3], "FP1"."FP1_ERLEDIGER_GPN"[VARbbbbAR2,8],                                 
       "FP1"."FP1_ERSTELLER_bb"[VARbbbbAR2,4], "FP1"."FP1_ERSTELLER_MKZ"[VARbbbbAR2,3],                                  
       "FP1"."FP1_ESKALATOR_LETZTER"[VARbbbbAR2,12], "FP1"."FP1_ESKALATIONSSTUFE"[NUMBER,22],                          
       "FP1"."FP1_KUNDE_BCNR"[VARbbbbAR2,4], "FP1"."FP1_KUNDE_STAMMNR"[VARbbbbAR2,8],                                    
       "FP1"."FP1_PS_GPID"[VARbbbbAR2,12], "FP1"."FP1_PS_GR_GPID"[VARbbbbAR2,12],                                        
       "FP1"."FP1_PS_LE_GPID"[VARbbbbAR2,12], "FP1"."FP1_PS_B_GPID"[VARbbbbAR2,12],                                      
       "FP1"."FP1_ERbbFFNUNG"[DATE,7], "FP1"."FP1_ERLEDIGUNG"[DATE,7], "FP1"."FP1_AVISIERUNG"[DATE,7],               
       "FP1"."FP1_FAELLIGKEIT"[DATE,7], "FP1"."FP1_KUNDE"[VARbbbbAR2,100], "FP1"."FP1_ORT"[VARbbbbAR2,50],               
       "FP1"."FP1_RATING"[VARbbbbAR2,10], "FP1"."FP1_CKKUR"[VARbbbbAR2,2],                                               
       "FP1"."FP1_ANZAHL_KOMMENTARE"[NUMBER,22], "FP1"."FP1_LETZTER_KOMMENTAR"[VARbbbbAR2,2000],                       
       "FP1"."FP1_AUFTRAGSDETAILS_D"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_F"[VARbbbbAR2,2000],                   
       "FP1"."FP1_AUFTRAGSDETAILS_I"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_E"[VARbbbbAR2,2000],                   
       "FP1"."FP1_ZUSATZINFO1"[VARbbbbAR2,4000], "FP1"."FP1_ZUSATZINFO2"[VARbbbbAR2,4000],                               
       "FP1"."FP1_ZUSATZINFO3"[VARbbbbAR2,4000], "FP1"."FP1_ZUSATZINFO4"[VARbbbbAR2,4000],                               
       "FP1_SMCV_KEY"[NUMBER,22], "FP1"."FP1_CEO_ERLEDIGER_bb"[VARbbbbAR2,5],                                          
       "FP1"."FP1_CPA_TYP_ID"[NUMBER,22], "FP1"."FP1_CES_STUFE_ID"[NUMBER,22],                                       
       "FP1"."FP1_CKS_STATUS_ID"[NUMBER,22], "FP1"."FP1_DPST_STATUS_CD"[VARbbbbAR2,3],                                 
       "FP1"."FP1_KUNDE_PIbbbb_DEF"[VARbbbbAR2,16], "FP1"."FP1_KUNDE_SbbbbUTZ_CD"[VARbbbbAR2,2],                             
       "FP1"."FP1_KUNDE_BAP_FLAG"[VARbbbbAR2,1], "FP1"."FP1_KUNDE_BC_STAMM"[VARbbbbAR2,13],                              
       "FP1"."FP1_KUBE_GPN"[VARbbbbAR2,8], "FP1"."FP1_CO_GPN"[VARbbbbAR2,8],                                             
       "FP1"."FP1_ERSTELLER_GPN"[VARbbbbAR2,8], "FP1"."FP1_ZUSATZINFO5"[VARbbbbAR2,4000],                                
       "FP1"."FP1_ZUSATZINFO6"[VARbbbbAR2,4000], "FP1"."FP1_ZUSATZINFO7"[VARbbbbAR2,4000],                               
       "FP1"."FP1_ZUSATZINFO8"[VARbbbbAR2,4000], "FP1"."FP1_INITIAL_CREATION_DATE"[DATE,7],                            
       "FP1"."FP1_FOLLOWUP_COUNTER"[NUMBER,22], "FP1_DKG_GRUPPEN_ID"[NUMBER,22],                                     
       "FP1_DPK_KATEGORIE_ID"[NUMBER,22]                                                                             
                                                                                                                     
   3 - "AFMO"."AFMO_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4],                                            
       "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8], "AFMO"."AFMO_CREATED"[DATE,7],                                             
       "AFMO"."AFMO_AUT_TYP"[NUMBER,22], "SMCV"."DKG_GRUPPEN_ID"[NUMBER,22],                                         
       "SMCV"."DKG_TEXT_D"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_F"[VARbbbbAR2,250],                                         
       "SMCV"."DKG_TEXT_I"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_E"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_KATEGORIE_ID"[NUMBER,22], "SMCV"."DPK_TEXT_D"[VARbbbbAR2,250],                                      
       "SMCV"."DPK_TEXT_F"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_I"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_TEXT_E"[VARbbbbAR2,250], "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22],                              
       "SMCV"."REF_TEXT_D"[VARbbbbAR2,300], "SMCV"."REF_TEXT_F"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_I"[VARbbbbAR2,300], "SMCV"."REF_TEXT_E"[VARbbbbAR2,300],                                         
       "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3], "DPST"."DPST_TEXT_D"[VARbbbbAR2,50],                                       
       "DPST"."DPST_TEXT_F"[VARbbbbAR2,50], "DPST"."DPST_TEXT_I"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "DPST"."DPST_CREATED"[DATE,7], "AUbb_bb"[VARbbbbAR2,4],                      
       "AUbb"."AUbb_CREATED"[DATE,7], "USERID"[VARbbbbAR2,20], "SbbbbUTZCODE"[VARbbbbAR2,2],                               
       "OSUSER"[VARbbbbAR2,20], "FP1".ROWID[ROWID,10]                                                                  
                                                                                                                     
   4 - "AFMO"."AFMO_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4],                                            
       "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8], "AFMO"."AFMO_CREATED"[DATE,7],                                             
       "AFMO"."AFMO_AUT_TYP"[NUMBER,22], "SMCV"."DKG_GRUPPEN_ID"[NUMBER,22],                                         
       "SMCV"."DKG_TEXT_D"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_F"[VARbbbbAR2,250],                                         
       "SMCV"."DKG_TEXT_I"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_E"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_KATEGORIE_ID"[NUMBER,22], "SMCV"."DPK_TEXT_D"[VARbbbbAR2,250],                                      
       "SMCV"."DPK_TEXT_F"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_I"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_TEXT_E"[VARbbbbAR2,250], "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22],                              
       "SMCV"."REF_TEXT_D"[VARbbbbAR2,300], "SMCV"."REF_TEXT_F"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_I"[VARbbbbAR2,300], "SMCV"."REF_TEXT_E"[VARbbbbAR2,300],                                         
       "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3], "DPST"."DPST_TEXT_D"[VARbbbbAR2,50],                                       
       "DPST"."DPST_TEXT_F"[VARbbbbAR2,50], "DPST"."DPST_TEXT_I"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "DPST"."DPST_CREATED"[DATE,7], "AUbb_bb"[VARbbbbAR2,4],                      
       "AUbb"."AUbb_CREATED"[DATE,7], "USERID"[VARbbbbAR2,20], "SbbbbUTZCODE"[VARbbbbAR2,2],                               
       "OSUSER"[VARbbbbAR2,20]                                                                                         
                                                                                                                     
   5 - "AFMO"."AFMO_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4],                                            
       "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8], "AFMO"."AFMO_CREATED"[DATE,7],                                             
       "AFMO"."AFMO_AUT_TYP"[NUMBER,22], "SMCV"."DKG_GRUPPEN_ID"[NUMBER,22],                                         
       "SMCV"."DKG_TEXT_D"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_F"[VARbbbbAR2,250],                                         
       "SMCV"."DKG_TEXT_I"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_E"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_KATEGORIE_ID"[NUMBER,22], "SMCV"."DPK_TEXT_D"[VARbbbbAR2,250],                                      
       "SMCV"."DPK_TEXT_F"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_I"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_TEXT_E"[VARbbbbAR2,250], "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22],                              
       "SMCV"."REF_TEXT_D"[VARbbbbAR2,300], "SMCV"."REF_TEXT_F"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_I"[VARbbbbAR2,300], "SMCV"."REF_TEXT_E"[VARbbbbAR2,300],                                         
       "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3], "DPST"."DPST_TEXT_D"[VARbbbbAR2,50],                                       
       "DPST"."DPST_TEXT_F"[VARbbbbAR2,50], "DPST"."DPST_TEXT_I"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "DPST"."DPST_CREATED"[DATE,7], "AUbb_bb"[VARbbbbAR2,4],                      
       "AUbb"."AUbb_CREATED"[DATE,7]                                                                                 
                                                                                                                     
   6 - "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8], "AFMO"."AFMO_bb"[VARbbbbAR2,4],                                              
       "AFMO"."AFMO_AUT_TYP"[NUMBER,22], "AFMO"."AFMO_CREATED"[DATE,7]                                               
                                                                                                                     
   7 - "SMCV"."DKG_GRUPPEN_ID"[NUMBER,22], "SMCV"."DKG_TEXT_D"[VARbbbbAR2,250],                                        
       "SMCV"."DKG_TEXT_F"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_I"[VARbbbbAR2,250],                                         
       "SMCV"."DKG_TEXT_E"[VARbbbbAR2,250], "SMCV"."DPK_KATEGORIE_ID"[NUMBER,22],                                      
       "SMCV"."DPK_TEXT_D"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_F"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_TEXT_I"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_E"[VARbbbbAR2,250],                                         
       "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22], "SMCV"."REF_TEXT_D"[VARbbbbAR2,300],                              
       "SMCV"."REF_TEXT_F"[VARbbbbAR2,300], "SMCV"."REF_TEXT_I"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_E"[VARbbbbAR2,300], "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3],                                       
       "DPST"."DPST_TEXT_D"[VARbbbbAR2,50], "DPST"."DPST_TEXT_F"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_I"[VARbbbbAR2,50], "DPST"."DPST_TEXT_E"[VARbbbbAR2,50],                                         
       "DPST"."DPST_CREATED"[DATE,7], "AUbb_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4],                    
       "AUbb"."AUbb_CREATED"[DATE,7]                                                                                 
                                                                                                                     
   8 - "SMCV"."DKG_GRUPPEN_ID"[NUMBER,22], "SMCV"."DKG_TEXT_D"[VARbbbbAR2,250],                                        
       "SMCV"."DKG_TEXT_F"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_I"[VARbbbbAR2,250],                                         
       "SMCV"."DKG_TEXT_E"[VARbbbbAR2,250], "SMCV"."DPK_KATEGORIE_ID"[NUMBER,22],                                      
       "SMCV"."DPK_TEXT_D"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_F"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_TEXT_I"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_E"[VARbbbbAR2,250],                                         
       "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22], "SMCV"."REF_TEXT_D"[VARbbbbAR2,300],                              
       "SMCV"."REF_TEXT_F"[VARbbbbAR2,300], "SMCV"."REF_TEXT_I"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_E"[VARbbbbAR2,300], "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3],                                       
       "DPST"."DPST_TEXT_D"[VARbbbbAR2,50], "DPST"."DPST_TEXT_F"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_I"[VARbbbbAR2,50], "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "DPST"."DPST_CREATED"[DATE,7]           
                                                                                                                     
   9 - "SMCV"."DKG_GRUPPEN_ID"[NUMBER,22], "SMCV"."DKG_TEXT_D"[VARbbbbAR2,250],                                        
       "SMCV"."DKG_TEXT_F"[VARbbbbAR2,250], "SMCV"."DKG_TEXT_I"[VARbbbbAR2,250],                                         
       "SMCV"."DKG_TEXT_E"[VARbbbbAR2,250], "SMCV"."DPK_KATEGORIE_ID"[NUMBER,22],                                      
       "SMCV"."DPK_TEXT_D"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_F"[VARbbbbAR2,250],                                         
       "SMCV"."DPK_TEXT_I"[VARbbbbAR2,250], "SMCV"."DPK_TEXT_E"[VARbbbbAR2,250],                                         
       "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22], "SMCV"."REF_TEXT_D"[VARbbbbAR2,300],                              
       "SMCV"."REF_TEXT_F"[VARbbbbAR2,300], "SMCV"."REF_TEXT_I"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_E"[VARbbbbAR2,300]                                                                             
                                                                                                                     
  10 - "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3], "DPST"."DPST_TEXT_D"[VARbbbbAR2,50],                                       
       "DPST"."DPST_TEXT_F"[VARbbbbAR2,50], "DPST"."DPST_TEXT_I"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "DPST"."DPST_CREATED"[DATE,7]                                              
                                                                                                                     
  11 - "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3], "DPST"."DPST_TEXT_D"[VARbbbbAR2,50],                                       
       "DPST"."DPST_TEXT_F"[VARbbbbAR2,50], "DPST"."DPST_TEXT_I"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "DPST"."DPST_CREATED"[DATE,7]                                              
                                                                                                                     
  12 - "AUbb_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4], "AUbb"."AUbb_CREATED"[DATE,7]                     
                                                                                                                     
  13 - "AUbb_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4], "AUbb"."AUbb_CREATED"[DATE,7]                     
                                                                                                                     
  14 - "USERID"[VARbbbbAR2,20], "SbbbbUTZCODE"[VARbbbbAR2,2], "OSUSER"[VARbbbbAR2,20]                                        
                                                                                                                     
  15 - "USERID"[VARbbbbAR2,20], "SbbbbUTZCODE"[VARbbbbAR2,2], "OSUSER"[VARbbbbAR2,20]                                        
                                                                                                                     
  16 - "FP1".ROWID[ROWID,10]                                                                                         
                                                                                                                     
  17 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]                                                 
                                                                                                                     
  18 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  19 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  20 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  21 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  22 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  23 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                      




The below is the output for select <column list>

PLAN_TABLE_OUTPUT                                                                                              
--------------------------------------------------------------------------------------------------------------       
SQL_ID  8zxtt9xa46vz7, bbbbild number 0                                                                                
-------------------------------------                                                                                
SELECT /*+ ordered INDEX_COMBINE(fp1 FP1_DPST_FK_I FP1_SMCV_FK_I FP1_KUBE_GPN_I FP1_KUBE_bb_I                        
            FP1_ERbbFFNUNG_I FP1_ERLEDIGUNG_I FP1_AVISIERUNG_I FP1_FAELLIGKEIT_I                                     
FP1_KUNDE_SbbbbUTZ_CD_I FP1_DKG_GRUPPEN_ID_I FP1_DPK_KATEGORIE_ID_I)*/ afmo.AFMO_TNUMMER                               
                   --Selektionskriterien:                        ,        'PReKUBEbb',                               
aubb.AUbb_bb,        aubb.AUbb_UGEORD_bb,        fp1.FP1_KUBE_MKZ,        fp1.FP1_KUBE_GPN                           
                                   --01.06.09                        ,                                               
fp1.FP1_DPST_STATUS_CD                                   --16.04.08 +++                                              
                                    --15.04.11 +++        --,smcv.DKG_GRUPPEN_ID                                     
--,smcv.DPK_KATEGORIE_ID        ,        fp1.FP1_DKG_GRUPPEN_ID,        fp1.FP1_DPK_KATEGORIE_ID                     
                             --15.04.11 ---                                                                          
        --16.04.08 ---        ,        fp1                                                                           
                                                                                                                     
Plan hash value: 3223555614                                                                                          
                                                                                                                     
------------------------------------------------------------------------------------------------------------         
| Id  | Operation                       | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |         
------------------------------------------------------------------------------------------------------------         
|   0 | SELECT STATEMENT                |                          |       |       | 23553 (100)|          |         
|*  1 |  FILTER                         |                          |       |       |            |          |         
|*  2 |   HASH JOIN                     |                          |     1 |  1131 | 23553  (16)| 00:03:14 |         
|*  3 |    TABLE ACCESS BY INDEX ROWID  | F_P_2          |     1 |   871 |   193   (0)| 00:00:02 |         
|   4 |     BITMAP CONVERSION TO ROWIDS |                          |       |       |            |          |         
|   5 |      BITMAP AND                 |                          |       |       |            |          |         
|*  6 |       BITMAP INDEX SINGLE VALUE | FP1_DPK_KATEGORIE_ID_I   |       |       |            |          |         
|*  7 |       BITMAP INDEX SINGLE VALUE | FP1_DKG_GRUPPEN_ID_I     |       |       |            |          |         
|   8 |       BITMAP OR                 |                          |       |       |            |          |         
|*  9 |        BITMAP INDEX SINGLE VALUE| FP1_DPST_FK_I            |       |       |            |          |         
|* 10 |        BITMAP INDEX SINGLE VALUE| FP1_DPST_FK_I            |       |       |            |          |         
|* 11 |        BITMAP INDEX SINGLE VALUE| FP1_DPST_FK_I            |       |       |            |          |         
|  12 |       BITMAP OR                 |                          |       |       |            |          |         
|* 13 |        BITMAP INDEX SINGLE VALUE| FP1_SMCV_FK_I            |       |       |            |          |         
|* 14 |        BITMAP INDEX SINGLE VALUE| FP1_SMCV_FK_I            |       |       |            |          |         
|* 15 |        BITMAP INDEX SINGLE VALUE| FP1_SMCV_FK_I            |       |       |            |          |         
|* 16 |        BITMAP INDEX SINGLE VALUE| FP1_SMCV_FK_I            |       |       |            |          |         
|  17 |    MERGE JOIN CARTESIAN         |                          |  7714K|  1912M| 23208  (15)| 00:03:11 |         
|* 18 |     HASH JOIN                   |                          |  5372 |  1301K|    76   (3)| 00:00:01 |         
|* 19 |      INDEX RANGE SCAN           | AFMO_PK                  | 22551 |   308K|    46   (0)| 00:00:01 |         
|  20 |      MERGE JOIN CARTESIAN       |                          |  4444 |  1015K|    28   (0)| 00:00:01 |         
|  21 |       MERGE JOIN CARTESIAN      |                          |     6 |  1344 |     4   (0)| 00:00:01 |         
|* 22 |        MAT_VIEW ACCESS FULL     | D_PEND_CR   |     4 |   668 |     2   (0)| 00:00:01 |         
|  23 |        BUFFER SORT              |                          |     1 |    57 |     2   (0)| 00:00:01 |         
|* 24 |         TABLE ACCESS FULL       | D_PEND_TEXTE |     1 |    57 |     0   (0)|          |         
|  25 |       BUFFER SORT               |                          |   741 |  7410 |    27   (0)| 00:00:01 |         
|* 26 |        INDEX RANGE SCAN         | AUbb_PK                  |   741 |  7410 |     4   (0)| 00:00:01 |         
|  27 |     BUFFER SORT                 |                          |  1436 | 17232 | 23161  (15)| 00:03:11 |         
|* 28 |      INDEX FAST FULL SCAN       | PK_AUT_T_USER_SbbbbUTZCODE |  1436 | 17232 |     4  (25)| 00:00:01 |         
------------------------------------------------------------------------------------------------------------         
                                                                                                                     
Query Block Name / Object Alias (identified by operation id):                                                        
-------------------------------------------------------------                                                        
                                                                                                                     
   1 - SEL$5C160134                                                                                                  
   3 - SEL$5C160134 / FP1@SEL$1                                                                                      
  19 - SEL$5C160134 / AFMO@SEL$1                                                                                     
  22 - SEL$5C160134 / SMCV@SEL$1                                                                                     
  24 - SEL$5C160134 / DPST@SEL$1                                                                                     
  26 - SEL$5C160134 / AUbb@SEL$1                                                                                     
  28 - SEL$5C160134 / AUT_T_USER_SbbbbUTZCODE_SLS@SEL$3                                                                
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   1 - filter(NVL(SYS_CONTEXT('gensales','rqcountrycd'),'bbbb')='bbbb')                                                  
   2 - access("FP1"."FP1_KUBE_bb"="AUbb"."AUbb_UGEORD_bb" AND                                                        
              "FP1"."FP1_KUNDE_SbbbbUTZ_CD"="SbbbbUTZCODE" AND "DPST"."DPST_STATUS_CD"="FP1"."FP1_DPST_STATUS_CD"        
              "SMCV"."REF_ID_CATEGORY_VALUE_ID"="FP1"."FP1_SMCV_KEY")                                                
   3 - filter(("FP1"."FP1_DPST_STATUS_CD" AND "FP1_SMCV_KEY"))                                                       
   6 - access("FP1_DPK_KATEGORIE_ID"=3020)                                                                           
   7 - access("FP1_DKG_GRUPPEN_ID"=3004)                                                                             
   9 - access("FP1"."FP1_DPST_STATUS_CD"='PEN')                                                                      
  10 - access("FP1"."FP1_DPST_STATUS_CD"='VIS')                                                                      
  11 - access("FP1"."FP1_DPST_STATUS_CD"='ZUR')                                                                      
  13 - access("FP1_SMCV_KEY"=1368487)                                                                                
  14 - access("FP1_SMCV_KEY"=1368488)                                                                                
  15 - access("FP1_SMCV_KEY"=2885624)                                                                                
  16 - access("FP1_SMCV_KEY"=2885625)                                                                                
  18 - access("AUbb"."AUbb_UGEORD_bb"="AFMO"."AFMO_bb")                                                              
  19 - access("AFMO"."AFMO_TNUMMER"='t434250')                                                                       
  22 - filter(("SMCV"."REF_ID_CATEGORY_VALUE_ID"=1368487 OR                                                          
              "SMCV"."REF_ID_CATEGORY_VALUE_ID"=1368488 OR "SMCV"."REF_ID_CATEGORY_VALUE_ID"=2885624 OR              
              "SMCV"."REF_ID_CATEGORY_VALUE_ID"=2885625))                                                            
  24 - filter(("DPST"."DPST_STATUS_CD"='PEN' OR "DPST"."DPST_STATUS_CD"='VIS' OR                                     
              "DPST"."DPST_STATUS_CD"='ZUR'))                                                                        
  26 - access("AUbb_bb"='A08G')                                                                                      
  28 - filter(LOWER("USERID")='t434250')                                                                             
                                                                                                                     
Column Projection Information (identified by operation id):                                                          
-----------------------------------------------------------                                                          
                                                                                                                     
   1 - "FP1"."FP1_KUBE_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4],                                         
       "FP1"."FP1_DPST_STATUS_CD"[VARbbbbAR2,3], "FP1"."FP1_SMCV_KEY"[NUMBER,22],                                      
       "FP1"."FP1_ID_PENDING_ITEM"[NUMBER,22], "FP1"."FP1_NAME_PENDING_ITEM_D"[VARbbbbAR2,100],                        
       "FP1"."FP1_NAME_PENDING_ITEM_F"[VARbbbbAR2,100], "FP1"."FP1_NAME_PENDING_ITEM_I"[VARbbbbAR2,100],                 
       "FP1"."FP1_NAME_PENDING_ITEM_E"[VARbbbbAR2,100], "FP1_DPK_KATEGORIE_ID"[NUMBER,22],                             
       "FP1"."FP1_KUBE_MKZ"[VARbbbbAR2,3], "FP1"."FP1_CO_bb"[VARbbbbAR2,4],                                              
       "FP1"."FP1_ERLEDIGER_bb"[VARbbbbAR2,4], "FP1"."FP1_ERLEDIGER_GPN"[VARbbbbAR2,8],                                  
       "FP1"."FP1_ERSTELLER_bb"[VARbbbbAR2,4], "FP1"."FP1_KUNDE_BCNR"[VARbbbbAR2,4],                                     
       "FP1"."FP1_KUNDE_STAMMNR"[VARbbbbAR2,8], "FP1_PS_GR_GPID"[VARbbbbAR2,12],                                         
       "FP1_PS_LE_GPID"[VARbbbbAR2,12], "FP1"."FP1_ERbbFFNUNG"[DATE,7], "FP1_ERLEDIGUNG"[DATE,7],                      
       "FP1_AVISIERUNG"[DATE,7], "FP1_FAELLIGKEIT"[DATE,7], "FP1_KUNDE"[VARbbbbAR2,100],                               
       "FP1_ORT"[VARbbbbAR2,50], "FP1_RATING"[VARbbbbAR2,10], "FP1_CKKUR"[VARbbbbAR2,2],                                   
       "FP1_ANZAHL_KOMMENTARE"[NUMBER,22], "FP1_LETZTER_KOMMENTAR"[VARbbbbAR2,2000],                                   
       "FP1"."FP1_AUFTRAGSDETAILS_D"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_F"[VARbbbbAR2,2000],                   
       "FP1"."FP1_AUFTRAGSDETAILS_I"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_E"[VARbbbbAR2,2000],                   
       "FP1_ZUSATZINFO1"[VARbbbbAR2,4000], "FP1_ZUSATZINFO2"[VARbbbbAR2,4000],                                           
       "FP1_ZUSATZINFO3"[VARbbbbAR2,4000], "FP1_INITIAL_CREATION_DATE"[DATE,7],                                        
       "FP1_FOLLOWUP_COUNTER"[NUMBER,22], "FP1_DKG_GRUPPEN_ID"[NUMBER,22],                                           
       "FP1"."FP1_KUBE_GPN"[VARbbbbAR2,8], "FP1"."FP1_CO_GPN"[VARbbbbAR2,8],                                             
       "FP1"."FP1_ERSTELLER_GPN"[VARbbbbAR2,8], "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8],                                     
       "SMCV"."REF_TEXT_D"[VARbbbbAR2,300], "SMCV"."REF_TEXT_F"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_I"[VARbbbbAR2,300], "SMCV"."REF_TEXT_E"[VARbbbbAR2,300], "AUbb_bb"[VARbbbbAR2,4],                  
       "DPST"."DPST_TEXT_D"[VARbbbbAR2,50], "DPST"."DPST_TEXT_F"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_I"[VARbbbbAR2,50], "DPST"."DPST_TEXT_E"[VARbbbbAR2,50]                                          
                                                                                                                     
   2 - "FP1"."FP1_KUBE_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4],                                         
       "FP1"."FP1_DPST_STATUS_CD"[VARbbbbAR2,3], "FP1"."FP1_SMCV_KEY"[NUMBER,22],                                      
       "FP1"."FP1_ID_PENDING_ITEM"[NUMBER,22], "FP1"."FP1_NAME_PENDING_ITEM_D"[VARbbbbAR2,100],                        
       "FP1"."FP1_NAME_PENDING_ITEM_F"[VARbbbbAR2,100], "FP1"."FP1_NAME_PENDING_ITEM_I"[VARbbbbAR2,100],                 
       "FP1"."FP1_NAME_PENDING_ITEM_E"[VARbbbbAR2,100], "FP1_DPK_KATEGORIE_ID"[NUMBER,22],                             
       "FP1"."FP1_KUBE_MKZ"[VARbbbbAR2,3], "FP1"."FP1_CO_bb"[VARbbbbAR2,4],                                              
       "FP1"."FP1_ERLEDIGER_bb"[VARbbbbAR2,4], "FP1"."FP1_ERLEDIGER_GPN"[VARbbbbAR2,8],                                  
       "FP1"."FP1_ERSTELLER_bb"[VARbbbbAR2,4], "FP1"."FP1_KUNDE_BCNR"[VARbbbbAR2,4],                                     
       "FP1"."FP1_KUNDE_STAMMNR"[VARbbbbAR2,8], "FP1_PS_GR_GPID"[VARbbbbAR2,12],                                         
       "FP1_PS_LE_GPID"[VARbbbbAR2,12], "FP1"."FP1_ERbbFFNUNG"[DATE,7], "FP1_ERLEDIGUNG"[DATE,7],                      
       "FP1_AVISIERUNG"[DATE,7], "FP1_FAELLIGKEIT"[DATE,7], "FP1_KUNDE"[VARbbbbAR2,100],                               
       "FP1_ORT"[VARbbbbAR2,50], "FP1_RATING"[VARbbbbAR2,10], "FP1_CKKUR"[VARbbbbAR2,2],                                   
       "FP1_ANZAHL_KOMMENTARE"[NUMBER,22], "FP1_LETZTER_KOMMENTAR"[VARbbbbAR2,2000],                                   
       "FP1"."FP1_AUFTRAGSDETAILS_D"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_F"[VARbbbbAR2,2000],                   
       "FP1"."FP1_AUFTRAGSDETAILS_I"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_E"[VARbbbbAR2,2000],                   
       "FP1_ZUSATZINFO1"[VARbbbbAR2,4000], "FP1_ZUSATZINFO2"[VARbbbbAR2,4000],                                           
       "FP1_ZUSATZINFO3"[VARbbbbAR2,4000], "FP1_INITIAL_CREATION_DATE"[DATE,7],                                        
       "FP1_FOLLOWUP_COUNTER"[NUMBER,22], "FP1_DKG_GRUPPEN_ID"[NUMBER,22],                                           
       "FP1"."FP1_KUBE_GPN"[VARbbbbAR2,8], "FP1"."FP1_CO_GPN"[VARbbbbAR2,8],                                             
       "FP1"."FP1_ERSTELLER_GPN"[VARbbbbAR2,8], "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8],                                     
       "SMCV"."REF_TEXT_D"[VARbbbbAR2,300], "SMCV"."REF_TEXT_F"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_I"[VARbbbbAR2,300], "SMCV"."REF_TEXT_E"[VARbbbbAR2,300], "AUbb_bb"[VARbbbbAR2,4],                  
       "DPST"."DPST_TEXT_D"[VARbbbbAR2,50], "DPST"."DPST_TEXT_F"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_I"[VARbbbbAR2,50], "DPST"."DPST_TEXT_E"[VARbbbbAR2,50]                                          
                                                                                                                     
   3 - "FP1"."FP1_ID_PENDING_ITEM"[NUMBER,22], "FP1"."FP1_NAME_PENDING_ITEM_D"[VARbbbbAR2,100],                        
       "FP1"."FP1_NAME_PENDING_ITEM_F"[VARbbbbAR2,100], "FP1"."FP1_NAME_PENDING_ITEM_I"[VARbbbbAR2,100],                 
       "FP1"."FP1_NAME_PENDING_ITEM_E"[VARbbbbAR2,100], "FP1"."FP1_KUBE_bb"[VARbbbbAR2,4],                               
       "FP1"."FP1_KUBE_MKZ"[VARbbbbAR2,3], "FP1"."FP1_CO_bb"[VARbbbbAR2,4],                                              
       "FP1"."FP1_ERLEDIGER_bb"[VARbbbbAR2,4], "FP1"."FP1_ERLEDIGER_GPN"[VARbbbbAR2,8],                                  
       "FP1"."FP1_ERSTELLER_bb"[VARbbbbAR2,4], "FP1"."FP1_KUNDE_BCNR"[VARbbbbAR2,4],                                     
       "FP1"."FP1_KUNDE_STAMMNR"[VARbbbbAR2,8], "FP1_PS_GR_GPID"[VARbbbbAR2,12],                                         
       "FP1_PS_LE_GPID"[VARbbbbAR2,12], "FP1"."FP1_ERbbFFNUNG"[DATE,7], "FP1_ERLEDIGUNG"[DATE,7],                      
       "FP1_AVISIERUNG"[DATE,7], "FP1_FAELLIGKEIT"[DATE,7], "FP1_KUNDE"[VARbbbbAR2,100],                               
       "FP1_ORT"[VARbbbbAR2,50], "FP1_RATING"[VARbbbbAR2,10], "FP1_CKKUR"[VARbbbbAR2,2],                                   
       "FP1_ANZAHL_KOMMENTARE"[NUMBER,22], "FP1_LETZTER_KOMMENTAR"[VARbbbbAR2,2000],                                   
       "FP1"."FP1_AUFTRAGSDETAILS_D"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_F"[VARbbbbAR2,2000],                   
       "FP1"."FP1_AUFTRAGSDETAILS_I"[VARbbbbAR2,2000], "FP1"."FP1_AUFTRAGSDETAILS_E"[VARbbbbAR2,2000],                   
       "FP1_ZUSATZINFO1"[VARbbbbAR2,4000], "FP1_ZUSATZINFO2"[VARbbbbAR2,4000],                                           
       "FP1_ZUSATZINFO3"[VARbbbbAR2,4000], "FP1_SMCV_KEY"[NUMBER,22],                                                  
       "FP1"."FP1_DPST_STATUS_CD"[VARbbbbAR2,3], "FP1"."FP1_KUNDE_SbbbbUTZ_CD"[VARbbbbAR2,2],                              
       "FP1"."FP1_KUBE_GPN"[VARbbbbAR2,8], "FP1"."FP1_CO_GPN"[VARbbbbAR2,8],                                             
       "FP1"."FP1_ERSTELLER_GPN"[VARbbbbAR2,8], "FP1_INITIAL_CREATION_DATE"[DATE,7],                                   
       "FP1_FOLLOWUP_COUNTER"[NUMBER,22], "FP1_DKG_GRUPPEN_ID"[NUMBER,22],                                           
       "FP1_DPK_KATEGORIE_ID"[NUMBER,22]                                                                             
                                                                                                                     
   4 - "FP1".ROWID[ROWID,10]                                                                                         
                                                                                                                     
   5 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]                                                 
                                                                                                                     
   6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
   7 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
   8 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]                                                 
                                                                                                                     
   9 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  10 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  11 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  12 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]                                                 
                                                                                                                     
  13 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  14 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  15 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  16 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 16112]                                                 
                                                                                                                     
  17 - "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4], "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8],                                       
       "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22], "SMCV"."REF_TEXT_D"[VARbbbbAR2,300],                              
       "SMCV"."REF_TEXT_F"[VARbbbbAR2,300], "SMCV"."REF_TEXT_I"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_E"[VARbbbbAR2,300], "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3],                                       
       "DPST"."DPST_TEXT_D"[VARbbbbAR2,50], "DPST"."DPST_TEXT_F"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_I"[VARbbbbAR2,50], "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "AUbb_bb"[VARbbbbAR2,4],                  
       "SbbbbUTZCODE"[VARbbbbAR2,2]                                                                                      
                                                                                                                     
  18 - "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4], "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8],                                       
       "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22], "SMCV"."REF_TEXT_D"[VARbbbbAR2,300],                              
       "SMCV"."REF_TEXT_F"[VARbbbbAR2,300], "SMCV"."REF_TEXT_I"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_E"[VARbbbbAR2,300], "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3],                                       
       "DPST"."DPST_TEXT_D"[VARbbbbAR2,50], "DPST"."DPST_TEXT_F"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_I"[VARbbbbAR2,50], "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "AUbb_bb"[VARbbbbAR2,4]                   
                                                                                                                     
  19 - "AFMO"."AFMO_TNUMMER"[VARbbbbAR2,8], "AFMO"."AFMO_bb"[VARbbbbAR2,4]                                               
                                                                                                                     
  20 - "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22], "SMCV"."REF_TEXT_D"[VARbbbbAR2,300],                              
       "SMCV"."REF_TEXT_F"[VARbbbbAR2,300], "SMCV"."REF_TEXT_I"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_E"[VARbbbbAR2,300], "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3],                                       
       "DPST"."DPST_TEXT_D"[VARbbbbAR2,50], "DPST"."DPST_TEXT_F"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_I"[VARbbbbAR2,50], "DPST"."DPST_TEXT_E"[VARbbbbAR2,50], "AUbb_bb"[VARbbbbAR2,4],                  
       "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4]                                                                           
                                                                                                                     
  21 - "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22], "SMCV"."REF_TEXT_D"[VARbbbbAR2,300],                              
       "SMCV"."REF_TEXT_F"[VARbbbbAR2,300], "SMCV"."REF_TEXT_I"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_E"[VARbbbbAR2,300], "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3],                                       
       "DPST"."DPST_TEXT_D"[VARbbbbAR2,50], "DPST"."DPST_TEXT_F"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_I"[VARbbbbAR2,50], "DPST"."DPST_TEXT_E"[VARbbbbAR2,50]                                          
                                                                                                                     
  22 - "SMCV"."REF_ID_CATEGORY_VALUE_ID"[NUMBER,22], "SMCV"."REF_TEXT_D"[VARbbbbAR2,300],                              
       "SMCV"."REF_TEXT_F"[VARbbbbAR2,300], "SMCV"."REF_TEXT_I"[VARbbbbAR2,300],                                         
       "SMCV"."REF_TEXT_E"[VARbbbbAR2,300]                                                                             
                                                                                                                     
  23 - "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3], "DPST"."DPST_TEXT_D"[VARbbbbAR2,50],                                       
       "DPST"."DPST_TEXT_F"[VARbbbbAR2,50], "DPST"."DPST_TEXT_I"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_E"[VARbbbbAR2,50]                                                                             
                                                                                                                     
  24 - "DPST"."DPST_STATUS_CD"[VARbbbbAR2,3], "DPST"."DPST_TEXT_D"[VARbbbbAR2,50],                                       
       "DPST"."DPST_TEXT_F"[VARbbbbAR2,50], "DPST"."DPST_TEXT_I"[VARbbbbAR2,50],                                         
       "DPST"."DPST_TEXT_E"[VARbbbbAR2,50]                                                                             
                                                                                                                     
  25 - "AUbb_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4]                                                    
                                                                                                                     
  26 - "AUbb_bb"[VARbbbbAR2,4], "AUbb"."AUbb_UGEORD_bb"[VARbbbbAR2,4]                                                    
                                                                                                                     
  27 - "SbbbbUTZCODE"[VARbbbbAR2,2]                                                                                      
                                                                                                                     
  28 - "SbbbbUTZCODE"[VARbbbbAR2,2]                                                                                      
                                  



Regards,
Pointers

[Updated on: Wed, 20 March 2013 11:48]

Report message to a moderator

Re: select column list vs select * [message #580130 is a reply to message #580128] Wed, 20 March 2013 11:49 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

Please note I have replaced few literal values manually which is varchar is displayed as varbbbbar2

Regards,
Pointers
Re: select column list vs select * [message #580140 is a reply to message #580130] Wed, 20 March 2013 13:34 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
1. try please with
select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ALL RUNSTATS_LAST'));


2. upload your exact sql texts with the execution plans

3. your sqls, that you upload originally, don't have any binds. What binds do you mean?

4. if there are some binds in the sqls, you have to set they properly, for ex.
var b1 number
exec :b1:=1;


5. the uploaded execution plans are different. But you have to make your test once more and properly (s. above).
Re: select column list vs select * [message #580144 is a reply to message #580140] Wed, 20 March 2013 14:44 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Try please this, too

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED RUNSTATS_LAST'));


I'm not sure, that it works in 10.1.
Re: select column list vs select * [message #580145 is a reply to message #580123] Wed, 20 March 2013 14:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
pointers wrote on Wed, 20 March 2013 15:41

In general, the query finally gives 220 rows which was just retrived in 5 sec (with hints) when select * was used, but when the column list (it has decode, case, nvl and replace functions) it takes more than 10 min even all hints are removed.

So you're comparing a select that does all the above functions with one that doesn't?
Re: select column list vs select * [message #580201 is a reply to message #580145] Thu, 21 March 2013 05:28 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

@LNossov:
1. I am not sure if there is an option 'ADVANCED RUNSTATS_LAST', did you mean 'ALL RUNSTATS_LAST'
2. I know, bind variables improve performance and various other things--security etc, but is it really needed to analyze the explain plan as you asked me to replace the literals with bind variables and then get the explain plan.


@cookiemonster:
The details are -- The query (which was shown in the post) takes almost 10 min when select <column_list, expr<column_list>> is used, where as the same query takes only 5 sec when the column_list is replaced by select * while the predicates are same --only * is replaced by column_list(it has decode, case, replace functions as shown). So, I wanted to tune the later part.

Regards,
Pointers
Re: select column list vs select * [message #580204 is a reply to message #580201] Thu, 21 March 2013 05:52 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Quote:
@LNossov:
1. I am not sure if there is an option 'ADVANCED RUNSTATS_LAST', did you mean 'ALL RUNSTATS_LAST'
2. I know, bind variables improve performance and various other things--security etc, but is it really needed to analyze the explain plan as you asked me to replace the literals with bind variables and then get the explain plan.


1. try the both please. I don't have any 10.1 database, where I could check, if the argument ADVANCED works for 10.1.
2. I need the execution plan of your original sql (i.e. with binds). Is it a problem for you?
Re: select column list vs select * [message #580205 is a reply to message #580201] Thu, 21 March 2013 05:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Select "column list with functions" can't possibly return the same data as "select *".
Ignoring the column mismatch, the functions change the data, and they take time.
According to the last set of plans the column select should be faster. If it's not then my first assumption would be that the functions are making the difference.
Remove them, then time it.
Re: select column list vs select * [message #580229 is a reply to message #580205] Thu, 21 March 2013 11:01 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
@LNossov,cookiemonster:

I do see both the queries select * and select <colum_list > takes almost same time now, I did not do any changes (please see below for the analysis), I am not sure if the explain plan is needed.

Hi,

I have observed now the select * and select <column_list> almost taking the same time -- I dint change anything, it was as it is.
I have been working on this, my observation is that in the morning hours the query(select * or select <column_list>) takes around 6 to 10 min(it varies on some days) but in the afternoon it takes just secs (2 to 6 sec).

We have daily jobs that run in the night which lasts in the morning as well. I believe, there was memory deficit, but i dont know what memory settings (infact, any other settings) affecting this. I am not sure if it is hash_sort_area or some sort of this.

What i am thinking is to run the query in the morning and if it is slow wanted to monitor some parameters and also in the afternoon for the same.

But the question drilled down to what parameters. Could you please advice what settings/parameters that could affect this.

Thank you very much in advance.

Regards,
Pointers
Re: select column list vs select * [message #580392 is a reply to message #580229] Sat, 23 March 2013 18:28 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Quote:
I believe, there was memory deficit.


Is the execution plan the same? Could you please check this.
Re: select column list vs select * [message #580400 is a reply to message #580392] Sun, 24 March 2013 03:33 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

Yes the execution plan was same.

Could you share what parameters I might monitor.

Regards,
Pointers
Re: select column list vs select * [message #580405 is a reply to message #580400] Sun, 24 March 2013 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
faster=true

Regards
Michel
Re: select column list vs select * [message #580415 is a reply to message #580400] Sun, 24 March 2013 05:49 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
How did you check, that the execution plan was the same? You have to check this in AWR.

If it was really the same, I would compare the runtime statistics of this sql in AWR for the good and bad period of time (DISK_READS, BUFFER_GETS, EXECUTIONS, ROWS_PROCESSED, CONCURRENCY_WAIT_TIME, USER_IO_WAIT_TIME).
Re: select column list vs select * [message #580483 is a reply to message #580415] Mon, 25 March 2013 04:36 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
@LNossov:
Thank you very much for the kind reply.

I have generated the execution plan using explain plan for for both the queries and then compared.

I have never worked with AWR, but your point makes me to think something in new direction. I understand from your thought that the AWR shows these statistics for different timings but not the explain plan, the latter is only to see the exection as if it is executed now.

May be you could enlighten me more.

Thank you very much in advance.

Regards,
Pointers
Re: select column list vs select * [message #580491 is a reply to message #580483] Mon, 25 March 2013 07:31 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You can use for that the script ?/rdbms/admin/awrsqrpt.sql. Start it twice for the good and for the bad time.
Re: select column list vs select * [message #581180 is a reply to message #580491] Wed, 03 April 2013 00:19 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Can you post TKPROF for both queries?
Previous Topic: How to create index
Next Topic: Capacity Planning for 250 user OLTP shop
Goto Forum:
  


Current Time: Thu Mar 28 12:40:21 CDT 2024