frank_z_j_at_hotmail.com (Frank Jiang) wrote in message news:<e4ffcd8f.0212042152.68d1c33c_at_posting.google.com>...
> 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.
>
Depends on the plan. It's true for hash and merge joins. It's not
true for nested loops. If you have nested loops only, filtering
is done in the end. This is how it works in 8i at least. May be
it's changed in 9i.
> 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 Thu Dec 05 2002 - 11:03:16 CST
