- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- SELECT MAX(field) / SELECT MIN(field) return wrong...
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
SELECT MAX(field) / SELECT MIN(field) return wrong values
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 08-26-2008 9:56 AM
Hi,
I have the query:
Select
MAX(b1.time_s) as time_s
From
BUSINESS_INVOCATIONS b1
Where
b1.time_s >= 1219217034000
AND b1.rec_sess_id = 2197756621378027521
AND b1.comp_id IN ( Select id From J2EE_CONFIGURATION_BEAN Where j2eeApplicationsTableId = 378302371920347137 )This return the MAX value of the comp_id and not the MAX value of the b1.time_s
looks like there is a BUG in MAXDB when i have a join of two tables the MAX/MIN functions works on the wrong fields!
If i change the query to be:
Select
max(b1.time_s) as time_s
From
BUSINESS_INVOCATIONS b1,
J2EE_CONFIGURATION_BEAN jcb
Where
b1.time_s >= 1219217034000
AND b1.rec_sess_id = 2197756621378027521
AND jcb.j2eeApplicationsTableId = 378302371920347137
AND b1.comp_id = jcb.idThe MIN/MAX functions works good and return the right value
Pleasehelp me to find what is the problem and if any one already have a solution.
Thanks,
Yosef
- SAP Managed Tags:
- SAP MaxDB
Accepted Solutions (0)
Answers (10)
Answers (10)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Explain Select
MAX(b1.time_s) as time_s
From
BUSINESS_INVOCATIONS b1
Where
b1.time_s >= 1219217034000
AND b1.rec_sess_id = 2197756621378027521
AND b1.comp_id IN ( Select id From J2EE_CONFIGURATION_BEAN Where j2eeApplicationsTableId = 378302371920347137 )The explain shows 'NO STRATEGY NOW (ONLY AT EXECUTION TIME)'
How join works in MAXDB?
Does it replace the subquery with the value of ID?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
looks that the OPTIM_INV_ONLY NO solved the problem:)
since we want to update this paramer automaticly during the installation,
is there any SQL command to update this parameter
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
holger_becker
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi,
you get the execution plan by adding the keyword 'explain' to the command.
Example:
Explain Select
MAX(b1.time_s) as time_s
From
BUSINESS_INVOCATIONS b1
Where
b1.time_s >= 1219217034000
AND b1.rec_sess_id = 2197756621378027521
AND b1.comp_id IN ( Select id From J2EE_CONFIGURATION_BEAN Where j2eeApplicationsTableId = 378302371920347137 )
In your case it will be necessary to replace the subquery with the value of ID.
Otherwise the explain will only show something like 'NO STRATEGY NOW (ONLY AT EXECUTION TIME)'
because the explain doesn't execute anything and the result size of the subquery, which is important for the optimizer,
is only know at execution time.
You could change paramter values via
DBMGui: Configuration -> Parameters -> Extended -> OPTIM_INV_ONLY -> new value: NO
dbmcli: dbmcli -d <dbname> -u <user,pwd> param_directput OPTIM_INV_ONLY NO
In both cases you have to restart the db to activate the changes.
Kind regards
Holger
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
How can check/update the OPTIM_INV_ONLY to NO
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Ok, looks that i found the problem:
It is the index:
BI__RECSESS_COMP_TIME_TOPP_IDX ON "BUSINESS_INVOCATIONS" ("REC_SESS_ID", "COMP_ID", "TIME_S", "TOP_PARENT")How i know the Explain Or the excution plan of the query in MAXDB
Thanks,
Yosef.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
holger_becker
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi,
I suppose it is a problem with a special index optimization for set functions.
If my assumption is true you could bypass the problem by setting the database parameter OPTIM_INV_ONLY to NO.
As you are using a very old release I would suggest to upgrade to a newer release, 7.6 for example.
The problem should be solved there.
Kind regards
Holger
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Dta for BUSINESS_INVOCATIONS tabel:
ID,CLASS_TYPE,M_TIME,FUNCTIONENTRYID,TOP_PARENT,START_TIME,TIME_S,COMP_ID,REC_SESS_ID,PARENT,HTTP_SESSION,INVOCATION_HASH_CODE,MINOR_INVOCATION,TRANSACTION_ID,USER_TRACING_COOKIE,TRACINGTYPE
2089670230321135617,H,1219738865499,1387108688451338241,2089670230321135617,1219217053734,1219217053734,414331168939311111,2197756621378027521,(null),2179742222868545537,-1238173181,GET,null,null,-1
2089670230321135618,H,1219738865499,1387108688451338242,2089670230321135618,1219217071156,1219217071156,414331168939311108,2197756621378027521,(null),2179742222868545538,-628203986,GET,null,null,-1
2089670230321135619,H,1219738865499,1387108688451338279,2089670230321135619,1219217073531,1219217073531,414331168939311112,2197756621378027521,(null),2179742222868545538,545948335,GET,null,null,-1
2089670230321135620,H,1219738865499,1387108688451338284,2089670230321135620,1219217075671,1219217075671,414331168939311119,2197756621378027521,(null),2179742222868545538,1958406838,GET,null,null,-1
2089670230321135621,H,1219738865499,1387108688451338289,2089670230321135621,1219217077359,1219217077359,414331168939311106,2197756621378027521,(null),2179742222868545538,-2089005650,GET,null,null,-1
2089670230321135622,H,1219738865499,1387108688451338292,2089670230321135622,1219218442312,1219218442312,414331168939311115,2197756621378027521,(null),2179742222868545538,-1298180580,GET,null,null,-1
2089670230321135623,H,1219738865499,1387108688451338297,2089670230321135623,1219218467765,1219218467765,414331168939311107,2197756621378027521,(null),2179742222868545538,1318337921,GET,null,null,-1
2107684628830617601,E,1219738865515,1387108688451338244,2089670230321135618,1219217071171,1219217071171,324259176391901185,2197756621378027521,2089670230321135618,2179742222868545538,586868856,(null),(null),(null),-1
2107684628830617602,E,1219738865515,1387108688451338281,2089670230321135619,1219217073531,1219217073531,324259176391901185,2197756621378027521,2089670230321135619,2179742222868545538,1886325459,(null),(null),(null),-1
2107684628830617603,E,1219738865515,1387108688451338286,2089670230321135620,1219217075671,1219217075671,324259176391901185,2197756621378027521,2089670230321135620,2179742222868545538,-550916224,(null),(null),(null),-1
2107684628830617604,E,1219738865515,1387108688451338291,2089670230321135621,1219217077359,1219217077359,324259176391901185,2197756621378027521,2089670230321135621,2179742222868545538,1676270392,(null),(null),(null),-1
2107684628830617605,E,1219738865515,1387108688451338294,2089670230321135622,1219218442312,1219218442312,324259176391901185,2197756621378027521,2089670230321135622,2179742222868545538,-2074647744,(null),(null),(null),-1
2107684628830617606,E,1219738865515,1387108688451338299,2089670230321135623,1219218467765,1219218467765,324259176391901185,2197756621378027521,2089670230321135623,2179742222868545538,63795915,(null),(null),(null),-1
3278620531946946561,T,1219738865483,1387108688451338291,2089670230321135621,1219217077359,1219217077359,2828260569209896968,2197756621378027521,2107684628830617604,2179742222868545538,-1511714853,java.lang.ArithmeticException,(null),(null),18
Daa for J2EE_CONFIGURATION_BEAN Table:
select * from IDENTIFY.J2EE_CONFIGURATION_BEAN
ID,J2EEAPPLICATIONSTABLEID,J2EEAPPLICATIONID,J2EECOMPONENTID
180144094616485889,0,0,0
180144094616485890,0,0,0
180144094616485891,0,0,0
180144094616485892,0,0,0
180144094616485893,378302478220787713,0,0
180144094616485894,0,0,0
306244884182859777,378302478220787713,360288079711305731,306244884182859777
324259282692341761,378302478220787713,360288079711305731,306244884182859777
360288079711305729,378302478220787713,360288079711305729,0
360288079711305730,378302478220787713,360288079711305730,0
360288079711305731,378302478220787713,360288079711305731,0
360288079711305732,378302478220787713,360288079711305732,0
360288079711305733,378302478220787713,360288079711305733,0
360288079711305734,378302478220787713,360288079711305734,0
414331275239751681,378302478220787713,360288079711305731,594475260334571522
414331275239751682,378302478220787713,360288079711305731,594475260334571522
414331275239751683,378302478220787713,360288079711305731,594475260334571522
414331275239751684,378302478220787713,360288079711305731,594475260334571522
414331275239751685,378302478220787713,360288079711305731,594475260334571522
414331275239751686,378302478220787713,360288079711305731,594475260334571522
414331275239751687,378302478220787713,360288079711305731,594475260334571522
414331275239751688,378302478220787713,360288079711305731,594475260334571522
414331275239751689,378302478220787713,360288079711305731,594475260334571522
414331275239751690,378302478220787713,360288079711305731,594475260334571522
414331275239751691,378302478220787713,360288079711305731,594475260334571522
414331275239751692,378302478220787713,360288079711305731,594475260334571522
414331275239751693,378302478220787713,360288079711305731,594475260334571522
414331275239751694,378302478220787713,360288079711305731,594475260334571522
414331275239751695,378302478220787713,360288079711305731,594475260334571522
540432064806125569,378302478220787713,360288079711305732,594475260334571523
540432064806125570,378302478220787713,360288079711305732,594475260334571523
540432064806125571,378302478220787713,360288079711305732,594475260334571523
540432064806125572,378302478220787713,360288079711305732,594475260334571523
540432064806125573,378302478220787713,360288079711305732,594475260334571523
540432064806125574,378302478220787713,360288079711305732,594475260334571523
540432064806125575,378302478220787713,360288079711305734,594475260334571524
540432064806125576,378302478220787713,360288079711305734,594475260334571524
576460861825089537,378302478220787713,360288079711305730,576460861825089537
576460861825089538,378302478220787713,360288079711305731,576460861825089538
576460861825089539,378302478220787713,360288079711305732,576460861825089539
576460861825089540,378302478220787713,360288079711305733,576460861825089540
576460861825089541,378302478220787713,360288079711305734,576460861825089541
594475260334571521,378302478220787713,360288079711305729,594475260334571521
594475260334571522,378302478220787713,360288079711305731,594475260334571522
594475260334571523,378302478220787713,360288079711305732,594475260334571523
594475260334571524,378302478220787713,360288079711305734,594475260334571524
2810246277000855553,378302478220787713,360288079711305729,2810246277000855553
2810246277000855554,378302478220787713,360288079711305729,2810246277000855554
2810246277000855555,378302478220787713,360288079711305729,2810246277000855555
2810246277000855556,378302478220787713,360288079711305729,2810246277000855556
2810246277000855557,378302478220787713,360288079711305729,2810246277000855557
2810246277000855558,378302478220787713,360288079711305729,2810246277000855558
2810246277000855559,378302478220787713,360288079711305729,2810246277000855559
2828260675510337537,378302478220787713,360288079711305729,2846275074019819521
2828260675510337538,378302478220787713,360288079711305729,2846275074019819521
2828260675510337539,378302478220787713,360288079711305729,2846275074019819521
2828260675510337540,378302478220787713,360288079711305729,2846275074019819521
2828260675510337541,378302478220787713,360288079711305729,2846275074019819521
2828260675510337542,378302478220787713,360288079711305729,2846275074019819521
2828260675510337543,378302478220787713,360288079711305729,2846275074019819521
2828260675510337544,378302478220787713,360288079711305729,2846275074019819521
2828260675510337545,378302478220787713,360288079711305729,2846275074019819521
2828260675510337546,378302478220787713,360288079711305729,2846275074019819521
2828260675510337547,378302478220787713,360288079711305729,2846275074019819521
2828260675510337548,378302478220787713,360288079711305729,2846275074019819521
2828260675510337549,378302478220787713,360288079711305729,2846275074019819521
2828260675510337550,378302478220787713,360288079711305729,2846275074019819521
2828260675510337551,378302478220787713,360288079711305729,2846275074019819521
2828260675510337552,378302478220787713,360288079711305729,2846275074019819521
2846275074019819521,378302478220787713,360288079711305729,2846275074019819521
3152519848681013249,378302478220787713,360288079711305729,3152519848681013249
3152519848681013250,378302478220787713,360288079711305730,3152519848681013250
3152519848681013251,378302478220787713,360288079711305731,3152519848681013251
3152519848681013252,378302478220787713,360288079711305732,3152519848681013252
3152519848681013253,378302478220787713,360288079711305733,3152519848681013253
3152519848681013254,378302478220787713,360288079711305734,3152519848681013254
3170534138742571009,0,0,0
3170534138742571010,0,0,0
3170534247190495233,0,0,0
3170534247190495234,0,0,0
3602879811418062849,378302478220787713,360288079711305729,594475260334571521
Hope this will help:)
Thanks,
Yosef
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
The select statements that show the error - in the fierst post
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi And thanks for the fasr replay,
create table BUSINESS_INVOCATIONS (
ID FIXED(19,0) not null,
CLASS_TYPE CHAR(1) not null,
M_TIME FIXED(19,0) null,
functionEntryId FIXED(19,0) null,
TOP_PARENT FIXED(19,0) null,
START_TIME FIXED(19,0) null,
TIME_S FIXED(19,0) null,
COMP_ID FIXED(19,0) null,
REC_SESS_ID FIXED(19,0) null,
parent FIXED(19,0) null,
HTTP_SESSION FIXED(19,0) null,
INVOCATION_BYTES LONG BYTE null,
INVOCATION_HASH_CODE INT null,
MINOR_INVOCATION VARCHAR(255) null,
TRANSACTION_ID VARCHAR(255) null,
USER_TRACING_COOKIE VARCHAR(255) null,
PARAMS_NAMES_BYTES LONG BYTE null,
PARAMS_VALUES_BYTES LONG BYTE null,
tracingType SMALLINT null,
primary key (ID)
)
CREATE INDEX "BI__HTTPSESS_TIME_IDX" ON "BUSINESS_INVOCATIONS" ("HTTP_SESSION", "TIME_S")
CREATE INDEX "BI__RECESS_TX_IDX" ON "BUSINESS_INVOCATIONS" ("REC_SESS_ID", "TRANSACTION_ID")
CREATE INDEX "BI__RECSESS_CLSTYP_MNRINV_IDX" ON "BUSINESS_INVOCATIONS" ("REC_SESS_ID", "CLASS_TYPE", "MINOR_INVOCATION")
CREATE INDEX "BI__RECSESS_COMP_TIME_TOPP_IDX" ON "BUSINESS_INVOCATIONS" ("REC_SESS_ID", "COMP_ID", "TIME_S", "TOP_PARENT")
CREATE INDEX "BI__RECSESS_TIME_HTTPSESS_IDX" ON "BUSINESS_INVOCATIONS" ("REC_SESS_ID", "TIME_S", "HTTP_SESSION")
CREATE INDEX "BI__RECSESS_TIME_IDX" ON "BUSINESS_INVOCATIONS" ("REC_SESS_ID", "TIME_S")
CREATE BITMAP INDEX "BI__RECSESS_USER_IDX" ON "BUSINESS_INVOCATIONS" ("REC_SESS_ID", "USER_TRACING_COOKIE")
CREATE INDEX "BI__TOPP_CLSTYP_IDX" ON "BUSINESS_INVOCATIONS" ("TOP_PARENT", "CLASS_TYPE")
create table J2EE_CONFIGURATION_BEAN (
ID FIXED(19,0) not null,
J2EEAPPLICATIONSTABLEID FIXED(19,0) null,
J2EEAPPLICATIONID FIXED(19,0) null,
J2EECOMPONENTID FIXED(19,0) null,
primary key (ID)
)
CREATE UNIQUE INDEX "J2EEBEAN__APP_ID_IDX" ON "J2EE_CONFIGURATION_BEAN" ("J2EEAPPLICATIONID", "ID")
CREATE UNIQUE INDEX "J2EEBEAN__APP_TABLE_IDX" ON "J2EE_CONFIGURATION_BEAN" ("J2EEAPPLICATIONSTABLEID", "ID")
CREATE UNIQUE INDEX "J2EEBEAN__COMP_ID_IDX" ON "J2EE_CONFIGURATION_BEAN" ("J2EECOMPONENTID", "ID")
The exact version of MaxDB in use : 7.4.03.07
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
>
> Hi And thanks for the fasr replay,
You're welcome!
> CREATE BITMAP INDEX "BI__RECSESS_USER_IDX" ON "BUSINESS_INVOCATIONS" ("REC_SESS_ID", "USER_TRACING_COOKIE")
That's no MaxDB Statement (Oracle perhaps?). MaxDB has no such thing like 'BITMAP INDEX' - it just does not make any sense there.
>
> The exact version of MaxDB in use : 7.4.03.07
Ok, still missing are:
INSERT statements - we will need at least some data to test.
DB Parameters (you can just post the startup part from the knldiag file).
regards,
Lars
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Yosef,
please provide a small testcase.
Give us:
- create table statements
- create index statemens
- insert statements
- select statements that show the error
Also we need to know:
- exact version of MaxDB in use
- Parametersetup of MaxDB in use
Thanks,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 81 | |
| 10 | |
| 10 | |
| 9 | |
| 7 | |
| 6 | |
| 6 | |
| 5 | |
| 4 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.