We know that Oracle sometimes, may cause the statement to select an error in the execution plan, such as the index scan, due to the problem of statistical information, and the index scanning, but use full table scanning. For many systems more trend to use index scans, because often, the price of index scans is indeed less than the full table scan. . .
We look at an actual example SQL> SET Autot TRACE EXPSQL> Select Count (*) from Auction_auctions A, Auction_BIDS B 2 Where A.Id = B.AUCTION and AUCTION_TYPE = 'A' 3 and ends> to_date ('2004-05- 21 ',' YYYY-MM-DD ') 4 and Approve_Status> = 0;
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 65151 Card = 1 Bytes = 80) 1 0 Sort (aggregate) 2 1 Hash Join (COST = 65151 Card = 955916 BYtes = 76473280) 3 2 View of 'Index $ _JOIN $ _001' (COST = 64015 Card = 95048 bytes = 4467256) 4 3 Hash Join 5 4 Hash Join 6 5 Partition Range (Iterator) 7 6 IND (FAST FULL SCAN) of 'IND_AUCTION_ZO_CLO_STA_CAT' (Non- UNIQUE) (Cost = 8713 Card = 95048 Bytes = 4467256) 8 5 PARTITION RANGE (ITERATOR) 9 8 INDEX (FAST FULL SCAN) OF 'PK_AUCTION_AUCTIONS_ID' (UNIQUE) (Cost = 8713 Card = 95048 Bytes = 4467256) 10 4 PARTITION RANGE (ITERATOR) 11 10 INDEX (FAST FULL SCAN) OF 'IND_AUC_USER_CLO_TYP_BID_STA' (NON-UNIQUE) (Cost = 8713 Card = 95048 Bytes = 4467256) 12 2 INDEX (FAST FULL SCAN) OF 'IND_BIDS_AUCTIONSTATUS' (NON-UNIQUE) (Cost = 397 Card = 955920 bytes = 31 545360 In the above example, a query of the table has employed a method of rapid scanning, and a plurality of scan results are connected via HASH, for a very large table, the query time is below 10s.
However, when we modify the query conditions as follows: SQL> Select Count (*) from Auction_auctions A, AUCTION_BIDS B 2 Where A.Id = B.Auction and Auction_Type = 'A' 3 and ends> to_date ('2004-07- 01 ',' YYYY-MM-DD ') 4 and Approve_Status> = 0; Execution Plan ---------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- 0 Select Statement Optimizer = Choose (COST = 35001 Card = 1 Bytes = 80) 1 0 Sort 2 1 Hash Join (COST = 35001 Card = 807907 BYtes = 64632560) 3 2 Partition Range (item) 4 3 Table Access (Full) Of 'Auction_auctions' (COST = 33888 Card = 68567 BYtes = 3222649) 5 2 INDEX (Fast Full scan) of 'Ind_bids_auctionStatus' (COST = 397 Card = 955920 BYtes = 31545360) This time, we can see that Oracle uses full table scans, although the record number of this query statement is more than the previous The number of records of the query is less, but because this table is a partition table of the ENDS partition, when Ends> 07-01, only need to query in a partition, the Oracle error calculates cost (COST = 35001, far away Small than the cost COST = 65151 using the index is used), so the full mete scan within the partition range is employed. But the actual situation is that the query needs to be> 10 minutes to get the results of the query. Obviously, there is a problem when Oracle calculation costs here, and the full range scan here is much lower than the time of index scans.
So, what do we have to make Oracle return to an execution plan for the index scan? We first consider Hint. SQL> select / * index (a IND_AUCTION_ZOO_CLO_STA_CAT) index (a PK_AUCTION_AUCTIONS_ID) index (a IND_AUC_USER_CLO_TYP_BID_STA) * / 2 count (*) from auction_auctions a, auction_bids b 3 where a.id = b.auction and auction_type = 'a' 4 And ends> to_date ('2004-07-21', 'yyyy-mm-dd') 5 and approve_status> = 0; ELAPSED: 00: 00: 00.00
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 143358 Card = 1 Bytes = 80) 1 0 Sort (aggregate) 2 1 Hash Join (COST = 143358 Card = 807907 BYtes = 64632560) 3 2 PARTITION RANGE (ITERATOR) 4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'AUCTION_AUCTIONS' (Cost = 142245 Card = 68567 Bytes = 3222649) 5 4 INDEX (SKIP SCAN) OF 'IND_AUC_USER_CLO_TYP_BID_STA' (NON-UNIQUE) (Cost = 28314 Card = 152804) 6 2 IND_BIDS_AUCTIONSTATUS '(COST = 397 Card = 955920 BYTES = 31545360) You can see, no matter how you specify an index, Oracle can only be a very dead. One it thinks the fastest index, without using the above comparison method optimized multiple index rapid scanning methods, and through the specified index scan, Oracle calculates costs (COST = 143358), in fact, The execution of the statement is also very slow, even slowly from the partition full table scan. Is there any way to make Oracle's simplest use of the above execution plan? We can consider parameter Optimizer_index_cost_adj, which represents a percentage, with a value between 1 and 10,000. This parameter represents a comparison of index scanning and full meant scanning costs, and the default value 100 represents an index scan into an equivalent conversion and a full metric scanning cost. We modify this parameter value SQL> ALTER session set optimizer_index_cost_adj = 40; session altered.
SQL> SELECT Count (*) from Auction_auctions A, AUCTION_BIDS B 2 WHERE A.ID = B.Auction and Auction_Type = 'A' 3 and ends> to_date ('2004-07-01', 'YYYY-MM-DD') 4 and Approve_Status> = 0;
