Hi,
In below query one full table scan is happening since composite index is not utilizing .When i tried to use forcibly using INDEX hint cost is increasing hugely compare to full table scan .
Some one can advice how i can avoid full table scan
Oracle
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
indexs on full table scan
IBIS CWPT_YMGS NONUNIQUE VALID NORMAL N NO YEAR, MONTH, GROUP_NUMBER, SUB_GROUP
IBIS PK_PORTFOLIO_TEMPLATE UNIQUE VALID NORMAL N NO PORTFOLIO_TEMPLATE_ID
IBIS YEAR_MON_PORTFOLIO_TEMPLATE NONUNIQUE VALID NORMAL N NO YEAR, MONTH, SUBJECT, SUBJECT_OPTION, COMPONENT
SELECT cpt.PORTFOLIO_TEMPLATE_ID
--INTO :v_portfolio_template_id
FROM ibis.subject_component sc,
IBIS.cw_portfolio_template cpt
WHERE sc.year = :v_Year
AND sc.month = :v_Month
AND sc.paper_code = :v_Paper_Code
AND cpt.year = sc.year
AND cpt.month = sc.month
AND cpt.subject = sc.subject
AND cpt.subject_option = sc.subject_option
AND sc.lvl = cpt.lvl
AND sc.component = cpt.component;
Full table scan explan plan
line 1: SQLPLUS Command Skipped: SET LINESIZE 130
line 2: SQLPLUS Command Skipped: SET PAGESIZE 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1196036036
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 127 | 647 (7)| 00:00:10 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SUBJECT_COMPONENT | 1 | 76 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 127 | 647 (7)| 00:00:10 |
|* 3 | TABLE ACCESS FULL | CW_PORTFOLIO_TEMPLATE | 1 | 51 | 644 (7)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | SUBJECT_COMPONENT_PK | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SC"."PAPER_CODE"=:V_PAPER_CODE)
3 - filter("CPT"."LVL" IS NOT NULL AND "CPT"."SUBJECT_OPTION" IS NOT NULL AND
"CPT"."SUBJECT" IS NOT NULL AND "CPT"."YEAR"=TO_NUMBER(:V_YEAR) AND "CPT"."MONTH"=:V_MONTH)
4 - access("SC"."YEAR"=TO_NUMBER(:V_YEAR) AND "SC"."MONTH"=:V_MONTH AND
"CPT"."SUBJECT"="SC"."SUBJECT" AND "SC"."LVL"="CPT"."LVL" AND
"CPT"."SUBJECT_OPTION"="SC"."SUBJECT_OPTION" AND "SC"."COMPONENT"="CPT"."COMPONENT")
filter("CPT"."SUBJECT_OPTION"="SC"."SUBJECT_OPTION" AND
"SC"."COMPONENT"="CPT"."COMPONENT")
23 rows selected
forcibly using index explain pan
line 1: SQLPLUS Command Skipped: SET LINESIZE 130
line 2: SQLPLUS Command Skipped: SET PAGESIZE 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1405749015
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 127 | 4781 (1)| 00:01:13 |
|* 1 | TABLE ACCESS BY INDEX ROWID | CW_PORTFOLIO_TEMPLATE | 1 | 51 | 4777 (1)| 00:01:13 |
| 2 | NESTED LOOPS | | 1 | 127 | 4781 (1)| 00:01:13 |
| 3 | TABLE ACCESS BY INDEX ROWID| SUBJECT_COMPONENT | 1 | 76 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SUBJECT_COMPONENT_PAPER_CODE | 1 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | YEAR_MON_PORTFOLIO_TEMPLATE | 1 | | 4776 (1)| 00:01:13 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CPT"."LVL" IS NOT NULL AND "SC"."LVL"="CPT"."LVL")
4 - access("SC"."YEAR"=TO_NUMBER(:V_YEAR) AND "SC"."MONTH"=:V_MONTH AND
"SC"."PAPER_CODE"=:V_PAPER_CODE)
5 - access("CPT"."YEAR"=TO_NUMBER(:V_YEAR) AND "CPT"."MONTH"=:V_MONTH AND
"CPT"."SUBJECT"="SC"."SUBJECT" AND "CPT"."SUBJECT_OPTION"="SC"."SUBJECT_OPTION" AND
"SC"."COMPONENT"="CPT"."COMPONENT")
filter("CPT"."SUBJECT_OPTION" IS NOT NULL AND "CPT"."SUBJECT" IS NOT NULL)
23 rows selected