Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> help getting cost down in plan table?

help getting cost down in plan table?

From: <bbulsara23_at_hotmail.com>
Date: 28 Feb 2006 09:17:03 -0800
Message-ID: <1141147023.067082.318830@u72g2000cwu.googlegroups.com>


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); Received on Tue Feb 28 2006 - 11:17:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US