How To Uncompress The Most Popular Formats

This morning at work, a colleague who told me that a file could not apparently uncompres, the problem was that he used a binary that was not the right format for file compression used.

Here I put the format to decompress the most common compression formats;

 
– Uncompres .7z

> 7z x

– Uncompres .z

> uncompress .z

– Uncompres .tgz

> tar -xvzf archivo.tgz

– Uncompres .tar

> tar -xvf archivo.tar

– Uncompres .bz2

> bzip2 -d fichero.bz2

– Uncompres .tar.bz2

> tar jxvf archivo.tar.bz2

– Uncompres .gz

> gzip -d fichero.gz

– Uncompres .tar.gz

> tar zxf archivo.tar.gz

– Uncompres .rar

> rar x archivo.rar

– Uncompres .lha

> lha x archivo.lha

– Uncompres .zip

> unzip archivo.zip

– Uncompres .arj

> unarj archivo.arj

– Uncompres .zoo

> zoo x archivo.zoo

 
HTH – Antonio NAVARRO

Error 1064 When Select Count (*) In MySQL

This morning at work has come an incidence from development team. I show below the error message;

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘*) from actor’ at line 1

 
This error occurs when you run the following statement;

select count (*) from table1

The problem is the space between “count” and “(*)”. This problem is not new, according MySQL is not an error, but in other databases it works. If execute it without the blank space works, as in this example;

select count(*) from table1

It can be modified to ignore blank as follows;

set @@sql_mode='IGNORE_SPACE';

For more information, please access this link;

Bug MySQL Count (*)

HTH – Antonio NAVARRO

OTN Contributor Level

I recently received a letter by mail (sometimes there is a good thing into the mailbox), in which I indicated that gained a new level in the OTN forums. The OTN forums They are great from differents points;

i) If you are a new user it is a place more where resort (and is the only site if you haven’t support), we have all been new.

ii) Is the place to share testcase and rare cases, the opinions of other users are often valuable and profitable.

iii) It is a place to learn, based on the problems of other users.

Post below a scanned copy of the letter;

My recommendation is that you perceive, if not know it yet, and a stroll through the forums and interesting if you see it, get ready.

Thanks Monique.

HTH – Antonio NAVARRO

 

postscript; I already put the sticker.

How To Group By Days In SQL Server

Today I have had to optimize an statement in a database SQL Server 2008 R2, the reason for this post is not the performance itself of the sentence, if not rather the way to the group by for days, exactly it used the following clause;

GROUP BY DATEDIFF(DAY, 0, DateOfOperation)

Objectively and from a practical point of view, it works and it is correct, at least to some extent. One of the things I’ve always said that as a rule all SQL statements inside a database, including the framework (Transact SQL in Microsoft, PL/SQL in Oracle) is alive and evolves over time and with the the provider’s own technology. In fact in this case it is better to use or become clearer with the following function;

GROUP BY CAST(DateOfOperation AS Date)

Besides, to see the difference that makes one and another I put these querys;

 

 
select DATEDIFF(DAY, 0, GETDATE ())    

// Return:  42450

select CAST(getdate()  AS Date)         

// Return: 2016-03-23

HTH – Antonio NAVARRO

 

Unindexed Foreign Keys

A performance issue very common in Oracle, is the FK constrainst that are not indexed, this means that under certain conditions and especially when working with tables large or very large, performance plummets. Oracle recommends that as a rule Foreign Keys all have their associated index. However, one thing is theory and another practice, sometimes we can not have all FK indexed, from problems of physical space (of course disk is more cheaper every day) up to has performance problem. I have worked with tables that had the order of hundreds of indexes, the problem comes when do a simple insert will be 2 msg (only the data in the block in memory) and update all its indexes take a minute.

Here I put a little script to identify all FKs that are not indexed.

 

 
SELECT  
  FK.TABLE_NAME, 
  FK.CONSTRAINT_NAME
FROM    
  DBA_CONSTRAINTS FK
WHERE   
  FK.CONSTRAINT_TYPE = 'R' AND     
  EXISTS (   
    SELECT  
      FC.POSITION, 
      FC.COLUMN_NAME
    FROM    
      DBA_CONS_COLUMNS FC
    WHERE   
       FC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    MINUS
    SELECT  
       IC.COLUMN_POSITION, 
       IC.COLUMN_NAME
    FROM    
       DBA_IND_COLUMNS IC
    WHERE   
       IC.TABLE_NAME = FK.TABLE_NAME
  )  /*** END OF EXISTS ***/  
/

 
HTH – Antonio NAVARRO

No Use Select * In Create View

A very old rule in Oracle has always been not user “select *” clause to create a view, technically can be done, but if you take a look at the following example;

 TE*ANTO> create view anr_view as select * from anr_dba_users; 

Being anr_users a copy of the dba_users.

Create the next view using “select *” clause;

 
TE*ANTO> create view anr_view as select * from anr_dba_users;

We looked at the definition of the view in the dba_views;

 
TE*ANTO> SELECT TEXT from dba_views where view_name ='ANR_VIEW';

TEXT
________________________________________________________________________________
select "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE
","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_
CONSUMER_GROUP","EXTERNAL_NAME" from anr_dba_users

Oracle translates the “select *” to all the columns that exist in the table at the time of the creation of the view.
Now a problem is when you add columns or delete columns.

 TE*ANTO> ALTER TABLE ANR_DBA_USERS ADD (NEWCOL VARCHAR2(10)); Vista creada. TE*ANTO> SELECT TEXT from dba_views where view_name ='ANR_VIEW'; TEXT ________________________________________________________________________________ select "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE ","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_ CONSUMER_GROUP","EXTERNAL_NAME","NEWCOL" from anr_dba_users 

The table has changed but continued the same view, use a compile does not solve the problem. The only way is to do a create/rebuild;

 
TE*ANTO> CREATE OR REPLACE
  2  view anr_view as select * from anr_dba_users;

Vista creada.

Transcurrido: 00:00:00.21
TE*ANTO> SELECT TEXT from dba_views where view_name ='ANR_VIEW';

TEXT
________________________________________________________________________________
select "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE
","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_
CONSUMER_GROUP","EXTERNAL_NAME","NEWCOL" from anr_dba_users

If we look we see that the new column NEWCOL longer appears.
Recommendation: Always use the fields you want to use or need in the definition of the view.

HTH – Antonio NAVARRO

What Is SHEBANG

Shebang is, in Unix slumng, the name given to the pair of characters #! found at the beginning of the executable programs interpreted. Sometimes it is called also hash-bang or sharpbang.

Following these characters complete the interpreter of the orders contained therein indicated route. Basically it is used to indicate that shell executes the commands contained in the following lines. Although many people think that you can run only shells, but also you can execute perl, awk, etc.

In the case of Oracle enviroments (also I have seen a lot in Sybase and MySQL on Linux) is often used #!/bin/ksh (korn Shell Which was developed by David Korn at Bell Labs in the early 1980s).

HTH – Antonio NAVARRO