Thanks Daniel. We have stats gathered everynight through a scheduled
job. My test system was created today and the job hadn't run yet. I
created the stats manually and the indexes are now being used. Thanks
for pointing out the obvious, which wasn't so obvious ;)
Regards
Barry
DA Morgan wrote:
> 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)
Received on Tue Feb 28 2006 - 14:00:49 CST
