[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MIN(TO_NUMBER(v.value))
INTO l_dop
FROM v$parameter v
WHERE v.name = 'parallel_max_servers'
OR v.name = 'cpu_count';
v_statement := 'SELECT /*+ PARALLEL(JTF_TAE_TRANS_OBJS_GROUP, ' || l_dop ||') */ stddev(x) ';
v_statement := v_statement || ' FROM ( SELECT /*+ PARALLEL(' || p_table_name || ' ,'|| l_dop ||') */ COUNT(*) x ';
* Procedure : CAL_SELECTIVITY
* Type : Private
* Pre_reqs :
* Parameters :
* input
* table_name
* v_colname : array of column name
* numcol : number of name
* output
* v_colname : array of sorted column name by selectivity
* o_sel : array of odinal selectivity
* std_dev : array of standard deviation
*/
FUNCTION CAL_SELECTIVITY( p_table_name IN varchar2,
v_colname IN OUT NOCOPY name_varray,
o_sel IN OUT NOCOPY value_varray,
std_dev IN OUT NOCOPY value_varray,
flag IN OUT NOCOPY value_varray,
numcol IN integer) return number IS
v_cardinality number;
SELECT MIN(TO_NUMBER(v.value))
INTO l_dop
FROM v$parameter v
WHERE v.name = 'parallel_max_servers'
OR v.name = 'cpu_count';
v_statement := ' SELECT NVL(dt.NUM_ROWS,1) FROM dba_tables dt ' ||
' WHERE dt.owner = UPPER(:b_schema) ' ||
' AND dt.table_name = UPPER(:b_table_name) ';
' SELECT 100 - (NVL(dtc.num_distinct,1)*100/:b_cardinality) ' ||
' FROM dba_tab_columns dtc ' ||
' WHERE dtc.owner = UPPER(:b_schema) ' ||
' AND dtc.table_name = UPPER(:b_table_name) ' ||
' AND dtc.column_name = UPPER(:b_col_name) ';
ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CAL_SELECTIVITY: [1]: Call to GROUPSTDDEV failed.';
ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CAL_SELECTIVITY: [2]: Call to GROUPSTDDEV failed.';
ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CAL_SELECTIVITY: [END] NO_DATA_FOUND: ' ||
SQLERRM;
ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.CAL_SELECTIVITY: [END] OTHERS: ' ||
SQLERRM;
END CAL_SELECTIVITY;
* Procedure : SELECTIVITY
* Type : Private
* Pre_reqs :
* Description :
* Parameters :
* input JTF_TAE_QUAL_FACTORS.SQUAL_ALIAS
* outout JTF_TAE_QUAL_FACTORS.INPUT_SELECTIVITY is populated with selectivity order
JTF_TAE_QUAL_FACTORS.INPUT_ORDINAL_SELECTIVITY is populated with ordinal_selectivity
JTF_TAE_QUAL_FACTORS.INPUT_DEVIATION is populated with standard deviation
* return
* 0: no data in JTF_TAE_QUAL_FACTORS
* 1: success
*/
FUNCTION SELECTIVITY(p_TABLE_NAME IN VARCHAR2) return number IS
errbuf varchar2(3000);
SELECT
DISTINCT A.TAE_COL_MAP sqname,
A.INPUT_DEVIATION dev,
A.INPUT_ORDINAL_SELECTIVITY ord_sele,
A.INPUT_SELECTIVITY sele,
decode(A.UPDATE_SELECTIVITY_FLAG,'Y',1,0) flag
FROM JTF_TAE_QUAL_FACTORS A
WHERE A.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
AND A.TAE_COL_MAP is not null ;
if CAL_SELECTIVITY(p_table_name, col_name, o_sel, std_dev, flag, i-1) = 0
then return 0;
UPDATE JTF_TAE_QUAL_FACTORS
SET INPUT_SELECTIVITY = i,
INPUT_ORDINAL_SELECTIVITY = o_sel(i),
INPUT_DEVIATION = std_dev(i)
WHERE TAE_COL_MAP = col_name(i);
ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.SELECTIVITY: [END] NO_DATA_FOUND: ' ||
SQLERRM;
ERRBUF := 'JTF_TAE_INDEX_CREATION_PVT.SELECTIVITY: [END] OTHERS: ' ||
SQLERRM;
END SELECTIVITY;
select din.index_name, din.owner
from dba_indexes din
where din.table_name = p_table_name
and din.table_owner = p_owner
and exists (
select 1
from dba_ind_columns dic
where din.index_name = dic.index_name
and dic.index_owner = p_owner
and dic.column_name = 'WORKER_ID'
and dic.column_position = 1 );
select column_name, column_position
from dba_ind_columns
where index_name = p_index_name
and index_owner = p_owner
and column_position <> 1
order by column_position;
SELECT P.qual_product_id qual_product_id,
/* JDOCHERT: 08/04/02: Added RELATION_PRODUCT to Cursor */
p.RELATION_PRODUCT RELATION_PRODUCT,
MAX(p.index_name) index_name,
MAX(p.first_char_flag) first_char_flag,
COUNT(r.qual_factor_id) cartesian_terr_X_factors
FROM jtf_terr_denorm_rules_all d
,jtf_terr_qtype_usgs_all jtqu
,jtf_qual_type_usgs_all jqtu
,JTF_TAE_QUAL_PRODUCTS P
, JTF_TAE_QUAL_PROD_FACTORS R
WHERE /* ARPATEL 01/06/2004 bug#3337382: use qual_relation_factor in jtf_terr_qtype_usgs_all */
--d.qual_relation_product = p.relation_product
jtqu.qual_relation_product = p.relation_product
AND jqtu.source_id = d.source_id
AND jqtu.qual_type_id = p.trans_object_type_id
AND d.terr_id = jtqu.terr_id
AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
--and d.terr_id = d.related_terr_id
/* ARPATEL: 12/09/2003 denorm_rules_all is no longer striped by TX id for Oracle Sales */
--AND d.qual_type_id = p.trans_object_type_id
AND d.source_id = p.source_id
AND P.qual_product_id = R.qual_product_id
AND P.BUILD_INDEX_FLAG = 'Y'
AND p.TRANS_OBJECT_TYPE_ID = p_trans_object_type_id
AND P.SOURCE_ID = p_source_id
GROUP BY P.qual_product_id, p.RELATION_PRODUCT
ORDER BY cartesian_terr_X_factors DESC, first_char_flag;
SELECT distinct TAE_COL_MAP, J2.INPUT_SELECTIVITY
FROM JTF_TAE_QUAL_PRODUCTS J3,
JTF_TAE_QUAL_FACTORS J2,
JTF_TAE_QUAL_PROD_FACTORS J1
WHERE J1.qual_product_id = pid
and J1.qual_product_id = J3.qual_product_id
and J1.qual_factor_id = J2.qual_factor_id
-- and J1.qual_usg_id = J2.qual_usg_id
and J2.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
ORDER BY J2.INPUT_SELECTIVITY;
SELECT P.qual_product_id qual_product_id,
/* JDOCHERT: 08/04/02: Added RELATION_PRODUCT to Cursor */
p.RELATION_PRODUCT RELATION_PRODUCT,
p.index_name index_name,
p.first_char_flag first_char_flag,
rownum index_counter
FROM JTF_TAE_QUAL_PRODUCTS P
WHERE P.BUILD_INDEX_FLAG = 'Y'
AND P.FIRST_CHAR_FLAG = 'Y'
AND p.TRANS_OBJECT_TYPE_ID = p_trans_object_type_id
AND P.SOURCE_ID = p_source_id;
select f.tae_col_map, qual_usg_id, input_selectivity
from jtf_tae_qual_prod_factors pf,
jtf_tae_qual_factors f
where qual_product_id = pid
and f.qual_factor_id = pf.qual_factor_id
and f.tae_col_map is not null
order by input_selectivity desc;
select NON_NULL_TAE_COL_MAPS, RELATION_PRODUCT
from (
select count(*) NON_NULL_TAE_COL_MAPS,
p.qual_product_id QUAL_PRODUCT_ID,
p.relation_product RELATION_PRODUCT
from JTF_TAE_QUAL_products p,
JTF_TAE_QUAL_prod_factors pf,
JTF_TAE_QUAL_FACTORS f
where p.qual_product_id = pf.qual_product_id
and pf.qual_factor_id = f.qual_factor_id
and p.source_id = p_source_id
and p.trans_object_type_id = p_trans_object_type_id
and p.relation_product > 0
and tae_col_map is not null
and p.relation_product = cp_product
group by p.qual_product_id, p.relation_product
)
where NON_NULL_TAE_COL_MAPS = cp_non_null_tae_col_maps;
SELECT MIN(TO_NUMBER(v.value))
INTO l_dop
FROM v$parameter v
WHERE v.name = 'parallel_max_servers'
OR v.name = 'cpu_count';
SELECT i.tablespace, i.index_tablespace, u.oracle_username
INTO l_table_tablespace, l_idx_tablespace, l_ora_username
FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
WHERE a.application_short_name = 'JTF'
AND a.application_id = i.application_id
AND u.oracle_id = i.oracle_id;
** 3. return aidx.owner in SELECT */
Cursor getIndexList(p_jtf_schema varchar2) IS
SELECT aidx.owner, aidx.INDEX_NAME
FROM DBA_INDEXES aidx
WHERE aidx.table_name = p_table_name
AND aidx.table_owner = p_jtf_schema
AND aidx.index_name not in ('JTF_TAE_TN1002_CASE_N1W', 'JTF_TAE_TN1003_CASE_N1W', 'JTF_TAE_TN1004_CASE_N1W');
SELECT u.oracle_username
INTO l_ora_username
FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
WHERE a.application_short_name = 'JTF'
AND a.application_id = i.application_id
AND u.oracle_id = i.oracle_id;
SELECT MIN(TO_NUMBER(v.value))
INTO l_dop
FROM v$parameter v
WHERE v.name = 'parallel_max_servers'
OR v.name = 'cpu_count';