Home » RDBMS Server » Performance Tuning » cost is more after releasing FTS on big table (partition table) (11g, 11.2.0.3, Window server)
cost is more after releasing FTS on big table (partition table) [message #642319] Tue, 08 September 2015 01:38 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

Here, table Gen_prop_information_tab is partition one. Gather stats is scheduled daily (stats is upto date). While tuning the query, developer has removed FTS (full table scan) and tuned to index based. But thing is that, after removing the FTS, cost increased from 2 to 29.

Below are the explain plan of the queries (present and modified one).

/* *************************************************** Q1 : initial query plan *********************************** */
EXPLAIN PLAN FOR
SELECT TABC.TXT_POLICY_NO_CHAR,
       TABC.TXT_CUSTOMER_NAME,
       TABC.NUM_COVERNOTE_NO,
       TABC.DAT_COVER_NOTE_DATE,
       TABC.TXT_COVER_NOTE_HOUR,
       TABC.DAT_POLICY_EFF_FROMDATE,
       TABC.TXT_POLICY_EFF_FROMHOUR,
       TABC.DAT_POLICY_EFF_TODATE,
       TABC.TXT_POLICY_EFF_TOHOUR,
       TABC.NUM_TOTAL_SI,
       TABC.TXT_CUSTOMER_ID,
       TABC.TXT_BRANCH_OFFICE_CODE,
       DECODE(UPPER(TABC.TXT_ACTIVE_FLAG), 'YES', 'ACTIVE', 'NOT ACTIVE'),
       TABC.NUM_NET_PREMIUM
  FROM (SELECT *
          FROM GEN_PROP_INFORMATION_TAB G
         WHERE (G.TXT_POLICY_NO_CHAR = :B3 AND
               NVL(G.TXT_INTERNAL_CERTIFICATE_NO, '0') = NVL(:B2, '0') AND
               G.NUM_ENDORSEMENT_NO =
               (SELECT MAX(XXC.NUM_ENDORSEMENT_NO)
                   FROM GEN_PROP_INFORMATION_TAB XXC
                  WHERE XXC.TXT_POLICY_NO_CHAR = G.TXT_POLICY_NO_CHAR
                    AND NVL(XXC.TXT_INTERNAL_CERTIFICATE_NO, '0') =
                        NVL(:B2, '0')
                    AND TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') BETWEEN
                        TO_DATE((TO_CHAR(XXC.DAT_POLICY_EFF_FROMDATE,
                                         'dd/mm/yyyy') || ' ' ||
                                TO_CHAR(TO_DATE(XXC.TXT_POLICY_EFF_FROMHOUR,
                                                 'hh24:mi'),
                                         'hh24:mi')),
                                'dd/mm/yyyy hh24:mi') AND
                        TO_DATE((TO_CHAR(XXC.DAT_POLICY_EFF_TODATE,
                                         'dd/mm/yyyy') || ' ' ||
                                TO_CHAR(TO_DATE(XXC.TXT_POLICY_EFF_TOHOUR,
                                                 'hh24:mi'),
                                         'hh24:mi')),
                                'dd/mm/yyyy hh24:mi')
                    AND TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') >
                        (TO_DATE((TO_CHAR(NVL(XXC.DAT_ENDORSEMENT_EFF_DATE,
                                              XXC.DAT_POLICY_EFF_FROMDATE),
                                          'dd/mm/yyyy') || ' ' ||
                                 TO_CHAR(TO_DATE(NVL(XXC.TXT_ENDORSEMENT_EFF_TIME,
                                                      TXT_POLICY_EFF_FROMHOUR),
                                                  'hh24:mi'),
                                          'hh24:mi')),
                                 'dd/mm/yyyy hh24:mi'))) AND
               TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') BETWEEN
               TO_DATE((TO_CHAR(G.DAT_POLICY_EFF_FROMDATE, 'dd/mm/yyyy') || ' ' ||
                        TO_CHAR(TO_DATE(G.TXT_POLICY_EFF_FROMHOUR,
                                         'hh24:mi'),
                                 'hh24:mi')),
                        'dd/mm/yyyy hh24:mi') AND
               TO_DATE((TO_CHAR(G.DAT_POLICY_EFF_TODATE, 'dd/mm/yyyy') || ' ' ||
                        TO_CHAR(TO_DATE(G.TXT_POLICY_EFF_TOHOUR, 'hh24:mi'),
                                 'hh24:mi')),
                        'dd/mm/yyyy hh24:mi'))
           AND ROWNUM = 1
            OR (G.NUM_REFERENCE_NUMBER =
               (SELECT D.NUM_REFERENCE_NO
                   FROM GC_CLM_GEN_INFO D
                  WHERE D.NUM_UPDATE_NO =
                        (SELECT MAX(DI.NUM_UPDATE_NO)
                           FROM GC_CLM_GEN_INFO DI
                          WHERE DI.NUM_CLAIM_NO = D.NUM_CLAIM_NO
                            AND DI.NUM_CLAIM_NO = :B4)))
           AND G.NUM_ENDORSEMENT_NO =
               (SELECT MAX(T.NUM_ENDORSEMENT_NO)
                  FROM GEN_PROP_INFORMATION_TAB T
                 WHERE T.TXT_POLICY_NO_CHAR = G.TXT_POLICY_NO_CHAR)) TABC
 WHERE ROWNUM = 1;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

Plan hash value: 317493415
 
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                               |     1 |   387 |     2   (0)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                          |                               |       |       |            |          |       |       |
|   2 |   VIEW                                  |                               |     1 |   387 |     2   (0)| 00:00:01 |       |       |
|   3 |    COUNT                                |                               |       |       |            |          |       |       |
|*  4 |     FILTER                              |                               |       |       |            |          |       |       |
|   5 |      PARTITION RANGE ALL                |                               |     1 |   108 |     2   (0)| 00:00:01 |     1 |     6 |
|   6 |       PARTITION HASH ALL                |                               |     1 |   108 |     2   (0)| 00:00:01 |     1 |    30 |
|   7 |        TABLE ACCESS FULL                | GEN_PROP_INFORMATION_TAB      |     1 |   108 |     2   (0)| 00:00:01 |     1 |   180 |
|   8 |      SORT AGGREGATE                     |                               |     1 |    50 |            |          |       |       |
|*  9 |       TABLE ACCESS BY GLOBAL INDEX ROWID| GEN_PROP_INFORMATION_TAB      |     1 |    50 |     5   (0)| 00:00:01 | ROWID | ROWID |
|* 10 |        INDEX RANGE SCAN                 | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     3   (0)| 00:00:01 |       |       |
|  11 |      NESTED LOOPS                       |                               |       |       |            |          |       |       |
|  12 |       NESTED LOOPS                      |                               |     1 |    51 |     5   (0)| 00:00:01 |       |       |
|  13 |        VIEW                             | VW_SQ_1                       |     1 |    26 |     3   (0)| 00:00:01 |       |       |
|  14 |         SORT GROUP BY                   |                               |     1 |    15 |     3   (0)| 00:00:01 |       |       |
|* 15 |          INDEX RANGE SCAN               | PK_GC_CLM_GEN_INFO            |     6 |    90 |     3   (0)| 00:00:01 |       |       |
|* 16 |        INDEX UNIQUE SCAN                | PK_GC_CLM_GEN_INFO            |     1 |       |     1   (0)| 00:00:01 |       |       |
|  17 |       TABLE ACCESS BY GLOBAL INDEX ROWID| GC_CLM_GEN_INFO               |     1 |    25 |     2   (0)| 00:00:01 | ROWID | ROWID |
|  18 |      SORT AGGREGATE                     |                               |     1 |    10 |            |          |       |       |
|  19 |       TABLE ACCESS BY GLOBAL INDEX ROWID| GEN_PROP_INFORMATION_TAB      |     3 |    30 |     5   (0)| 00:00:01 | ROWID | ROWID |
|* 20 |        INDEX RANGE SCAN                 | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     3   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   4 - filter("G"."TXT_POLICY_NO_CHAR"=:B3 AND NVL("G"."TXT_INTERNAL_CERTIFICATE_NO",'0')=NVL(:B2,'0') AND 
              "G"."NUM_ENDORSEMENT_NO"= (SELECT MAX("XXC"."NUM_ENDORSEMENT_NO") FROM "GEN_PROP_INFORMATION_TAB" "XXC" WHERE 
              "XXC"."TXT_POLICY_NO_CHAR"=:B1 AND TO_DATE(TO_CHAR(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_FROMDATE"),'dd/mm/yyyy')||' 
              '||TO_CHAR(TO_DATE("XXC"."TXT_POLICY_EFF_FROMHOUR",'hh24:mi'),'hh24:mi'),'dd/mm/yyyy hh24:mi')<=TO_DATE(:B1,'dd/mm/yyyy 
              hh24:mi') AND TO_DATE(TO_CHAR(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_TODATE"),'dd/mm/yyyy')||' 
              '||TO_CHAR(TO_DATE("XXC"."TXT_POLICY_EFF_TOHOUR",'hh24:mi'),'hh24:mi'),'dd/mm/yyyy hh24:mi')>=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') 
              AND TO_DATE(TO_CHAR(NVL("XXC"."DAT_ENDORSEMENT_EFF_DATE","XXC"."DAT_POLICY_EFF_FROMDATE"),'dd/mm/yyyy')||' 
              '||TO_CHAR(TO_DATE(NVL("XXC"."TXT_ENDORSEMENT_EFF_TIME","TXT_POLICY_EFF_FROMHOUR"),'hh24:mi'),'hh24:mi'),'dd/mm/yyyy 
              hh24:mi')<TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND NVL("XXC"."TXT_INTERNAL_CERTIFICATE_NO",'0')=NVL(:B2,'0')) AND ROWNUM=1 AND 
              TO_DATE(TO_CHAR(INTERNAL_FUNCTION("G"."DAT_POLICY_EFF_FROMDATE"),'dd/mm/yyyy')||' 
              '||TO_CHAR(TO_DATE("G"."TXT_POLICY_EFF_FROMHOUR",'hh24:mi'),'hh24:mi'),'dd/mm/yyyy hh24:mi')<=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') 
              AND TO_DATE(TO_CHAR(INTERNAL_FUNCTION("G"."DAT_POLICY_EFF_TODATE"),'dd/mm/yyyy')||' 
              '||TO_CHAR(TO_DATE("G"."TXT_POLICY_EFF_TOHOUR",'hh24:mi'),'hh24:mi'),'dd/mm/yyyy hh24:mi')>=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') OR 
              "G"."NUM_REFERENCE_NUMBER"= (SELECT "D"."NUM_REFERENCE_NO" FROM "GC_CLM_GEN_INFO" "D", (SELECT MAX("DI"."NUM_UPDATE_NO") 
              "MAX(DI.NUM_UPDATE_NO)","DI"."NUM_CLAIM_NO" "ITEM_1" FROM "GC_CLM_GEN_INFO" "DI" WHERE "DI"."NUM_CLAIM_NO"=TO_NUMBER(:B4) GROUP 
              BY "DI"."NUM_CLAIM_NO") "VW_SQ_1" WHERE "D"."NUM_UPDATE_NO"="MAX(DI.NUM_UPDATE_NO)" AND "ITEM_1"="D"."NUM_CLAIM_NO") AND 
              "G"."NUM_ENDORSEMENT_NO"= (SELECT MAX("T"."NUM_ENDORSEMENT_NO") FROM "GEN_PROP_INFORMATION_TAB" "T" WHERE 
              "T"."TXT_POLICY_NO_CHAR"=:B2))
   9 - filter(TO_DATE(TO_CHAR(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_FROMDATE"),'dd/mm/yyyy')||' 
              '||TO_CHAR(TO_DATE("XXC"."TXT_POLICY_EFF_FROMHOUR",'hh24:mi'),'hh24:mi'),'dd/mm/yyyy hh24:mi')<=TO_DATE(:B1,'dd/mm/yyyy 
              hh24:mi') AND TO_DATE(TO_CHAR(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_TODATE"),'dd/mm/yyyy')||' 
              '||TO_CHAR(TO_DATE("XXC"."TXT_POLICY_EFF_TOHOUR",'hh24:mi'),'hh24:mi'),'dd/mm/yyyy hh24:mi')>=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') 
              AND TO_DATE(TO_CHAR(NVL("XXC"."DAT_ENDORSEMENT_EFF_DATE","XXC"."DAT_POLICY_EFF_FROMDATE"),'dd/mm/yyyy')||' 
              '||TO_CHAR(TO_DATE(NVL("XXC"."TXT_ENDORSEMENT_EFF_TIME","TXT_POLICY_EFF_FROMHOUR"),'hh24:mi'),'hh24:mi'),'dd/mm/yyyy 
              hh24:mi')<TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND NVL("XXC"."TXT_INTERNAL_CERTIFICATE_NO",'0')=NVL(:B2,'0'))
  10 - access("XXC"."TXT_POLICY_NO_CHAR"=:B1)
  15 - access("DI"."NUM_CLAIM_NO"=TO_NUMBER(:B4))
  16 - access("ITEM_1"="D"."NUM_CLAIM_NO" AND "D"."NUM_UPDATE_NO"="MAX(DI.NUM_UPDATE_NO)")
  20 - access("T"."TXT_POLICY_NO_CHAR"=:B1)

/* ****************************************Q2: Final restructured query for correct results******************************************************* */
EXPLAIN PLAN FOR
SELECT DISTINCT TABC.TXT_POLICY_NO_CHAR,
                TABC.TXT_CUSTOMER_NAME,
                TABC.NUM_COVERNOTE_NO,
                TABC.DAT_COVER_NOTE_DATE,
                TABC.TXT_COVER_NOTE_HOUR,
                TABC.DAT_POLICY_EFF_FROMDATE,
                TABC.TXT_POLICY_EFF_FROMHOUR,
                TABC.DAT_POLICY_EFF_TODATE,
                TABC.TXT_POLICY_EFF_TOHOUR,
                TABC.NUM_TOTAL_SI,
                TABC.TXT_CUSTOMER_ID,
                TABC.TXT_BRANCH_OFFICE_CODE,
                DECODE(UPPER(TABC.TXT_ACTIVE_FLAG),
                       'YES',
                       'ACTIVE',
                       'NOT ACTIVE'),
                TABC.NUM_NET_PREMIUM
  FROM (SELECT *
          FROM GEN_PROP_INFORMATION_TAB G
         WHERE (G.TXT_POLICY_NO_CHAR = :B3 AND
               NVL(G.TXT_INTERNAL_CERTIFICATE_NO, '0') = NVL(:B2, '0') AND
               G.NUM_ENDORSEMENT_NO =
               (SELECT MAX(XXC.NUM_ENDORSEMENT_NO)
                   FROM GEN_PROP_INFORMATION_TAB XXC
                  WHERE XXC.TXT_POLICY_NO_CHAR = G.TXT_POLICY_NO_CHAR
                    AND NVL(XXC.TXT_INTERNAL_CERTIFICATE_NO, '0') =
                        NVL(:B2, '0')
                    AND TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') BETWEEN
                        TO_DATE(CONCAT(XXC.DAT_POLICY_EFF_FROMDATE,
                                       CHR(32) || XXC.TXT_POLICY_EFF_FROMHOUR),
                                'dd/mm/yyyy hh24:mi') AND
                        TO_DATE(CONCAT(XXC.DAT_POLICY_EFF_TODATE,
                                       CHR(32) || XXC.TXT_POLICY_EFF_TOHOUR),
                                'dd/mm/yyyy hh24:mi')
                    AND TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') >
                        (TO_DATE(CONCAT(NVL(XXC.DAT_ENDORSEMENT_EFF_DATE,
                                            XXC.DAT_POLICY_EFF_FROMDATE),
                                        CHR(32) ||
                                        NVL(XXC.TXT_ENDORSEMENT_EFF_TIME,
                                            TXT_POLICY_EFF_FROMHOUR)),
                                 'dd/mm/yyyy hh24:mi'))) AND
               TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') BETWEEN
               TO_DATE(CONCAT(G.DAT_POLICY_EFF_FROMDATE,
                               CHR(32) || G.TXT_POLICY_EFF_FROMHOUR),
                        'dd/mm/yyyy hh24:mi') AND
               TO_DATE(CONCAT(G.DAT_POLICY_EFF_TODATE,
                               CHR(32) || G.TXT_POLICY_EFF_TOHOUR),
                        'dd/mm/yyyy hh24:mi'))
           AND ROWNUM = 1
           AND G.NUM_ENDORSEMENT_NO =
               (SELECT MAX(T.NUM_ENDORSEMENT_NO)
                  FROM GEN_PROP_INFORMATION_TAB T
                 WHERE T.TXT_POLICY_NO_CHAR = G.TXT_POLICY_NO_CHAR) /* if all conditions are TRUE then it will return some rows */
        UNION ALL
        SELECT *
          FROM GEN_PROP_INFORMATION_TAB G
         WHERE G.NUM_REFERENCE_NUMBER =
               (SELECT D.NUM_REFERENCE_NO
                  FROM GC_CLM_GEN_INFO D
                 WHERE D.NUM_UPDATE_NO =
                       (SELECT MAX(DI.NUM_UPDATE_NO)
                          FROM GC_CLM_GEN_INFO DI
                         WHERE DI.NUM_CLAIM_NO = D.NUM_CLAIM_NO
                           AND DI.NUM_CLAIM_NO = :B4))
           AND G.NUM_ENDORSEMENT_NO =
               (SELECT MAX(T.NUM_ENDORSEMENT_NO)
                  FROM GEN_PROP_INFORMATION_TAB T
                 WHERE T.TXT_POLICY_NO_CHAR = G.TXT_POLICY_NO_CHAR) /* if Q_1 returns 0 or more rows then this UNION ALL will suffice the OR condition in original query */
           AND ROWNUM = 1) TABC
 WHERE ROWNUM = 1;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

Plan hash value: 2384036150
 
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                               |     1 |    88 |    29   (4)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                                 |                               |     1 |    88 |    29   (4)| 00:00:01 |       |       |
|*  2 |   COUNT STOPKEY                              |                               |       |       |            |          |       |       |
|   3 |    VIEW                                      |                               |     2 |   176 |    28   (0)| 00:00:01 |       |       |
|   4 |     UNION-ALL                                |                               |       |       |            |          |       |       |
|*  5 |      COUNT STOPKEY                           |                               |       |       |            |          |       |       |
|*  6 |       FILTER                                 |                               |       |       |            |          |       |       |
|   7 |        NESTED LOOPS                          |                               |       |       |            |          |       |       |
|   8 |         NESTED LOOPS                         |                               |     1 |   128 |     9   (0)| 00:00:01 |       |       |
|   9 |          VIEW                                | VW_SQ_1                       |     1 |    30 |     5   (0)| 00:00:01 |       |       |
|  10 |           HASH GROUP BY                      |                               |     1 |    10 |     5   (0)| 00:00:01 |       |       |
|  11 |            TABLE ACCESS BY GLOBAL INDEX ROWID| GEN_PROP_INFORMATION_TAB      |     1 |    10 |     5   (0)| 00:00:01 | ROWID | ROWID |
|* 12 |             INDEX RANGE SCAN                 | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     3   (0)| 00:00:01 |       |       |
|* 13 |          INDEX RANGE SCAN                    | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     2   (0)| 00:00:01 |       |       |
|* 14 |         TABLE ACCESS BY GLOBAL INDEX ROWID   | GEN_PROP_INFORMATION_TAB      |     1 |    98 |     4   (0)| 00:00:01 | ROWID | ROWID |
|  15 |        SORT AGGREGATE                        |                               |     1 |    50 |            |          |       |       |
|* 16 |         TABLE ACCESS BY GLOBAL INDEX ROWID   | GEN_PROP_INFORMATION_TAB      |     1 |    50 |     5   (0)| 00:00:01 | ROWID | ROWID |
|* 17 |          INDEX RANGE SCAN                    | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     3   (0)| 00:00:01 |       |       |
|* 18 |      COUNT STOPKEY                           |                               |       |       |            |          |       |       |
|  19 |       NESTED LOOPS                           |                               |     1 |   114 |     9   (0)| 00:00:01 |       |       |
|  20 |        TABLE ACCESS BY GLOBAL INDEX ROWID    | GEN_PROP_INFORMATION_TAB      |     1 |   101 |     4   (0)| 00:00:01 | ROWID | ROWID |
|* 21 |         INDEX RANGE SCAN                     | PK_GEN_PROP_INFO              |     1 |       |     3   (0)| 00:00:01 |       |       |
|  22 |          NESTED LOOPS                        |                               |       |       |            |          |       |       |
|  23 |           NESTED LOOPS                       |                               |     1 |    51 |     5   (0)| 00:00:01 |       |       |
|  24 |            VIEW                              | VW_SQ_2                       |     1 |    26 |     3   (0)| 00:00:01 |       |       |
|  25 |             SORT GROUP BY                    |                               |     1 |    15 |     3   (0)| 00:00:01 |       |       |
|* 26 |              INDEX RANGE SCAN                | PK_GC_CLM_GEN_INFO            |     6 |    90 |     3   (0)| 00:00:01 |       |       |
|* 27 |            INDEX UNIQUE SCAN                 | PK_GC_CLM_GEN_INFO            |     1 |       |     1   (0)| 00:00:01 |       |       |
|  28 |           TABLE ACCESS BY GLOBAL INDEX ROWID | GC_CLM_GEN_INFO               |     1 |    25 |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 29 |        VIEW PUSHED PREDICATE                 | VW_SQ_3                       |     1 |    13 |     5   (0)| 00:00:01 |       |       |
|* 30 |         FILTER                               |                               |       |       |            |          |       |       |
|  31 |          SORT AGGREGATE                      |                               |     1 |    10 |            |          |       |       |
|  32 |           TABLE ACCESS BY GLOBAL INDEX ROWID | GEN_PROP_INFORMATION_TAB      |     3 |    30 |     5   (0)| 00:00:01 | ROWID | ROWID |
|* 33 |            INDEX RANGE SCAN                  | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM=1)
   5 - filter(ROWNUM=1)
   6 - filter("G"."NUM_ENDORSEMENT_NO"= (SELECT MAX("XXC"."NUM_ENDORSEMENT_NO") FROM "GEN_PROP_INFORMATION_TAB" "XXC" WHERE 
              "XXC"."TXT_POLICY_NO_CHAR"=:B1 AND TO_DATE(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_FROMDATE")||(' 
              '||"XXC"."TXT_POLICY_EFF_FROMHOUR"),'dd/mm/yyyy hh24:mi')<=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND 
              TO_DATE(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_TODATE")||(' '||"XXC"."TXT_POLICY_EFF_TOHOUR"),'dd/mm/yyyy 
              hh24:mi')>=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND TO_DATE(NVL("XXC"."DAT_ENDORSEMENT_EFF_DATE","XXC"."DAT_POLICY_EFF_FROMDATE")||(' 
              '||NVL("XXC"."TXT_ENDORSEMENT_EFF_TIME","TXT_POLICY_EFF_FROMHOUR")),'dd/mm/yyyy hh24:mi')<TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND 
              NVL("XXC"."TXT_INTERNAL_CERTIFICATE_NO",'0')=NVL(:B2,'0')))
  12 - access("T"."TXT_POLICY_NO_CHAR"=:B3)
  13 - access("G"."TXT_POLICY_NO_CHAR"=:B3)
  14 - filter(TO_DATE(INTERNAL_FUNCTION("G"."DAT_POLICY_EFF_FROMDATE")||(' '||"G"."TXT_POLICY_EFF_FROMHOUR"),'dd/mm/yyyy 
              hh24:mi')<=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND TO_DATE(INTERNAL_FUNCTION("G"."DAT_POLICY_EFF_TODATE")||(' 
              '||"G"."TXT_POLICY_EFF_TOHOUR"),'dd/mm/yyyy hh24:mi')>=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND 
              NVL("G"."TXT_INTERNAL_CERTIFICATE_NO",'0')=NVL(:B2,'0') AND "G"."NUM_ENDORSEMENT_NO"="MAX(T.NUM_ENDORSEMENT_NO)")
  16 - filter(TO_DATE(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_FROMDATE")||(' '||"XXC"."TXT_POLICY_EFF_FROMHOUR"),'dd/mm/yyyy 
              hh24:mi')<=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND TO_DATE(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_TODATE")||(' 
              '||"XXC"."TXT_POLICY_EFF_TOHOUR"),'dd/mm/yyyy hh24:mi')>=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND 
              TO_DATE(NVL("XXC"."DAT_ENDORSEMENT_EFF_DATE","XXC"."DAT_POLICY_EFF_FROMDATE")||(' 
              '||NVL("XXC"."TXT_ENDORSEMENT_EFF_TIME","TXT_POLICY_EFF_FROMHOUR")),'dd/mm/yyyy hh24:mi')<TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND 
              NVL("XXC"."TXT_INTERNAL_CERTIFICATE_NO",'0')=NVL(:B2,'0'))
  17 - access("XXC"."TXT_POLICY_NO_CHAR"=:B1)
  18 - filter(ROWNUM=1)
  21 - access("G"."NUM_REFERENCE_NUMBER"= (SELECT "D"."NUM_REFERENCE_NO" FROM "GC_CLM_GEN_INFO" "D", (SELECT 
              MAX("DI"."NUM_UPDATE_NO") "MAX(DI.NUM_UPDATE_NO)","DI"."NUM_CLAIM_NO" "ITEM_2" FROM "GC_CLM_GEN_INFO" "DI" WHERE 
              "DI"."NUM_CLAIM_NO"=TO_NUMBER(:B4) GROUP BY "DI"."NUM_CLAIM_NO") "VW_SQ_2" WHERE "D"."NUM_UPDATE_NO"="MAX(DI.NUM_UPDATE_NO)" AND 
              "ITEM_2"="D"."NUM_CLAIM_NO"))
  26 - access("DI"."NUM_CLAIM_NO"=TO_NUMBER(:B4))
  27 - access("ITEM_2"="D"."NUM_CLAIM_NO" AND "D"."NUM_UPDATE_NO"="MAX(DI.NUM_UPDATE_NO)")
  29 - filter("G"."NUM_ENDORSEMENT_NO"="MAX(T.NUM_ENDORSEMENT_NO)")
  30 - filter(COUNT(*)>0)
  33 - access("T"."TXT_POLICY_NO_CHAR"="G"."TXT_POLICY_NO_CHAR")


Table structure -

-- Create table
create table GEN_PROP_INFORMATION_TAB
(
  num_reference_number           NUMBER(15),
  dat_reference_date             DATE,
  num_trans_ref_number           NUMBER(15),
  dat_trans_reference_date       DATE,
  txt_customer_reference_no      VARCHAR2(30),
  dat_customer_reference_date    DATE,
  txt_office_code                VARCHAR2(10),
  txt_office_name                VARCHAR2(200),
  txt_branch_office_code         VARCHAR2(30),
  dat_proposal_date              DATE,
  txt_customer_id                VARCHAR2(30),
  txt_customer_name              VARCHAR2(400),
  txt_payer_customer_id          VARCHAR2(30),
  txt_payer_customer_name        VARCHAR2(400),
  txt_business_type              VARCHAR2(100),
  txt_sector                     VARCHAR2(30),
  txt_serv_tax_excempt_category  VARCHAR2(100),
  dat_policy_eff_fromdate        DATE,
  txt_policy_eff_fromhour        VARCHAR2(30),
  dat_policy_eff_todate          DATE,
  txt_policy_eff_tohour          VARCHAR2(30),
  num_department_code            NUMBER(10),
  num_product_code               NUMBER(10),
  txt_department_name            VARCHAR2(200),
  txt_product_name               VARCHAR2(200),
  num_polciy_no                  NUMBER(20),
  num_endorsement_no             NUMBER(20),
  dat_endorsement_eff_date       DATE,
  txt_endorsement_eff_time       VARCHAR2(30),
  num_total_si                   NUMBER(31,4),
  num_endorsement_si             NUMBER(31,4),
  txt_active_flag                VARCHAR2(30),
  txt_type_of_calculation        VARCHAR2(30),
  txt_method_of_calculation      VARCHAR2(30),
  txt_endorsement_wording        VARCHAR2(4000),
  txt_type_of_policy             VARCHAR2(30),
  txt_deal_id                    VARCHAR2(19),
  num_bank_id                    NUMBER(10),
  txt_covernote_no               VARCHAR2(25),
  txt_covernote_gen_type         VARCHAR2(30),
  txt_inward_no                  VARCHAR2(30),
  txt_cnfgtr_m_id                VARCHAR2(7),
  txt_product_index              VARCHAR2(7),
  txt_cnfgtr_status              VARCHAR2(10),
  num_covernote_no               NUMBER(10),
  txt_policy_no_char             VARCHAR2(30),
  txt_business_source_info       VARCHAR2(30),
  txt_retain_cancel_premium      VARCHAR2(50),
  txt_type_of_business           VARCHAR2(20),
  txt_old_covernote_no           VARCHAR2(15),
  txt_no_prev_insurance_flag     VARCHAR2(10) default 'False',
  txt_remarks                    VARCHAR2(2000),
  num_insert_trans_id            NUMBER(15),
  num_modify_trans_id            NUMBER(15),
  dat_insert_date                DATE,
  dat_modify_date                DATE,
  num_basic_premium              NUMBER(31,4),
  num_net_premium                NUMBER(31,4),
  num_service_tax                NUMBER(31,4),
  num_stamp_duty                 NUMBER(31,4),
  num_stamp_duty_aplicability_yn VARCHAR2(10),
  num_total_premium              NUMBER(31,4),
  num_terrorism_premium          NUMBER(31,4),
  num_endorsement_premium        NUMBER(31,4),
  num_endorsement_service_tax    NUMBER(31,4),
  num_endorsement_stamp_duty     NUMBER(31,4),
  num_endorsement_erf_amount     NUMBER(31,4),
  num_endorsement_terrorism_prem NUMBER(31,4),
  num_sp_pr_lt_factor            NUMBER(10,6),
  num_erf_amount                 NUMBER(31,4),
  num_net_od_premium             NUMBER(31,4),
  num_net_tp_premium             NUMBER(31,4),
  num_od_loading_rate            NUMBER(31,4),
  num_tp_loading_rate            NUMBER(31,4),
  num_child_reference_number     NUMBER(15),
  dat_child_reference_date       DATE,
  txt_location_code              VARCHAR2(30),
  txt_location_description       VARCHAR2(2000),
  txt_quotation_number           VARCHAR2(30),
  txt_ilpos_policy_number        VARCHAR2(30),
  txt_gen_il_flag                VARCHAR2(10) default 'GENISYS' not null,
  txt_policy_information1        VARCHAR2(200),
  txt_policy_information2        VARCHAR2(200),
  txt_cancellation_reason        VARCHAR2(50),
  txt_employee_name              VARCHAR2(200),
  txt_policy_information3        VARCHAR2(40),
  txt_policy_information4        VARCHAR2(30),
  txt_policy_information5        VARCHAR2(30),
  txt_policy_information6        VARCHAR2(200),
  txt_policy_information7        VARCHAR2(200),
  num_inst_exists                NUMBER default 0 not null,
  txt_renewal_rate_as_on         VARCHAR2(30),
  txt_renewal_notice_date        DATE,
  sm_code                        VARCHAR2(6),
  txt_relationship_type          VARCHAR2(100),
  txt_re_insurance_inward        VARCHAR2(100),
  txt_industry_type              VARCHAR2(100),
  txt_offline_quote_no           VARCHAR2(100),
  txt_quoted_uw                  VARCHAR2(100),
  txt_proposal_form_no           VARCHAR2(30),
  txt_referral_code              VARCHAR2(30),
  txt_plan_type                  VARCHAR2(30),
  txt_broker_code                VARCHAR2(200),
  txt_modeofoperation            VARCHAR2(40),
  num_declaration_no             NUMBER,
  txt_nonliable_start_date       DATE,
  txt_nonliable_end_date         DATE,
  num_request_type_code          NUMBER(5),
  txt_request_type_desc          VARCHAR2(200),
  num_fsl                        NUMBER(31,4),
  num_endorsement_fsl            NUMBER(31,4),
  txt_policyterm                 VARCHAR2(10),
  txt_cancellation_option        VARCHAR2(100),
  txt_cancellation_reason_comm   VARCHAR2(200),
  bln_certificate_vintage_car    VARCHAR2(5),
  txt_cover_type                 VARCHAR2(100),
  bln_dataentry_error            VARCHAR2(5),
  dat_dateof_endt_from_insured   DATE,
  txt_description                VARCHAR2(2000),
  bln_doc_for_comm_private       VARCHAR2(5),
  txt_doc_of_requisition         VARCHAR2(100),
  txt_doc_proof_others_specify   VARCHAR2(200),
  txt_doc_proof_for_ncb_comm     VARCHAR2(100),
  txt_double_ins_option          VARCHAR2(100),
  txt_ins_co_name                VARCHAR2(200),
  txt_ins_co_off_code_add        VARCHAR2(200),
  bln_is_seller_insured          VARCHAR2(5),
  dat_laid_up_from_date          DATE,
  dat_laid_up_to_date            DATE,
  bln_major_repair_renovation    VARCHAR2(5),
  bln_noc_financer               VARCHAR2(5),
  dat_policy_from                DATE,
  dat_policy_to                  DATE,
  dat_policy_issue_date          DATE,
  num_policy_no                  NUMBER(38),
  txt_reason_for_name_transfer   VARCHAR2(100),
  bln_usage_in_insured_premises  VARCHAR2(5),
  bln_valuation_report           VARCHAR2(5),
  txt_vehicle_laid_up_type       VARCHAR2(100),
  bln_org_certificate_surrender  VARCHAR2(5),
  txt_cover_note_used_flag       VARCHAR2(10),
  dat_cover_note_date            DATE,
  txt_cover_note_hour            VARCHAR2(30),
  dat_endorsement_date           DATE,
  txt_original_cert_flag         VARCHAR2(10),
  txt_doc_proof_for_ncb          VARCHAR2(200),
  txt_if_other                   VARCHAR2(200),
  txt_usaege_of_vehicle_flag     VARCHAR2(10),
  txt_doc_proof_for_commercial   VARCHAR2(10),
  txt_name_insurenceco           VARCHAR2(200),
  txt_officecode_address         VARCHAR2(200),
  txt_dblins_policyno            VARCHAR2(200),
  txt_dblins_covertype           VARCHAR2(200),
  dat_dblins_period_from         DATE,
  txt_dblins_period_to           DATE,
  dat_dblins_policy_issuedate    DATE,
  txt_reason_for_cancellation    DATE,
  txt_option_cancellation        DATE,
  txt_option_cancellation_ex     VARCHAR2(100),
  num_info_other_insurence       NUMBER(10),
  txt_dblins_option_cancellation VARCHAR2(150),
  num_original_cert_surrendered  NUMBER(10),
  num_cancellation_due_claim     NUMBER(10),
  bln_cancel_dueto_claim         VARCHAR2(5),
  txt_cover_note_usedf_lag       VARCHAR2(10),
  txt_policy_shedule             VARCHAR2(15),
  txt_ser_deal_id                VARCHAR2(19),
  txt_covernoteplace             VARCHAR2(30),
  txt_customertype               VARCHAR2(10),
  txt_displayofficecode          VARCHAR2(10),
  txt_ser_channel_id             VARCHAR2(19),
  num_endt_serial_no             NUMBER(2),
  txt_isncbapplicable            VARCHAR2(5),
  num_ncbper                     NUMBER,
  txt_typeoftransfer             VARCHAR2(30),
  num_gross_cover_premium        NUMBER(31,4),
  txt_periodoflaidof             VARCHAR2(10),
  num_transfer_fee               NUMBER(15) default 0,
  num_declaration_endt_sl_no     NUMBER default 0,
  txt_opt_for_calculation        VARCHAR2(30),
  dat_uform_from_date            DATE,
  dat_uform_to_date              DATE,
  num_inst_collect_mode          INTEGER default 0,
  num_is_floater                 NUMBER(1),
  bln_ismiapplicable             NUMBER(1),
  txt_claim_no                   VARCHAR2(20),
  num_inst_interval              NUMBER,
  num_edu_cess                   NUMBER(31,4),
  num_higher_edu_cess            NUMBER(31,4),
  num_endt_edu_cess              NUMBER(31,4),
  num_endt_higher_edu_cess       NUMBER(31,4),
  num_net_premium_curr           NUMBER(31,4),
  num_total_premium_curr         NUMBER(31,4),
  num_endorsement_premium_curr   NUMBER(31,4),
  txt_master_policy_no           VARCHAR2(30),
  num_mstr_pol_renewal_counter   NUMBER(10) default 0,
  txt_internal_certificate_no    VARCHAR2(10),
  num_percent_on_credit          NUMBER(3,2),
  txt_policy_on_credit           VARCHAR2(5)
)
partition by range (NUM_REFERENCE_NUMBER)
subpartition by hash (TXT_BRANCH_OFFICE_CODE)
(
  partition EARLIER_2013 values less than (201401000000000)
    tablespace INS
    pctfree 10
    initrans 30
    maxtrans 255
  (
    subpartition SYS_SUBP55 tablespace INS_PART1,
    subpartition SYS_SUBP56 tablespace INS_PART2,
    subpartition SYS_SUBP57 tablespace INS_PART3,
    subpartition SYS_SUBP58 tablespace INS_PART4,
    subpartition SYS_SUBP59 tablespace INS_PART5,
    subpartition SYS_SUBP60 tablespace INS_PART6,
    subpartition SYS_SUBP61 tablespace INS_PART1,
    subpartition SYS_SUBP62 tablespace INS_PART2,
    subpartition SYS_SUBP63 tablespace INS_PART3,
    subpartition SYS_SUBP64 tablespace INS_PART4,
    subpartition SYS_SUBP65 tablespace INS_PART5,
    subpartition SYS_SUBP66 tablespace INS_PART6,
    subpartition SYS_SUBP67 tablespace INS_PART1,
    subpartition SYS_SUBP68 tablespace INS_PART2,
    subpartition SYS_SUBP69 tablespace INS_PART3,
    subpartition SYS_SUBP70 tablespace INS_PART4,
    subpartition SYS_SUBP71 tablespace INS_PART5,
    subpartition SYS_SUBP72 tablespace INS_PART6,
    subpartition SYS_SUBP73 tablespace INS_PART1,
    subpartition SYS_SUBP74 tablespace INS_PART2,
    subpartition SYS_SUBP75 tablespace INS_PART3,
    subpartition SYS_SUBP76 tablespace INS_PART4,
    subpartition SYS_SUBP77 tablespace INS_PART5,
    subpartition SYS_SUBP78 tablespace INS_PART6,
    subpartition SYS_SUBP79 tablespace INS_PART1,
    subpartition SYS_SUBP80 tablespace INS_PART2,
    subpartition SYS_SUBP81 tablespace INS_PART3,
    subpartition SYS_SUBP82 tablespace INS_PART4,
    subpartition SYS_SUBP83 tablespace INS_PART5,
    subpartition SYS_SUBP84 tablespace INS_PART6
  ),
  partition NUM_REF_2014 values less than (201501000000000)
    tablespace INS
    pctfree 10
    initrans 30
    maxtrans 255
  (
    subpartition SYS_SUBP85 tablespace INS_PART1,
    subpartition SYS_SUBP86 tablespace INS_PART2,
    subpartition SYS_SUBP87 tablespace INS_PART3,
    subpartition SYS_SUBP88 tablespace INS_PART4,
    subpartition SYS_SUBP89 tablespace INS_PART5,
    subpartition SYS_SUBP90 tablespace INS_PART6,
    subpartition SYS_SUBP91 tablespace INS_PART1,
    subpartition SYS_SUBP92 tablespace INS_PART2,
    subpartition SYS_SUBP93 tablespace INS_PART3,
    subpartition SYS_SUBP94 tablespace INS_PART4,
    subpartition SYS_SUBP95 tablespace INS_PART5,
    subpartition SYS_SUBP96 tablespace INS_PART6,
    subpartition SYS_SUBP97 tablespace INS_PART1,
    subpartition SYS_SUBP98 tablespace INS_PART2,
    subpartition SYS_SUBP99 tablespace INS_PART3,
    subpartition SYS_SUBP100 tablespace INS_PART4,
    subpartition SYS_SUBP101 tablespace INS_PART5,
    subpartition SYS_SUBP102 tablespace INS_PART6,
    subpartition SYS_SUBP103 tablespace INS_PART1,
    subpartition SYS_SUBP104 tablespace INS_PART2,
    subpartition SYS_SUBP105 tablespace INS_PART3,
    subpartition SYS_SUBP106 tablespace INS_PART4,
    subpartition SYS_SUBP107 tablespace INS_PART5,
    subpartition SYS_SUBP108 tablespace INS_PART6,
    subpartition SYS_SUBP109 tablespace INS_PART1,
    subpartition SYS_SUBP110 tablespace INS_PART2,
    subpartition SYS_SUBP111 tablespace INS_PART3,
    subpartition SYS_SUBP112 tablespace INS_PART4,
    subpartition SYS_SUBP113 tablespace INS_PART5,
    subpartition SYS_SUBP114 tablespace INS_PART6
  ),
  partition NUM_REF_2015 values less than (201601000000000)
    tablespace INS
    pctfree 10
    initrans 30
    maxtrans 255
  (
    subpartition SYS_SUBP115 tablespace INS_PART1,
    subpartition SYS_SUBP116 tablespace INS_PART2,
    subpartition SYS_SUBP117 tablespace INS_PART3,
    subpartition SYS_SUBP118 tablespace INS_PART4,
    subpartition SYS_SUBP119 tablespace INS_PART5,
    subpartition SYS_SUBP120 tablespace INS_PART6,
    subpartition SYS_SUBP121 tablespace INS_PART1,
    subpartition SYS_SUBP122 tablespace INS_PART2,
    subpartition SYS_SUBP123 tablespace INS_PART3,
    subpartition SYS_SUBP124 tablespace INS_PART4,
    subpartition SYS_SUBP125 tablespace INS_PART5,
    subpartition SYS_SUBP126 tablespace INS_PART6,
    subpartition SYS_SUBP127 tablespace INS_PART1,
    subpartition SYS_SUBP128 tablespace INS_PART2,
    subpartition SYS_SUBP129 tablespace INS_PART3,
    subpartition SYS_SUBP130 tablespace INS_PART4,
    subpartition SYS_SUBP131 tablespace INS_PART5,
    subpartition SYS_SUBP132 tablespace INS_PART6,
    subpartition SYS_SUBP133 tablespace INS_PART1,
    subpartition SYS_SUBP134 tablespace INS_PART2,
    subpartition SYS_SUBP135 tablespace INS_PART3,
    subpartition SYS_SUBP136 tablespace INS_PART4,
    subpartition SYS_SUBP137 tablespace INS_PART5,
    subpartition SYS_SUBP138 tablespace INS_PART6,
    subpartition SYS_SUBP139 tablespace INS_PART1,
    subpartition SYS_SUBP140 tablespace INS_PART2,
    subpartition SYS_SUBP141 tablespace INS_PART3,
    subpartition SYS_SUBP142 tablespace INS_PART4,
    subpartition SYS_SUBP143 tablespace INS_PART5,
    subpartition SYS_SUBP144 tablespace INS_PART6
  ),
  partition NUM_REF_2016 values less than (201701000000000)
    tablespace INS
    pctfree 10
    initrans 30
    maxtrans 255
  (
    subpartition SYS_SUBP145 tablespace INS_PART1,
    subpartition SYS_SUBP146 tablespace INS_PART2,
    subpartition SYS_SUBP147 tablespace INS_PART3,
    subpartition SYS_SUBP148 tablespace INS_PART4,
    subpartition SYS_SUBP149 tablespace INS_PART5,
    subpartition SYS_SUBP150 tablespace INS_PART6,
    subpartition SYS_SUBP151 tablespace INS_PART1,
    subpartition SYS_SUBP152 tablespace INS_PART2,
    subpartition SYS_SUBP153 tablespace INS_PART3,
    subpartition SYS_SUBP154 tablespace INS_PART4,
    subpartition SYS_SUBP155 tablespace INS_PART5,
    subpartition SYS_SUBP156 tablespace INS_PART6,
    subpartition SYS_SUBP157 tablespace INS_PART1,
    subpartition SYS_SUBP158 tablespace INS_PART2,
    subpartition SYS_SUBP159 tablespace INS_PART3,
    subpartition SYS_SUBP160 tablespace INS_PART4,
    subpartition SYS_SUBP161 tablespace INS_PART5,
    subpartition SYS_SUBP162 tablespace INS_PART6,
    subpartition SYS_SUBP163 tablespace INS_PART1,
    subpartition SYS_SUBP164 tablespace INS_PART2,
    subpartition SYS_SUBP165 tablespace INS_PART3,
    subpartition SYS_SUBP166 tablespace INS_PART4,
    subpartition SYS_SUBP167 tablespace INS_PART5,
    subpartition SYS_SUBP168 tablespace INS_PART6,
    subpartition SYS_SUBP169 tablespace INS_PART1,
    subpartition SYS_SUBP170 tablespace INS_PART2,
    subpartition SYS_SUBP171 tablespace INS_PART3,
    subpartition SYS_SUBP172 tablespace INS_PART4,
    subpartition SYS_SUBP173 tablespace INS_PART5,
    subpartition SYS_SUBP174 tablespace INS_PART6
  ),
  partition NUM_REF_2017 values less than (201801000000000)
    tablespace INS
    pctfree 10
    initrans 30
    maxtrans 255
  (
    subpartition SYS_SUBP175 tablespace INS_PART1,
    subpartition SYS_SUBP176 tablespace INS_PART2,
    subpartition SYS_SUBP177 tablespace INS_PART3,
    subpartition SYS_SUBP178 tablespace INS_PART4,
    subpartition SYS_SUBP179 tablespace INS_PART5,
    subpartition SYS_SUBP180 tablespace INS_PART6,
    subpartition SYS_SUBP181 tablespace INS_PART1,
    subpartition SYS_SUBP182 tablespace INS_PART2,
    subpartition SYS_SUBP183 tablespace INS_PART3,
    subpartition SYS_SUBP184 tablespace INS_PART4,
    subpartition SYS_SUBP185 tablespace INS_PART5,
    subpartition SYS_SUBP186 tablespace INS_PART6,
    subpartition SYS_SUBP187 tablespace INS_PART1,
    subpartition SYS_SUBP188 tablespace INS_PART2,
    subpartition SYS_SUBP189 tablespace INS_PART3,
    subpartition SYS_SUBP190 tablespace INS_PART4,
    subpartition SYS_SUBP191 tablespace INS_PART5,
    subpartition SYS_SUBP192 tablespace INS_PART6,
    subpartition SYS_SUBP193 tablespace INS_PART1,
    subpartition SYS_SUBP194 tablespace INS_PART2,
    subpartition SYS_SUBP195 tablespace INS_PART3,
    subpartition SYS_SUBP196 tablespace INS_PART4,
    subpartition SYS_SUBP197 tablespace INS_PART5,
    subpartition SYS_SUBP198 tablespace INS_PART6,
    subpartition SYS_SUBP199 tablespace INS_PART1,
    subpartition SYS_SUBP200 tablespace INS_PART2,
    subpartition SYS_SUBP201 tablespace INS_PART3,
    subpartition SYS_SUBP202 tablespace INS_PART4,
    subpartition SYS_SUBP203 tablespace INS_PART5,
    subpartition SYS_SUBP204 tablespace INS_PART6
  ),
  partition NUM_REF_MAX values less than (MAXVALUE)
    tablespace INS
    pctfree 10
    initrans 30
    maxtrans 255
  (
    subpartition SYS_SUBP205 tablespace INS_PART1,
    subpartition SYS_SUBP206 tablespace INS_PART2,
    subpartition SYS_SUBP207 tablespace INS_PART3,
    subpartition SYS_SUBP208 tablespace INS_PART4,
    subpartition SYS_SUBP209 tablespace INS_PART5,
    subpartition SYS_SUBP210 tablespace INS_PART6,
    subpartition SYS_SUBP211 tablespace INS_PART1,
    subpartition SYS_SUBP212 tablespace INS_PART2,
    subpartition SYS_SUBP213 tablespace INS_PART3,
    subpartition SYS_SUBP214 tablespace INS_PART4,
    subpartition SYS_SUBP215 tablespace INS_PART5,
    subpartition SYS_SUBP216 tablespace INS_PART6,
    subpartition SYS_SUBP217 tablespace INS_PART1,
    subpartition SYS_SUBP218 tablespace INS_PART2,
    subpartition SYS_SUBP219 tablespace INS_PART3,
    subpartition SYS_SUBP220 tablespace INS_PART4,
    subpartition SYS_SUBP221 tablespace INS_PART5,
    subpartition SYS_SUBP222 tablespace INS_PART6,
    subpartition SYS_SUBP223 tablespace INS_PART1,
    subpartition SYS_SUBP224 tablespace INS_PART2,
    subpartition SYS_SUBP225 tablespace INS_PART3,
    subpartition SYS_SUBP226 tablespace INS_PART4,
    subpartition SYS_SUBP227 tablespace INS_PART5,
    subpartition SYS_SUBP228 tablespace INS_PART6,
    subpartition SYS_SUBP229 tablespace INS_PART1,
    subpartition SYS_SUBP230 tablespace INS_PART2,
    subpartition SYS_SUBP231 tablespace INS_PART3,
    subpartition SYS_SUBP232 tablespace INS_PART4,
    subpartition SYS_SUBP233 tablespace INS_PART5,
    subpartition SYS_SUBP234 tablespace INS_PART6
  )
);
-- Add comments to the columns 
comment on column GEN_PROP_INFORMATION_TAB.txt_retain_cancel_premium
  is 'RETAIN CANCELLATION PREMIUM';
comment on column GEN_PROP_INFORMATION_TAB.txt_type_of_business
  is 'Added Later';
comment on column GEN_PROP_INFORMATION_TAB.txt_policy_information3
  is 'Concatenated policy number for DTM';
comment on column GEN_PROP_INFORMATION_TAB.num_endt_serial_no
  is 'Endorsement serial number for UIIC';
comment on column GEN_PROP_INFORMATION_TAB.num_declaration_endt_sl_no
  is 'Endorsement serial no on declaration.';
comment on column GEN_PROP_INFORMATION_TAB.num_percent_on_credit
  is 'To store credit percent details';
comment on column GEN_PROP_INFORMATION_TAB.txt_policy_on_credit
  is 'To store credit policy details';
-- Create/Recreate indexes 
create index IDX2_GEN_PROP_INFORMATION_TAB on GEN_PROP_INFORMATION_TAB (TXT_ILPOS_POLICY_NUMBER)
  tablespace INDX
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 4M
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
create index IDX_GEN_PROP_EFF_FROM_DATE on GEN_PROP_INFORMATION_TAB (DAT_POLICY_EFF_FROMDATE)
  tablespace INDX
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_GEN_PROP_INFORMATION_TAB on GEN_PROP_INFORMATION_TAB (NUM_POLCIY_NO)
  tablespace INDX
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 3M
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
create index IDX_GEN_PROP_INFO_TAB_POLCHAR on GEN_PROP_INFORMATION_TAB (TXT_POLICY_NO_CHAR)
  tablespace INS
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 16M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_NUM_COVERNOTE_NO on GEN_PROP_INFORMATION_TAB (NUM_COVERNOTE_NO)
  tablespace INDX
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 256K
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
create index IDX_TXT_CUSTOMER_NAME on GEN_PROP_INFORMATION_TAB (TXT_CUSTOMER_NAME)
  tablespace INDX
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 3M
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
create index INDX_CUSTOMER_ID on GEN_PROP_INFORMATION_TAB (TXT_CUSTOMER_ID)
  tablespace INS
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDX_DAT_PROPOSAL_DATE on GEN_PROP_INFORMATION_TAB (DAT_PROPOSAL_DATE)
  tablespace INDX
  pctfree 10
  initrans 100
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDX_GEN_PROP_COVER_NOTE on GEN_PROP_INFORMATION_TAB (TXT_COVERNOTE_NO)
  tablespace INDX
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDX_GEN_PROP_INFO_TAB1 on GEN_PROP_INFORMATION_TAB (DAT_POLICY_EFF_TODATE, DAT_CUSTOMER_REFERENCE_DATE, NUM_POLCIY_NO)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDX_OFFICE_CD_1 on GEN_PROP_INFORMATION_TAB (TXT_OFFICE_CODE)
  tablespace INS
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDX_PRODUCT_CD on GEN_PROP_INFORMATION_TAB (NUM_PRODUCT_CODE)
  tablespace INS
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDX_RELATIONSHIP_TYPE on GEN_PROP_INFORMATION_TAB (TXT_RELATIONSHIP_TYPE)
  tablespace INS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDX_TXT_BRANCH_OFFICE_CODE on GEN_PROP_INFORMATION_TAB (TXT_BRANCH_OFFICE_CODE)
  tablespace INDX
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 2M
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
create index MODOPR_PRDCODE_IDX on GEN_PROP_INFORMATION_TAB (TXT_MODEOFOPERATION, NUM_PRODUCT_CODE)
  tablespace INDX
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table GEN_PROP_INFORMATION_TAB
  add constraint PK_GEN_PROP_INFO primary key (NUM_REFERENCE_NUMBER, DAT_REFERENCE_DATE)
  novalidate
  using index 
  tablespace INS
  pctfree 10
  initrans 60
  maxtrans 255
  storage
  (
    initial 16M
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate check constraints 
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;
alter table GEN_PROP_INFORMATION_TAB
  add check (TXT_NO_PREV_INSURANCE_FLAG in ('True', 'False'))
  novalidate;



Please let us know why cost increased after removing FTS on big table?

Regards,
Ashish Kumar Mahanta
Re: cost is more after releasing FTS on big table (partition table) [message #642320 is a reply to message #642319] Tue, 08 September 2015 01:55 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
If your statistics are correct, there is only one row in GEN_PROP_INFORMATION_TAB. And it has 180 partitions.
Do you think FTS or scan is significant? Any test you do is meaningless when you have no data.
Re: cost is more after releasing FTS on big table (partition table) [message #642322 is a reply to message #642320] Tue, 08 September 2015 02:12 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear John,

This table doesn't have only one row. It is fetching only one row from big table. Table size is 3.5GB.

Regards,
Ashish Kumar Mahanta
Re: cost is more after releasing FTS on big table (partition table) [message #642323 is a reply to message #642322] Tue, 08 September 2015 02:18 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Either you are correct or your statistics are correct, but not both. This line is unequivocal:
|   7 |        TABLE ACCESS FULL                | GEN_PROP_INFORMATION_TAB      |     1 |   108 |     2   (0)| 00:00:01 |     1 |   180 |
Do you really think that a full scan of a 3.5GB table should have an estimate like that? You need to gather statistics. All sorts of statistics.
Re: cost is more after releasing FTS on big table (partition table) [message #642326 is a reply to message #642323] Tue, 08 September 2015 03:00 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
The two queries aren't equivalent
The first has an unbracketed OR. It's basically this:
SELECT *
FROM (SELECT cols
      FROM GEN_PROP_INFORMATION_TAB
      WHERE <condition set 1>
      AND rownum = 1
      OR NUM_REFERENCE_NUMBER  = ....
      AND G.NUM_ENDORSEMENT_NO = ....
     )
WHERE rownum = 1

Which becomes
SELECT *
FROM (SELECT cols
      FROM GEN_PROP_INFORMATION_TAB
      WHERE (<condition set 1>
             AND rownum = 1
            )
      OR (NUM_REFERENCE_NUMBER  = ....
          AND G.NUM_ENDORSEMENT_NO = ....
         )
     )
WHERE rownum = 1

So the inner rownum check only applies to rows that match the first set of conditions and NUM_ENDORSEMENT_NO is only checked if rows don't match the first set of conditions.

The 2nd query boils down to this:

SELECT *
FROM (SELECT cols
      FROM GEN_PROP_INFORMATION_TAB
      WHERE <condition set 1>
      AND rownum = 1
      AND NUM_ENDORSEMENT_NO = ......
      UNION ALL
      SELECT cols
      FROM GEN_PROP_INFORMATION_TAB
      WHERE NUM_REFERENCE_NUMBER  = ....
      AND G.NUM_ENDORSEMENT_NO = ....
      AND rownum = 1
     )
WHERE rownum = 1


So this time NUM_ENDORSEMENT_NO is checked in both cases.

More than that your use of rownum makes no sense. If the inline view can return multiple rows without the rownum restriction then the use of rownum means you're returning a random row from all the ones that can match the rest of the where clause.
So before you start worrying about performance you need to write a query that actually makes sense.
Re: cost is more after releasing FTS on big table (partition table) [message #642327 is a reply to message #642326] Tue, 08 September 2015 03:19 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looking closer your rewritten version makes less sense..
both selects in the union have this:
AND G.NUM_ENDORSEMENT_NO =
               (SELECT MAX(T.NUM_ENDORSEMENT_NO)
                  FROM GEN_PROP_INFORMATION_TAB T
                 WHERE T.TXT_POLICY_NO_CHAR = G.TXT_POLICY_NO_CHAR)

But the first has an additional restriction on NUM_ENDORSEMENT_NO, I rather doubt both restrictions will be true simultaneously.
The original query was probably more what you want but you should always use brackets with ORs to make clear what goes with what. And your use of rownum is still highly suspect.



Also your date handling is a mess. In the original you've got this:
                        TO_DATE((TO_CHAR(XXC.DAT_POLICY_EFF_TODATE,
                                         'dd/mm/yyyy') || ' ' ||
                                TO_CHAR(TO_DATE(XXC.TXT_POLICY_EFF_TOHOUR,
                                                 'hh24:mi'),
                                         'hh24:mi')),
                                'dd/mm/yyyy hh24:mi')

I assume DAT_POLICY_EFF_TODATE is a date and TXT_POLICY_EFF_TOHOUR is a varchar. I which case the above is overkill.
It was altered in the rewrite to this:
TO_DATE(CONCAT(XXC.DAT_POLICY_EFF_TODATE,
                                       CHR(32) || XXC.TXT_POLICY_EFF_TOHOUR),
                                'dd/mm/yyyy hh24:mi')

Which gets rid of the unecessary conversion of TXT_POLICY_EFF_TOHOUR but removes a necessary conversion of DAT_POLICY_EFF_TODATE - forcing oracle to do an implicit conversion. If anyone changes the nls_date_format it'll break.
Should be this:
to_date((to_char(xxc.dat_policyy_eff_todate, 'dd/mm/yyyy') || xc.txt_policy_eff_tohour), 'dd/mm/yyyyhh24:mi')
Re: cost is more after releasing FTS on big table (partition table) [message #642329 is a reply to message #642322] Tue, 08 September 2015 03:59 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Actually, Ashish, it looks as though the developer needs to be fired. CM has already pointed out errors in the query, so I shall now point out errors in the DDL.
You are range partitioning on NUM_REFERENCE_NUMBER, and your primary key is (NUM_REFERENCE_NUMBER, DAT_REFERENCE_DATE). This makes no sense, unless you run all your queries with predicates such as NUM_REFERENCE_NUMBER BETWEEN.... and no-one does that on a surrogate primary key column. So your range partitioning is useless: you will never get any partition pruning. Incidentally, is NUM_REFERENCE_NUMBER meant to be unique? It may not be.
You are hash subpartitioning on TXT_BRANCH_OFFICE_CODE, and then creating a global index on that same column. Why? Do you want subpartition pruning and scanning, or indexed access? You can't have both.
In fact, your partitioning may be worse than useless. Your developer has realized this, and therefore created all his indexes as global indexes. This means you will be in a disastrous situation if you ever do any partition DDL. So your partitioning strategy is not only useless for performance, but seriously bad for availability.
If you re-create the table as non-partitioned and gather statistics, you may find that you get significantly better execution plans. Apart from anything else, 3.5GB is rather small for partitioning to have much benefit - even when done appropriately.
Re: cost is more after releasing FTS on big table (partition table) [message #642349 is a reply to message #642329] Tue, 08 September 2015 07:09 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear John,

Regarding the DDL, will take time for the implementation. Thanks for your valuable inputs.

Dear Cookiemonster,

As suggested by you, asked developer to re-write the query.

After doing the changes, plan doesn't change
SELECT DISTINCT TABC.TXT_POLICY_NO_CHAR,
       TABC.TXT_CUSTOMER_NAME,
       TABC.NUM_COVERNOTE_NO,
       TABC.DAT_COVER_NOTE_DATE,
       TABC.TXT_COVER_NOTE_HOUR,
       TABC.DAT_POLICY_EFF_FROMDATE,
       TABC.TXT_POLICY_EFF_FROMHOUR,
       TABC.DAT_POLICY_EFF_TODATE,
       TABC.TXT_POLICY_EFF_TOHOUR,
       TABC.NUM_TOTAL_SI,
       TABC.TXT_CUSTOMER_ID,
       TABC.TXT_BRANCH_OFFICE_CODE,
       DECODE(UPPER(TABC.TXT_ACTIVE_FLAG), 'YES', 'ACTIVE', 'NOT ACTIVE'),
       TABC.NUM_NET_PREMIUM
  FROM (SELECT *
          FROM GEN_PROP_INFORMATION_TAB G
         WHERE (G.TXT_POLICY_NO_CHAR = :B3 AND
               NVL(G.TXT_INTERNAL_CERTIFICATE_NO, '0') = NVL(:B2, '0') AND
               G.NUM_ENDORSEMENT_NO =
               (SELECT MAX(XXC.NUM_ENDORSEMENT_NO)
                   FROM GEN_PROP_INFORMATION_TAB XXC
                  WHERE XXC.TXT_POLICY_NO_CHAR = G.TXT_POLICY_NO_CHAR
                    AND NVL(XXC.TXT_INTERNAL_CERTIFICATE_NO, '0') =
                        NVL(:B2, '0')
                    AND TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') BETWEEN
                        TO_DATE(TO_CHAR(XXC.DAT_POLICY_EFF_FROMDATE,
                                        'dd/mm/yyyy') || ' ' ||
                                XXC.TXT_POLICY_EFF_FROMHOUR,
                                'dd/mm/yyyy hh24:mi') AND
                        TO_DATE(TO_CHAR(XXC.DAT_POLICY_EFF_TODATE,
                                        'dd/mm/yyyy') || ' ' ||
                                XXC.TXT_POLICY_EFF_TOHOUR,
                                'dd/mm/yyyy hh24:mi')
                    AND TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') >
                        (TO_DATE(TO_CHAR(NVL(XXC.DAT_ENDORSEMENT_EFF_DATE,
                                             XXC.DAT_POLICY_EFF_FROMDATE),
                                         'dd/mm/yyyy') || ' ' ||
                                 NVL(XXC.TXT_ENDORSEMENT_EFF_TIME,
                                     TXT_POLICY_EFF_FROMHOUR),
                                 'dd/mm/yyyy hh24:mi'))) AND
               TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') BETWEEN
               TO_DATE(TO_CHAR(G.DAT_POLICY_EFF_FROMDATE, 'dd/mm/yyyy') || ' ' ||
                        G.TXT_POLICY_EFF_FROMHOUR,
                        'dd/mm/yyyy hh24:mi') AND
               TO_DATE(TO_CHAR(G.DAT_POLICY_EFF_TODATE, 'dd/mm/yyyy') || ' ' ||
                        G.TXT_POLICY_EFF_TOHOUR,
                        'dd/mm/yyyy hh24:mi'))
           AND G.NUM_ENDORSEMENT_NO =
               (SELECT MAX(T.NUM_ENDORSEMENT_NO)
                  FROM GEN_PROP_INFORMATION_TAB T
                 WHERE T.TXT_POLICY_NO_CHAR = G.TXT_POLICY_NO_CHAR)
        UNION ALL
        SELECT *
          FROM GEN_PROP_INFORMATION_TAB G
         WHERE G.NUM_REFERENCE_NUMBER =
               (SELECT D.NUM_REFERENCE_NO
                  FROM GC_CLM_GEN_INFO D
                 WHERE D.NUM_UPDATE_NO =
                       (SELECT MAX(DI.NUM_UPDATE_NO)
                          FROM GC_CLM_GEN_INFO DI
                         WHERE DI.NUM_CLAIM_NO = D.NUM_CLAIM_NO
                           AND DI.NUM_CLAIM_NO = :B4))
           AND G.NUM_ENDORSEMENT_NO =
               (SELECT MAX(T.NUM_ENDORSEMENT_NO)
                  FROM GEN_PROP_INFORMATION_TAB T
                 WHERE T.TXT_POLICY_NO_CHAR = G.TXT_POLICY_NO_CHAR)) TABC
 WHERE ROWNUM = 1;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

Plan hash value: 1469018427
 
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                               |     1 |    88 |    29   (7)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                                  |                               |     1 |    88 |    29   (7)| 00:00:01 |       |       |
|*  2 |   COUNT STOPKEY                               |                               |       |       |            |          |       |       |
|   3 |    VIEW                                       |                               |     2 |   176 |    28   (4)| 00:00:01 |       |       |
|   4 |     UNION-ALL                                 |                               |       |       |            |          |       |       |
|*  5 |      FILTER                                   |                               |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                            |                               |       |       |            |          |       |       |
|   7 |        NESTED LOOPS                           |                               |     1 |   128 |     9   (0)| 00:00:01 |       |       |
|   8 |         VIEW                                  | VW_SQ_1                       |     1 |    30 |     5   (0)| 00:00:01 |       |       |
|   9 |          HASH GROUP BY                        |                               |     1 |    10 |     5   (0)| 00:00:01 |       |       |
|  10 |           TABLE ACCESS BY GLOBAL INDEX ROWID  | GEN_PROP_INFORMATION_TAB      |     1 |    10 |     5   (0)| 00:00:01 | ROWID | ROWID |
|* 11 |            INDEX RANGE SCAN                   | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     3   (0)| 00:00:01 |       |       |
|* 12 |         INDEX RANGE SCAN                      | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     2   (0)| 00:00:01 |       |       |
|* 13 |        TABLE ACCESS BY GLOBAL INDEX ROWID     | GEN_PROP_INFORMATION_TAB      |     1 |    98 |     4   (0)| 00:00:01 | ROWID | ROWID |
|  14 |       SORT AGGREGATE                          |                               |     1 |    50 |            |          |       |       |
|* 15 |        TABLE ACCESS BY GLOBAL INDEX ROWID     | GEN_PROP_INFORMATION_TAB      |     1 |    50 |     5   (0)| 00:00:01 | ROWID | ROWID |
|* 16 |         INDEX RANGE SCAN                      | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     3   (0)| 00:00:01 |       |       |
|* 17 |      FILTER                                   |                               |       |       |            |          |       |       |
|  18 |       HASH GROUP BY                           |                               |     1 |   123 |    14   (8)| 00:00:01 |       |       |
|  19 |        NESTED LOOPS                           |                               |       |       |            |          |       |       |
|  20 |         NESTED LOOPS                          |                               |     1 |   123 |     8   (0)| 00:00:01 |       |       |
|* 21 |          TABLE ACCESS BY GLOBAL INDEX ROWID   | GEN_PROP_INFORMATION_TAB      |     1 |   113 |     4   (0)| 00:00:01 | ROWID | ROWID |
|* 22 |           INDEX RANGE SCAN                    | PK_GEN_PROP_INFO              |     1 |       |     3   (0)| 00:00:01 |       |       |
|  23 |            NESTED LOOPS                       |                               |       |       |            |          |       |       |
|  24 |             NESTED LOOPS                      |                               |     1 |    51 |     5   (0)| 00:00:01 |       |       |
|  25 |              VIEW                             | VW_SQ_2                       |     1 |    26 |     3   (0)| 00:00:01 |       |       |
|  26 |               SORT GROUP BY                   |                               |     1 |    15 |     3   (0)| 00:00:01 |       |       |
|* 27 |                INDEX RANGE SCAN               | PK_GC_CLM_GEN_INFO            |     6 |    90 |     3   (0)| 00:00:01 |       |       |
|* 28 |              INDEX UNIQUE SCAN                | PK_GC_CLM_GEN_INFO            |     1 |       |     1   (0)| 00:00:01 |       |       |
|  29 |             TABLE ACCESS BY GLOBAL INDEX ROWID| GC_CLM_GEN_INFO               |     1 |    25 |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 30 |          INDEX RANGE SCAN                     | IDX_GEN_PROP_INFO_TAB_POLCHAR |     1 |       |     2   (0)| 00:00:01 |       |       |
|  31 |         TABLE ACCESS BY GLOBAL INDEX ROWID    | GEN_PROP_INFORMATION_TAB      |     1 |    10 |     4   (0)| 00:00:01 | ROWID | ROWID |
-----------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM=1)
   5 - filter("G"."NUM_ENDORSEMENT_NO"= (SELECT MAX("XXC"."NUM_ENDORSEMENT_NO") FROM "GEN_PROP_INFORMATION_TAB" "XXC" WHERE 
              "XXC"."TXT_POLICY_NO_CHAR"=:B1 AND TO_DATE(TO_CHAR(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_FROMDATE"),'dd/mm/yyyy')||' 
              '||"XXC"."TXT_POLICY_EFF_FROMHOUR",'dd/mm/yyyy hh24:mi')<=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND 
              TO_DATE(TO_CHAR(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_TODATE"),'dd/mm/yyyy')||' '||"XXC"."TXT_POLICY_EFF_TOHOUR",'dd/mm/yyyy 
              hh24:mi')>=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND TO_DATE(TO_CHAR(NVL("XXC"."DAT_ENDORSEMENT_EFF_DATE","XXC"."DAT_POLICY_EFF_FROMDATE"),
              'dd/mm/yyyy')||' '||NVL("XXC"."TXT_ENDORSEMENT_EFF_TIME","TXT_POLICY_EFF_FROMHOUR"),'dd/mm/yyyy hh24:mi')<TO_DATE(:B1,'dd/mm/yyyy 
              hh24:mi') AND NVL("XXC"."TXT_INTERNAL_CERTIFICATE_NO",'0')=NVL(:B2,'0')))
  11 - access("T"."TXT_POLICY_NO_CHAR"=:B3)
  12 - access("G"."TXT_POLICY_NO_CHAR"=:B3)
  13 - filter(TO_DATE(TO_CHAR(INTERNAL_FUNCTION("G"."DAT_POLICY_EFF_FROMDATE"),'dd/mm/yyyy')||' 
              '||"G"."TXT_POLICY_EFF_FROMHOUR",'dd/mm/yyyy hh24:mi')<=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND 
              TO_DATE(TO_CHAR(INTERNAL_FUNCTION("G"."DAT_POLICY_EFF_TODATE"),'dd/mm/yyyy')||' '||"G"."TXT_POLICY_EFF_TOHOUR",'dd/mm/yyyy 
              hh24:mi')>=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND NVL("G"."TXT_INTERNAL_CERTIFICATE_NO",'0')=NVL(:B2,'0') AND 
              "G"."NUM_ENDORSEMENT_NO"="MAX(T.NUM_ENDORSEMENT_NO)")
  15 - filter(TO_DATE(TO_CHAR(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_FROMDATE"),'dd/mm/yyyy')||' 
              '||"XXC"."TXT_POLICY_EFF_FROMHOUR",'dd/mm/yyyy hh24:mi')<=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND 
              TO_DATE(TO_CHAR(INTERNAL_FUNCTION("XXC"."DAT_POLICY_EFF_TODATE"),'dd/mm/yyyy')||' '||"XXC"."TXT_POLICY_EFF_TOHOUR",'dd/mm/yyyy 
              hh24:mi')>=TO_DATE(:B1,'dd/mm/yyyy hh24:mi') AND TO_DATE(TO_CHAR(NVL("XXC"."DAT_ENDORSEMENT_EFF_DATE","XXC"."DAT_POLICY_EFF_FROMDATE"),
              'dd/mm/yyyy')||' '||NVL("XXC"."TXT_ENDORSEMENT_EFF_TIME","TXT_POLICY_EFF_FROMHOUR"),'dd/mm/yyyy hh24:mi')<TO_DATE(:B1,'dd/mm/yyyy 
              hh24:mi') AND NVL("XXC"."TXT_INTERNAL_CERTIFICATE_NO",'0')=NVL(:B2,'0'))
  16 - access("XXC"."TXT_POLICY_NO_CHAR"=:B1)
  17 - filter("G"."NUM_ENDORSEMENT_NO"=MAX("T"."NUM_ENDORSEMENT_NO"))
  21 - filter("G"."TXT_POLICY_NO_CHAR" IS NOT NULL)
  22 - access("G"."NUM_REFERENCE_NUMBER"= (SELECT "D"."NUM_REFERENCE_NO" FROM "GC_CLM_GEN_INFO" "D", (SELECT MAX("DI"."NUM_UPDATE_NO") 
              "MAX(DI.NUM_UPDATE_NO)","DI"."NUM_CLAIM_NO" "ITEM_2" FROM "GC_CLM_GEN_INFO" "DI" WHERE "DI"."NUM_CLAIM_NO"=TO_NUMBER(:B4) GROUP BY 
              "DI"."NUM_CLAIM_NO") "VW_SQ_2" WHERE "D"."NUM_UPDATE_NO"="MAX(DI.NUM_UPDATE_NO)" AND "ITEM_2"="D"."NUM_CLAIM_NO"))
  27 - access("DI"."NUM_CLAIM_NO"=TO_NUMBER(:B4))
  28 - access("ITEM_2"="D"."NUM_CLAIM_NO" AND "D"."NUM_UPDATE_NO"="MAX(DI.NUM_UPDATE_NO)")
  30 - access("T"."TXT_POLICY_NO_CHAR"="G"."TXT_POLICY_NO_CHAR")
       filter("T"."TXT_POLICY_NO_CHAR" IS NOT NULL)


Please suggest, what more changes can be done on this?

Regards,
Ashish Kumar Mahanta
Re: cost is more after releasing FTS on big table (partition table) [message #642350 is a reply to message #642349] Tue, 08 September 2015 08:01 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the query still seems wrong to me - though I admit I don't know anything about your tables or what the query is actually supposed to do.
The first select still has two separate restrictions on NUM_ENDORSEMENT_NO which probably aren't going to be true simultaneously.
Your use of rownum is still problematic - the inline view will select x rows, the outer query will then pick one of those at random. Generally queries with rownum are written to prioritize some records from the possible matching set over others - a top-n query in other words - you've got nothing to do that. I imagine you want results from one of the two selects in the inline-view to take precedence over the other - if so you need something to make that happen.

Once you've sorted that out - the explain plan still thinks the table is basically empty. Are you generating the plan in the DB that actually has the 3.5GB of data or are you doing it in an empty DEV instance?
The plan must be generated in the DB with the real data with up to date stats, otherwise it's a waste of time.

And since John has pointed out that the partitioning approach is:
a) flawed
b) probably unnecessary for the amount of data
I'd suggest creating a copy of the table with all the data but without any partitioning and see what plan you get against that.
Re: cost is more after releasing FTS on big table (partition table) [message #642351 is a reply to message #642350] Tue, 08 September 2015 08:06 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
The other thing that'd probably help is getting rid of all the hour columns and just using date columns for date and time like oracle intended.
So this:
TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') BETWEEN
               TO_DATE(TO_CHAR(G.DAT_POLICY_EFF_FROMDATE, 'dd/mm/yyyy') || ' ' ||
                        G.TXT_POLICY_EFF_FROMHOUR,
                        'dd/mm/yyyy hh24:mi') AND
               TO_DATE(TO_CHAR(G.DAT_POLICY_EFF_TODATE, 'dd/mm/yyyy') || ' ' ||
                        G.TXT_POLICY_EFF_TOHOUR,
                        'dd/mm/yyyy hh24:mi'))

becomes:
TO_DATE(:B1, 'dd/mm/yyyy hh24:mi') BETWEEN G.DAT_POLICY_EFF_FROMDATE AND G.DAT_POLICY_EFF_TODATE


That'll either make it a bit faster or a lot faster depending on whether there are any indexes on the date columns oracle can make use of
Re: cost is more after releasing FTS on big table (partition table) [message #642371 is a reply to message #642351] Wed, 09 September 2015 00:55 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Cookiemonster,

Stats are upto date and plan is generating in production database only, which is of 3.5 gb of size.
Even though, its cost is only 2 for FTS on big table and cost goes increases while removing FTS.

Will check and ask developer to implement your valuable feedback and revert.

Thank you once again.

Regards,
Ashish Kumar Mahanta
Re: cost is more after releasing FTS on big table (partition table) [message #642372 is a reply to message #642371] Wed, 09 September 2015 01:35 Go to previous message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Ashish, you keep sayig that the statistics are correct. Your very first plan shows that the full scan of the GEN_PROP_INFORMATION_TAB table with no predicate will return one row. Well, how many rows are in that table?
Previous Topic: Query in active session for more than 2 hours
Next Topic: Would it be useful to enable Hyper-Threading in Oracle 12.1.0.2 on Linux
Goto Forum:
  


Current Time: Tue Mar 19 02:43:59 CDT 2024