Is everything okay?
I would like to take a question with you:
is more performative, give a
COUNT(1)
than a COUNT(*)
Positive case, why? Why does the count (1) searches the account through PK, automatically?
Thank you very much.
COUNT(1)
than a COUNT(*)
Counting records
------------------------------------------------- -------------------------------
Over The Years, There has been Been Many Debates Regarding What is The Optimal Way To Count The Selection of Records from A Table. Without Going Into The Large History Of This Topic, Different Versions Of Oracle Mandated Different Approaches to Best Counting Records.
However, in the More Recent Releases of Oracle, The Fast Full Index Scan (Bitmap Specialty) Has Generally Become The Most Prudent Way To Count The Records. Thankfully, Under The Cost Optimiser, Oracle Now Performs This for Most Of Combinations of "Count (*)" That DBA's Have Advocated in The Past, Thus Any of Them Will Perform Equivalently AS Can Be Seen From The Examples Below
Example with indexed table
Example with unindexed table/ quote]SQL> select count(*) from PURCHASED_VEHICLES; COUNT(*) ---------- 283761 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11952 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'PURCHASED_VEHICLES_JN' (Cost=119 52 Card=459871) Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 126734 consistent gets 126569 physical reads 0 redo size 369 bytes sent via SQL*Net to client 424 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(1) from PURCHASED_VEHICLES_JN; COUNT(1) ---------- 478957 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11952 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'PURCHASED_VEHICLES_JN' (Cost=119 52 Card=459871) Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 126734 consistent gets 126569 physical reads 0 redo size 369 bytes sent via SQL*Net to client 424 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(rowid) from PURCHASED_VEHICLES_JN; COUNT(ROWID) -------------- 478957 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11952 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'PURCHASED_VEHICLES_JN' (Cost=119 52 Card=459871) Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 126734 consistent gets 126569 physical reads 0 redo size 369 bytes sent via SQL*Net to client 424 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
From what I realized, in current versions, we no longer have this difference.
Source: [/ b [url=http://www.oracledba.co.uk/tips/count_speed.htm]http://www.oracledba.co.uk/tips/count_speed.htm
Users browsing this forum: No registered users and 12 guests