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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #642323 is a reply to message #642322] |
Tue, 08 September 2015 02:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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
|
|
|
|
|
Goto Forum:
Current Time: Sat Aug 31 19:35:09 CDT 2024
|