Oracle Performance and Engineering
Random notes on database features, code, practice, optimization and techniques.Monday, August 21, 2006
Why correct datatype is SO important in schema design!
The following SQL was found to about 75% of the total load (logical I/O) from AWRStats report on a 10.1.0.4 database.
SELECT * FROM V WHERE report_type_id = :1;
On querying, I found the view looks like -
CREATE OR REPLACE VIEW V as
SELECT ROWNUM AS MIGRATOR_KEY
, PARENT_RT.report_type_id AS PARENT_MIGRATOR_KEY
, 'RT_SG_REVERSED' AS RESTRICTION_TYPE
, 'Reversed image of RT_SG restrictions' AS RESTRICTION_TYPE_NAME
, PARENT_RT.report_type_id
, PARENT_RT.report_type_name
, E.entity_name
, CHILD_SG.security_group_id
, CHILD_SG.security_group_name
FROM KNTA_Entities E
, KNTA_REPORT_TYPES PARENT_RT
, KNTA_SECURITY_GROUPS CHILD_SG
WHERE CHILD_SG.enabled_flag = 'Y'
AND CHILD_SG.security_group_id NOT IN (
SELECT ER.child_value
FROM KNTA_ENTITY_RESTRICTIONS ER
WHERE ER.restriction_type = 'RT_SG'
AND ER.parent_value = TO_CHAR( PARENT_RT.report_type_id )
)
AND PARENT_RT.report_view_access_code <> 'ALL'
AND E.entity_id = 15 -- security group entity id;
There are (to me) two things wrong with the highlighted part.
(a) It refers to an OUTER table within a NOT IN block.
(b) It compares VARCHAR2 with TO_CHAR(NUMBER)
So, I rewrote the view with a more fitting NOT EXISTS -
CREATE OR REPLACE VIEW v1 AS
SELECT ROWNUM AS MIGRATOR_KEY
, PARENT_RT.report_type_id AS PARENT_MIGRATOR_KEY
, 'RT_SG_REVERSED' AS RESTRICTION_TYPE
, 'Reversed image of RT_SG restrictions' AS RESTRICTION_TYPE_NAME
, PARENT_RT.report_type_id
, PARENT_RT.report_type_name
, E.entity_name
, CHILD_SG.security_group_id
, CHILD_SG.security_group_name
FROM KNTA_ENTITIES E
, KNTA_REPORT_TYPES PARENT_RT
, KNTA_SECURITY_GROUPS CHILD_SG
WHERE CHILD_SG.enabled_flag = 'Y'
AND NOT EXISTS (
SELECT ER.child_value
FROM KNTA_ENTITY_RESTRICTIONS ER
WHERE ER.restriction_type = 'RT_SG'
AND ER.parent_value = PARENT_RT.report_type_id
AND er.child_value = child_sg.security_group_id)
AND PARENT_RT.report_view_access_code <> 'ALL'
AND E.entity_id = 15 -- security group entity id;
And, changed the data types of PARENT_VALUE and CHILD_VALUE from VARCHAR2(80) to NUMBER (easy with 10g and all -- create a new field with desired datatype; update it to fill with values; drop the old column; rename the new column; rebuild the indexes).
The plan changed from (25000 LIO, 2 sec+)
Operation OBJECT Name ROWS Bytes Cost OBJECT Node IN/OUT PStart PStop
SELECT STATEMENT Optimizer MODE=ALL_ROWS 14 K 45912.0421527342
VIEW MITG.KNTA_RESTRICT_RT_SG_MIGE_V 14 K 4 M 45912.0421527342
COUNT
FILTER
NESTED LOOPS 14 K 1 M 332.450479722286
NESTED LOOPS 113 7 K 6.02673530593326
TABLE ACCESS BY INDEX ROWID MITG.KNTA_ENTITIES 1 20 1.00224632611494
INDEX UNIQUE SCAN MITG.KNTA_ENTITIES_U1 1 .00111934092369478
TABLE ACCESS FULL MITG.KNTA_REPORT_TYPES 113 4 K 5.02448897981832
TABLE ACCESS FULL MITG.KNTA_SECURITY_GROUPS 132 3 K 2.88870570279958
TABLE ACCESS FULL MITG.KNTA_ENTITY_RESTRICTIONS 1 34 5.11315481360784
to (doing just about 50LIO -- returning within a fraction of second)
Operation OBJECT Name ROWS Bytes Cost OBJECT Node IN/OUT PStart PStop
SELECT STATEMENT Optimizer MODE=ALL_ROWS 1 338.287484705643
VIEW MITG.V1 1 297 338.287484705643
COUNT
HASH JOIN RIGHT ANTI 1 127 338.287484705643
TABLE ACCESS FULL MITG.KNTA_ENTITY_RESTRICTIONS 224 7 K 5.10449694166571
MERGE JOIN CARTESIAN 14 K 1 M 332.450479722286
NESTED LOOPS 113 7 K 6.02673530593326
TABLE ACCESS BY INDEX ROWID MITG.KNTA_ENTITIES 1 20 1.00224632611494
INDEX UNIQUE SCAN MITG.KNTA_ENTITIES_U1 1 .00111934092369478
TABLE ACCESS FULL MITG.KNTA_REPORT_TYPES 113 4 K 5.02448897981832
BUFFER SORT 132 3 K 327.425990742468
TABLE ACCESS FULL MITG.KNTA_SECURITY_GROUPS 132 3 K 2.88870570279958
Call it Grapevine - and it will be the definitive history of Bollywood gossip.
PS: Make it a collaborative blog. Like our BCL membership ;-)
<< Home
Archives
January 2005 February 2005 March 2005 April 2005 May 2005 March 2006 April 2006 August 2006 September 2006 October 2006 January 2007 May 2008
