Oracle performance issue in COUNT ()

Question

I am using Oracle 11g, the main table contains about 10 million records. Here is my request:

SELECT COUNT (*) FROM CONTACT c INNER JOIN STATUS S ON C.STATUS = S.STATUS WHERE C.USER = 1 AND S.REQUIRE = 1 AND ROWNUM = 1; 

The cost is 3736, but when I changed it to this form:

 SELECT COUNT (*) FROM (SELECT 1 FROM CONTACT c INNER JOIN STATUS S ON C.STATUS = S.STATUS WHERE C.USER = 1 AND S.REQUIRE = 1 AND ROWNUM = 1); 

The cost has become 5! What is the difference between these two queries?

Here is an explanation plan for both queries:

First request:

 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 3736 (1)| 00:00:45 | | 1 | SORT AGGREGATE | | 1 | 10 | | | |* 2 | COUNT STOPKEY | | | | | | | 3 | NESTED LOOPS | | 4627 | 46270 | 3736 (1)| 00:00:45 | | 4 | TABLE ACCESS BY INDEX ROWID| CONTACT | 6610 | 33050 | 3736 (1)| 00:00:45 | |* 5 | INDEX RANGE SCAN | IX_CONTACT_USR | 6610 | | 20 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IX_CONTACT_STATUS | 1 | 5 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(ROWNUM=1) 5 - access("C"."USER"=1) 6 - access("C"."STATUS"="S"."STATUS" AND "S"."REQUIRE"=1) 

Second request:

 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | VIEW | | 1 | | 5 (0)| 00:00:01 | |* 3 | COUNT STOPKEY | | | | | | | 4 | NESTED LOOPS | | 2 | 20 | 5 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| CONTACT | 3 | 15 | 5 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IX_CONTACT_USR | 6610 | | 3 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | IX_CONTACT_STATUS | 1 | 5 | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(ROWNUM=1) 6 - access("C"."USER"=1) 7 - access("C"."STATUS"="S"."STATUS" AND "S"."REQUIRE"=1) 

I made 2 requests, the first of which sometimes costs 45 s + (for example, the first start or change of the user ID), otherwise it will cost <1s. I totally don’t know why this is so different, maybe db cache?

When I executed the second query, I can always get the result in 1 second. Therefore, I think the second is better, but I am not the reason why it improves a lot.

Answer 1

You can see what the difference is by comparing the row in the execution plans that access the CONTACT table (looks at the row column, first).

At first:

 | 4 | TABLE ACCESS BY INDEX ROWID| CONTACT | 6610 | 33050 | 3736 (1)| 00:00:45 | 

Secondly:

 | 5 | TABLE ACCESS BY INDEX ROWID| CONTACT | 3 | 15 | 5 (0)| 00:00:01 | 

In the first example, the predicate ROWNUM = 1 not applied until the CONTACT table is received, so you will get the rows 6610 returned from this table. If your second query optimizer returns 3 . This is several orders of magnitude smaller, so you see that the second request is completed faster.

As for why the second execution of the “slow” request is “fast”, you think it is right: the data is loaded from disk to the buffer cache, so access is much faster.

Answer 2

Most likely, this is just an estimate of the difference, and they will have the same performance statistics. Track as + tkprof to get real data. Also, if you want to get more detailed information about the optimizer logic, do a hard analysis with event 10053.

Answer 3

Cost is not only a factor for requests, sometimes it depends on the server, which shows that it is the cost of the processor or the cost of I / O, sometimes it depends on me, because of the size of the Column Cardinality, the conditions of the request. if you want to see a lot of clarification on the requests, get an explanation plan or TKPROOF so you know about it, it will be a full table scan or which index picks up and execution time.