>Preparing query: SELECT a.CON_ID, a.* FROM XRD_RC_DATA_VIEW_F a Prepare >time: 3.338s Field #01: XRD_RC_DATA_VIEW_F.CON_ID Alias:CON_ID >Type:INTEGER Field #02: XRD_RC_DATA_VIEW_F.CON_ID Alias:CON_ID >Type:INTEGER Field #03: XRD_RC_DATA_VIEW_F.MIN_FWHM_GAN_002 >Alias:MIN_FWHM_GAN_002 Type:FLOAT Field #04: >XRD_RC_DATA_VIEW_F.AVG_FWHM_GAN_002 Alias:AVG_FWHM_GAN_002 Type:DOUBLE >PRECISION Field #05: XRD_RC_DATA_VIEW_F.MAX_FWHM_GAN_002 >Alias:MAX_FWHM_GAN_002 Type:FLOAT Field #06: >XRD_RC_DATA_VIEW_F.COUNT_FWHM_GAN_002 Alias:COUNT_FWHM_GAN_002 >Type:INTEGER Field #07: XRD_RC_DATA_VIEW_F.MIN_FWHM_ALN_002 >Alias:MIN_FWHM_ALN_002 Type:FLOAT Field #08: >XRD_RC_DATA_VIEW_F.AVG_FWHM_ALN_002 Alias:AVG_FWHM_ALN_002 Type:DOUBLE >PRECISION Field #09: XRD_RC_DATA_VIEW_F.MAX_FWHM_ALN_002 >Alias:MAX_FWHM_ALN_002 Type:FLOAT Field #10: >XRD_RC_DATA_VIEW_F.COUNT_FWHM_ALN_002 Alias:COUNT_FWHM_ALN_002 >Type:INTEGER Field #11: XRD_RC_DATA_VIEW_F.MIN_FWHM_GAN_102 >Alias:MIN_FWHM_GAN_102 Type:FLOAT Field #12: >XRD_RC_DATA_VIEW_F.AVG_FWHM_GAN_102 Alias:AVG_FWHM_GAN_102 Type:DOUBLE >PRECISION Field #13: XRD_RC_DATA_VIEW_F.MAX_FWHM_GAN_102 >Alias:MAX_FWHM_GAN_102 Type:FLOAT Field #14: >XRD_RC_DATA_VIEW_F.COUNT_FWHM_GAN_102 Alias:COUNT_FWHM_GAN_102 >Type:INTEGER PLAN (A DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER IDX_DATA_XRD_RC3 INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER >IDX_DATA_XRD_RC9 INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC ORDER IDX_DATA_XRD_RC3 INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER IDX_DATA_XRD_RC9 INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER >IDX_DATA_XRD_RC3 INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC ORDER IDX_DATA_XRD_RC9 INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A A NATURAL) > >Executing... >Done. >0 fetches, 0 marks, 0 reads, 0 writes. >0 inserts, 0 updates, 0 deletes, 127020 index, 960 seq. >Delta memory: -72 bytes. >Total execution time: 14.561s >Script execution finished. > >If I use DISTINCT > >Preparing query: SELECT DISTINCT a.CON_ID, a.* FROM XRD_RC_DATA_VIEW_F >a Prepare time: 3.962s ... >PLAN (A DATA_XRD_RC ORDER IDX_DATA_XRD_RC9 INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER IDX_DATA_XRD_RC3 INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER >IDX_DATA_XRD_RC9 INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC ORDER IDX_DATA_XRD_RC3 INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER IDX_DATA_XRD_RC9 INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER >IDX_DATA_XRD_RC3 INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC ORDER IDX_DATA_XRD_RC3 INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER IDX_DATA_XRD_RC9 INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER >IDX_DATA_XRD_RC3 INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A >DATA_XRD_RC ORDER IDX_DATA_XRD_RC9 INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX (IDX_DATA_XRD_RC1, >IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER IDX_DATA_XRD_RC3 INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC INDEX >(IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN (A DATA_XRD_RC ORDER >IDX_DATA_XRD_RC9 INDEX (IDX_DATA_XRD_RC1, IDX_DATA_XRD_RC4)) PLAN SORT >((A A NATURAL)) > >Executing... >Done. >0 fetches, 0 marks, 0 reads, 0 writes. >0 inserts, 0 updates, 0 deletes, 5761198 index, 33335 seq. >Delta memory: 121544 bytes. >Total execution time: 0:03:51 (hh:mm:ss) Script execution finished. > >As you see distinct (3:51 min vs 15sec) really slow down query. I have >impression I use all needed indexing as so on. That is why I was looking for >option where I can select only first row from the query: > >SELECT * FROM DATA_SUMMARY a >left join >(select first 1 * from DATA_VIEW) as c on c.CON_ID_2=a.CON_ID_1 > >unfortunately it doesn't work. > >If you have any other suggestions please let me know. > >Now I see where I was mistaken, Andrzej, I thought DATA_SUMMARY and DATA_VIEW >were two tables. Answering your question is impossible without >knowing the view definition, so please show us that (the 'fix' is even likely >to be in there since 15 seconds is also a long time for a table >of only a few thousand records) and tell us which fields each index in the >plans refer to.
One quick check you can do, is to see whether the plan is identical for SELECT DISTINCT a.CON_ID, a.* FROM XRD_RC_DATA_VIEW_F and SELECT DISTINCT a.CON_ID+0 AS CON_ID, a.* FROM XRD_RC_DATA_VIEW_F If the latter is quicker, I would still say that it is not necessarily fixing your problem, it could also be a case of hiding a problem with your view (without the view definition, all suggested "solutions" are basically guesses - probably based on experiences with Firebird, but without knowledge of whether that experience is relevant for your case or not). Set
