Subscribe to our ezine for more valuable Oracle tips and tricks
- just go to
www.asktheoracle.net/oracle-tips-signup.html
The resources section has recommendations for further reading to enhance your knowledge of Oracle and contains links to information on OTN and other places. More Oracle tips and tricks available here.
From: Heba el Leithy - I am studying computer science at the American University in Cairo. I am taking a database course and I am intreseted to know more about the new release of oracle 10g. I would like to know what is meant by grid techonology as well as what are the new feautures provided by this oracle 10g. My last question is that who are the users of 10g or who will be most benefited by 10g.
You can find out more about Oracle 10g, Heba, by going to this page: http://www.smart-soft.co.uk/Oracle/oracle10g-new-features.htm
---------
From: ela varasu - how to tune the sql & pl/sql query ?. What are all the tools available for tuning ?
Ela, We have a whole series of articles about tuning SQL on our website. Start from: http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm and follow the links.
---------
From: Hegde,
Santhosh - I am using a delete query using “NOT EXISTS” clause. The query
is:
delete from giant_prices where not
exists ( select orderable_product from product_master_tbl , giant_prices where
orderable_product=sku )
But
this will not delete even single row even there are rows in giant_prices table
having sku not exists in product_master_tbl.orderable_product. Please advice
me why this query is not working.
NOT EXISTS is used for correlated sub queries (see the Oracle and SQL tutorials on our web site - start from http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm and follow the links to the tutorials). For this to work the inner query has to be correlated with the outer query. In this case there is no correlation between the inner and outer queries, to make the query work you would have to correlate the inner query with the outer delete or change not exists to not in. For example:
DELETE FROM giant_prices WHERE NOT EXISTS ( SELECT orderable_product FROM product_master_tbl WHERE orderable_product=sku AND giant_prices.<primary_key> = product_master_tbl.<foreign_key> )
---------
From: sanu sanjel - how can I use the sql command for the greater than a row number like select * from purchase where rownum>5?
The short answer is no! Rownum is only a pseudo-column, it is generated as rows are returned from the query (starting at 1) so you can only select where rownum < limit. To do this you would have to do it programmatically using PL/SQL (or any other programming language), for example as follows:
DECLARE
i PLS_INTEGER := 0;
BEGIN
FOR sal_recs IN (SELECT * FROM purchase) LOOP
i := i + 1;
IF i >5 THEN <do whatever is needed> END IF;END LOOP;
END;
---------
From:Parthiban G Pillai- The following script is taking a 3 min to give the result when performed on 1000 entries. Can u pls help me out in optimising this query so as to give faster result. The table is not indexed. Query is:--
SELECT
nvl(a.FIELD7,'x') ,nvl(a.FIELD11,'x') ,nvl(a.FIELD14,'x')
,nvl(a.FIELD25,'x') ,n vl(a.FIELD30,'x') , nvl(a.FIELD32,'x')
,min(decode(d.FIELD11,'8',d.FIELD15,'999999')) tfmin15 , min(a.id) ID
from (select * from partial_cdr where leg_id = 1) a
, (select * from partial_cdr where leg_id = 3) b
, partial_cdr c
, (select * from partial_cdr where field11 = '8') d
where nvl(a.FIELD7,'x') = nvl(b.FIELD7,'x') AND nvl(a.FIELD11,'x') = nvl(b.FIELD11,'x')
AND nvl(a.FIELD14,'x') = nvl(b.FIELD14,'x') AND nvl(a.FIELD25,'x') = nvl(b.FIELD25,'x')
AND nvl(a.FIELD30,'x') = nvl(b.FIELD30,'x')
AND nvl(a.FIELD32,'x') = nvl(b.FIELD32,'x') and nvl(c.FIELD7,'x') = nvl(b.FIELD7,'x')
AND nvl(c.FIELD11,'x') = nvl(b.FIELD11,'x') AND nvl(c.FIELD14,'x') = nvl(b.FIELD14,'x')
AND nvl(c.FIELD25,'x') = nvl(b.FIELD25,'x')
AND nvl(c.FIELD30,'x') = nvl(b.FIELD30,'x') AND nvl(c.FIELD32,'x') = nvl(b.FIELD32,'x')
and nvl(d.FIELD7 (+) ,'x') = nvl(b.FIELD7,'x') AND nvl(d.FIELD14 (+),'x')
= nvl(b.FIELD14,'x') AND nvl(d.FIELD32 (+),'x') = nvl(b.FIELD32,'x')
group by nvl(a.FIELD7,'x'),nvl(a.FIELD11,'x'),nvl(a.FIELD14,'x'),nvl(a.FIELD25,'x'),
nvl(a.FIELD30,'x'),nvl(a.FIELD32,'x')
having max(b.field15) = count(distinct c.field15
The first thing I noticed about this statement was that the table partial_cdr was being joined to itself 3 times. This may be the only way of doing things - without knowing the application and the database structure I can't tell. However, I would try to combine the sub queries on partial_cdr so that it is only referenced once. Also I would run an explain plan for this statement to find out what the database is doing with it, so that I could play around with it to improve the performance. We have a whole series of articles on performance tuning available on our website, start from http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm and follow the links to the pperformance tuning articles.
---------
From saritha v: I would appreciate if u could answer these questions and provide me with links and resources where i could find info. 1. What are different types of cursors? 2. What cursor type do you use to retrieve multiple recordsets? 3. What are different types of index? 4. which type of index does the oracle create when we define primary key? 5. what are tradeoffs having index? 6. What is the diference between "NULL in C" and "NULL in Oracle?" 7. What are "HINTS"? What is "index covering" of a query?
That's a lot of questions and to answer them fully would require a whole book, and I would recommend that you take a look at Oracle Essentials. This provides an excellent overview of Oracle covering Oracle 9i, Oracle8i and Oracle8 and dicusses OLTP systems, data warehousing, distributed systems, installation, maintenance etc. and gets 5 stars as the average customer rating on Amazon.
Meanwhile here are some brief answers.
For more Oracle help, see our web site, where we have a welath of free articles and tutorials. Start from http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm and just follow the links.
---------------------------------------
Looking for more Oracle tips and tricks? If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.