[Home] [Help]
35: /**
36: * Procedure : Groupstddev
37: * Type : Private
38: * Pre_reqs :
39: * Description : standard deviation on group
40: * Parameters :
41: * input parameters
42: * column_name : column name
43: * output parameters
40: * Parameters :
41: * input parameters
42: * column_name : column name
43: * output parameters
44: * standard deviation
45: */
46: FUNCTION Groupstddev( p_table_name IN varchar2, p_column_name IN varchar2 ) return number IS
47: v_statement varchar2(250);
48: g_stddev number;
160: * numcol : number of name
161: * output
162: * v_colname : array of sorted column name by selectivity
163: * o_sel : array of odinal selectivity
164: * std_dev : array of standard deviation
165: */
166: FUNCTION CAL_SELECTIVITY( p_table_name IN varchar2,
167: v_colname IN OUT NOCOPY name_varray,
168: o_sel IN OUT NOCOPY value_varray,
226:
227: -- sort
228: Bubble_SORT(o_sel, v_colname, std_dev ,flag, numcol);
229:
230: -- calculate standard deviation
231: /* JDOCHERT: 04/10/03: bug#2607186 */
232: /*for i IN 1..numcol-1 loop
233: if o_sel(i)*1.02 >= o_sel(i+1) then
234: -- for two similar selectivity
317: * Parameters :
318: * input JTF_TAE_QUAL_FACTORS.SQUAL_ALIAS
319: * outout JTF_TAE_QUAL_FACTORS.INPUT_SELECTIVITY is populated with selectivity order
320: JTF_TAE_QUAL_FACTORS.INPUT_ORDINAL_SELECTIVITY is populated with ordinal_selectivity
321: JTF_TAE_QUAL_FACTORS.INPUT_DEVIATION is populated with standard deviation
322: * return
323: * 0: no data in JTF_TAE_QUAL_FACTORS
324: * 1: success
325: */
814: --dbms_output.put_line(' prd_nulltae_mul_of_324347 = ' || prd_nulltae_mul_of_324347);
815: /* INDEX CREATION METHOD LOGIC */
816: IF (prd_nulltae_mul_of_4841 = 'Y') THEN
817: /* JDOCHERT: 08/04/02:
818: ** Get Static STANDARD INDEX Definition
819: ** for specific Qualifier Combinations:
820: ** 4841 = Postal Code + Country
821: */
822: --dbms_output.put_line(' rel product ' || prd.relation_product || ' special name');
842: --dbms_output.put_line(' rel product ' || prd.relation_product || ' special name');
843:
844: IF (lx_324347_idx_created = 'N') THEN
845: /* JDOCHERT: 08/04/02:
846: ** Get Static STANDARD INDEX Definition
847: ** for specific Qualifier Combinations:
848: ** 324347 = Customer Name Range + Postal Code + Country Combination
849: */
850: --dbms_output.put_line(' generating 324347 index N');
863: l_create_index_flag := 'N';
864: END IF;
865:
866: ELSE
867: --dbms_output.put_line(' rel product ' || prd.relation_product || ' standard name');
868: -- OIC processing
869: IF p_run_mode = 'OIC_TAP' THEN
870: /* ARPATEL 04/26/2004 GSCC error for hardcoded schema name */
871: --v_statement := 'CREATE INDEX JTF.' || prd.index_name || 'SC' || ' ON ' || p_table_name || '( ';
937: ** 324347 = Customer Name Range + Postal Code + Country Combination
938: */
939: /* NOTE: REVERSE INDEXES ARE ONLY NECCESSARY WHERE THERE ARE
940: ** CUSTOMER NAME RANGE LIKE '%ABC' values defined.
941: ** This is because leading '%' means that the STANDARD INDEX
942: ** on SQUAL_FC01, SQUAL_CHAR01,... will never be used.
943: */
944:
945: -- EIHSU 08/14/02: Set flags for determining if relation_prods
978: --dbms_output.put_line(' [R]rel product ' || idxCand.relation_product || ' special name');
979:
980: IF (lx_rev_324347_idx_created = 'N') THEN
981: /* JDOCHERT: 08/04/02:
982: ** Get Static STANDARD INDEX Definition
983: ** for specific Qualifier Combinations:
984: ** 324347 = Customer Name Range + Postal Code + Country Combination
985: */
986: --dbms_output.put_line(' [R]generating 324347 index Y');