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.
