On Mar 19, 9:50 am, Tom Alspaugh
<[email protected]> wrote:
> Dual is a Psuedo-table, it doesnt have 1 column or any set number of
> columns, Evidenced by the follows
>
> select 'this should appear' col1, 'this shouldnt appear' col2 from
> dual;
>
> COL1 COL2
> ------------------ --------------------
> this should appear this shouldnt appear
>
> 1 row selected.
>
> I did check on the rest of the information and will just point
> everyone to this link of asktom (no not me) where he goes over ALL of
> the intricacies of count(?)
>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID...
>
> On Mar 19, 8:58 am, ddf <[email protected]> wrote:
>
>
>
> > On Mar 18, 4:30 pm, Tom Alspaugh
>
> > <[email protected]> wrote:
> > > Couple of minor tweaks to the information you provided here,
> > > When you say count(1) you are actually using a row ordinal in this
> > > case not a numeric value as you state.
>
> > Sorry to burst your bubble but that is incorrect:
>
> > SQL> select count(1) FROM DUAL;
>
> > COUNT(1)
> > ----------
> > 1
>
> > SQL> c/1/2
> > 1* select count(2) FROM DUAL
> > SQL> /
>
> > COUNT(2)
> > ----------
> > 1
>
> > SQL> c/2/3
> > 1* select count(3) FROM DUAL
> > SQL> /
>
> > COUNT(3)
> > ----------
> > 1
>
> > SQL> c/3/4
> > 1* select count(4) FROM DUAL
> > SQL> /
>
> > COUNT(4)
> > ----------
> > 1
>
> > SQL> c/4/5
> > 1* select count(5) FROM DUAL
> > SQL> /
>
> > COUNT(5)
> > ----------
> > 1
>
> > SQL>
>
> > How can I be selecting the 5th column from a single-column table?
>
> > > for example
> > > select count(1) from USER_TABLES; selects the nonnull values in the
> > > first row and returns that count.
>
> > Again, sorry that's incorrect as illustrated above.
>
> > > select count(2) from USER_TABLES; selects the nonnull values in the
> > > second row and returns that count. otherwise this second query would
> > > return a number twice as large as the first query.
>
> > Your logic is flawed in that COUNT() returns a count, not a sum.
> > Substituting 2 for each row rather than a 1 still will return the same
> > count as before.
>
> > > Your performance tuning tip does have one more issue, it is true that
> > > in MSSQL the count(1) vs Count(*) will give a better performance in
> > > the 1st scenario,
>
> > I'll take your word on that but I expect that only applies to versions
> > of SQL Server prior to 2005.
>
> > > However in Oracle even if you were to use count(0)
> > > (which would use the virtual rownum column)
>
> > Nope, it uses the same mechanism as count(1), count(2), count(12) and
> > count(743) and all of those will work and return results on a single-
> > column table:
>
> > SQL> select count(743) from dual;
>
> > COUNT(743)
> > ----------
> > 1
>
> > > the execution plan would
> > > be the exact same for all of these PLSQL statements.- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
DUAL is a single-column, single-row table, NOT a pseudo-table. But to
make you happy I'll create my own single-column, single-row table from
which I can select any number of string literals and not violate
anything:
SQL> create table yark(
2 snazzo number
3 );
Table created.
SQL>
SQL> insert into yark values(456);
1 row created.
SQL>
SQL> select 'yerp','trellopan','grimple','framp'
2 from yark;
'YER 'TRELLOPA 'GRIMPL 'FRAM
---- --------- ------- -----
yerp trellopan grimple framp
SQL>
Because they are string literals any number are allowed to be selected
from any table. Now let's look at the various count() options and see
if your 'theory' holds water:
SQL> set autotrace on
SQL>
SQL> select count(1)
2 from yark;
COUNT(1)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3040377463
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| YARK | 1 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select count(7)
2 from yark;
COUNT(7)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3040377463
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| YARK | 1 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select count(311)
2 from yark;
COUNT(311)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3040377463
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| YARK | 1 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select count(9458677)
2 from yark;
COUNT(9458677)
--------------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3040377463
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| YARK | 1 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
521 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select count(*)
2 from yark;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3040377463
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| YARK | 1 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Still looks like your theory is wrong.
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
To unsubscribe from this group, send email to
oracle-plsql+unsubscribegooglegroups.com or reply to this email with the words
"REMOVE ME" as the subject.
