Oracle Training To Help Make Your

Systems Better, Faster and Smarter

For more valuable Oracle tips and tricks just subscribe to our ezine and every month we'll send you some tips and tricks to help you make your Oracle systems better, faster and smarter.

Contents

• How to see what indexes have been created • The difference between PL/SQL cursors and views • What is the SQL to query a hierarchy? From: Manish - how to see all the indexes created on a particular table? how to view all the indexes in a tablespace? To see all the indexes created on a particular table you need to query all_indexes specifying the name of the table of interest as in this example: SELECT index_name FROM all_indexes WHERE table_name = 'EMP'; To see all the indexes in a particular tablespace you just have to query all_indexes specifying the name of the tablespace of interest as follows: SELECT index_name FROM all_indexes WHERE tablespace_name = 'USERS'; This will only show indexes on tables accessible to you. You could instead use the view user_indexes if you’re only interested in indexes owned by you. A dba would probably use dba_indexes which shows all the indexes in the database. --------- From: Bill Rowe - I read your articles on optimizing views and wondered what the differences between cursors in packages and views might be. I've been reading Feuerstein's O'Reilly books on PL/SQL and wonder if a cursor would work as well as a view, if the cursor were in a package specification. This is an interesting question. On one level, views and PL/SQL cursors are completely different: cursors are defined in PL/SQL and views are defined in the database, but a more fundamental level they are very similar - they are both pre-compiled queries so they could be used in similar ways. However using views would provide more flexibility, they can be combined into other queries which you could not do with cursors, they can also be used for database updates which again you could not do with an explicit cursor. The answer, therefore, is that a cursor would work as well as a view if you only need to use it for querying the database and you don't want to combine the view with other queries, but the database would have the overhead of switching from SQL to PL/SQL and back again. --------- From: Hrishikesh Srivatsa - I have a question regarding a SQL query. There is an employee table emp which has 3 fields namely EmpNo (Employee number), EmpName (Employee name) and MgrEmpNo (Employee's manager No). The CEO's Employee Manager No can be assumed to be null or a special value. A query has to be written to display the organization's hierarchy. The solution to this is to use the hierarchical query clause of the select statement - i.e. use the connect by prior keyword to indicate the relationship between the parent and child columns and the start with keywords to indicate the root of the tree. In this example, the query would be: SELECT EmpName FROM emp CONNECT BY PRIOR EmpNo = MgrEmpNo START WITH MgrEmpNo IS NULL; The Oracle resources page has recommendations for further reading as well as links to the Oracle Technology Network and other useful sites. See here for more Oracle tips and tricks. ---------  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity.  Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. © Smartsoft Computing Ltd, Bristol, England Need help? Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.   View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.