Re: help getting cost down in plan table?

bbulsara23_at_hotmail.com wrote:
> Hello everyone. I think I need some guidance to get my query to use an
> index. When I look at a plan table for a query I see "TABLE ACCESS
> FULL" with bytes 186M.
>
> -----------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
> -----------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1172K| 72M| 2775
> (5)| 00:00:34 |
> |* 1 | HASH JOIN | | 1172K| 72M| 2775
> (5)| 00:00:34 |
> | 2 | TABLE ACCESS FULL| TBL_FVALJOIN | 3 | 78 | 3
> (0)| 00:00:01 |
> | 3 | TABLE ACCESS FULL| TBL_FVAL | 5007K| 186M| 2725
> (3)| 00:00:33 |
> -----------------------------------------------------------------------------------
>
> The query performance is okay but I expected to see some index use
> rather than "TABLE ACCESS FULL". Alternatively I don't understand what
> the plan table is telling me so I would appreciate you telling too if
> this is the case.
>
> Table tbl_fval contains about 5million rows and table tbl_fvaljoin
> contains a lot less (only contains 3 rows in the sample data below but
> in our production system is contains a lot more). We are using Oracle
> 10.1.0.4 on Windows 2000 SP2. I have included the ddl to create the
> tables, indexes etc, and the SQL to use the plan table. Am I missing
> something to get the cost down (and reduce the 186M above)?
>
> Thank you
> Barry
>
>
> create table tbl_fval(b_id number,b_fid number,b_fval number);
> --
> --and now insert lots and lots of data
> --
> create index idx_fval_id
> on tbl_fval(b_id);
> create index idx_fval_fid
> on tbl_fval(b_fid);
> create index idx_fval_fval
> on tbl_fval(b_fval);
>
>
> create table tbl_fvaljoin(b_fid number,b_fval number);
> insert into tbl_fvaljoin values(100,10);
> insert into tbl_fvaljoin values(101,12);
> insert into tbl_fvaljoin values(103,150);
>
> create index idx_fvaljoin_fid
> on tbl_fvaljoin(b_fid);
> create index idx_fvaljoin_fval
> on tbl_fvaljoin(b_fval);
>
>
> explain plan for
> select t1.b_id
> from tbl_fval t1,tbl_fvaljoin t2
> where t1.b_fid=t2.b_fid
> and t1.b_fval=t2.b_fval;
>
> --
> --this is the plan table shown at the start of the message
> select plan_table_output from table(dbms_xplan.display);

Are statistics are current and created with DBMS_STATS? What is the cardinality of the data is?

Oracle uses indexes when doing so makes sense. It appears from what you've posted Oracle thinks your indexes of no value.

In the case of your demo Oracle will not use the index because the cost of a FTS on the table versus reading the index is zero.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)