SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Row sum in PIVOT query

ATael-OracleJun 28 2010 — edited Jun 29 2010

All,
I am running 11.2.0.1 on OEL 5 and I am struggling with figuring out how to do a row sum in a pivot query.

Here is the data

CREATE TABLE  "KZ_REG" 
   (	"ID" NUMBER, 
	"AMOUNT" NUMBER, 
	"KZ_NAME_ID" NUMBER, 
	"REGION_ID" NUMBER, 
	 CONSTRAINT "KZ_REG_PK" PRIMARY KEY ("ID") ENABLE
   )
/
CREATE TABLE  "KZ_NAME_LOOKUP" 
   (	"KZ_NAME_ID" NUMBER NOT NULL ENABLE, 
	"KZ_NAME" VARCHAR2(45) NOT NULL ENABLE, 
	 PRIMARY KEY ("KZ_NAME_ID") ENABLE
   )
/
CREATE TABLE  "REGION_LOOKUP" 
   (	"REGION_ID" NUMBER NOT NULL ENABLE, 
	"REGION" VARCHAR2(5) NOT NULL ENABLE, 
	 PRIMARY KEY ("REGION_ID") ENABLE
   )
/
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(1,'EMEA');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(2,'LAD');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(3,'APAC');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(4,'NAS');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(5,'JAPAN');
/
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(1,'KZ_1');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(2,'KZ_2');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(3,'KZ_3');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(4,'KZ_4');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(5,'KZ_5');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(6,'KZ_6');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(7,'KZ_7');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(8,'KZ_8');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(9,'KZ_9');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(10,'KZ_10');
/
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(41,5,2,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(42,7,2,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(43,2,2,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(44,13,2,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(45,0,2,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(46,6,4,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(47,7,4,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(48,2,4,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(49,13,4,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(50,0,4,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(51,20,6,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(52,31,6,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(53,9,6,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(54,19,6,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(55,0,6,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(56,24,1,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(57,39,1,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(58,8,1,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(59,26,1,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(60,0,1,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(61,24,3,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(62,37,3,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(63,8,3,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(64,29,3,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(65,1,3,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(66,26,7,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(67,41,7,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(68,10,7,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(69,23,7,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(70,0,7,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(71,26,5,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(72,40,5,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(73,10,5,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(74,31,5,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(75,0,5,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(76,29,8,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(77,42,8,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(78,10,8,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(79,35,8,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(80,0,8,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(81,29,9,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(82,49,9,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(83,17,9,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(84,43,9,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(85,2,9,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(86,47,10,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(87,67,10,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(88,25,10,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(89,36,10,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(90,1,10,5);
/

Here is my query:

SELECT kn,
  EMEA,
  LAD,
  APAC,
  NAS,
  JAPAN
FROM
  (SELECT KZ.KZ_NAME AS kn,
    RL.REGION        AS re,
    KR.AMOUNT        AS am
  FROM REGION_LOOKUP RL,
    KZ_NAME_LOOKUP KZ,
    KZ_REG KR
  WHERE rl.region_id       = kr.region_id
  AND kz.kz_name_id        = kr.kz_name_id
  ) pivot (SUM(AM) FOR re IN ('EMEA' AS EMEA, 'LAD' AS LAD, 'APAC' AS APAC, 'NAS' AS NAS, 'JAPAN' AS JAPAN))
ORDER BY kn

which returns the following, I changed the KN values as the data is sort of sensitve:

KN	EMEA	LAD	APAC	NAS	JAPAN
KZ_XX	37	8	24	29	1
KZ_XX	31	9	20	19	0
KZ_XX	7	2	5	13	0
KZ_XX	41	10	26	23	0
KZ_XX	42	10	29	35	0
KZ_XX	7	2	6	13	0
KZ_XX	40	10	26	31	0
KZ_XX	49	17	29	43	2
KZ_XX	67	25	47	36	1
KZ_XX	39	8	24	26	0

But what I'd like to get is something like this, note tha extra line and column Total.

KN	EMEA	LAD	APAC	NAS	JAPAN	Total
KZ_XX	37	8	24	29	1	99
KZ_XX	31	9	20	19	0	79
KZ_XX	7	2	5	13	0	27
KZ_XX	41	10	26	23	0	100
KZ_XX	42	10	29	35	0	116
KZ_XX	7	2	6	13	0	28
KZ_XX	40	10	26	31	0	107
KZ_XX	49	17	29	43	2	140
KZ_XX	67	25	47	36	1	176
KZ_XX	39	8	24	26	0	97
Total	360	101	236	268	4	969

Any idea on how I could accomplish this?

Thanks
Andy

L. Fernigrini

The way sequences work, when you use the CACHE option, the first time you use a number for the sequence, Oracle will return the first number to you, it will be incremented by 5000 (in your example) the lates used value on the sequence definition, and will store the 4999 remaining unused numbers in memory (cache).

The next time you ask for a number to the sequence, rather than searching from it on the definition adding one, and saving again the sequence definition, Oracle just return the next available value from the cache, making thing notable quicker.

The "bad" thing is, since those values are in memory, if the instance is shut down (normally or abnormally) those values are lost. That's the expected behavior.

The next time the sequence is used after an instance restart, it will get the value from the definition, add 5000 and save it, return the first one and store the other 4999 in the cache.

Instance shutdown is not something frequent and you should not worry about that, also sequence should be used (my opinion) to create PK (surrogate keys) values that are not shown to final users, so there should be no issues in having some gaps from time to time (the same would happen if you rollback a transaction, the sequence value is lost and gap is generated). Sequences are not gap-less.

jflack

You know that a shutdown and startup will lose cached sequences. Export and Import can also reload the sequence cache. But why do you care? Oracle makes clear that values generated by sequences can be generated out of order and can skip numbers. The only guarantee is that you get a new unique number every time you read NEXTVAL. Which is perfect for generating surrogate primary keys. If you care, then DON'T use sequences, but accept the performance hit that you'll take for using any other method for generating sequential values.

Sven W.

L. Fernigrini wrote:

...

The "bad" thing is, since those values are in memory, if the instance is shut down (normally or abnormally) those values are lost. That's the expected behavior.

...

That is not correct.

A normal shutdown, a shutdown transactional and a shutdown immediate will NOT loose the cached sequence values (unless you are in a very old database version like Oracle 7).

Only a SHUTDOWN ABORT will loose the cache.

To the OP: I recently wrote about some further ideas why IDs sometimes are or seems to be lost:

https://svenweller.wordpress.com/2019/08/20/some-quick-facts-about-sequence-caches-and-gaps-in-ids/

The most common reason is when a sequence ages out of the shared pool. You can either keep the sequence pinned in the shared pool or increase your shared pool size.

1 person found this helpful

L. Fernigrini

Well, i kind of remember seeing that on 9i (9.2.0.7), but I may be wrong, I never cared a lot about gaps.

If normal shutdowns save the last used value then the chances of having gaps with sequences is not very common.

I've read you post, it's really interesting, I will test the pin option ASAP

Sven W.

L. Fernigrini wrote:

Well, i kind of remember seeing that on 9i (9.2.0.7), but I may be wrong, I never cared a lot about gaps.

If normal shutdowns save the last used value then the chances of having gaps with sequences is not very common.

I've read you post, it's really interesting, I will test the pin option ASAP

I know the behaviour during shutdown is documented somewhere in detail, but I just looked for 15 min and could not find it.

My point is we should not care about small gaps, but we should care about frequent large gaps.

Those kind of gaps are an indication that something is sub-optimal with our system.

And this is excatly what OP seems to be doing.

-- Edit:

Also I feel he need to add. If you do a normal or transactional shutdown on a RAC, then cached values probably might be lost.

The reason is, that during shutdown the last value needs to be persisted. And in that case I expect that the highest used value from one node is used. Which also means the cached values from the other node will be lost.

2 people found this helpful

Shamed H

Thank you for your reply ... And you are almost to the problem statemnt that I have posted !! The shared pool flush also will reset the CACHE for sequence I have one more , If we provide any GRANT on the seqience to other schema , will that also reset the CACHE ? We did a test on this and we found it is retesting the CACHE , if the first CACHED sequence is used atleast by one. I am not sure this is also a cuase for the sequence GAP

Sven W.

Answer

Shamed H wrote:

Yes ALTER SYSTEM FLUSH SHARED POOL will also loose the cached values. Are you flushing the shared pool frequently? Why?
I would not expect a GRANT on a sequence to loose the sequence cache. I did NOT test it thou. Can you share how you tested it?

Marked as Answer by Shamed H · Sep 27 2020

1 person found this helpful

Shamed H

Thanks Sven. I was using the Alter command to forward the sequence to some speficif number and then I was granting to other users. I was thinking the grant is making the CACHED lost , but I understand from testing that the ALTER comment is making the cache lose , rather then the grant

Sven W.

Depending on your exact database version here is a nice little (undocumented) trick to reset a sequence:

ALTER SEQUENCE mySeq RESTART START WITH yourNewValue;

This should work in 12.2.0.1 already.

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 27 2010

Added on Jun 28 2010

8 comments

50,671 views

SQL & PL/SQL

Row sum in PIVOT query

Comments

Post Details