Query optimization with 3000000 in single date oracle

Question

Table x contains millions of rows and I have to fetch data for single date using function based index(trunc). Single date data for eg, for 22-07-16 we have 3000000 rows. I am also using case for sum of columns. Query taking 18 sec. How I can reduce time.

EDIT QUERY:

 SELECT SUM(
  CASE
    WHEN cssgoldenc1_.impact='Low'
    THEN 1
    ELSE 0
  END) AS col_0_0_,
  SUM(
  CASE
    WHEN cssgoldenc1_.impact='High'
    THEN 1
    ELSE 0
  END) AS col_1_0_
FROM CSSCOMPLIANCEDETAIL csscomplia0_,
  CSSGoldenConfiguration cssgoldenc1_,
  CSS css7_
WHERE csscomplia0_.cssGoldenConfigurationID_FK=cssgoldenc1_.CSSGoldenConfigurationId_PK
AND csscomplia0_.cssID_FK                     =css7_.cssId_PK
AND (cssgoldenc1_.cmcategory                 IN ('Access List','Application of QoS Policy','Archive','BFD','BGP', 'CPU','Clock','Debug','Default settings','Entity Check','IGP Routing','Inclusion in VRF', 'Interface Parameters','LDP','LDP Establishment','License','Logging/Syslog/Debug','MTU Size', 'Multicast','Multilink','NodeReadiness','Nomenclature Related','Performance Optimization', 'QoS','Router OAM','Routing','SNMP','Security','Services','System Recovery', 'Type of Interface','Unicast','Unrequired Services','mBGP'))
AND TRUNC(csscomplia0_.creationDate)          =to_Date('22-07-16','dd-mm-yy')
AND (css7_.softwareVersion                   IN ('/asr920-universalk9.V155_1S2_SR635680903_6.bin', '/asr920-universalk9_npe.03.13.00.S.154-3.S-ext.bin','/asr920-universalk9_npe.03.14.02.S.155-1.S2-std.bin', '/asr920-universalk9_npe.V155_1_S2_SR635680903_2.bin','/asr920-universalk9_npe.V155_1_S2_SR635680903_6.bin', '/bootflash','asr901-universalk9-mz.155-3.S1a.bin','asr903rsp1-universalk9_npe.V155_1_S2_SR635680903_10.bin', 'asr920-universalk9.V155_1S2_SR635680903_6.bin','asr920-universalk9_npe.03.13.00.S.154-3.S-ext.bin', 'asr920-universalk9_npe.03.13.00z.S.154-3.S0z-ext.bin','asr920-universalk9_npe.03.14.02.S.155-1.S2-', 'asr920-universalk9_npe.03.14.02.S.155-1.S2-std.bin','asr920-universalk9_npe.03.15.01.S.155-2.S1-std.bin', 'asr920-universalk9_npe.03.16.01a.S.155-3.S1a-ext.bin','asr920-universalk9_npe.2016-05-10_07.53_saappuku.bin' ,'asr920-universalk9_npe.V155_1_S2_SR635680903_2.bin','asr920-universalk9_npe.V155_1_S2_SR635680903_6.bin',
  'asr920-universalk9_npe.V155_1_S2_SR635680903_6.binn','bootflash'));

Index :

create index idx_fnc on CSSCOMPLIANCEDETAIL(trunc(creationDate));

Please post table definition including indexes. Query and explain plan. — vercelli, Jul 25 '16 at 10:16
I can not copy it from other server. This is all I can post, also when I removed in condition its taking 8 seconds. So I can index that column? — Aamir, Jul 25 '16 at 10:42
Sorry, I don't understand. what did you remove? What column is to be indexed? I just meant that table `CSS` is not being used (not in condition nor selection). So, you can't copy the explain plan, but can you tell us if the index idx_fnc is being used? — vercelli, Jul 25 '16 at 10:45
Have a look on complete query. Index is on date column only. And yes index idx_fnc is used in query. — Aamir, Jul 25 '16 at 10:51
I'm sorry but without more info I'm out of ideas. Maybe you could try to index `softwareVersion` or `cmcategory`. I guess `cssGoldenConfigurationID_FK` and `cssID_FK` are indexed. Or maybe you could try to partition by `creationDate` — vercelli, Jul 25 '16 at 11:07
all columns specified above are indexed, but it is using trunc(creationdate) [range index scan] column only because softwareversion and cmcategory have low cardinality. All foreign keys are indexed. — Aamir, Jul 25 '16 at 11:37
Not sure how much this will help, but it's worth a try. Index `csscomplia0_.creationDate` (instead of using function-based index). Then the condition `trunc(date1) = dt` can be written as `date1 >= dt and date1 < dt + 1`. Note that to enter dates with no time of day component (that is, with 00:00:00), you can do what you did, which is perfectly fine, or you can save some typing with the date literal syntax (using ANSI standard), that is: `date '2016-07-22'` (always in `'yyyy-mm-dd'` format). https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ — , Jul 25 '16 at 11:58
"Table x contains millions of rows and I have to fetch data for single date" is the candidate for partition table. check all you SQL logic is based on date criteria. Create table partition by day. Then check SQL plan to find partitioning pruning happens. — Ramki, Jul 26 '16 at 11:09

vercelli · Answer 1 · 2016-07-25T14:55:40.617

Try this. Basically I took the CASE to a subquery since this way it shouldn't be evaluated 3M times. I also change the query in order to use JOIN

with  cssgoldenc1_ as 
    (select /*+ Materialize */   CASE  WHEN impact='Low' THEN 1
                                                         ELSE 0  END AS col_0_0_,
                                 CASE WHEN impact='High' THEN 1
                                                         ELSE 0 END AS col_1_0_,
                                 CSSGoldenConfigurationId_PK
       from CSSGoldenConfiguration
      where cssgoldenc1_.cmcategory IN ('Access List','Application of QoS Policy','Archive','BFD','BGP', 'CPU','Clock','Debug','Default settings','Entity Check','IGP Routing','Inclusion in VRF', 'Interface Parameters','LDP','LDP Establishment','License','Logging/Syslog/Debug','MTU Size', 'Multicast','Multilink','NodeReadiness','Nomenclature Related','Performance Optimization', 'QoS','Router OAM','Routing','SNMP','Security','Services','System Recovery', 'Type of Interface','Unicast','Unrequired Services','mBGP')
     )
SELECT SUM(col_0_0_) AS col_0_0_,
       SUM(col_1_0_) AS col_1_0_
FROM CSSCOMPLIANCEDETAIL csscomplia0_ join cssgoldenc1_ on csscomplia0_.cssGoldenConfigurationID_FK = cssgoldenc1_.CSSGoldenConfigurationId_PK
                                      join CSS css7_   on csscomplia0_.cssID_FK                     = css7_.cssId_PK
WHERE TRUNC(csscomplia0_.creationDate)          =to_Date('22-07-16','dd-mm-yy')
  AND css7_.softwareVersion                   IN ('/asr920-universalk9.V155_1S2_SR635680903_6.bin', '/asr920-universalk9_npe.03.13.00.S.154-3.S-ext.bin','/asr920-universalk9_npe.03.14.02.S.155-1.S2-std.bin', '/asr920-universalk9_npe.V155_1_S2_SR635680903_2.bin','/asr920-universalk9_npe.V155_1_S2_SR635680903_6.bin', '/bootflash','asr901-universalk9-mz.155-3.S1a.bin','asr903rsp1-universalk9_npe.V155_1_S2_SR635680903_10.bin', 'asr920-universalk9.V155_1S2_SR635680903_6.bin','asr920-universalk9_npe.03.13.00.S.154-3.S-ext.bin', 'asr920-universalk9_npe.03.13.00z.S.154-3.S0z-ext.bin','asr920-universalk9_npe.03.14.02.S.155-1.S2-', 'asr920-universalk9_npe.03.14.02.S.155-1.S2-std.bin','asr920-universalk9_npe.03.15.01.S.155-2.S1-std.bin', 'asr920-universalk9_npe.03.16.01a.S.155-3.S1a-ext.bin','asr920-universalk9_npe.2016-05-10_07.53_saappuku.bin' ,'asr920-universalk9_npe.V155_1_S2_SR635680903_2.bin','asr920-universalk9_npe.V155_1_S2_SR635680903_6.bin',
  'asr920-universalk9_npe.V155_1_S2_SR635680903_6.binn','bootflash');

Query optimization with 3000000 in single date oracle

1 Answers1