[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,
225:
226: -- sort
227: Bubble_SORT(o_sel, v_colname, std_dev ,flag, numcol);
228:
229: -- calculate standard deviation
230: /* JDOCHERT: 04/10/03: bug#2607186 */
231: /*for i IN 1..numcol-1 loop
232: if o_sel(i)*1.02 >= o_sel(i+1) then
233: -- for two similar selectivity
316: * Parameters :
317: * input JTF_TAE_QUAL_FACTORS.SQUAL_ALIAS
318: * outout JTF_TAE_QUAL_FACTORS.INPUT_SELECTIVITY is populated with selectivity order
319: JTF_TAE_QUAL_FACTORS.INPUT_ORDINAL_SELECTIVITY is populated with ordinal_selectivity
320: JTF_TAE_QUAL_FACTORS.INPUT_DEVIATION is populated with standard deviation
321: * return
322: * 0: no data in JTF_TAE_QUAL_FACTORS
323: * 1: success
324: */
813: --dbms_output.put_line(' prd_nulltae_mul_of_324347 = ' || prd_nulltae_mul_of_324347);
814: /* INDEX CREATION METHOD LOGIC */
815: IF (prd_nulltae_mul_of_4841 = 'Y') THEN
816: /* JDOCHERT: 08/04/02:
817: ** Get Static STANDARD INDEX Definition
818: ** for specific Qualifier Combinations:
819: ** 4841 = Postal Code + Country
820: */
821: --dbms_output.put_line(' rel product ' || prd.relation_product || ' special name');
841: --dbms_output.put_line(' rel product ' || prd.relation_product || ' special name');
842:
843: IF (lx_324347_idx_created = 'N') THEN
844: /* JDOCHERT: 08/04/02:
845: ** Get Static STANDARD INDEX Definition
846: ** for specific Qualifier Combinations:
847: ** 324347 = Customer Name Range + Postal Code + Country Combination
848: */
849: --dbms_output.put_line(' generating 324347 index N');
862: l_create_index_flag := 'N';
863: END IF;
864:
865: ELSE
866: --dbms_output.put_line(' rel product ' || prd.relation_product || ' standard name');
867: -- OIC processing
868: IF p_run_mode = 'OIC_TAP' THEN
869: /* ARPATEL 04/26/2004 GSCC error for hardcoded schema name */
870: --v_statement := 'CREATE INDEX JTF.' || prd.index_name || 'SC' || ' ON ' || p_table_name || '( ';
936: ** 324347 = Customer Name Range + Postal Code + Country Combination
937: */
938: /* NOTE: REVERSE INDEXES ARE ONLY NECCESSARY WHERE THERE ARE
939: ** CUSTOMER NAME RANGE LIKE '%ABC' values defined.
940: ** This is because leading '%' means that the STANDARD INDEX
941: ** on SQUAL_FC01, SQUAL_CHAR01,... will never be used.
942: */
943:
944: -- EIHSU 08/14/02: Set flags for determining if relation_prods
977: --dbms_output.put_line(' [R]rel product ' || idxCand.relation_product || ' special name');
978:
979: IF (lx_rev_324347_idx_created = 'N') THEN
980: /* JDOCHERT: 08/04/02:
981: ** Get Static STANDARD INDEX Definition
982: ** for specific Qualifier Combinations:
983: ** 324347 = Customer Name Range + Postal Code + Country Combination
984: */
985: --dbms_output.put_line(' [R]generating 324347 index Y');