06 Mar

Top Secret of 2010-Got Revealed-Watch video free

Yes the video will reveal the top most secret of 2010!! The video will give you the Hero…

  • How can you, I, our friends really trust our selves that what we see on TV and hear on radio is true?

 

  • How do we know that our openions are really our own?

 

 

  • How can we be sure that the weak voices are heard and not scared into silence?

 

This video will tell you how.

There is one person we can think for all this.

The person will give us new perspectives, giving us choice, gives us alternative to uniformity and short term thinking.

We owe this person for making an ordinary day into something special…

Watch em NOW!!

Spread across… Let every one know…

Do you wanna know How? Comment here!!

05 Mar

Ideas for select all columns but one mysql stored procedure

Assume we’ve a table with 100 rows and we need to select all columns but one.
The problem is headache of actually typing out all 99 required columns!!

Solutions / Ideas to above problem are:

  1. Ignorance is bliss. Select all(*) and ignore the column.
  2. Manually type column names or manage it with copy paste!
  3. Create a view from original table and drop the unnecessary column!
  4. MySQL Database command / syntax that allows us to do this; which is not there unfortunately.
  5. Use my stored procedure to select all but one columns:

No need to explain why all such hacks are initiated with the help of information_schema!! 🙂

The idea:

Prepare column list to be selected from COLUMNS table of information_schema database for specified database name and table name. Thus we can select all columns but one and prepare sql statement and execute.
It can be further extended for select all but SOME; instead of “column_name<>in_colm_nm” one can use “column_name not in …” to prepare the column list to be selected.

Here you go with the code: [Download selectAllButOne.sql at the end of the page.]
DELIMITER $$

DROP PROCEDURE IF EXISTS `selectAllButOne` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `selectAllButOne`(in_db_nm varchar(20),in_tbl_nm varchar(20),in_colm_nm varchar(20))
BEGIN

SET @stmnt= CONCAT(‘SELECT ‘,(SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name=in_tbl_nm AND table_schema=in_db_nm AND column_name<>in_colm_nm), ‘ FROM ‘,in_db_nm,’.’,in_tbl_nm,’;’);
PREPARE stmnt FROM @stmnt;
EXECUTE stmnt;
END $$

DELIMITER ;

As one of the ideas, #4 says about MySQL syntax come to our rescue.

I wish if MySQL can ease out us with syntax as follows:
SELECT * FROM TABLENAME EXCEPT COLUMN COLUMNNAME[,COLUMNNAME…]
But…

Download Select all but one mysql stored procedure.

Any other ideas?

02 Mar

No More Spool Space Teradata Query Solution

Have you ever come across a situation called NO MORE SPOOL SPACE?  My friend does and hence I studied a bit about Teradata.

The query:
SELECT DISTINCT fieldname FROM tablename;

The error:
“NO MORE SPOOL SPACE”.

Correct, the problem wasn’t related to MySQL, that was for something called Teradata.

Initially, I could see that query should be using temporary space (from name SPOOL SPACE I guessed) to get distinct data – may be it’s a huge table or something.
So I think one has to look into temporary table space’s size – they call it SPOOL SPACE.

Next is Google’s turn to explain me more, I Googled with appropriate keywords and got following understandings:
Spool is temporary disk space used to hold intermediate rows during query processing, and to hold the rows in the answer set of a transaction.

Spool space is allocated to a user as a total amount available for that user, however this total amount is spread across all AMPS. This means if you’re allocated 200G of spool and you’re on a system with 24 AMPS, you’re allocated ~8.3G of spool on each AMP. If any AMP exceeds its allocated amount of spool during a transaction you submit, your query is cancelled with error: NO MORE SPOOL SPACE.

Common reasons for “No More Spool Space Error”:

1) All the rows in the query are being processed on a few amps creating a “hot amp” situation. In this case, just a few amps are racking up spool while the others sit comparatively idle. This is caused when the tables in the query are missing stats, have been improperly PI’d, or are otherwise “untuned”.

2) The query is excluding critical join criteria resulting in Cartesian products.

3) You just don’t have enough space to perform the query.
From other readings I suggested one query change:

SELECT fieldname FROM tablename GROUP BY fieldname;

The reason behind above query is:
Teradata uses two different methods to produce the result sets of above queries, However the performance varies dramatically depending on the data.

SELECT DISTINCT is designed for data sets that are nearly unique to begin with and works by sorting the entire intermediate spool file and discarding duplicate values.

SELECT/GROUP BY is designed for data sets that have relatively few unique values and works by performing an AMP local grouping operation and then merging the partial result sets for final processing.

Few other steps (even) I can suggest to solve such problem are:

– Explain to see execution plan to have an idea.
– Check table definition for indexes: Show table table-name;
– CREATE INDEX indexName (field-name) on table-name;

Finally, if nothing happens contact your DBA or the “right person” 🙂

I hope this will help someone, somewhere,  someday.

If you know that I’ve misunderstood something please comment and give respective links – I should be correcting it asap.

-- Kedar Vaijanapurkar --