Lately I was working on a query which was fetching like 2million rows and was running for a few hours .I was able to tune this query to give out results in less than 15 mins but i still have questions regarding the explain plan of the old un-tuned query.I am not putting down my tuned SQL as this has got nothing to do with my question.
This was the query I was working on
SELECT ppl_cd, 'PPL-CD', NULL, ppl_org_cd, TO_NUMBER (NULL), ppl_fttl_cd,
ppl_fst_nm, ppl_mid_init, ppl_lst_nm, ppl_infrml_fst_nm, ppl_suffix_nm,
ppl_srch_nm, ppl_lang_cd, ppl_gender, NULL ext_job_role,
ppl_job_title_nm, 'Y', NVL (ppl_prim_cont_flg, 'Y'),
TO_CHAR (ppl_add_dt, 'YYYYMMDD') last_modified_date,
DECODE (ppl_active_flg, 'N', 'S', '') status_indicator,
(SELECT DISTINCT pcat_pcs_cd
FROM ppl_categories
WHERE pcat_ppl_cd = ppl_cd
AND pcat_pcs_cd IN (210, 211)) user_class_cd,
NULL
FROM people
/
I have the explain plan on TOAD and therefore I am typing it manually
SELECT STATEMENT
HASH UNIQUE
TABLE ACCESS BY INDEX ROWID TABLE PINNACLE.PPL_CATEGORIES
INDEX RANGE SCAN INDEX PINNACLE.PCAT_PPL_CD
TABLE ACCESS FULL TABLE PINNACLE.PEOPLE
My question is the 1st operation is
INDEX RANGE SCAN INDEX PINNACLE.PCAT_PPL_CD
There is no where condition on this in the query.All there I can see is a join(pcat_ppl_cd = ppl_cd).So where is it getting the values for doing the index range scan.To me , it looks like there should be a FTS on people table to drive this index scan but that is not shown in the above explain plan.
Can you please throw some light on this?
Regards
Gautham
You have a query:
select <columns>,
<scalar subquery from other_table where column = TABLE.PPL_CD>
from table;
that is processed like:
for x in ( select * from table )
loop
select ... into ....
from other_table
where column = :BIND;
output record
end loopthere is no join here. The HASH UNIQUE step is your DISTINCT clause.
You are just seeing the plan for your scalar subquery, consider:
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> sELECT ppl_cd, 'PPL-CD', NULL, ppl_org_cd, TO_NUMBER (NULL), ppl_fttl_cd,
2 ppl_fst_nm, ppl_mid_init, ppl_lst_nm, ppl_infrml_fst_nm, ppl_suffix_nm,
3 ppl_srch_nm, ppl_lang_cd, ppl_gender, NULL ext_job_role,
4 ppl_job_title_nm, 'Y', NVL (ppl_prim_cont_flg, 'Y'),
5 TO_CHAR (ppl_add_dt, 'YYYYMMDD') last_modified_date,
6 DECODE (ppl_active_flg, 'N', 'S', '') status_indicator,
7 (SELECT DISTINCT pcat_pcs_cd
8 FROM ppl_categories
9 WHERE pcat_ppl_cd = ppl_cd
10 AND pcat_pcs_cd IN (210, 211)) user_class_cd,
11 NULL
12 FROM people
13 /
Execution Plan
----------------------------------------------------------
Plan hash value: 820210871
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000K| 190M|
| 1 | HASH UNIQUE | | 200 | 5200 |
|* 2 | TABLE ACCESS BY INDEX ROWID| PPL_CATEGORIES | 200 | 5200 |
|* 3 | INDEX RANGE SCAN | PCAT_PPL_CD_IDX | 8000 | |
| 4 | TABLE ACCESS FULL | PEOPLE | 2000K| 190M|
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PCAT_PCS_CD"=210 OR "PCAT_PCS_CD"=211)
3 - access("PCAT_PPL_CD"=:B1)
ops$tkyte%ORA10GR2> variable ppl_cd number
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT DISTINCT pcat_pcs_cd
2 FROM ppl_categories
3 WHERE pcat_ppl_cd = :ppl_cd
4 AND pcat_pcs_cd IN (210, 211);
Execution Plan
----------------------------------------------------------
Plan hash value: 2133281571
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 5200 |
| 1 | HASH UNIQUE | | 200 | 5200 |
|* 2 | TABLE ACCESS BY INDEX ROWID| PPL_CATEGORIES | 200 | 5200 |
|* 3 | INDEX RANGE SCAN | PCAT_PPL_CD_IDX | 8000 | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PCAT_PCS_CD"=210 OR "PCAT_PCS_CD"=211)
3 - access("PCAT_PPL_CD"=TO_NUMBER(:PPL_CD))
ops$tkyte%ORA10GR2> set autotrace off
A scalar subquery would be entirely inappropriate here if ppl_cd is nearly unique (which is probably is, sounds like it could be a primary key even)
hopefully, your rewritten query looks like:
sELECT ppl_cd, 'PPL-CD', NULL, ppl_org_cd, TO_NUMBER (NULL), ppl_fttl_cd,
ppl_fst_nm, ppl_mid_init, ppl_lst_nm, ppl_infrml_fst_nm, ppl_suffix_nm,
ppl_srch_nm, ppl_lang_cd, ppl_gender, NULL ext_job_role,
ppl_job_title_nm, 'Y', NVL (ppl_prim_cont_flg, 'Y'),
TO_CHAR (ppl_add_dt, 'YYYYMMDD') last_modified_date,
DECODE (ppl_active_flg, 'N', 'S', '') status_indicator,
x.pcat_pcs_cd user_class_cd,
NULL
FROM people,
(SELECT distinct pcat_ppl_cd, pcat_pcs_cd
FROM ppl_categories
WHERE pcat_pcs_cd IN (210, 211)) x
where x.pcat_ppl_cd = people.ppl_cd
/
and only has the DISTINCT if in fact distinct is necessary