Skip navigation

We heard count(1) is faster then count(*).  Lets do a workshop on this .

 

TABLE_STRACTURE.png

Above is Copy_customer table where inserted both null and not null values.

lets execute  the queries   Count(*)  and count(1) and  verify the result  and verify the work load.

count_star

count_1

In both cases query will return the same output. Now lets compare the work load of both the queries.

Let's check for count(*)

count_star_tk

Now Count(1)

count_1_tk

From  the above screen shot its clearly shows both  queries are taking  same amount of  work load to execute.

Now lets verify the same query  output and work load with actual column name instead  of '1' and '*'.

cnt_column-name.png

Now if check the below output it will ignore the null from the result and trace file also using the index.cnt_c_id-tk.png

Here Elapsed time is little more to count(1) and count(*). Because here it also fetches more then 90% record. This article is only to demonstrate count(1) and count(*) are same.

 

Thank you....for eading

Sometimes listener log file grows very large in size and we have to shrink that in order to reclaim space on the mount. I see some people just wonder how they can compress the listener log file while it is currently in use in production system as they can't just stop and start the listener to compress/rename the file as unavailability of listener will cause user connections to fail and that will be encountered as production outage by end user.

 

Below I have outlined two steps where we tell listener to stop logging in the listener log file and then rename/compress/purge the listener log file and finally we can tell listener back that it can start logging the info in the listener.log file.

 


Step : 1 - First of all turn off the logging status of listener using below command at lsnrctl prompt.

 

 

LSNRCTL> SET LOG_STATUS OFF Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajdbsolutions.com)(PORT=1522)) LISTENER parameter "log_status" set to OFF The command completed successfully

 

After the above step, go to listener log location and rename the listener.log file to something else e.g. Listener_old.log

 

Again turn back the listener logging ON using below command.

 

 

LSNRCTL> SET LOG_STATUS ON Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajdbsolutions.com)(PORT=1522)) LISTENER parameter "log_status" set to ON The command completed successfully

 

When you turn ON the listener log using above command, a new listener.log file will be created there and it will be used hereafter for database connectivity.

 

 

Hope it would help someone...!!