CODEWITHSUNDEEP
performanceoracle-database
paktrick
paktrick

Reputation: 93

Table not doing index scan

Hey guy so some reason my table is doing index fast full scan.

here is my query 

SELECT bo.bid,cu.cid 
FROM ass2master_booking bo, ass2master_customer cu 
WHERE bo.cid = cu.cid 
and rownum < 135000;

here is the trace file

SELECT bo.bid,cu.cid 
FROM ass2master_booking bo, ass2master_customer cu 
WHERE bo.cid = cu.cid 
and rownum < 135000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch     9001      0.19       0.41        387       2131          0      134999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     9004      0.19       0.41        387       2131          0      134999

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5594  

Rows     Row Source Operation
-------  ---------------------------------------------------
134999  COUNT STOPKEY (cr=2131 pr=387 pw=189 time=411804 us)
134999   HASH JOIN  (cr=2131 pr=387 pw=189 time=276737 us)
150000    INDEX FAST FULL SCAN CUSTOMER_CID_IDX (cr=320 pr=315 pw=0 time=263 us)(object   id 332052)
7412    TABLE ACCESS FULL ASS2MASTER_BOOKING (cr=1811 pr=44 pw=0 time=7566 us)

basically i was told to add structure to unstructred data i was given 2 tables with 150,000 rows and determ which of the following structures were the best.

And the query i chose to do the testing with is given above.

Upvotes: 0

Views: 104

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81998

You are selecting a large part of both tables, so index access doesn't makes sense. (at least oracle thinks that)

It uses Index Fast Full Scan instead of Table Access Full because because it finds everything it needs (cu.cid) in the index and doesn't need the table.

I have no idea what you mean with

non clustered index clustered index hash clustered unstructured data.

Update:

I think a rule of thumb I use:

If you need more then 20% of a table I expect a full table scan. If you need less then 5% of a table I expect some kind of index access.

One of the first things I do when tuning SQL statement is to look in the execution plan and check the number of rows oracle expects to get back from each step. If those numbers are completely wrong, the execution plan is probably pretty bad.

Upvotes: 1

Related Questions