Should I use an index with this query?

Question

I have question regarding index,

I am using an index on CID on customer table why because I'm querying this table as well as joining with the booking table.

Schema Booking table

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BID                                       NOT NULL NUMBER(16)
 CID                                                NUMBER(16)
 FNO                                                NUMBER(16)
 OID                                                NUMBER(16)
 SEAT                                               NUMBER(6)
 SEAT_PRICE                                         NUMBER(6)
 FLIGHT_DATE                                        DATE

customer

 CID                                       NOT NULL NUMBER(16)
 FIRSTNAME                                 NOT NULL VARCHAR2(10)
 LASTNAME                                  NOT NULL VARCHAR2(10)
 STREET                                    NOT NULL VARCHAR2(20)
 TOWN                                      NOT NULL VARCHAR2(10)

Query

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

My question is: does it really make any difference whether I add an index to CID by referring to the query?

I have added it because its being joined and selected.

I tested this query with unstructured data (without index) and there is no significant decrease in CPU but the disk column in trace file has drop from 1800 to 337.

Unstructured disk was 1800, with index its 337.

By the index is doing a fast full scan on customer table.

Answer 1

1) I'm not seeing a CID column in the CUSTOMER table you described which is a bit confusing. Perhaps your query intended to refer to the FNO column or perhaps there is a CID column that you accidentally cut off from the output of the DESCRIBE command.

2) How many rows are in the table? You're fetching an arbitrary 135,000 rows and you don't have any meaningful predicates so an index isn't going to be particularly helpful other than as a covering index so that Oracle can scan the index rather than scanning the table. But that advantage will disappear if you need to add additional columns to the SELECT list.

3) Given the query as you posted it, it's not obvious to my why you're joining to the CUSTOMER table. BOOKING has both BID and CID so the join doesn't appear to be doing anything beneficial. Perhaps the intention was to join to the CUSTOMER table so that you could add additional columns to the SELECT list. If that's the case, however, the benefit of the covering index may be lost (unless, of course, you add the additional columns to the index as well)

Answer 2

The answer is yes if it improves the performance.

Don't add indexes just to add them. That's easy enough to test. The indexed query is either faster or it isn't. Remember, indexes slow down tables during inserts. So you have to weigh your specific data and performance needs.