>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. Set
