Code:
SELECT cli_id, ship_id, s1 - s2 FROM ( SELECT cli_id, ship_id, sum(ship_qty) s1 FROM shipment GROUP BY ship_id, cli_id ) a, ( SELECT ship_id, sum(proc_qty) s2 FROM processed GROUP BY ship_id ) b WHERE a.ship_id = b.ship_id
Gints...
gintsp
Last Activity: Mar 21 '08, 02:46 PM
Joined: Aug 2 '07
Location:
-
Something like
BTW it is not clear from your description how many shipments one client may have.
Gints... -
Do not use this max page no.
Are you completely sure that customers are really using it and it add some REAL value?
What is this report on million pages? Where is THAT PERSON who can read a report on million pages. I'd like to meet him, he really deserves to be in Guiness book.
Think whether your requirement isn't one of these bad requirements.
Gints Plivna
http://www.gplivna.eu...Leave a comment:
-
Mmmm, what is the result of count(*)?
Are you sure that normal query (i.e. SELECT * ) returns __ALL__ rows or just some N first ones?
Gints Plivna
http://www.gplivna.eu...Leave a comment:
-
Using google and searching for oracle scripts will give so much scripts that you'll be fed all your lifetime ;)
Gints Plivna...Leave a comment:
-
Leave a comment:
-
Each and every version of Oracle DB has New features guide containing features that are new compared to previous version. You can access all version docs starting from here, so pick version and browse new features guide
Gints Plivna
http://www.gplivna.eu...Leave a comment:
-
AS Scott:
GRANT <necessary privileges> ON <necessary objects> TO <new_user>;
For easier use as new user:
CREATE SYNONYM <synonym name> FOR SCOTT.<necessar y objects>;
See GRANT synatx and available object privileges here
Table 18-3 Object Privileges Available for Particular Objects
Create synonym syntax here
Gints...Leave a comment:
-
Mhmm that's not true:
...Code:SQL> ed Wrote file afiedt.buf 1 select rownum, owner, count(*) 2 from dba_source 3 where rownum <=10 4* group by rownum, owner SQL> / ROWNUM OWNER COUNT(*) ---------- ------------------------------ ---------- 4 SYS 1 6 SYSLeave a comment:
-
Leave a comment:
-
MM sometimes full scan of a table is better access path than index scan-table scan. Also there are obligatory indexes for Primary/Unique keys and very strongly advised indexes on Foreign keys :)
OK to the OP question is - are you really sure that using indexes is your problem?
Then you can try hint all_rows or the same optimizer_mode. It informs Oracle that you need get back all the data as fast as possible and not only the...Leave a comment:
-
Just remember that pragma autonomous_tran saction and truncate is very dangerous tool. In case your main transaction is rolled back, truncated table will remain truncated anyway.
Gints Plivna
http://www.gplivna.eu...Leave a comment:
-
It is not clear what you want. If you wnat just rows where commision is null the write as follows:
select ename,commissio n from emp e
where commission IS NULL
If you want to compare two values with a requirement that if both values are null, then accept them as equivalent, then you can use
nvl(value1, <not_existing_v alue>) = nvl(value2, <not_existing_v alue>)
This compare has danger...Leave a comment:
-
Go to http://www.trivadis.com/e/knowhow/pu...onen/index.asp and browse CBO - A Configuration Roadmap by Christian Antognini.
Gints Plivna
http://www.gplivna.euLeave a comment:
-
Go to Oracle documentation site pick version, browse book list and pick new features guide.
Gints Plivna
http://www.gplivna.eu...Leave a comment:
-
My blog entry has quite many links explaining B-tree, bitmap indexes by various Oracle experts.
Gints Plivna
http://www.gplivna.eu...Leave a comment:
-
Leave a comment:
-
Every DML (at least all SELECT, DELETE, UPDATE, MERGE and also INSERT with subqueries) statement is cursor. There are two types of cursors - implicit (managed automatically, for each above mentioned statement) and explicit where user explicitly writes OPEN, FETCH and CLOSE statements.
See more Managing Cursors in PL/SQL
Gints Plivna
http://www.gplivna.eu...Leave a comment:
-
Hmmm does Oracle 8i even Function Based indexes? Somehow I doubt it...
Gints PlivnaLeave a comment:
-
You really don't want to use qouted identifiers. They only make mess and headache further developing and maintaining the code, because you'll have always to use them.
Gints PlivnaLeave a comment:
-
See database object (identifier) naming rules here
And BTW DON'T use qouted identifiers without VER VERY strong necessity.
Excerpt:
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore...Leave a comment:
No activity results to display
Leave a comment: