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  |
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 #580123 is a reply to message #580121] |
Wed, 20 March 2013 10:41  |
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 #580128 is a reply to message #580120] |
Wed, 20 March 2013 11:38 |
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 #580145 is a reply to message #580123] |
Wed, 20 March 2013 14:46 |
cookiemonster
Messages: 13961
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 |
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 #580205 is a reply to message #580201] |
Thu, 21 March 2013 05:55 |
cookiemonster
Messages: 13961
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 |
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 #580483 is a reply to message #580415] |
Mon, 25 March 2013 04:36 |
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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 18 00:56:19 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
