Hi,
Have you used hints in a query involving multiple tables along with alias names for them?

Try hint on a query like this.

SELECT /*+index(IX_ILX_FCT_AR_6)*/A.AGING_BUCKET_KEY, B.AGING_BUCKET_KEY
FROM ILX_FCT_AR A, ILX_FCT_AR_LN B
WHERE A.INV_ID = B.INV_ID
AND A.RECORD_SOURCE_SYSTEM_NAME = B.RECORD_SOURCE_SYSTEM_NAME
AND A.SOURCE_RECORD_TYPE_NAME = B.SOURCE_RECORD_TYPE_NAME
AND A.AGING_BUCKET_KEY=’Z’
AND A.AGING_BUCKET_KEY<> B.AGING_BUCKET_KEY

Get the explain plan now.

Index is still not used???

Yes, because we have to specify the alias too! So here is the right hint to force the usage of the index.

SELECT /*+index(A IX_ILX_FCT_AR_6)*/A.AGING_BUCKET_KEY, B.AGING_BUCKET_KEY
FROM ILX_FCT_AR A, ILX_FCT_AR_LN B
WHERE A.INV_ID = B.INV_ID
AND A.RECORD_SOURCE_SYSTEM_NAME = B.RECORD_SOURCE_SYSTEM_NAME
AND A.SOURCE_RECORD_TYPE_NAME = B.SOURCE_RECORD_TYPE_NAME
AND A.AGING_BUCKET_KEY=’Z’
AND A.AGING_BUCKET_KEY<> B.AGING_BUCKET_KEY

Hi,
I recently purchased O’Reilly’s SQL cookbook. Its good. I came to know about the Oracle Window functions (analytic functions).

Some examples are
1. row_number() over()
2. dense_rank() over()
3. max() over()
4. Sum() over()
5. Lead() over()
6. Lag() over()

See here for a detailed explanation.

Check this example

Now, you have the trade_val column. But you want a running count of the same. How do you achieve it? See the query below. I have used the sum() over() function to get the sum. But hey! Its wrong. Its giving me the entire total and not the running total.

Yes, since I didnt specify anything in the over() section, it gave an entire total. So now I say order by stk_nam_key, date_key so that the running count is done as per the original order. See results below.

But hey! Again there is something different I need. I need the running count for each stk_nam_key and not for the entire table. See in the upper query, I have indicated with red arrow. When the stk_nam_key changed to 2, it gave me the running count. But I want to start fresh for every new grouping.

So what do I do? I say over(partition by stk_nam_key order by stk_nam_key,date_key). Check the results now.

Dont get confused by the first value for the new grouping 3. The date_key for both 3’s are same and hence the sum is same for both.

Hi,

Today, I saw a question in an orkut SQL forum where the guy wanted to transpose rows to columns.

I replied to it.

Let me explain about it.

See this snapshot. I do a query of my test2 table. There are 2 rows. And the 4 weekly data has to be extracted into single column.

See, there are 4 columns that need to be turned into 1 column in 4 rows. So we need to do a cartesian join 4 times. So I use this table below.

See the cartesian join done and the results. See that each ord_no appears 4 times.

Now, using decode, I am getting only what I need.

Hi,
Have you tried any query like this?
select * from time_dim where rownum between 1 and 5
What will be the answer?

Okay, what will be the answer for this one?
select * from time_dim where rownum between 2 and 5

Surprised? Rownum will return any row only if it starts with 1. Everything else will not return anything.

Okay, I guess you knew this part already. Then try this query.
select * from time_dim where rownum between 1 and 5 order by time_key asc

Do you think you will get the first 5 ranks ordered by time_key? Try it. You didnt. You got some 5 records ordered by time_key, not necessarily the top 5.

Why? Because rownum is a pseudo column that is assigned to rows that are fetched. The first 5 records that got fetched were returned with order by on time_key.

Then how do you make sure that you get the first 5 only?

For that you need something called pagination. Try this query
select * from
(
select rownum as rank, a.* from
(
select * from time_dim order by time_key
) a
) b
where rank=8

Here you passed ordered the data from the query in a sub-query. Then passed it to the outer one with the rownum and gave it an alias. Then you used it in the outer query to get that rank.

It is necessary to first order the data and then pass it to an outer query to use the rownum. If you use rownum from the same query where you ordered, it will not be ordered correctly.

Hi,
Today had an issue in production. Right after I moved my job modification to production. To aggravate things, the record count was the highest I had seen in some time! 3 times the average count!

I had to tune the account receivables fact job. I did some changes to the job based on
1. Index modification
2. A few structural changes in DI for better performance
3. Caching lookups, table comparisons whenever small
4. Parallel loading at some places rather than sequential.
5. Other changes

This job had 2 target tables. AR and AR line table. And AR Line had an integrity constraint that AR be loaded first so that the foreign keys are already populated. I didnt know about this constraint.

I went ahead and modified the job such that all data flows that load AR run in parallel with ones that load AR Line table. Clever move, only if that integrity constraint had not been there.

You might ask me “what happened to a process called testing?”

I did test it. And successfully. Wow! How?

The data in my test environment and QA environment only had data upto April 30th. And these records had already been loaded into the AR table.

When I ran my job, it only did updates and no inserts. So, it looked fine. And passed the constraint.

The issue would have occurred only when AR Line was populated first with a new record that was not loaded in AR first.

I should have deleted the records in AR table and AR Line table for atleast one case and tried loading it as an entirely insert load. That might have caught the error.

Today, had to revert the job modification. Talk about proper testing and any tester worth his salt will not appreciate this!

Well, you have to learn from your mistakes. I love to learn from my stumblings!

select dataflow_name, ROW_COUNT, to_date(START_time,’yyyy.mm.dd hh24:mi:ss’), to_date(end_time,’yyyy.mm.dd hh24:mi:ss’), trunc((EXECUTION_TIME/3600))’ Hr ‘trunc((EXECUTION_TIME - trunc((EXECUTION_TIME/3600))*3600)/60)’ Min’ as execution_time
from ALVW_FLOW_STAT
where OBJECT_TYPE = ‘Oracle Loader’
And dataflow_name =’&DF_NAME’
order by end_time desc

This query returns all the rows that fall in a certain rank range. Please note that the second condition is rnum and not rownum. If you use rownum, nothing will be returned.

select * from
( select a.*, rownum rnum from
(select * from expense_fct where expense is not null order by expense desc) a
where rownum <= &upperbound )
where rnum >= &lowerbound

Hi,
This query is for my reference. So dont bother to think why I have written it here.

select time_key,
max(decode(item_key, 3, expense, null)) as Food,
max(decode(item_key, 4, expense, null)) as Phone,
max(decode(item_key, 5, expense, null)) as Travel
from expense_fct
where item_key in (3, 4, 5)
group by time_key

However, if you are keen on knowing something about pivoting queries, here is a good link.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063

Hi,
If you are an Informatica developer, you shouldnt miss this.

Pls visit http://my.informatica.com/ and register with your office IDs. Pls wait for an approval mail, reply to it. Then you can start using it.

(Your company needs to be a partner or client for the registration)

Then click on Velocity. You will find a lot of Best Build Logic, best practises documents, sample deliverables, tips and techniques,etc.

In addition to it, the site also has quite a few templates and documents, Advanced Mapping Concepts, Transformation Techniques, etc that are very useful.