You Asked
I have a table,
When selecting all columns without "order by" clause, query is very quick : select * from LOG_STATS_JUNIT;
When adding "order by" clause, query go down : select * from LOG_STATS_JUNIT order by date_exec;
If using DBMS_LOB.SUBSTR() : execution time is correct !
select t.DATE_EXEC, t.SERVEUR, t.ENV, t.CLASSE, t.METHODE, t.PARAMETRE, t.ETAT, t.MESSAGE, t.DATE_DEBUT, t.DATE_FIN, t.NB_TEST_SUCCES, t.NB_TEST_ECHEC,
DBMS_LOB.SUBSTR(t.info1,4000,1),DBMS_LOB.SUBSTR(t.info2,2000,1),DBMS_LOB.SUBSTR(t.info2,2000,2)
from LOG_STATS_JUNIT t
order by t.date_exec;
Do you have an explanation ?
create table LOG_STATS_JUNIT (
DATE_EXEC TIMESTAMP(6) NOT NULL ENABLE,
SERVEUR VARCHAR2(125 char) NOT NULL ENABLE,
ENV VARCHAR2(32 char) NOT NULL ENABLE,
CLASSE VARCHAR2(256 char) NOT NULL ENABLE,
METHODE VARCHAR2(125 char) NOT NULL ENABLE,
PARAMETRE VARCHAR2(125 char) NOT NULL ENABLE,
ETAT VARCHAR2(1 char),
MESSAGE VARCHAR2(400 char),
DATE_DEBUT TIMESTAMP(6),
DATE_FIN TIMESTAMP(6),
NB_TEST_SUCCES NUMBER(6,0),
NB_TEST_ECHEC NUMBER(6,0),
DURATION NUMBER(12,2),
INFO1 CLOB(4000),
INFO2 BLOB(4000));
ALTER TABLE LOG_STATS_JUNIT ADD CONSTRAINT LOG_STATS_JUNIT_PK PRIMARY KEY (DATE_EXEC, SERVEUR, ENV, CLASSE, METHODE, PARAMETRE);
with this data in it (2224 rows) :
insert into LOG_STATS_JUNIT (DATE_EXEC,SERVEUR,ENV,CLASSE,METHODE,PARAMETRE,ETAT,MESSAGE,DATE_DEBUT,DATE_FIN,NB_TEST_SUCCES,NB_TEST_ECHEC,DURATION,INFO1,INFO2) values ('10/08/2016 11:07:56','DUBUS','L','test.fmk.srv.query.TestSQLSelect','doExecuteManyOnManyRaise3_Error','JUnitCtx','O',null,'10/08/2016 11:08:30','10/08/2016 11:08:30',1,0,58,null,null);
insert into LOG_STATS_JUNIT (DATE_EXEC,SERVEUR,ENV,CLASSE,METHODE,PARAMETRE,ETAT,MESSAGE,DATE_DEBUT,DATE_FIN,NB_TEST_SUCCES,NB_TEST_ECHEC,DURATION,INFO1,INFO2) values ('10/08/2016 11:07:56','DUBUS','L','test.fmk.srv.query.TestSQLSelect','doExecuteManyOnManyRaise4_Error','JUnitCtx','O',null,'10/08/2016 11:08:30','10/08/2016 11:08:30',1,0,3,null,null);
insert into LOG_STATS_JUNIT (DATE_EXEC,SERVEUR,ENV,CLASSE,METHODE,PARAMETRE,ETAT,MESSAGE,DATE_DEBUT,DATE_FIN,NB_TEST_SUCCES,NB_TEST_ECHEC,DURATION,INFO1,INFO2) values ('10/08/2016 11:07:56','DUBUS','L','test.fmk.srv.query.TestSQLSelect','doExecuteSelectFromWhere','JUnitCtx','O',null,'10/08/2016 11:08:30','10/08/2016 11:08:30',1,0,35,null,null);
insert into LOG_STATS_JUNIT (DATE_EXEC,SERVEUR,ENV,CLASSE,METHODE,PARAMETRE,ETAT,MESSAGE,DATE_DEBUT,DATE_FIN,NB_TEST_SUCCES,NB_TEST_ECHEC,DURATION,INFO1,INFO2) values ('10/08/2016 11:07:56','DUBUS','L','test.fmk.srv.query.TestSQLSelect','doExecuteMany','JUnitCtx','O',null,'10/08/2016 11:08:30','10/08/2016 11:08:30',1,0,3,null,null);
insert into LOG_STATS_JUNIT (DATE_EXEC,SERVEUR,ENV,CLASSE,METHODE,PARAMETRE,ETAT,MESSAGE,DATE_DEBUT,DATE_FIN,NB_TEST_SUCCES,NB_TEST_ECHEC,DURATION,INFO1,INFO2) values ('10/08/2016 11:07:56','DUBUS','L','test.fmk.srv.query.TestSQLSelect','doExecuteManyOnManyRaise1_Good','JUnitCtx','O',null,'10/08/2016 11:08:30','10/08/2016 11:08:30',1,0,3,null,null);
...
and Connor said...
Couple of things here
1) When people say "query without order by runs fast", you need to make you are comparing things correctly here. Most tools will NOT retrieve all the data when you do that - they just enough rows to fill the screen/window/etc.
Add the order by, and of course, we need to get ALL of the data so it can be sorted.
2) A lot of that will depend on the size of the lob and how it has been stored.
For 'enable storage in row', a small lobs will be stored right there in the block along with the row. Slightly larger ones, will have direct pointers in the row out to the lob chunks in the lob segment. Larger than that, we will use a lob 'locator' to lookup a lob index, which will then point to the lob segment.
So if you're just asking for the first 'n' bytes of a lob, then you might be avoiding a *lot* of workload navigating a lob index/lob segment (depending on the size of the lob).
One thing I will ask though - your sample data has nulls for the clob/blob. Are you saying that *all* of your data has no clob/blob data ?
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment