Re: Why not using index in execution plan?

Guess you are really help. To be honest if I have any clue, I won't post here.

The assignment is to analysis estimate cost of query execution plan. Even though you say something smart, I still have to do calculation.

OK, I guess the reason why Oracle access table by full scan is that full table access does selection on the attributes that are not indexed. The result contains much less tuples such that can fit into buffer and piplined into next operation.

After C.state = 'CA' AND C.city = 'Lakewood' C contains only 112 tuples.

Is it make sense?

Thanks

Frank

Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<7pequu4pst5mhotlb3pb0a43pg2fmceqe3_at_4ax.com>...
> On 3 Dec 2002 14:43:30 -0800, frank_z_j_at_hotmail.com (Frank Jiang)
> wrote:
>
> >Hello,
> >This is for my database class. Assignment due in 3 days. Please help
> >me.
> >
> >table COMPANY_BTR (200,000 rows) B+ tree index on Company_ID(PK)
> >table ACCOUNTS_BTR (4,000 rows) B+ tree index on Company_ID,
> >Account_ID(PK)
> >
> >query:
> >SELECT A.account_id, C.company_id, C.name, C.state, C.city
> >FROM accounts_btr A, company_btr C
> >WHERE A.company_id = C.company_id
> >AND C.state = 'CA'
> >AND C.city = 'Lakewood';
> >
> >EXCUTION_PLAN
> >
> > SELECT STATEMENT
> > TABLE ACCESS BY INDEX ROWID ACCOUNTS_BTR
> > NESTED LOOPS
> > TABLE ACCESS FULL COMPANY_BTR
> > INDEX RANGE SCAN ACCOUNTS_BTR_CID
> >
> >My question is why Oracle does a full table scan on big table without
> >using index. I believe Oracle must have a reason to do this.
> >
> >Another question is that is the full table scan step the inner loop?
> >
> >Thank you.
> >
> >Frank Jiang
>
>
> Apparently, given the nature of your questions, the only thing they
> learned you is to have others do your work for you for free. The
> questions above should have been addressed in the class, and this is
> not the place to have your homework done.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Wed Dec 04 2002 - 23:52:14 CST