Very cool
Dan Kefford, February 14, 2005 - 1:40 pm UTC
Tom...
Do you need to actually update the product table, or would a SELECT... FOR UPDATE suffice?
February 14, 2005 - 5:37 pm UTC
serializable....
the select on product_booking would not see any changes (since you issued the set transaction).
so, the transactions would serialize, but not necessarily see others work.
the update is there to raise an ora-8177 if someone else updated that table, I'd rather fail than get the wrong answer ;)
if you don't use serializable, select for update would do it.
Play with two sessions and read committed/serializable -- with select for update vs update. This is important stuff at the end of the day (to be able to recognize)
I'm still learning sometimes myself (programmers tend to think "linear", the database however thinks "massively concurrent"...)
Amazing
Anil, February 14, 2005 - 2:03 pm UTC
Amazing Tom, This is simply superb, I didn't expect such a wonderful answer in such short time. This is very usefull.
I added one additional predicate to make it work for multiple product(other wise query was giving ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
)
. Hope it does not change anything. Initial tests proved every thing fine
select x.dt,
(select pieces
from product
where
prod_code='PXP' and -- Added this line
sdate <= x.dt
and edate >= x.dt )
- nvl(sum(pb.bkd_pieces),0) really_available
from product_booking pb,
(select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
from dual
connect by 1=1
and rownum <=
to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
) x
where x.dt between pb.sdate(+) and pb.edate(+)
and pb.prod_code(+) = 'PXP'
group by x.dt
Thanks once again for the wonderful answer.
Rgds
Anil
February 14, 2005 - 5:44 pm UTC
exactly -- i missed that predicate (should have myself expanded your test case!)
(thanks to a guy named Mikito for that neat connect by trick with dual btw ;)
Correction
Tamil, February 14, 2005 - 2:30 pm UTC
Tom,
Excellent explanation and very useful SQL statements.
How do you get 15 rows for the following query:
var x varchar2(30);
var y varchar2(30);
exec :x := '01-JAN-2005';
exec :y := '15-JAN-2005';
select x.dt
,
(select pieces
from product
where sdate <= x.dt
and edate >= x.dt )
- nvl(sum(pb.bkd_pieces),0) really_available
from product_booking pb,
(select to_date(:x,'DD-MON-YYYY')+rownum-1 dt
from dual
connect by 1=1
and rownum <=
(to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY'))
) x
where x.dt between pb.sdate(+) and pb.edate(+)
and pb.prod_code(+) = 'PXP'
group by x.dt
;
13:32:08 SQL> @gen_dates
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
DT REALLY_AVAILABLE
--------- ----------------
01_JAN-05 10
02_JAN-05 10
03_JAN-05 10
04_JAN-05 10
05_JAN-05 10
06_JAN-05 10
07_JAN-05 10
08_JAN-05 10
09_JAN-05 10
10_JAN-05 10
11_JAN-05 10
12_JAN-05 10
13_JAN-05 5
14_JAN-05 5
14 rows selected.
I got only 14 days.
If I modify the line
and rownum <=
(to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY'))+1,
I get 15 rows ( including the last day 15-JAN-2005).
Thanks
Tamil
February 14, 2005 - 5:50 pm UTC
hmm, something different between 9ir2 and 10gr1 on that.
ok, the dual think is a trick -- use any table with "plenty of rows" instead of the connect by.
OR
use
(select to_date(:x,'DD-MON-YYYY')+rownum-1 dt
from dual
connect by 1=1
and LEVEL <=
(to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY') +1)
which is consistent -- and I'll file a bug.
Some doubts
Anil, February 14, 2005 - 2:48 pm UTC
Hi Tom
Some clarifiactions, Why below SQl returns different results in Oracle 9i. I am getting only one row in 9i but in 10g I am getting 15 rows
variable x varchar2(20)
variable y varchar2(20)
exec :x := '01-jan-2005'; :y := '15-jan-2005';
select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
from dual
connect by 1=1
and rownum <=
to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
DT
---------
01-JAN-05
In 10g
========
select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
2 from dual
3 connect by 1=1
4 and rownum <=
5 to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
6
ANIL@NGDEV1-SQL> /
DT
---------
01-JAN-05
02-JAN-05
03-JAN-05
04-JAN-05
05-JAN-05
06-JAN-05
07-JAN-05
08-JAN-05
09-JAN-05
10-JAN-05
11-JAN-05
12-JAN-05
13-JAN-05
14-JAN-05
15-JAN-05
Would you please explain what is the difference between connect by 1=1 and connect by prior ...
Rgds
Anil
February 14, 2005 - 5:52 pm UTC
see above -- use level.
prior is just a modifier.
connect by PRIOR column = something
says "look at the row you just processed, the prior row, grab COLUMN from that, now find all of the rows where something = that prior value"
nice locking technique...oracle does the Job for us :)
pasko, February 14, 2005 - 3:42 pm UTC
Hi Tom,
thanks for a nice Locking Technique there, but i have several Questions:
1.
I would like know why didn't you use the 'select for update' on product Table and opted for the update instead?
Is this a better Technique than using the for update clause?
-->Does it mean that all Concurrent Users wanting to Book Pieces will be forced to wait on that single Update for a given Product ? Is this locking guaranteed to happen without causing any Deadlocks ?
2.Is it also necessary to lock the Product_Booking Table first , even before locking the Product-Table ?
Thanks in advance.
February 14, 2005 - 6:00 pm UTC
1) see above -- serializable.
the goal is -- you HAVE to serialize at the product level, have to. overlap detection is IMPOSSIBLE.
it won't deadlock, it'll block and wait and in serializable isolation, it'll get a 8177 if someone modified the dependant data (which is important -- you want to FAIL and restart the transaction, you have to)
2) nope, you serialize at the level of a product, easiest way to do that is lock the product row
Great Nice to know that dual can be used for this
Raj, February 14, 2005 - 4:11 pm UTC
Hi Tom,
Thanks for all your help and great website.
I tried this it works for the date difference upto 25 days and does not working beyond it. What I mean is when I give :x = '01-JAN-2005' and :y = '31-JAN-2005' I only get date upto 01-25-2005 and not upto 01/31/2005.
February 14, 2005 - 6:02 pm UTC
are you sure, version? cut and paste -- i cannot reproduce.
I am sorry my mistake
Raj, February 14, 2005 - 7:22 pm UTC
Hi Tom,
I am sorry, I was running below given SQL in TOAD hence I had that problem. Now I tried with extra select on top of it and it works like a charm.
OLD
select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
from dual
connect by 1=1
and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy' )
NEW
select * from (
select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
from dual
connect by 1=1
and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy' ) )
Thank you for immediate response and correcting me.
DB Version 9.2.0.3
Interesting...
Doug, February 15, 2005 - 9:03 am UTC
I get different results from this query from different versions. In 9iR2, I only get one row unless I use it as a subquery. It only returns one row - and doesn't display the number of rows selected.
10g returns one more row than 9i.
This was run with SQL*Plus 9.2.0.5:
doug@DOUG> select * from v$version where rownum < 2;
BANNER
----------------------------------------------------------------
Personal Oracle9i Release 9.2.0.5.0 - Production
doug@DOUG> select rownum from dual connect by 1=1 and rownum <= 10;
ROWNUM
----------
1
doug@DOUG> select * from (select rownum from dual connect by 1=1 and rownum <= 10);
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
doug@DOUG> @connect doug/<pw>@d650
doug@D650> select * from v$version where rownum < 2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
doug@D650> select rownum from dual connect by 1=1 and rownum <= 10;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
11 rows selected.
CONNECT BY trick not quite working
Dan Kefford, February 15, 2005 - 9:16 am UTC
Tom...
I, too, was not able to reproduce the behavior of your/Mikito's CONNECT BY trick until I inlined the SQL:
SQL> variable x varchar2(20)
SQL> variable y varchar2(20)
SQL> exec :x := '01-jan-2005';
PL/SQL procedure successfully completed.
SQL> exec :y := '15-jan-2005';
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 select to_date(:x,'DD-MON-YYYY')+rownum-1 dt
2 from dual
3 connect by 1=1
4 and LEVEL <=
5* (to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY') +1)
SQL> /
DT
---------
01-JAN-05
SQL> ed
Wrote file afiedt.buf
1 select * from
2 (
3 select to_date(:x,'DD-MON-YYYY')+rownum-1 dt
4 from dual
5 connect by 1=1
6 and LEVEL <=
7 (to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY') +1)
8* )
SQL> /
DT
---------
01-JAN-05
02-JAN-05
03-JAN-05
04-JAN-05
05-JAN-05
06-JAN-05
07-JAN-05
08-JAN-05
09-JAN-05
10-JAN-05
11-JAN-05
12-JAN-05
13-JAN-05
14-JAN-05
15-JAN-05
15 rows selected.
I am using Oracle9iEE 9.2.0.4 on AIX 5.1.
Wow, what can I say, way cool thread
Kevin Meade, February 15, 2005 - 11:05 am UTC
The people I work with think I am good, but you... you are a freak Tom (in a good way (he smiles slyly)). I didn't understand half what people were saying here and you blamm out way cool answers in under 30 minutes. I've been doing Oracle since 1985? and I still get humbled ever time I come to this site. Thanks Tom, and thanks to you guys who asked these questions, great stuff; this old dog still learns tricks from all of you.
Kevin Meade
SQL Check Constraints or Trigger?
David R. Thrash, February 23, 2005 - 3:33 pm UTC
Tom;
What's your opinion on declarative constraints such as those allowed by the
ANSI SQL standard? Specifically Multi-Row/Multi-Table CHECK constraints and
database ASSERTIONS. If you agree that these constructs are useful could you
influence Oracle Corporation to fully support them in their RDBMS?
Seems the main problem addressed in this thread could be more easily solved
using declarative CHECK constraints. In lieu of constraints what's you opinion
on using a trigger? Wouldn't all of the concurrency issues be "automigically"
handled by the RDBMS? Also the constraint or trigger would work for the
UPDATE operator too.
Note: The RDBMS should optimize CHECK constraints by utilizing
the fact that only modified rows can violate a CHECK condition.
Unfortunately The RDBMS has no such opportunity with triggers.
Thanks for your comments and efforts here at "Ask Tom".
Concretely, he's my nickels worth showing the check constraints, commented out, and a
trigger solution.
/* Generate IOT one-time - Add four years */
CREATE TABLE production_date (
adate DATE PRIMARY KEY,
CONSTRAINT adate_midnight CHECK ( adate = TRUNC( adate ) )
)
ORGANIZATION INDEX
/
INSERT INTO production_date
SELECT TRUNC( SYSDATE ) - 365 * 2 + LEVEL
FROM DUAL CONNECT BY LEVEL < 365 * 4
/
CREATE TABLE product (
prod_code VARCHAR2( 10 ) NOT NULL,
sdate NOT NULL REFERENCES production_date,
edate NOT NULL REFERENCES production_date,
pieces NUMBER NOT NULL,
CONSTRAINT product_pk PRIMARY KEY ( prod_code, sdate, edate ),
CONSTRAINT product_temporal_pk1 CHECK ( sdate < edate ),
CONSTRAINT pieces_gt_0 CHECK ( pieces > 0 )
--
-- table constraint prohibiting overlapping production dates
-- not supported by Oracle. Must be coded as TRIGGER.
--
/*
CONSTRAINT product_temporal_pk2
CHECK ( NOT EXISTS
( SELECT 1
FROM product p1, product p2
WHERE p1.prod_code = p2.prod_code
AND ( p1.sdate BETWEEN p2.sdate AND p2.edate OR
p1.edate BETWEEN p2.sdate AND p2.edate )
)
)
*/
)
/
CREATE TABLE product_booking (
txn_number VARCHAR2( 1 ) PRIMARY KEY, -- Just had to add a PK
prod_code VARCHAR2( 10 ) NOT NULL,
sdate NOT NULL REFERENCES production_date,
edate NOT NULL REFERENCES production_date,
bkd_pieces NUMBER NOT NULL,
/*
CONSTRAINT prod_code_ck CHECK ( NOT EXSSTS
( SELECT prod_code FROM product_booking
MINUS
SELECT prod_code FROM product )
),
*/
CONSTRAINT bkd_pieces_gt_0 CHECK ( bkd_pieces > 0 )
--
-- database constraint to prohibit overbooking not supported
-- by Oracle. MUST be coded as TRIGGER.
--
/*
CONSTRANT qty_on_hand_all_days CHECK
( NOT EXISTS
( SELECT 1
FROM ( SELECT prod_Code, adate, pieces
FROM product, production_date
WHERE adate BETWEEN sdate AND edate
) p,
( SELECT prod_code, adate, SUM( bkd_pieces ) bkd_pieces
FROM product_booking, production_date
WHERE adate BETWEEN sdate AND edate
GROUP BY prod_code, adate
) b
WHERE p.prod_code = b.prod_code
AND p.adate = b.adate
AND p.pieces < b.bkd_pieces
)
)
*/
)
/
CREATE OR REPLACE TRIGGER product_booking_aiu
AFTER INSERT OR UPDATE ON product_booking
BEGIN
-- Get the overbookings
FOR aErr IN
( SELECT 'Overbooking of ' || p.prod_code || ' by ' ||
TO_CHAR( b.bkd_pieces - p.pieces ) ||
' pieces on ' || TO_CHAR( p.adate, 'dd-MON-YYYY' ) msg
FROM ( SELECT prod_code, adate, pieces
FROM product, production_date
WHERE adate BETWEEN sdate AND edate
) p,
( SELECT prod_code, adate, SUM( bkd_pieces ) bkd_pieces
FROM product_booking, production_date
WHERE adate BETWEEN sdate AND edate
GROUP BY prod_code, adate
) b
WHERE p.prod_code = b.prod_code
AND p.adate = b.adate
AND p.pieces < b.bkd_pieces
ORDER by p.adate asc ) LOOP
RAISE_APPLICATION_ERROR( -20001, aErr.msg );
END LOOP;
END;
/
14:12:10 DTHRASH@zeus9i >select * from product;
more...
PROD_CODE SDATE EDATE PIECES
---------- --------- --------- ----------
PXP 01-JAN-05 31-JAN-05 20
1 row selected.
Elapsed: 00:00:00.86
14:12:15 DTHRASH@zeus9i >select * from product_booking;
more...
T PROD_CODE SDATE EDATE BKD_PIECES
- ---------- --------- --------- ----------
A PXP 01-JAN-05 15-JAN-05 10
B PXP 13-JAN-05 25-JAN-05 5
2 rows selected.
1 insert into product_booking
2* values ( 'C', 'PXP', to_date('01-jan-2005'), to_date('07-jan-2005'), 7 )
1 row created.
Elapsed: 00:00:00.56
14:14:00 DTHRASH@zeus9i >commit;
Commit complete.
14:14:51 DTHRASH@zeus9i >select * from product_booking;
more...
T PROD_CODE SDATE EDATE BKD_PIECES
- ---------- --------- --------- ----------
A PXP 01-JAN-05 15-JAN-05 10
B PXP 13-JAN-05 25-JAN-05 5
C PXP 01-JAN-05 07-JAN-05 7
3 rows selected.
14:15:03 DTHRASH@zeus9i >delete from product_booking where txn_number = 'C';
1 row deleted.
Elapsed: 00:00:00.15
14:15:48 DTHRASH@zeus9i >commit;
Commit complete.
1 insert into product_booking
2* values ( 'D', 'PXP', to_date('01-jan-2005'), to_date('15-jan-2005'), 7 )
insert into product_booking
*
ERROR at line 1:
ORA-20001: Overbooking of PXP by 2 pieces on 13-JAN-2005
ORA-06512: at "DTHRASH.PRODUCT_BOOKING_AIU", line 20
ORA-04088: error during execution of trigger 'DTHRASH.PRODUCT_BOOKING_AIU'
February 24, 2005 - 5:20 am UTC
all feature requests must be logged via support -- else they will never get into the system.
I can say 'this would be really good to have', but what we need is for customers to state their desire for a feature with a business case to back it up. That is how you get new features into the database.
The ansi sql standard is by now so huge that I doubt any vendor will fully comply with it at any point in time (opinion). I remember the first copy I saw in the 80's, it was a pamphlet, now it is a multi-volume document.
I cannot really comment on the check constraint in this example (since it doesn't exist) but.... you are missing some things with the trigger.
tell you what, to make the thing simplier to see (why you will have a really hard time enforcing a constraint that CROSSES ROWS in a table or CROSSES tables -- like referential integrity) do this:
try to write a trigger in the manner you did above that makes it so that only one row can be in a table.
Then, open two sessions and in each, insert a row into the table. You'll find your trigger in session 1 won't see session2's insert (perhaps because it hasn't happened) but you'll also find that session 2's trigger won't see session 1's inserted record because we haven't committed.
When you commit both sessions, you'll find from a third session that you have 2 rows in there, but the trigger did fire both times, it just could not see the other sessions data.
Period Query
ANUPAM MANNA, February 24, 2005 - 5:41 am UTC
Hi Tom,
It's good and useful.
My problem is as below:
CREATE TABLE EMP_HOURS
(EMP_ID NUMBER(6),
TRAN_DATE DATE,
HOURS_WORKED NUMBER(4,2),
CONSTRAINT PK_EMP_HOURS PRIMARY KEY(EMP_ID,TRAN_DATE))
/
I want a query which will display all the employees in each date even though
employees didn't book his time on a particular date.If the employee didn't
book time on a particular date then HOURS_WORKED will be shown as 0.You can
consider the period as one month.
INSERT INTO EMP_HOURS(1,'01-jan-2005',8.5);
INSERT INTO EMP_HOURS(1,'03-jan-2005',8);
INSERT INTO EMP_HOURS(1,'04-jan-2005',9.5);
INSERT INTO EMP_HOURS(1,'06-jan-2005',8);
INSERT INTO EMP_HOURS(1,'08-jan-2005',8.5);
INSERT INTO EMP_HOURS(1,'09-jan-2005',9.5);
INSERT INTO EMP_HOURS(1,'10-jan-2005',10.5);
INSERT INTO EMP_HOURS(1,'11-jan-2005',8);
INSERT INTO EMP_HOURS(1,'13-jan-2005',8);
INSERT INTO EMP_HOURS(1,'15-jan-2005',7.6);
INSERT INTO EMP_HOURS(1,'16-jan-2005',6);
INSERT INTO EMP_HOURS(1,'17-jan-2005',8);
INSERT INTO EMP_HOURS(1,'21-jan-2005',8);
INSERT INTO EMP_HOURS(1,'29-jan-2005',5);
INSERT INTO EMP_HOURS(2,'01-jan-2005',9.5);
INSERT INTO EMP_HOURS(2,'02-jan-2005',10);
INSERT INTO EMP_HOURS(2,'06-jan-2005',11);
INSERT INTO EMP_HOURS(2,'09-jan-2005',12);
INSERT INTO EMP_HOURS(2,'10-jan-2005',16);
INSERT INTO EMP_HOURS(2,'11-jan-2005',7.5);
INSERT INTO EMP_HOURS(2,'12-jan-2005',4.5);
INSERT INTO EMP_HOURS(2,'13-jan-2005',9);
INSERT INTO EMP_HOURS(2,'14-jan-2005',10);
INSERT INTO EMP_HOURS(2,'17-jan-2005',8);
INSERT INTO EMP_HOURS(2,'18-jan-2005',8);
INSERT INTO EMP_HOURS(2,'22-jan-2005',8);
INSERT INTO EMP_HOURS(2,'25-jan-2005',8);
INSERT INTO EMP_HOURS(2,'30-jan-2005',8);
INSERT INTO EMP_HOURS(2,'31-jan-2005',8);
INSERT INTO EMP_HOURS(3,'01-jan-2005',10.5);
INSERT INTO EMP_HOURS(2,'02-jan-2005',9);
INSERT INTO EMP_HOURS(2,'04-jan-2005',11);
INSERT INTO EMP_HOURS(2,'06-jan-2005',12);
INSERT INTO EMP_HOURS(2,'8-jan-2005',16);
INSERT INTO EMP_HOURS(2,'10-jan-2005',7.5);
INSERT INTO EMP_HOURS(2,'12-jan-2005',4.5);
INSERT INTO EMP_HOURS(2,'14-jan-2005',9);
INSERT INTO EMP_HOURS(2,'16-jan-2005',10);
INSERT INTO EMP_HOURS(2,'20-jan-2005',4);
INSERT INTO EMP_HOURS(2,'22-jan-2005',8);
INSERT INTO EMP_HOURS(2,'24-jan-2005',8);
INSERT INTO EMP_HOURS(2,'26-jan-2005',11);
INSERT INTO EMP_HOURS(2,'28-jan-2005',5);
INSERT INTO EMP_HOURS(2,'30-jan-2005',6);
Therefore employee 1,2,3 should have output in each date in between the range
'01-jan-2005' to '31-jan-2005'.
Regards,
Anupam Manna
February 24, 2005 - 5:52 am UTC
what version.
Period Query
Anupam, February 24, 2005 - 5:52 am UTC
Previous asked Insert statements having problem ...
Here are the corrections
INSERT INTO EMP_HOURS VALUES(1,'01-jan-2005',8.5);
INSERT INTO EMP_HOURS VALUES(1,'03-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'04-jan-2005',9.5);
INSERT INTO EMP_HOURS VALUES(1,'06-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'08-jan-2005',8.5);
INSERT INTO EMP_HOURS VALUES(1,'09-jan-2005',9.5);
INSERT INTO EMP_HOURS VALUES(1,'10-jan-2005',10.5);
INSERT INTO EMP_HOURS VALUES(1,'11-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'13-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'15-jan-2005',7.6);
INSERT INTO EMP_HOURS VALUES(1,'16-jan-2005',6);
INSERT INTO EMP_HOURS VALUES(1,'17-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'21-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'29-jan-2005',5);
INSERT INTO EMP_HOURS VALUES(2,'01-jan-2005',9.5);
INSERT INTO EMP_HOURS VALUES(2,'02-jan-2005',10);
INSERT INTO EMP_HOURS VALUES(2,'06-jan-2005',11);
INSERT INTO EMP_HOURS VALUES(2,'09-jan-2005',12);
INSERT INTO EMP_HOURS VALUES(2,'10-jan-2005',16);
INSERT INTO EMP_HOURS VALUES(2,'11-jan-2005',7.5);
INSERT INTO EMP_HOURS VALUES(2,'12-jan-2005',4.5);
INSERT INTO EMP_HOURS VALUES(2,'13-jan-2005',9);
INSERT INTO EMP_HOURS VALUES(2,'14-jan-2005',10);
INSERT INTO EMP_HOURS VALUES(2,'17-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'18-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'22-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'25-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'30-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'31-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(3,'01-jan-2005',10.5);
INSERT INTO EMP_HOURS VALUES(3,'02-jan-2005',9);
INSERT INTO EMP_HOURS VALUES(3,'04-jan-2005',11);
INSERT INTO EMP_HOURS VALUES(3,'06-jan-2005',12);
INSERT INTO EMP_HOURS VALUES(3,'8-jan-2005',16);
INSERT INTO EMP_HOURS VALUES(3,'10-jan-2005',7.5);
INSERT INTO EMP_HOURS VALUES(3,'12-jan-2005',4.5);
INSERT INTO EMP_HOURS VALUES(3,'14-jan-2005',9);
INSERT INTO EMP_HOURS VALUES(3,'16-jan-2005',10);
INSERT INTO EMP_HOURS VALUES(3,'20-jan-2005',4);
INSERT INTO EMP_HOURS VALUES(3,'22-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(3,'24-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(3,'26-jan-2005',11);
INSERT INTO EMP_HOURS VALUES(3,'28-jan-2005',5);
INSERT INTO EMP_HOURS VALUES(3,'30-jan-2005',6);
Period Query
Anupam, February 24, 2005 - 5:54 am UTC
I am using Oracle 9i earlier version(9.0.1) ...
Please do the needful ...
February 24, 2005 - 6:34 am UTC
ops$tkyte@ORA9IR2> with
2 dates
3 as
4 (select to_date(:x,'mon-yyyy')+level-1 dt
5 from dual
6 connect by 1=1
7 and level <= to_number(to_char(last_day(to_date(:x,'mon-yyyy')),'dd'))
8 ),
9 emps
10 as
11 (select distinct emp_id from emp_hours -- if you have a table of emps elsewhere, use it
12 ),
13 both
14 as
15 (select * from dates, emps)
16 select *
17 from both, emp_hours
18 where both.emp_id = emp_hours.emp_id(+)
19 and both.dt = emp_hours.tran_date(+)
20 /
DT EMP_ID EMP_ID TRAN_DATE HOURS_WORKED
--------- ---------- ---------- --------- ------------
01-JAN-05 1 1 01-JAN-05 8.5
02-JAN-05 1
03-JAN-05 1 1 03-JAN-05 8
04-JAN-05 1 1 04-JAN-05 9.5
05-JAN-05 1
06-JAN-05 1 1 06-JAN-05 8
......
You need to generate the set of dates, the set of distinct emp_ids, cartesian product that together to get all of the dates/emp_ids you want and outer join to that the hours.
In 10g, you'll be able to use a partitioned outer join....
ops$tkyte@ORA10G> with
2 dates
3 as
4 (select to_date(:x,'mon-yyyy')+level-1 dt
5 from dual
6 connect by 1=1
7 and level <= to_number(to_char(last_day(to_date(:x,'mon-yyyy')),'dd'))
8 )
9 select *
10 from emp_hours partition by (emp_id)
11 right outer join dates on (dates.dt=emp_hours.tran_date)
12 /
EMP_ID TRAN_DATE HOURS_WORKED DT
---------- --------- ------------ ---------
1 01-JAN-05 8.5 01-JAN-05
1 02-JAN-05
1 03-JAN-05 8 03-JAN-05
1 04-JAN-05 9.5 04-JAN-05
1 05-JAN-05
1 06-JAN-05 8 06-JAN-05
1 07-JAN-05
.......
SQL Check Constraints
David R. Thrash, February 24, 2005 - 9:35 pm UTC
Thanks for your reply.
You write: "you will have a really hard time enforcing a constraint that CROSSES ROWS in a table or CROSSES tables...".
I couldn't agree more; enforcing business rules using procedural code is MOST difficult and error prone. Using declarative constraints delegates the enforcement work to the DBMS. The programmer is not concerned about serialization (with respect to the constraint) because the DBMS enforces the constraint AT ALL TIMES.
As you point out, the DBMS has a lot of issues to consider but it is in a much better position to do it efficiently and with less chance of error, than having every programmer write their own routines.
Alas, moving the mountain named Oracle Corp will take more than an enhancement request and one lone wolf. Hopefully forums, like this one, will inform programmers of not only solutions that can be implemented with the current product but also of solutions discussed and debated by many people ( ANSI committee ) much more qualified than me.
February 25, 2005 - 4:44 pm UTC
But I hope that means you actually filed the request -- for unless you do, all of the comments out here not withstanding -- it won't stand a chance.
Period Query
Anupam Manna, February 25, 2005 - 12:58 am UTC
Dear Tom,
It's really helpful for me what you have suggested.Thanks for your quick operation.
Please tell me if I make the query like as below:
1 SELECT P.EMP_ID,P.DT,NVL(Q.HOURS_WORKED,0) WHRS
2 FROM
3 (SELECT B.EMP_ID,A.X DT
4 FROM
5 (SELECT X FROM
6 (SELECT TO_DATE('01-JAN-2005','DD/MON/YYYY')+ROWNUM-1 "X"
7 FROM ALL_OBJECTS WHERE ROWNUM <= 31)) A,
8 (SELECT DISTINCT EMP_ID FROM EMP_HOURS) B) P,
9 (SELECT EMP_ID,TRAN_DATE,HOURS_WORKED FROM EMP_HOURS) Q
10 WHERE P.EMP_ID=Q.EMP_ID(+)
11* AND P.DT=Q.TRAN_DATE(+)
SQL> /
EMP_ID DT WHRS
---------- --------- ----------
1 01-JAN-05 8.5
1 02-JAN-05 0
1 03-JAN-05 8
2 01-JAN-05 9.5
2 02-JAN-05 10
EMP_ID DT WHRS
---------- --------- ----------
2 03-JAN-05 0
2 04-JAN-05 0
3 01-JAN-05 10.5
3 02-JAN-05 9
3 03-JAN-05 0
3 04-JAN-05 11
EMP_ID DT WHRS
---------- --------- ----------
3 05-JAN-05 0
Is there any peformance hit?
My second question is related with architecture(9.0.1).
If I have a dedicated Oracle Server with 1GB RAM. I made the parameters as :
db_block_size=16384 bytes = 16KB
db_cache_size=536870912 bytes = 512MB
java_pool_size=33554432 bytes = 32MB
large_pool_size=67108864 bytes = 64MB
shared_pool_reserved_size=4194304 bytes = 4MB
shared_pool_size=33554432 bytes =32MB
log_buffer=8388608 bytes=8MB
sort_area_retained_size=1048576 bytes = 1MB
sort_area_size=1048576 bytes = 1MB
And all the data tablespace,Temp make local management with 1 MB uniform extent size.
So there is any problem with this configuration?
February 25, 2005 - 4:57 pm UTC
is there a performance hit?
well, tell me how to evaluate that -- a performance hit is comparable only across two different implementations that do the same thing. Here we have "one" method. So, no -- no performance "hit", just the necessary processing to generate the dates, generate the unique emp_ids (like i said, you probably have them in a table elsewhere and don't need to distinct), generate the cartesian product of them and outer join that to the details.
if the database starts up, there are no problems with the configuration.
Now, is it the best setup for you? No one can answer that without knowing your system a bit better ;) (not asking you to put that here -- just a comment that if there were a 'best' configuration, we would have no parameters..)
Period Query
Anupam, June 08, 2006 - 4:55 pm UTC
Thanks for your response.