THE DDL of the 3 sample table : CREATE TABLE DESC_VARCHAR ( IDOBJ VARCHAR(20), COMMENT VARCHAR(10000) ); CREATE INDEX DESC_VARCHAR_IDX ON DESC_VARCHAR(IDOBJ);
******** CREATE TABLE DESC_BLOB ( IDOBJ VARCHAR(20) COMMENT BLOB ); CREATE INDEX DESC_BLOB_IDX ON DESC_BLOB(IDOBJ); ******** CREATE TABLE DESC_EMPTY ( IDOBJ VARCHAR(20) ); CREATE INDEX DESC_EMPTY_IDX ON DESC_EMPTY(IDOBJ); tables are newly created and filled with the exact same reccords (40 millions rows) the same query on the 3 tables : select IDObj From desc_varchar where IDObj='NOT_EXIST'; PLAN (DESC_VARCHAR INDEX (DESC_VARCHAR_IDX)) => around 430 ms to return select IDObj From desc_blob where IDObj='NOT_EXIST' PLAN (DESC_BLOB INDEX (DESC_BLOB_IDX)) => around 350 ms to return select IDObj From desc_xxxx where IDObj='NOT_EXIST' PLAN (DESC_EMPTY INDEX (DESC_EMPTY_IDX)) => around 1.5 ms to return if you understand something ..... moving the page size from 8 to 16 reduce by 10 the speed of the 2 first query, but still 10x more slower than the last variante --- In [email protected], Mark Rotteveel <mark@...> wrote: > > On Wed, 07 Mar 2012 09:23:11 -0000, "nathanelrick" <nathanelrick@...> > wrote: > > Thanks Svein, > > > >> Is your query, > >> > >> select IDObj From DESCRIPTION where ID='ID_HAS_NEVER_EXISTED', or > >> select IDObj From DESCRIPTION where > >> ID='ID_THAT_IS_RECENTLY_DELETED_UPDATED_OR_ADDED'? > > > > First case ID='ID_HAS_NEVER_EXISTED', so unfortunatly it's not this .. > > also i do the test on a fresh backup/restored database, even a newly > > created and filled table to be sure about versioning ... not look like > this > > What is the plan for the query? >
