Oracle Consulting and Training
For Better, Faster, Smarter
Oracle Training To Help Make Your Systems Better, Faster
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.
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
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
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
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their