An Extract from AskTom

Efficient SQL

This was probably the hardest part of the book to write - this chapter.  That is not 
because the material is all that complex, rather because I know what people want - and I 
know what can be delivered.  What people want:  The 10 step process by which you can tune 
any query.  What can be delivered:  Knowledge about how queries are processed, knowledge 
you can use and apply day to day as you develop them.

Think about it for a moment.  If there were a 10 step or even 1,000,000 step process by 
which any query can be tuned (or even X% of queries for that matter), we would write a 
program to do it.  Oh don't get me wrong, there are many programs that actually try to do 
this - Oracle Enterprise Manager with its tuning pack, SQL Navigator and others.  What 
they do is primarily recommend indexing schemes to tune a query, suggest materialized 
views, offer to add hints to the query to try other access plans.  They show you 
different query plans for the same statement and allow you to pick one.  They offer 
"rules of thumb" (what I generally call ROT since the acronym and the word is maps to are 
so appropriate for each other) SQL optimizations - which if they were universally 
applicable - the optimizer would do it as a matter of fact.  In fact, the cost based 
optimizer does that already - it rewrites our queries all of the time.  These tuning 
tools use a very limited set of rules that sometimes can suggest that index or set of 
indexes you really should have thought of during your design.  

I'll close this idea out with this thought - if there were an N step process to tuning a 
query, to writing efficient SQL - the optimizer would incorporate it all and we would not 
be having a discussion about this topic at all.  It is like the search for the holy grail 
- maybe someday the software will be sophisticated enough to be perfect in this regards, 
it will be able to take our SQL, understand the question being asked and process the 
question - rather then syntax.  

To me - writing efficient SQL requires a couple of things:

o    Knowledge of the physical organization of what I'm asked to query against.  That is 
- the schema.  Knowledge that the physical organization was actually designed in order to 
help me answer my frequently asked questions (refer back to the chapter on designing an 
efficient schema for advice in that arena)

o    Knowledge of what the database is capable of doing.  If I did not know about "skip 
scan indexes" and what they did (we'll cover them below) - I might look at a schema and 
say "ah hah, we are missing an index" when in fact we are not.

o    Knowledge of all of the intricacies of SQL - from the lowly "WHERE" clause on up to 
analytics and psuedo columns.  Knowledge of what using a particular construct will do to 
my runtime processing.

o    And most importantly of all - a solid understanding of the goal, of what the 
question is.  Tuning a query or process is really hard (impossible I would say) - unless 
you understand the question in the first place.  I cannot tell you how many times I've 
not been able to tune a query until I had the question in hand.  Certainly you can derive 
a question from a query - however, many times that derived question is much more 
confining then the real question being asked.  For example, many people use outer joins 
in all queries - they are "afraid" of losing a row (perhaps they got "burned" in some 
past experience and now use outer joins everywhere).  If the objects are related in a one 
to one mandatory fashion - we don't need an outer join at all.  The question derived from 
the query is much more confining then reality.

That last topic or point is so important, I'll close out this section with it. In this 
chapter we'll cover the topics of what the database is capable of doing in general - 
looking at many of the access paths and join operations available to us.  We'll look at 
what SQL is capable of doing - not by discussing the entire language, that in itself is a 
book.  Rather, we'll look at a couple of things that will whet you appetite - show you 
how powerful this language can be, how much more than just "SELECT" "FROM" "WHERE" and  
"ORDER BY" there is.  Then we'll close up with a look at that most important topic - why 
understanding the question is more important then having a query at hand to tune.

So, this section will not provide you with the N steps you need to follow in order to 
tune a query or write the best queries in the world.  For every rule of thumb out there 
anyone has ever shown me regarding writing "efficient SQL", I've been able to come up 
with a slew of common (not esoteric) counter cases to prove that rule of thumb is wrong 
in as many cases as it is right.  I've talked to people who swear "NOT IN" is fatal, 
never use it - always use NOT EXISTS.  Then I show them NOT IN running a query 10 times 
faster then NOT EXISTS.   I talk with people who feel NOT EXISTS is the worst construct 
on the planet - you must use IN.  Then I do the same - showing them how NOT EXISTS can 
run many times faster then IN.

On SQL Tuning

Found some interesting stuff... (Collection from many sources over internet)

1. Basic Tipcs from dba-oracle.com - http://www.dba-oracle.com/art_sql_tune.htm
2. a more light hearted approach - http://philip.greenspun.com/sql/tuning.html
3. http://www.dba-oracle.com/art_otn_cbo_p1.htm

On Oracle Joins

Some useful links on Oracle SQL optimization (more specifically on the way oracle joins tables)

1. Sachin Arora's explanation on Hash Joins/Nested Loops
2. Optimization of joins
3. Hash joins & Nested Loops on dbaForums
4. @OTN Forums on Nested Loops
5. Hash join Tips
6. Sizing PGA

Useful scripts for DBA perspective -

http://www.dbapool.com/scripts.php

More stuff on finding free space available in a database -

http://forums.oracle.com/forums/thread.jspa?threadID=624042

A sql that we found useful is here - (from the above link itself) -

SELECT
NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) NAME ,
mbytes_alloc mbytes ,
mbytes_alloc - NVL (mbytes_free, 0) used ,
NVL (mbytes_free, 0) free ,
((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100 pct_used,
100 - (((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100) pct_free
FROM
(
SELECT
SUM(BYTES) / 1024 / 1024 mbytes_free,
tablespace_name
FROM
SYS.dba_free_space
GROUP BY
tablespace_name
)
a,
(
SELECT
SUM(BYTES) / 1024 / 1024 mbytes_alloc,
tablespace_name
FROM
SYS.dba_data_files
GROUP BY
tablespace_name
)
b
WHERE
a.tablespace_name(+) = b.tablespace_name

UNION ALL

SELECT
f.tablespace_name ,
SUM (ROUND((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) ) "total MB" ,
SUM (ROUND(NVL (p.bytes_used, 0) / 1024 / 1024, 2)) "Used MB" ,
SUM (ROUND ( ((f.bytes_free + f.bytes_used) - NVL (p.bytes_used, 0) ) / 1024 / 1024, 2 ) ) "Free MB" ,
(SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
100 - (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100) / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)))
FROM
SYS.v_$temp_space_header f,
dba_temp_files d ,
SYS.v_$temp_extent_pool p
WHERE
f.tablespace_name(+) = d.tablespace_name AND
f.file_id(+) = d.file_id AND
p.file_id(+) = d.file_id
GROUP BY
f.tablespace_name
ORDER BY
5 DESC --&orderby
;

Compile all invalid packages in a schema : oracle

Came across this from sql developer, Looks like a very useful script, especially when its made available readymade

begin
FOR cur IN
(SELECT OBJECT_NAME, OBJECT_TYPE, owner
FROM all_objects
WHERE object_type in ('PACKAGE','PACKAGE BODY')
and owner = :OBJECT_OWNER AND status = 'INVALID' )
LOOP
BEGIN
if cur.OBJECT_TYPE = 'PACKAGE BODY'
then
EXECUTE IMMEDIATE 'alter package "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile body';
else
EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile';
end if;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
end loop;
end;

And then another one to check as to what all objects are invalid -

select object_type, count(*)
from user_objects
where status = 'INVALID'
GROUP BY object_type

Oracle : count(1) vs count(*)

It seems we should prefer using count (*) over count(1)

read the article from asktom here...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40208915257337

there've been other discussions and debates on this as well.

Free Adobe Flex Video Training

Just found about it...

http://www.adobe.com/devnet/flex/videotraining/?sdid=EGMNU

not been through it yet, but it looks pretty good, combination of pdf ebooks, audio/video stuff and all that...

More Free ebooks on Oracle - Unix - Linux

Find free to download ebooks on this site -

http://www.itstudy8.org

Changing System name in Ubuntu

When I installed Ubuntu for the first time, I named my system to reflect the os name (gutsy) and the architecture (64 bit). However, whenever there were system upgrades, the name got irrelevant. For example, when I upgraded to Hardy, the name still remained stuck to the last version of OS.

Therefore, I now thought of renaming my system to separate it from the os versions. This is so very simple to achieve in Ubuntu -

just edit the file /etc/hostname (owned by root, therefore use sudo). Put in the new name, and restart the system.

However, the consequences of this name change will have to be reflected across the network (if you have one). Hostname files on all other systems or dns entries will have to be updated to reflect the new system name.

Useful Articles on Oracle

Useful Articles on Oracle Cast function -
http://www.psoug.org/reference/cast.html

Performing DML with Collections
1. http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjcol.htm#sthref483
2. http://www.psoug.org/reference/collections.html

Answers to my question on whether or not its possible to perform DML on Oracle collections (in combination with Cast)
1. http://www.orafaq.com/forum/t/137485/133715/
2. http://forums.oracle.com/forums/thread.jspa?messageID=3176984

Some Notes on Joins & their syntaxes in Oracle
1. http://oreilly.com/catalog/orsqlpluspr2/chapter/ch01.html

Some articles on Oracle Explain Plan and how to generate/read/understand it
1. http://www.adp-gmbh.ch/ora/explainplan.html
2. http://www.adp-gmbh.ch/ora/sql/join/join_methods.html
3. http://download-uk.oracle.com/docs/cd/B12037_01/server.101/b10752/ex_plan.htm

Some articles on Oracle Global Temp Tables (GTT)
1. http://www.dbasupport.com/oracle/ora8/page2.shtml
2. http://www.dba-oracle.com/t_temporary_tables_sql.htm

On Oracle Memory Usage
http://www.dbasupport.com/oracle/ora11g/memory_usage.shtml

Useful mysql stuff...

Here's a compilation of my day to day use of mysql related stuff...

1. Resetting root password in mysql
   - Goto this page https://help.ubuntu.com/community/MysqlPasswordReset
   
   
2. To give privileges to a certain user in mysql
   - grant all on *.* to username
   
   
3. 
   

Finally... IE on Linux / Ubuntu...

Great..... It works... :)

Initially, when I needed to run a few IE specific sites on my Ubuntu installation, I spent quite some time searching around google, and some more prominent linux sites... But I did not hit success...

Today however, my search bore fruit... I found IEs4Linux project, and its so damned simple.

I am still running Hardy (not Interpid) on my desktop, and wine (the default). I picked up the instructions, kind of updated them myself to work in my hardy, and bingo.. it installed and works...absolutely.

Here's what I did -

1. Found this page for installation instructions on Ubuntu :
   http://www.tatanka.com.br/ies4linux/page/Installation:Ubuntu
   
   
2. Run the following commands -

3. sudo apt-get update
   sudo apt-get install wine cabextract

4. And then run this -
   

5. wget http://www.tatanka.com.br/ies4linux/downloads/ies4linux-latest.tar.gz
   tar zxvf ies4linux-latest.tar.gz
   cd ies4linux-*
   ./ies4linux
   

6. The last one would open a dialog box and would ask you basic questions about what version do you want, choose what u like and bingo.. it would start installation...
   
   
7. Within minutes, it automatically downloads some files from here and there, and is done.
   
   
8. It just works...

Agile : What is, Comparison, Transition...

Generally on Agile Methodology -
1. Agile methodology home page - http://www.agilemethodology.org/
2. Manifesto of Agile methodology - http://www.agilemanifesto.org/
3. Working with Agile methods - http://www.agileadvice.com/

Some links on Comparison of waterfall and Agile development methodologies.
1. http://agileintro.wordpress.com/2008/01/04/waterfall-vs-agile-methodology/
2. Youtube -http://www.youtube.com/watch?v=XokJLWp7icI
3. on Agile Advice - http://www.agileadvice.com/archives/2006/05/waterfall_vs_ag.html


Some links on the transition from Waterfall model to Agile methodology...
1. Agile Blog: Transitioning from Waterfall to Agile - Some tips
2. From Udayan
3. Scrum Alliance
4. A Presentation

Blogged with the Flock Browser

Some Oracle data dictionary help...

To get parameters information for your functions/procedures/packages etc. you can use all_arguments view. A sample query is attached...

select owner, object_name, package_name, argument_name, position, data_type, in_out
from all_arguments
where owner = (case when lc_user is null then user else lc_user end)
and package_name = (case when lc_pkg_nm is null then package_name else lc_pkg_nm end)
order by owner, package_name, object_name, position;

Further another view called all_source is also pretty useful if you have to look at the actual source code of a component.

Following links helped me on this -

1. http://www.eveandersson.com/writing/data-model-reverse-engineering#plsql
2. http://www.c-sharpcorner.com/Forums/ShowMessages.aspx?ThreadID=48498

My new quad core desktop system...

Recently managed to get a desktop system home, especially after coming back, it was necessary. The laptop just doesnt provide enough power.

This sure looks like a monster for a desktop system usage.

- Quad Core CPU (Core 2 Quad Q6600) at 2.4 GHz, 8 MB L2 Cache
- 2 GB RAM @800 MHz
- 250 GB Seagate Barracuda Hard Disk
- Here I was a bit disappointed. My local vendor just couldnt manage to get me a 10K rpm or higher hard disk from his supplier. He kept saying it would require a SCSI card and all that configuration. From my understanding it should be a simple SATA or SATA2 disk. But, well, this is the first one in place, may be we'd have more... :)

So, with this system in place, I plan to do some interesting things... e.g. running my long awaited local oracle server so that I can work with apex (http://apex.oracle.com)

Running Hardy on it. To say the least, hardy flies on this system. To start with, everything was detected normally on this configuration and I had no difficulty to make it work with Ubuntu Hardy. Even the live CD worked fine.

Now with the installed copy along with all the updates so far, the system literally flies... The system monitor shows me 4 CPUs and it adds so damned much to my confidence in using this power.... :)

The only issue I have found till now is with my power supply ratings/configuration. It somehow draws a lot of power (the rating on the PSU box says 450W) whereas the Display is not connected to the CPU power (its a TFT anyway). The UPS that I have is a APC with 500 VA rating. The UPS just doesnt hold.

All the power from the UPS comes to just this system and the display. Still it doesnt hold. About the power supply, I was told that the rating (450W) is an indication of how much can this provide, not how much does it need all the time. That might be wrong or right both. I am still figuring that out. No idea till now.

iPhone 3G in India...

Finally... finally... after such a long wait.. iPhone comes to India.. officially. But, despite all the hype and wait around it, the affordability of it really a huge question mark.

I didnt change my mobile phone for last 2 years, waiting for the iPhone to come to India. I booked an iPhone for me through both the prospective service providers in India, Airtel and Vodafone. It was all in anticipation that once its released in India, there would be huge queues for buying it, as was seen in other parts of world for iPhone 3G release.

However, the launch in India was not as expected. There was hype, but not as many customers. I am not a statistician and therefore cant give any numbers to prove my point, but I guess I have a feeling for the situation.

In my view, the pricing of the iPhone is a big reason for its slow launch. I cant say whether or not it would pick up in future, but right now, the price tag of 31000 and 36000 INR for iPhone is a very very high price. I mean, yes its a good phone, there are nice features, but thirty one thousand rupees... its just too much for a gadget in my view. And then, compare the price of the phone in US, 200 USD.. converts to around 8000 INR and so it shows that apple is selling that phone 4 times as costly as in US.. what kind of business strategy would that be ??? Especially in a cost sensitive market as India...

There would be people buying it, but not like me, not the average Indian IT guy, who knows the features, their meaning and usage and has a wish to own one. I dont know many who'd take up the phone at this price tag.

May be Apple will come back with a price cut in India as well, as they did in US... but will they offer people refunds for the difference.. we'll see...

Till the time apple decides to cut down on the price tag, my wait for owning an iPhone continues...

Data Warehousing on a Shoestring Budget : TDWI

Another interesting set of articles around low budget data warehousing...

Data Warehousing on a Shoestring Budget

Blogged with the Flock Browser

Ideas on DWH Testing...

Recently I was asked about strategy on data warehouse testing.  Realizing how rarely we talk about this, I went to google for this and spend about half an hour searching.

Found the following links, which still need to be researched/analyzed further.

DM Review : Where are the Articles on Data Warehouse Testing and Validation Strategy?

A Wordpress blog : Strategies for Testing Data Warehouse Applications « Business Intelligence and Datawarehousing

DM Review : Strategies for Testing Data Warehouse Applications

Blogged with the Flock Browser

Lookup Transformations in Informatica

Lookup is a transformation to look up the values from a relational table/view or a flat file. There are two types of Lookups in Powercenter, namely;

1. Connected Lookup 
2. Unconnected Lookup

Caching is an important facet of lookup transformation planning. You need to know what kind of data you are dealing with, how frequently do you call the lookup, how frequently does the data change, what is the size of your lookup table etc. among other things. Once you use cache, the trip to database can be avoided, thereby enhancing performance.

Different types of caches can be used with lookup like static, dynamic, persistent, and shared(The dynamic cache cannot be used while creating an un-connected lookup). Each of these has its own identification. For more details, refer to Informatica Transformation Guide.

Lookup is a passive transformation, and can be used either connected or unconnected.  Typically, connected lookup is used when you want to do the lookup for all rows. When you have selective lookup requirements, its normally better to go for unconnected lookup. Unconnected Lookup can be used as if its a function call.

To read more, here is a good article -
What is lookup transformation in informatica? - IT Community

Getting Started with ActiveScaffold

Recently I returned to my rails env to do some more experiments (which might be used for a future project)

To start with, I have seen a bit of active scaffold stuff and was very impressed with it originally already.  In the meantime, rails 2 have arrived.  Quite a few things have changed and I was thinking that it might have an effect on the overall picture of active scaffold also.

However, checking up on railsforum etc brought out that not much effect has gone down to active scaffold kind of plugins from rails 2.

Starting up active scaffold is really easy as they say on its website also...

try it here... www.activescaffold.com It turned out to be really simple to initialize yourself with it. 

Searching a bit more I came across a few tutorials -

Tutorial from Active Scaffold guys

Another one from someone like me, experimenting with activeScaffold...

AkillesBlog » Blog Archive » Ruby on Rails: Experimenting with ActiveScaffold   

Blogged with the Flock Browser