I understand that when you feed the count( ) function an asterisk as
an argument it runs
slower than if you use a column name as an argument. Can someone tell
me why this is so?

Thanks

nick wrote:

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

nick wrote:

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

nick wrote:

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

nick wrote:

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

On Jan 15, 9:34*am, nick <cupofjava1... (AT) aol (DOT) com> wrote:
Count on a column (or an expression) does not count nulls. Count with
an asterisk (or some non-null constant) counts all rows even if all
columns are null.

So you can try to use nulls in your data to prove it either way.

Note also that Oracle will attempt to satisfy a count from an index,
so if you have an index that is small and has some nulls, it might be
faster than what Oracle might look at for count(*), and one without
nulls might be faster still.

Then there are analytics...

Count what you need to count!

I think at one time there was a myth that the asterisk meant Oracle
had to spend time parse all the column names for the table or
something like that.

There may also be an ability with the asterisk to not have to look at
all the data in a block, as the block header knows how many rows it
contains. So small rows with little pctfree might count faster.

See what the plan says, and tracing variants may be interesting.

jg
--
@home.com
"Aim towards the Enemy" - Instructions printed on U.S. Rocket
Launcher

On Jan 15, 9:34*am, nick <cupofjava1... (AT) aol (DOT) com> wrote:
Count on a column (or an expression) does not count nulls. Count with
an asterisk (or some non-null constant) counts all rows even if all
columns are null.

So you can try to use nulls in your data to prove it either way.

Note also that Oracle will attempt to satisfy a count from an index,
so if you have an index that is small and has some nulls, it might be
faster than what Oracle might look at for count(*), and one without
nulls might be faster still.

Then there are analytics...

Count what you need to count!

I think at one time there was a myth that the asterisk meant Oracle
had to spend time parse all the column names for the table or
something like that.

There may also be an ability with the asterisk to not have to look at
all the data in a block, as the block header knows how many rows it
contains. So small rows with little pctfree might count faster.

See what the plan says, and tracing variants may be interesting.

jg
--
@home.com
"Aim towards the Enemy" - Instructions printed on U.S. Rocket
Launcher

On Jan 15, 9:34*am, nick <cupofjava1... (AT) aol (DOT) com> wrote:
Count on a column (or an expression) does not count nulls. Count with
an asterisk (or some non-null constant) counts all rows even if all
columns are null.

So you can try to use nulls in your data to prove it either way.

Note also that Oracle will attempt to satisfy a count from an index,
so if you have an index that is small and has some nulls, it might be
faster than what Oracle might look at for count(*), and one without
nulls might be faster still.

Then there are analytics...

Count what you need to count!

I think at one time there was a myth that the asterisk meant Oracle
had to spend time parse all the column names for the table or
something like that.

There may also be an ability with the asterisk to not have to look at
all the data in a block, as the block header knows how many rows it
contains. So small rows with little pctfree might count faster.

See what the plan says, and tracing variants may be interesting.

jg
--
@home.com
"Aim towards the Enemy" - Instructions printed on U.S. Rocket
Launcher

On Jan 15, 9:34*am, nick <cupofjava1... (AT) aol (DOT) com> wrote:
Count on a column (or an expression) does not count nulls. Count with
an asterisk (or some non-null constant) counts all rows even if all
columns are null.

So you can try to use nulls in your data to prove it either way.

Note also that Oracle will attempt to satisfy a count from an index,
so if you have an index that is small and has some nulls, it might be
faster than what Oracle might look at for count(*), and one without
nulls might be faster still.

Then there are analytics...

Count what you need to count!

I think at one time there was a myth that the asterisk meant Oracle
had to spend time parse all the column names for the table or
something like that.

There may also be an ability with the asterisk to not have to look at
all the data in a block, as the block header knows how many rows it
contains. So small rows with little pctfree might count faster.

See what the plan says, and tracing variants may be interesting.

jg
--
@home.com
"Aim towards the Enemy" - Instructions printed on U.S. Rocket
Launcher

On Jan 15, 2:50*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
Just a comment on Joel's wording for any less experienced readers, "if
you have an index that is small and has some nulls, it might be faster
than what Oracle might look at for count(*), and one without nulls
might be faster still. "

For a normal index Oracle does not index table rows where all indexed
columns (defined to a single index) are null. In the case of an index
built on a single column nulls are not indexed at all.

The CBO chooses how count() queries are performed just like any other
query based on the estimated cost of performing the query.

Bitmap indexes do index null values.

HTH -- Mark D Powell --