Hi all,
I have following query
SELECT SPT_SPCA_CAT||SPT_PGM_CODE SPORT_CODE,
Spt_Spca_Cat,
Spt_Pgm_Code,
Est_DT Start_Date,
Est_TM Start_Time,
EST_DURA DURATION,
Bc_DtTm, --CR_Start_DT,
Bc_End_DtTm, --CR_End_DT,
LIFE_CYCLE_STATUS STATUS,
EP_NUM_UNIQ,
Sched_CHG_IND,
Chg_Ind,
EP_Number, Log_Number,
Log_Network,
Simulcast_EP_Number,
Ep_Id,
EDT_IND,
Part_No
FROM V_EPISODE_AIRINGS
WHERE (Bc_DtTm BETWEEN Dbpk_bc.Add_Time(1, 20020812000000, 0)
AND Dbpk_bc.Add_Time(1, 20020812086399, -1 )
OR
Bc_End_DtTm BETWEEN Dbpk_bc.Add_Time(1, 20020812000000, 1)
AND Dbpk_bc.Add_Time(1, 20020812086399, -1) )
AND NVL(DID_NOT_AIR_IND,'N') = 'N'
AND log_number = 1
AND Repeat_Id >= 0
AND LIFE_CYCLE_STATUS IN (2,3,4)
ORDER BY Bc_DtTm, Est_Dura;
Where the v_episode_airings is a view with a union.
Under rule based optimizer I get this explain plan ...
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows |
Bytes | Cost |
----------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | |
| |
| 1 | SORT ORDER BY | | |
| |
| 2 | VIEW | V_EPISODE_AIRINGS | |
| |
| 3 | SORT UNIQUE | | |
| |
| 4 | UNION-ALL | | |
| |
| 5 | CONCATENATION | | |
| |
|* 6 | TABLE ACCESS BY INDEX ROWID| EPISODE_AIRINGS | |
| |
|* 7 | INDEX RANGE SCAN | BC_END_DT_INDX | |
| |
|* 8 | TABLE ACCESS BY INDEX ROWID| EPISODE_AIRINGS | |
| |
|* 9 | INDEX RANGE SCAN | BC_START_DT_INDX | |
| |
|* 10 | TABLE ACCESS FULL | EPISODE_AIRINGS | |
| |
----------------------------------------------------------------------------
-----------
For CHOOSE I get following explain plan ...
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 3184 | 11765 |
| 1 | SORT ORDER BY | | 16 | 3184 | 11765 |
| 2 | VIEW | V_EPISODE_AIRINGS | 16 | 3184 | 11764 |
| 3 | SORT UNIQUE | | 16 | 3096 | 11764 |
| 4 | UNION-ALL | | | | |
|* 5 | TABLE ACCESS FULL| EPISODE_AIRINGS | 15 | 2925 | 5880 |
|* 6 | TABLE ACCESS FULL| EPISODE_AIRINGS | 1 | 171 | 5880 |
----------------------------------------------------------------------------
These explains are gathered using following syntax ...
explain plan for ...<above query>
/
select * from table(dbms_xplan.display)
/
Question is when I have following indexes available
1. index EPISODE_AIRINGS.BC_DT_INDX(BC_DTTM, BC_END_DTTM)
2. index EPISODE_AIRINGS.BC_START_DT_INDX(BC_DTTM)
3. index EPISODE_AIRINGS.BC_END_DT_INDX (BC_END_DTTM)
Why is CBO avoiding index usage, even if I provide a hint ...?
DBPK_BC is a package that does some date manipulation things ...
I can generate 10053 output if anyone is inclined to read through, I can't
decipher all of that yet (even after reading that document). This is an
important view and the performance off of this select is really killing. The
parameters to dbpk_bc function are dynamic in real life. The table is
analyzed with estimate option and all indexes are analyzed as well.
Any ideas are welcome ...
Thanks in advance
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
*********************************************************************This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*********************************************************************2
