Re: JOIN on FIXED: msg#00270 db.maxdb

Hi, the output for FIXED(9,0) is

OWNER TABLENAME COLUMN_OR_INDEX
STRATEGY PAGE_COUNT
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--
A
IDX_RESOURCE_ACTIVITY_1 EQUAL CONDITION FOR INDEX
2
CHRR
IDX_COMPANY_HR_ROLE_1 JOIN VIA MULTIPLE INDEXED COLUMNS
1

E_COMP_HR_K_HUMAN_RESOURCE (USED INDEX COLUMN)

E_COMP_HR_K_COMPANY (USED INDEX COLUMN)
UD
IDX_UNIT_DIVISION_2 JOIN VIA INDEXED COLUMN
1
UNIT
E_U_DIV_K_UNIT_DIVISION JOIN VIA KEY RANGE
1

NO TEMPORARY RESULTS CREATED

RESULT IS COPIED , COSTVALUE IS 7

The output for FIXED(6,0) is exactly the same.

To make this last test, I altered the column from FIXED(9,0) to FIXED(6,0),
but now query returns correct result in both cases.
I really don't understant what's going on.

Thanks
Fabio

----- Original Message -----
From: "Grossmann, Gert" <gert.grossmann@xxxxxxx>
To: <maxdb@xxxxxxxxxxxxxxx>
Sent: Wednesday, February 23, 2005 7:49 AM
Subject: AW: JOIN on FIXED

Please provide output of

EXPLAIN
SELECT
unit.*
FROM
RESOURCE_ACTIVITY a
inner join COMPANY_HR_ROLE chrr on
(a.E_CHRR_K_HUMAN_RESOURCE=chrr.E_COMP_HR_K_HUMAN_RESOURCE
AND
a.E_CHRR_K_COMPANY=chrr.E_COMP_HR_K_COMPANY
AND
a.E_CHRR_D_START_EMP=chrr.E_COMP_HR_D_START)
inner join UNIT_DIVISION ud on
(chrr.E_U_DIV_K_UNIT_DIVISION=ud.K_UNIT_DIVISION)
inner join UNIT_DIVISION_NAME unit on
(ud.k_unit_division=unit.E_U_DIV_K_UNIT_DIVISION)
WHERE
a.K_RESOURCE_ACTIVITY=502

for further anylysis. Execute this EXPLAIN with different column definitions
(FIXED(6,0) and FIXED(9,0))

Gert

-----Ursprüngliche Nachricht-----
Von: Fabio Pinotti [mailto:fabio.pinotti@xxxxxxxxx]
Gesendet: Dienstag, 22. Februar 2005 17:19
An: Zabach, Elke; maxdb@xxxxxxxxxxxxxxx
Betreff: Re: JOIN on FIXED

Sorry, i forgot to provide some important informations.
I'm using MaxDb 7.5.0.23.

The query is (run in Oracle mode):

SELECT
unit.*
FROM
RESOURCE_ACTIVITY a
inner join COMPANY_HR_ROLE chrr on
(a.E_CHRR_K_HUMAN_RESOURCE=chrr.E_COMP_HR_K_HUMAN_RESOURCE
AND
a.E_CHRR_K_COMPANY=chrr.E_COMP_HR_K_COMPANY
AND
a.E_CHRR_D_START_EMP=chrr.E_COMP_HR_D_START)
inner join UNIT_DIVISION ud on
(chrr.E_U_DIV_K_UNIT_DIVISION=ud.K_UNIT_DIVISION)
inner join UNIT_DIVISION_NAME unit on
(ud.k_unit_division=unit.E_U_DIV_K_UNIT_DIVISION)
WHERE
a.K_RESOURCE_ACTIVITY=502

Here are definitions of the four tables:

CREATE TABLE "ELPVA"."RESOURCE_ACTIVITY"
(
"K_RESOURCE_ACTIVITY" Fixed (9,0) NOT NULL,
"E_ACT_K_ACTIVITY" Fixed (9,0) NOT NULL,
"E_ROLE_TYP_K_ROLE_TYPE" Fixed (6,0) NOT NULL,
"D_START" Timestamp NOT NULL,
"O_MODIFY" Timestamp NOT NULL,
"E_OPERATOR_K_OPERATOR" Varchar (20) ASCII NOT NULL,
"E_CHRR_K_HUMAN_RESOURCE" Fixed (9,0),
"E_CHRR_K_COMPANY" Fixed (9,0),
"E_CHRR_D_START_EMP" Timestamp,
"E_CHRR_D_START" Timestamp,
"E_CHRR_K_UNIT_DIVISION" Fixed (9,0),
"E_CHRR_K_ROLE_TYPE" Fixed (6,0),
"E_COMPANY_K_COMPANY" Fixed (9,0),
"C_NOTE" Varchar (250) ASCII,
"K_PROG" Fixed (3,0),
"D_END" Timestamp,
"E_HUM_RES_K_HUMAN_RESOURCE" Fixed (9,0),
"E_U_DIV_K_UNIT_DIVISION" Fixed (9,0),
"E_PTY_K_PARTY" Fixed (9,0)
)

CREATE TABLE "ELPVA"."COMPANY_HR_ROLE"
(
"E_COMP_HR_K_HUMAN_RESOURCE" Fixed (9,0) NOT NULL,
"E_COMP_HR_K_COMPANY" Fixed (9,0) NOT NULL,
"E_COMP_HR_D_START" Timestamp NOT NULL,
"E_ROLE_TYP_K_ROLE_TYPE" Fixed (6,0) NOT NULL,
"D_START" Timestamp NOT NULL,
"O_MODIFY" Timestamp NOT NULL,
"E_OPERATOR_K_OPERATOR" Varchar (20) ASCII NOT NULL,
"E_U_DIV_K_UNIT_DIVISION" Fixed (6,0),
"C_NOTE" Varchar (250) ASCII,
"K_PROG" Fixed (3,0),
"D_END" Timestamp
)

CREATE TABLE "ELPVA"."UNIT_DIVISION"
(
"K_UNIT_DIVISION" Fixed (9,0) NOT NULL,
"E_COMPANY_K_COMPANY" Fixed (9,0) NOT NULL,
"D_START" Timestamp NOT NULL,
"O_MODIFY" Timestamp NOT NULL,
"E_OPERATOR_K_OPERATOR" Varchar (20) ASCII NOT NULL,
"E_U_DIV_K_UNIT_DIVISION" Fixed (9,0),
"E_U_DIV_K_UNIT_DIVISION_NEW" Fixed (9,0),
"E_UD_TYP_K_UNIT_DIVISION_TYPE" Fixed (6,0),
"C_NOTE" Varchar (250) ASCII,
"D_END" Timestamp,
"C_TREE_PATH" Varchar (1000) ASCII
)

CREATE TABLE "ELPVA"."UNIT_DIVISION_NAME"
(
"N_UNIT_DIVISION" Varchar (100) ASCII NOT NULL,
"E_U_DIV_K_UNIT_DIVISION" Fixed (9,0) NOT NULL,
"K_PROG" Fixed (3,0) NOT NULL,
"D_START" Timestamp NOT NULL,
"O_MODIFY" Timestamp NOT NULL,
"E_OPERATOR_K_OPERATOR" Varchar (20) ASCII NOT NULL,
"N_SHORT_UNIT_DIVISION" Varchar (20) ASCII,
"C_NOTE" Varchar (250) ASCII,
"D_END" Timestamp,
PRIMARY KEY ("E_U_DIV_K_UNIT_DIVISION", "K_PROG")
)

Running the query with these definitions, return no result.
But changing COMPANY_HR_ROLE.E_U_DIV_K_UNIT_DIVISION to Fixed(9,0), makes
it work.

Tables were created in one unique step (in detail, columns used by join were
not added or altered later)

About parameters, I don't exaclty know what you need: can you tell me in
detail what I should look for?

Thanks in advance
Fabio

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:
http://lists.mysql.com/maxdb?unsub=fabio.pinotti@xxxxxxxxx

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx

Previous by Date:	RE: Where is documentation of C Precompiler of MAXDB ?, Paskamp, Marco
Next by Date:	weird behaviour when recreating bad indexes, Ajit Aranha
Previous by Thread:	AW: JOIN on FIXED, Grossmann, Gert
Next by Thread:	Where is documentation of C Precompiler of MAXDB ?, Gu Lei
Indexes:	[Date] [Thread] [Top] [All Lists]

<Prev in Thread]	Current Thread	[Next in Thread>

Re: JOIN on FIXED: msg#00270

db.maxdb

Free Magazines