db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kashyup <va...@quantum4d.com>
Subject Query optimization
Date Wed, 15 Jul 2009 19:06:07 GMT

Hello All,
I have a certain query and just want feedback/suggestions for the difference
in Derby and Oracle.

Query Time
2-3 secs in Embedded DB  (using SQuirrel)
.2 secs in Oralce (using Oracle SQL Developer)

TABLE SCRIPT (with Indexes):

CREATE TABLE "ENTITY"
(
   ENTITY_GLOBAL_ID varchar(255) PRIMARY KEY NOT NULL,
   E varchar(255) NOT NULL,
   ONTOLOGY_GLOBAL_ID varchar(255),
   ENTITYKIND varchar(255),
   TTYPE varchar(255),
   RTYPE varchar(255),
   ENDPOINTENTITY0 varchar(255),
   ENDPOINTENTITY1 varchar(255),
   DIRECTION varchar(255),
   METADATA clob(1073741823),
   METADATAMAP clob(1073741823)
)
;
CREATE INDEX SQL090714134438790 ON ENTITY(TTYPE) - FK
;
CREATE INDEX SQL090714134438670 ON ENTITY(RTYPE) - FK
;
CREATE INDEX RTYPE_ENDPOINT0_IDX ON ENTITY - an attempt to make query run
faster?
(
  RTYPE,
  ENDPOINTENTITY0
)
;
CREATE UNIQUE INDEX SQL090714134437020 ON ENTITY(ENTITY_GLOBAL_ID) - PK
;
CREATE INDEX SQL090714134438540 ON ENTITY(ONTOLOGY_GLOBAL_ID) - FK
;

THE QUERY

select hentity0_.endpointentity0, hentity1_.*
from ENTITY hentity0_, ENTITY hentity1_ 
where hentity0_.E in ('T', 'R') 
and hentity1_.E in ('T', 'R') 
and hentity0_.endpointentity1=hentity1_.entity_global_id 
and hentity0_.rtype='Metabase/S:default/R:schema.DisplayEntityRelType' 
and (hentity0_.endpointentity0 in (a list of 500 ENTITY_GLOBAL_IDs))

Some Additional Info:

Selectivity (unique/total rec.) of columns:
rtype - .002 
endpointentity0 - .24
endpointentity1 - .38

Does the Indexes looks good? or do I need some additional indexing?

thanks all

kashyup



-- 
View this message in context: http://www.nabble.com/Query-optimization-tp24503976p24503976.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message